InvokeQuery

A powershell module for querying databases.

Currently supported database types:

Sql Server
MySql
ODBC
PostgreSql
Sql Server CE
SqLite
Firebird
Oracle (requires separate driver download.)

It's trivial to add a new db type as long as it has an ADO.NET provider. Please submit a pull request or open an issue if you have one you'd like to add.

Why not use Invoke-Sqlcmd??

Invoke-Sqlcmd is just a wrapper for Sql Server's command-line executable, Sqlcmd.exe:

PS C:\windows\system32> Invoke-Sqlcmd

PS SQLSERVER:\>

Many admins are accustomed to using Sqlcmd; it's great at running DDL, however I think it's terrible at interacting with data. The Cmdlets in the InvokeQuery module are all built on ADO.NET, and therefore works great at querying and manipulating data. We also have functionality that's not possible using Sqlcmd, like scalar queries, returning the number of rows affected in a CUD operation, and ...oh yeah... transactions! It's also trivial to make the same codebase work with any DB type that has an ADO.NET provider.

How to install:

If you have powershell v5 you can install directly from the Powershell Gallery.

Install-Module -Name InvokeQuery

If you are on pre-v5 powershell, download the latest release and unzip to C:\Windows\System32\WindowsPowerShell\v1.0\Modules\InvokeQuery.

Usage:

All examples use Sql Server. Here's the definition of the table that I'm using in all examples:

use [test]
GO
CREATE TABLE [dbo].[table1](
  [pk] [uniqueidentifier] NOT NULL,
  [someint] [bigint] NOT NULL,
  [somestring] [nvarchar](500) NOT NULL,
  [somedatetime] [datetime2](7) NOT NULL,
  CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([pk] ASC)
)
GO
--Seed data
insert into table1 values (NEWID(), 123, 'blah blah blah', GETDATE())
insert into table1 values (NEWID(), 321, 'yo yo yo', GETDATE())
insert into table1 values (NEWID(), 213, 'hey hey hey', GETDATE())

Querying Data

Let's see how to do a simple query and then discuss:

PS C:\windows\system32> $db = "test"
PS C:\windows\system32> $sql = "select * from table1;"
PS C:\windows\system32> $results = Invoke-SqlServerQuery -Sql $sql -Database $db
PS C:\windows\system32> $results.Count
3
PS C:\windows\system32> $results | ft

pk                                   someint somestring     somedatetime       
--                                   ------- ----------     ------------       
d749777e-bdf0-43e1-901d-6874785e4c71     321 yo yo yo       5/1/2016 8:58:02 AM
63e58168-581d-4c28-9fb7-6b4b76c5c554     213 hey hey hey    5/1/2016 8:58:02 AM
0867b9cd-712e-4181-b739-b2f16cff520c     123 blah blah blah 5/1/2016 8:58:02 AM

As you can see, making queries is incredibly easy. Once you have the results, you can mainpulate it in powershell like any other object. Because we are connecting to a local database using windows authentication, we don't need to specify additional parameters; the Server property defaults to localhost, and the Credential property defaults to windows authentication. If you use the Verbose switch you can see all this happening:

PS C:\windows\system32> $results = $sql | Invoke-SqlServerQuery -Database $db -Verbose
VERBOSE: Server set to localhost
VERBOSE: Using the following connection string: Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI;
VERBOSE: Opening connection...
VERBOSE: Connection to database is open.
VERBOSE: Running query number 1
VERBOSE: Running the following query: select * from table1;
VERBOSE: Performing the operation "Run Query:`select * from table1;`" on target "Database server".
VERBOSE: Query returned 3 rows.
VERBOSE: Complete...
VERBOSE: Processed 1 queries in 11 milliseconds.

Not only did it state that it was using localhost, but it gives you the full connection string it will use to connect to the database. You can actually pass in a full connection string using the ConnectionString parameter if you need to use special db properties. You may also notice that this time we piped in the $sql variable instead of passing it as a parameter. We'll see how to make good use of that feature in a bit.

Scalar queries

Need a single value? No problem. Use the Scalar switch. It will return a single value.

PS C:\windows\system32> $db = "test"
PS C:\windows\system32> $sql = "select somestring from table1 where someint = 321;"
PS C:\windows\system32> $somestring = $sql | Invoke-SqlServerQuery -Database $db -Scalar
PS C:\windows\system32> $somestring
yo yo yo

CUD Operations

Performing Create/Update/Delete operations is really where things get fun, and InvokeQuery has some wonderful features to make your life easy. A simple example:

PS C:\windows\system32> $db = "test"
PS C:\windows\system32> $guid = New-Guid
PS C:\windows\system32> $sql = "insert into table1 values ('$guid', 432, 'powershell is awesome', getdate());"
PS C:\windows\system32> $rowcount = $sql | Invoke-SqlServerQuery -Database $db -CUD
PS C:\windows\system32> $rowcount
1

All we did was add the CUD switch. That tells the module to execute a "non-query" operation, i.e. this is a query that will not return data. When you use the CUD switch, it returns the number of rows created/updated/deleted.

Sidebar 1: this actually breaks a tenant that a cmdlet should behave consistently. i.e. if we are querying data, it returns data, but with the CUD switch, it's actually returning meta-data: e.g. row count. If this offends you, sorry. The alternative is to create a whole new cmdlet just for CUD operations (e.g. Invoke-SqlServerCUD) and I think that's going overboard.

Sidebar 2: If you run a SQL operation with CREATE, UPDATE, or DELETE, but do not include the CUD switch, you'll be prompted to confirm the operation. The only way to avoid the nag confirmation is to use the CUD switch.

Parameters

Okay, so that's all very simple, but we are using string concatenation to insert the guid into the sql statement. Let's look at a more problematic example:

PS C:\windows\system32> $guid = New-Guid
PS C:\windows\system32> $somestring = "It's great to see you!"
PS C:\windows\system32> $somestring
It's great to see you!
PS C:\windows\system32> $sql = "insert into table1 values ('$guid', 432, '$somestring', getdate());"
PS C:\windows\system32> $sql
insert into table1 values ('722dbaf2-5132-479e-80bb-62e88815e474', 432, 'It's great to see you!', getdate());

Do you see the problem with that insert statement? The single-quote from $somestring isn't escaped. If we try to execute this, Sql Server will throw an error. (Not to mention the possible sql injection vulnerabilities!) Generally speaking, using string concatenation to build a sql statement is considered very poor practice, even if you build your own escaping functionality, so let's not do it. Instead we can use the Parameters parameter to pass in a hash:

PS C:\windows\system32> $params = @{"pk"=$guid; "someint"=444; "somestring"=$somestring}
PS C:\windows\system32> $params

Name                           Value                                                             
----                           ----- 
somestring                     It's great to see you!
pk                             722dbaf2-5132-479e-80bb-62e88815e474
someint                        444

PS C:\windows\system32> $sql = "insert into table1 values (@pk, @someint ,@somestring, getdate());"
PS C:\windows\system32> $rowcount = $sql | Invoke-SqlServerQuery -Database $db -Parameters $params -CUD
PS C:\windows\system32> $rowcount
1
PS C:\windows\system32> $sql = "select somestring from table1 where pk = @pk;"
PS C:\windows\system32> $sql | Invoke-SqlServerQuery -Database $db -Parameters $params -Scalar
It's great to see you!

Transactions

There are two ways to utilize transactions with this module:

Explicitly creating one via Start-Transaction, and then using the UseTransaction switch on the cmdlet.
Piping in multiple queries and letting the cmdlet create a transaction for you.

Let's actually start with the latter: piping in multiple queries, using the Verbose switch, and see what happens.

PS C:\windows\system32> 1..4 | % {
    $num = $_
    $sql = "insert into table1 values (NEWID(), $num, 'testy mctestface $num',GETDATE())"
    $sql
} | Invoke-SqlServerQuery -Database $db -CUD -Verbose

VERBOSE: Server set to localhost
VERBOSE: Using the following connection string: Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI;
VERBOSE: Opening connection...
VERBOSE: Connection to database is open.
VERBOSE: Running query number 1
VERBOSE: Running the following query: insert into table1 values (NEWID(), 1, 'testy mctestface 1',GETDATE())
VERBOSE: Performing the operation "Run CUD Query:`insert into table1 values (NEWID(), 1, 'testy mctestface 1',GETDATE())`" on target "Database server"
.
VERBOSE: Starting transaction.
VERBOSE: CUD query complete. 1 rows affected.
1
VERBOSE: Running query number 2
VERBOSE: Running the following query: insert into table1 values (NEWID(), 2, 'testy mctestface 2',GETDATE())
VERBOSE: Performing the operation "Run CUD Query:`insert into table1 values (NEWID(), 2, 'testy mctestface 2',GETDATE())`" on target "Database server"
.
VERBOSE: CUD query complete. 1 rows affected.
1
VERBOSE: Running query number 3
VERBOSE: Running the following query: insert into table1 values (NEWID(), 3, 'testy mctestface 3',GETDATE())
VERBOSE: Performing the operation "Run CUD Query:`insert into table1 values (NEWID(), 3, 'testy mctestface 3',GETDATE())`" on target "Database server"
.
VERBOSE: CUD query complete. 1 rows affected.
1
VERBOSE: Running query number 4
VERBOSE: Running the following query: insert into table1 values (NEWID(), 4, 'testy mctestface 4',GETDATE())
VERBOSE: Performing the operation "Run CUD Query:`insert into table1 values

InvokeQueryPowershellModule

Install / Use

README