PowerShell and SQLite

I wanted to use SQLite with PowerShell, but without needing to rely on another module. Just SQLite… and PowerShell. So here is how to do that…

Step 1: Download and Prepare SQLite

SQLite is available for the .Net framework, which PowerShell rides on. Great! But you need to make sure that the SQLite binaries you download match the .Net version available to you. To check, open PowerShell and run $PSVersionTable and check the CLRVersion, it should look like below:

Per above, I need the SQLite download for .Net 4.0.

If you download the wrong architecture, you will likely run into this error:

If you download SQLite for a version of .Net you don’t have and can’t run, you will likely get this one:

Once you have your version figured out, download the matching SQLite binaries from https://system.data.sqlite.org/

As of today, for me, that is the sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0.zip

Next, and very important, you must unblock the zip file. You can do this by right-clicking the zip file, and selecting “Unblock” and then clicking “Apply”.

If you do not do this, when you unzip the files, all the decompressed files will also be blocked. If you try and then hook into the SQLite dll from PowerShell you will get the following error:

Step 2: Adding and Using SQLite

Once you have the version figured out, files unblocked. Actually using SQLite is really easy.

First you must import the dll with the Add-Type command. You can just point it at the path to the DLL file. This is the line that will fail if you chose the wrong DLL or forgot to unblock the files.

Add-Type -Path "C:\sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0\System.Data.SQLite.dll"

Then you can create a connection string defining your database’s file path and connect to it.

$connectionString = "Data Source=C:\sqlite-netFx40-binary-bundle-x64-2010-1.0.119.0\MyTest.db;Version=3;"

$connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
$connection.Open() | Out-Null

If you get this error

Exception calling “Open” with “0” argument(s): “unable to open database file”

Then you likely do not have permissions to the file. SQLite will attempt to create a new db file if one does not exist. You may want to check if the DB has been initiated before using in further code.

From here, you should be good to go, but here are some rapid-fire scenarios to get you started.

Create a table

$Query = "CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, creationtime DATETIME DEFAULT CURRENT_TIMESTAMP);"

$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)
$command.ExecuteNonQuery() | Out-Null
$command.Dispose() | Out-Null

Remember to dispose when you are done.

Also, lets make an index

$Query = "CREATE INDEX name_idx ON mytable (name);"
$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)
$command.ExecuteNonQuery() | Out-Null
$command.Dispose() | Out-Null

Insert into a table

$Query = "INSERT INTO mytable (name) VALUES (@name);"
$Name = "John.Doe"

$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)
$command.Parameters.AddWithValue("@name", $Name) | Out-Null
$command.ExecuteNonQuery() | Out-Null
$command.Dispose() | Out-Null

Note the use of Parameters. This helps protect against SQL injection attacks. You should use them whenever you are adding variable data into your query.

Query the table

$Query = "SELECT * FROM mytable WHERE name = @name;"
$Name = "John.Doe"

$command = New-Object System.Data.SQLite.SQLiteCommand($Query, $connection)
$command.Parameters.AddWithValue("@name", $Name) | Out-Null
$reader = $command.ExecuteReader()

while ($reader.Read()) {
    Write-Host ""
    for ($i = 0; $i -lt $reader.FieldCount; $i++) {
        Write-Host "$($reader.GetName($I)) = $($reader.GetValue($I))"
    }
}
$reader.Close() | Out-Null
$reader.Dispose() | Out-Null
$command.Dispose() | Out-Null

These commands have been silent so far, but this time we are getting data out of the database. This code should return:

id = 1
name = John.Doe
creationtime = <somedate>

Note that we are disposing the reader as well as the command this time. Both are disposable, so it’s good practice to dispose them when done using them.

We are also using parameters again, since the name we are putting into our query is variable.

Finally, at the end of your script

Surprise, you should dispose your connection to the database file itself

$connection.Close()
$connection.Dispose()

Hopefully this is useful to someone. If you are looking for a more complete example, I made a simple generic caching module using this info. Check it out on Github.