How To Query a MySQL Database with PowerShell

Recently, I had a project where I needed to query a MySQL database with PowerShell to perform an automated task to pull data and then trigger from the returned rows if any from MySQL. I had found various scripts that had been posted on the Microsoft Script center and various other resources, however, ran into issues with each script in one form or fashion. I stumbled onto a PowerShell module that was mention in a blog post/comment that turned out to be the solutiont that worked very well for my use case. Let’s take a look at how to query a MySQL database with PowerShell and see how to easily do this with a freely available PowerShell module.

PowerShell Module for Querying MySQL

The module found that easily allows querying MySQL with PowerShell is the SimplySQL module. You can read the official information on SimplySQL on the PowerShell Gallery page found here.

A few things that I like about the SimplySQL module is that it provides functionality to connect not only to MySQL but to Microsoft SQL Server so you can use the tool for multiple use cases.

Also, it is actively being developed. Many of the other tools/scripts that I found, had not been updated in quite some time, even years. Currently, at the time of this writing, SimplySQL is at version 1.6.2 and was updated around 4 months ago.

Installing SimplySQL for MySQL Queries

SimplySQL is easily installed as it is a module that can be pulled down and installed right from within PowerShell itself. To install SimplySQL, run the following command to install the module:

1. Install-Module -Name SimplySql

After installing the module, I ran a quick Get-Module command to get a quick status on the SimplySQL module:

1. Get-Module SimplySQL

Verifying the installation of SimplySQL in Windows 10 PowerShell

After installing the module, we can get started taking a look at how to query MySQL with PowerShell.

How To Query a MySQL Database with PowerShell

With the SimplySQL module installed, let’s see how we can start querying a MySQL DB. A great resource that is built right into PowerShell is the Get-Help module that allows getting help on a specific PowerShell module.

For SimplySQL MySQL queries with PowerShell, you have the Open-MySQLConnection and Invoke-SQLQuery cmdlet that can be used to connect to your MySQL server, pass along credentials, and also run a specified query for the cmdlet.

Below is the return from the Get-Help on the Open-MySQLConnection cmdlet showing the various parameters that can be passed along including:

• Server
• Database
• Credential
• ConnectionName
• CommandTimeout
• Port
• SSLMode

Opening a connection to MySQL with PowerShell with the Open-MySQLConnection cmdlet

Using the Invoke-SqlQuery you can pass along the MySQL query to the connection you open with the Open-MySQLConnection cmdlet.

Taking a look at Get-Help for Invoke-SQLQuery PowerShell cmdlet

Below are examples of running the Invoke-SqlQuery cmdlet. One thing to note is that you can’t point the cmdlet to a MySQL query file, or at least I didn’t see a way to do this. You can only embed the query inside the cmdlet. It would be nice to be able to point the cmdlet to a query file, especially those that are more complex.

Running MySQL queries using the Invoke-SQLQuery cmdlet in PowerShell

Just as an easy demonstration of how easy it is to query a folder, I just pointed a query to one of the tables in a WordPress DB I had on a test server and was able to easily pull values from the WordPress DB instance.

Running a query against a WordPress database using SimplySQL to query MySQL using PowerShellpng

Use Cases for Querying MySQL with PowerShell

The sky is the limit for various use cases of working with MySQL with PowerShell queries, however, for me, I had the need to run a scheduled task and query a MySQL table for a return and shoot out email alerts based on the return. This was easily doable by using SimplySQL to return values if there were any and then checking with PowerShell to see if there was a return. If the return was not blank or NULL, I sent out the email alert using a simple send-mailmessage cmdlet call.

Wrapping Up

Using Powershell to do anything is a powerful way to programmatically query and automate solutions. It is great to see there are very simply and easy modules that exist to query MySQL as well as SQL Server to make the process of reading database information from MySQL extremely easy. Be sure to check out the SimplySQL module for easily querying and working with MySQL DBs if you have the need to.