PowerShell and SQLite

Connecting PowerShell scripts to a database gives us a huge buster to process automation and processes/systems monitoring.
We can apply it to many different solutions as well as bypass, extend, or improve the functionality of other systems.

SQLite is perfect to store a script data and configuration (it is more flexible than hard-coded parameters or configuration .ini files

I also created a solution that monitors all configuration changes in different systems and creates an external audit trail functionality that can detect changes (and send a notification) applied directly to the database.
But let’s talk about efficient technics of working with databases in PowerShell.

SQLite is a very simple but most popular database engine in the world (based on the SQLite website, more than 1 trillion databases are in use). It is used in all smartphones and most operational systems. All data is stored in one file and managed by the C-language library. There are many extensions to SQLite that give some level of security (I will describe it in another post) and data compression, but the PowerShell library that I describe here has none of them.
You can create a database structure from the script, but in my opinion, the best tool to create a database, manually browse data, etc. is an open-source tool – DB Browser for SQLite (https://sqlitebrowser.org/), so I will focus on how to execute SQL statements from PowerShell on an SQLite database.

Module installation

SQLite module (PSSQLite) for PowerShell is available at PowerShell Gallery and you can easily install it with the following cmdlet:

Install-Module -Name PSSQLite -Force

Use example

First, need to load the module:
Import-Module PSSQLite
After that, execute any SQL statement (Select, Insert, Update, Delete, …) using:
$results = Invoke-SqliteQuery -Query "<query>" -DataSource "<sqlite_db_file_path>"

The result will be returned as an array of objects, but there is one exception. If the results return only one record, it is just an object, not an array. In this case, if you want to simply check the number of returned records $results.count, you receive an empty value instead of 1.
That is why I prefer to use the following method (always convert returned results to the array of PSObjects:
[pscustomobject[]]$results = Invoke-SqliteQuery -Query "<query>" -DataSource "<sqlite_db_file_path>"

The example of the source code can look like this:

Import-Module PSSQLite
[pscustomobject[]]$results = Invoke-SqliteQuery -Query "SELECT * FROM Customer" -DataSource "MyDB.sqlite3"
if ($results.count -gt 0) {
   Write-Host "Numer of selected records:" $results.count
} else {
   Write-Host "No records found"
}

The module supports also a few other cmdlets, like New-SQLiteConnection (creates SQLite connection), Invoke-SQLiteBulkCopy (allows load data faster), and Out-DataTable (creates data table for a PowerShell object).

SQlite database encryption

A default SQLite database file is not encrypted. But .Net library, used in the PSSQLite module gives us the possibility of database encryption.
The encryption algorithm is RC4, which is not the most advanced, but at least the database cannot be easily open without the password.
To encrypt the database, we have to use New-SQLiteConnection command and ChangePassword method:

$Connection = New-SQLiteConnection -DataSource "MyDB.sqlite3"
$Connection.ChangePassword("Password123")
$Connection.Close()

After encrypting the database can be open only with the password. The password has to be added as a part of the DataSource parameter:

Invoke-SqliteQuery -Query "SELECT * FROM Customer" -DataSource "MyDB.sqlite3;Password=Password123"

To remove password form the database the $null value has to be passed as a password:

$Connection = New-SQLiteConnection -DataSource "MyDB.sqlite3;Password=Password123"
$Connection.ChangePassword($null)
$Connection.Close()

I hope that this post will be helpful in SQLite access from PowerShell.
If you like it, please feel free to share.