The rising popularity of ChatGPT has led to the development of a PowerShell module called PowerShellAI, which allows users to access ChatGPT via API using scripts rather than a graphical user interface. Developed by Doug Finke, this module simplifies the process of interacting with ChatGPT, making it more convenient for those who prefer code over GUI. No more clicking through the web, just pure scripts 🙂
ChatGPT
ChatGPT is an artificial intelligence language model developed by OpenAI. It uses advanced natural language processing techniques to generate human-like responses to user inputs in text-based conversations. ChatGPT has gained popularity in recent years as a powerful tool for chatbots, virtual assistants, and customer service applications. It has the ability to learn from vast amounts of data and has been trained on a diverse range of topics, making it capable of producing accurate and relevant responses to a wide variety of queries.
OpenAI API keys
However, before we delve into the realm of AI magic, it's essential to prepare the necessary keys to the AI kingdom. Assuming you have signed up for a free account at OpenAi.com go to https://platform.openai.com and on the top right corner click your avatar. Then, select View API keys to obtain the necessary keys.
Or if you want a shortcut use this Account API Keys - OpenAI API
Next, to get a secret key use the button and save it in your vault, for example, Microsoft.PowerShell.SecretStore.
# Set the variable $SecretKey = 'sk-4vKV7e8dmhKzUy1DS7WWT8BlbkFJC3XgsXiPnJX00A9rGnKG4' # Register the Secret Vault Register-SecretVault -ModuleName Microsoft.PowerShell.SecretStore # Set the secret using a friendly name and a password that will be needed to get the secret Set-Secret -Name MyOpenAIKey -Secret $SecretKey -Vault Microsoft.PowerShell.SecretStore # Retrieve the secret - it will prompt for a password Get-Secret -Name MyOpenAIKey -Vault Microsoft.PowerShell.SecretStore
PowerShellAI module
Now, you can install it like any other module from the PowerShell gallery and see what's there.
# Install PowerShellAI module Install-Module -Name PowerShellAI # Review the available commands (Get-Command -Module PowerShellAI).Name <# ai ConvertFrom-GPTMarkdownTable copilot Disable-AIShortCutKey Enable-AIShortCutKey Get-ChatCompletion Get-ChatHistory Get-ChatInProgress Get-ChatSession Get-ChatSessionContent Get-ChatSessionPath Get-ChatTheme Get-DalleImage Get-GPT3Completion Get-OpenAIBaseRestURI Get-OpenAIChatCompletionUri Get-OpenAICompletionsURI Get-OpenAIEdit Get-OpenAIEditsURI Get-OpenAIImagesGenerationsURI Get-OpenAIKey Get-OpenAIModel Get-OpenAIModelsURI Get-OpenAIModeration Get-OpenAIModerationsURI Get-OpenAIUsage Get-OpenAIUser Import-ChatAssistantMessages Import-ChatMessages Import-ChatSession Import-ChatUserMessages Invoke-AIErrorHelper Invoke-AIExplain Invoke-ChatCompletion Invoke-OpenAIAPI New-Chat New-SpreadSheet Set-DalleImageAsWallpaper Set-OpenAIKey Stop-Chat Test-ChatInProgress #>
# set the API Key Set-OpenAIKey -Key (Get-Secret -Name MyOpenAIKey -Vault Microsoft.PowerShell.SecretStore)
and we are ready to go!
ChatGPT - let's SQL!
$Prompt = 'For SQL Server create a Customer table with the basic details' Get-GPT3Completion $Prompt
The results are surprising:
CREATE TABLE Customer ( CustomerID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(50) NOT NULL, Phone VARCHAR(20) NOT NULL, Address VARCHAR(100) NOT NULL );
Although I did not ask for names or types of columns it gave me something I can use.
Let's try something else. Generate another table schema and populate it with sample data. You may notice I saved the Response in the $Response variable.
$Prompt = 'Create a T-SQL for a book table with 10 metadata. Populate it with the five most popular comic books.' # Allowing more tokens to be returned, otherwise the response might be cut $Response = Get-GPT3Completion -Prompt $Prompt -Max_Tokens 2048 #See the response $Response
Meanwhile, we have some interesting and working T-SQL code, moreover, I did not ask for specific columns.
CREATE TABLE Book (
BookID INT IDENTITY(1,1) PRIMARY KEY,
Title VARCHAR(50) NOT NULL,
Author VARCHAR(50) NOT NULL,
Genre VARCHAR(50) NOT NULL,
Publisher VARCHAR(50) NOT NULL,
YearPublished INT NOT NULL,
Pages INT NOT NULL,
ISBN VARCHAR(50) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Rating INT NOT NULL
);
INSERT INTO Book (Title, Author, Genre, Publisher, YearPublished, Pages, ISBN, Price, Rating)
VALUES
('The Amazing Spider-Man', 'Stan Lee', 'Comic Book', 'Marvel Comics', 1962, 160, '9780785165419', 10.99, 8),
('Batman', 'Bob Kane', 'Comic Book', 'DC Comics', 1939, 192, '9781401247583', 12.99, 9),
('X-Men', 'Stan Lee', 'Comic Book', 'Marvel Comics', 1963, 176, '9780785165426', 11.99, 8),
('The Walking Dead', 'Robert Kirkman', 'Comic Book', 'Image Comics', 2003, 144, '9781582406725', 14.99, 9),
('Avengers', 'Stan Lee', 'Comic Book', 'Marvel Comics', 1963, 192, '9780785165433', 12.99, 8);Now it gets better, using dbatools to execute that code on your SQL Server.
# execute the T-SQL stored in the $Response variable on SQL server. Invoke-Dbaquery -SqlInstance $SqlInstance -Database $Database -Query $Response
In the SQL Server Management Studio Object Explorer we can see a new table:
Copilot
I saved the best for last. Notably, Doug created a function called copilot that can generate a runnable output from the prompt. As a result, the potential is quite significant. For instance, consider the following example:
# See how PowerShellAI copilot can create a code for you copilot -InputPrompt 'Create a T-SQL for a book table with 10 metadata. Populate it with the five most popular comic books. Save the output to a variable $Query, Then use Invoke-Dbaquery to run the $Query in the $Database database on $SqlInstance using $SqlCredential' -Max_Tokens 2048
Although copilot does exactly what we asked for, there's, even more, to explore when you scroll to the bottom.
╔═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║Q: Create a T-SQL for a book table with 10 metadata. Populate it with the five most popular comic books. Save the output to a variable $Query, Then use Invoke-Dbaquery to run the $Query in the $Database database on $SqlInstance using $SqlCredential ║
║═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════║
║1: $Query = @"
║
║2: CREATE TABLE Book (
║
║3: BookId INT IDENTITY(1,1) PRIMARY KEY,
║
║4: Title VARCHAR(255) NOT NULL,
║
║5: Author VARCHAR(255) NOT NULL,
║
║6: Publisher VARCHAR(255) NOT NULL,
║
║7: Genre VARCHAR(255) NOT NULL,
║
║8: YearPublished INT NOT NULL,
║
║9: Pages INT NOT NULL,
║
║10: Language VARCHAR(255) NOT NULL,
║
║11: ISBN VARCHAR(255) NOT NULL,
║
║12: CoverImage VARCHAR(255) NOT NULL
║
║13: );
║
║14: INSERT INTO Book (Title, Author, Publisher, Genre, YearPublished, Pages, Language, ISBN, CoverImage)
║
║15: VALUES
║
║16: ('The Amazing Spider-Man', 'Stan Lee', 'Marvel Comics', 'Superhero', 1962, 160, 'English', '9780785165417', 'https://images-na.ssl-images-amazon.com/images/I/51VX6VX6VXL._SX331_BO1,204,203,200_.jpg'), ║
║17: ('X-Men', 'Stan Lee', 'Marvel Comics', 'Superhero', 1963, 160, 'English', '9780785165418', 'https://images-na.ssl-images-amazon.com/images/I/51VX6VX6VXL._SX331_BO1,204,203,200_.jpg'), ║
║18: ('The Incredible Hulk', 'Stan Lee', 'Marvel Comics', 'Superhero', 1962, 160, 'English', '9780785165419', 'https://images-na.ssl-images-amazon.com/images/I/51VX6VX6VXL._SX331_BO1,204,203,200_.jpg'), ║
║19: ('Fantastic Four', 'Stan Lee', 'Marvel Comics', 'Superhero', 1961, 160, 'English', '9780785165420', 'https://images-na.ssl-images-amazon.com/images/I/51VX6VX6VXL._SX331_BO1,204,203,200_.jpg'), ║
║20: ('Daredevil', 'Stan Lee', 'Marvel Comics', 'Superhero', 1964, 160, 'English', '9780785165421', 'https://images-na.ssl-images-amazon.com/images/I/51VX6VX6VXL._SX331_BO1,204,203,200_.jpg'); ║
║21: "@
║
║22: Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $Query -SqlCredential $SqlCredential
║
╚═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
What would you like to do?
[Y] Yes [E] Explain [N] No [?] Help (default is "N"): yThe options to choose from, apart from [?], are:
Y - Yes, run the code
E - Yes, explain the code
N - No, do not run the code
When you select [Y] it will execute the code and here is the new object:
Conclusion
Furthermore, ChatGPT's potential is enormous, and with the use of the PowerShellAI module, we can seamlessly enjoy it from the terminal too. In fact, the possibilities are endless with the combined power of ChatGPT and PowerShellAI.
Thank you,
Mikey
