New SQLite module for PowerShell
Freshly back from #PSConfEU, and while still completing the organiser’s duty – paying invoices, completing accounting operations, reviewing feedback and preparing for next edition – I managed to squeeze-in the release of synedgy.PSSQlite!
SQLite – single file database
You probably have used SQLite databases in many applications, maybe without knewing. They’re often small and fast single-file database that are embedded in an application, whether on a mobile, desktop or sometimes server side app.
Don’t underestimate its capabilities because of its size or simplicity, it’s very powerful and I often recommend using them in some scenarios like caching for PowerShell Universal data, database local to a module or piece of software.
Another benefit, since it’s simple in nature and contained in a single file: any operation team knows how to perform a backup and manage a restore!
SQLite Limitations
Sure, there are some trade-offs, and they’re not always a drawback. Take its simple type system for instance, it only knows about the following types:
- INTEGER
- REAL
- TEXT
- BLOB
- ANY
This enables to store data in a simple way that is enough for most use case. When a stronger type system is needed to enforce stricter data normalisation, you can use another database. One day I’ve got to find the time to talk about my synedgy.SqlQuery module for Microsoft SQL Databases…
Another thing worth noting is that writing to a SQLite database locks the whole DB, and that could be a problem when having multiple concurrent connections attempting to write to the same file. That said, SQLite is so fast, that it’s rarely an issue when writing just a few rows at a time.
When using SQLite for caching though, I recommend having a job periodically updating the cache (avoiding having two jobs running at once), then you can query all the data you want!
The PowerShell Module: synedgy.PSSqlite
Why reinventing the wheel? Has someone not created a module on the PowerShell Gallery already?
A few of them exists, yet there are two main reasons:
- Using Microsoft.Data.SQLite (instead of System.Data.SQLite).
- Enable the use of SQLite without writing SQL for simple operations (CRUD).
The first reason lies in the context of PowerShell Universal: When running a default installation of PowerShell Universal configured to use SQLite as its default database, the required libraries for SQLite are already loaded. This means updating the module during a PowerShell Universal deployment should not create a handle on the loaded module (unless loaded in a different runspace/environment, in which case it should be terminated before the deployment).
The second reason will be detailed in the following section…
SQL Persistence without knowing SQL
Knowing your ways around SQL is useful, and I don’t recommend ignoring this technology under the false pretext you’re a PowerShell author not a DBA. That said, I completly get that it’s yet another hurdle in the way of doing things right. For those without the skills or the time to invest learning SQL, I wanted to lower the bar of entry, to help you see the value…
I’ve put a minimalist example on how to use synedgy.PSSqlite in the following repo: https://github.com/SynEdgy/SqlitExample
The SqlitExample repository shows a module called MyModule and a README.md that explains how to give it a go, the first step being making that module available with Install-Module.
The principle of the synedgy.PSSqlite module is illustrated by the function Get-Car. The idea behind this is that no SQL is required, all is needed is to call Get-PSSQliteRow specifying the name of the table to look into, pass another couple of parameters we’ll explain later, and the SQL is generated dynamically, executed, and the results returned.
function Get-Car
{
[CmdletBinding()]
param
(
[Parameter()]
[string]
$Make,
[Parameter()]
[string]
$Model,
[Parameter()]
[string]
$Colour,
[Parameter()]
[int]
$Year
)
$getPSSqliteRowParams = @{
SqliteDBConfig = (Get-myModuleConfig)
TableName = 'Cars'
ClauseData = $PSBoundParameters
verbose = $Verbose.IsPresent -or $VerbosePreference -in @('Continue', 'Inquire')
}
Get-PSSqliteRow @getPSSqliteRowParams
}That function is passing the $PSBoundParameters (the parameters used when invoking the command) to the ClauseData parameter of Get-PSSqliteRow. So if you ran:Get-Car -Colour yellow -Verbose
The output will look like this:
As you can see the module generates the SQL with a WHERE clause that does a case insensitive validation for rows that have the column Colour match yellow. The SQL statement generated uses parameters to reduce the risks of SQL injections.
Similar functions exist for all the CRUD operations:
- Get-PSSqliteRow
- New-PSSqliteRow
- Set-PSSqliteRow
- Remove-PSSqliteRow
And they have been implemented in the MyModule.psm1 to manage the Car objects in database. Zero SQL needed.
How the magic works?
There’s no magic involved and it’s open source, so I won’t be revealing any obscure secret trick…
The synedgy.PSSqlite module expects to have the calling module (in this case myModule) to have the database definition yaml file in its config folder.
# ./config/myModule.PSSqliteConfig.yml
DatabasePath: $repository
DatabaseFile: test.db
version: 0.0.3
Schema:
Tables:
cars:
columns:
id:
type: INTEGER
PrimaryKey: true
indexed: true
make:
type: TEXT
model:
type: TEXT
colour:
type: TEXT
year:
type: INTEGERThe schema portion declares the tables with their name and columns. You can add constraints as well, but that’s another topic for another time.
Here the cars table has the column id which is of type INTEGER and is the PRIMARY KEY. In SQLite that infers it will be auto incremented (alias to ROWID). The tables also has the columns make, model and colour of type TEXT, and the column year of type INTEGER.
With that information, when we pass key/values to ClauseData for a SELECT (Get-) or a DELETE (Remove-) or to RowData for an INSERT (New-) or UPDATE (Set-), when the key matches the column name, we can build the SQL query.
But since we have all the Table details in the schema, can’t we generate the database? Absolutely!
Generating CREATE statements
Because we have most of the required information of the schema in the config file, synedgy.PSSQlite can generate the CREATE statements from a configuration, and initialise your SQLite database.
But that’s not something you’d often do, you’d usually want to initialise the DB for you with Initialize-PSSqliteDatabase.
As you can see in the screenshot above, we’ve created the database file and executed the schema onto the DB, and the table cars is now available in this database.
There’s a few other things going on, like this _metadata table we’ve added, but we’ll get into that later…
Invoking your own SQL statement
As you’ve seen with the previous command, it’s not because the module offers simple CRUD without using SQL that we forbid directly invoking it. The Invoke-PSSqliteQuery let you do just that, and it’s useful whenever you want to customise a bit more the SQL statement you want to run.
# Loading the configuration
$dbconfig = Get-PSSqliteDBConfig -Path .\myModule\config\myModule.PSSqliteConfig.yml
# Creating the connection
$conn = New-PSSqliteConnection -ConnectionString $dbconfig.ConnectionString
# Invoking a custom SQL with Parameters
Invoke-PSSqliteQuery -SqliteConnection $conn -CommandText 'SELECT * FROM cars WHERE colour LIKE @colour' -Parameters @{colour = 'Yel%'}
<# assuming we had populated the DB
id : 2
make : Toyota
model : Corolla
colour : Yellow
year : 2024
#>Using synedgy.PSSqlite in your own module
The goal of is to enable your module to have a consistent way to persist and retrieve objects to a database. When you design your module, there are a few tricks that can make your work easier:
- Storing the configuration object in a module-scoped variable
- Having a getter for the configuration object to get from that variable
- Managing your connection and using
-KeepAliveadequatly
The Configuration Object
You’ll see that the New, Get, Set, Remove-PSSqliteRow functions require the database config to be passed around. Instead of creating the object from the YAML file every time, you can do this:
function Get-myModuleConfig
{
[CmdletBinding()]
param
(
[Parameter(DontShow)]
[string]
$ConfigFile,
[Parameter()]
[switch]
$Force
)
if ($null -eq $script:MyModuleDBConfig -or $Force)
{
if ([string]::IsNullOrEmpty($ConfigFile))
{
# Retrieve configuration from the current's module config subdirectory
$ConfigFile = Get-PSSqliteDBConfigFile
}
else
{
Write-Verbose -Message ('Loading configuration from {0}' -f $ConfigFile)
}
$script:MyModuleDBConfig = Get-PSSqliteDBConfig -ConfigFile $ConfigFile
}
return $script:MyModuleDBConfig
}Everytime you need the configuration object, it will be retrieved from a cached location (your module scope variable). Unless it’s not set already, in which case it will retrieve it on the fly and cache it.
By default, the Get-PSSqliteDBConfigFile will look into the caller’s module config folder for a file that looks like
Now every time the configuration object is retrieved, you just have to call your getter function, and after an initial load fom Yaml, it will be read from memory.
SQL Connection Management
Everytime a SQL statement is executed through a connection, the module attempts to close the connection by default. This avoids leaving a handle opened on the database file, blocking file manipulation.
Each *-SqliteRow function accepts a -SqlConnection parameter, so you can pass an existing connection. If you also use the -KeepAlive parameter, the connection won’t be closed everytime to avoid the overhead.
# load the config objecty
$dbconfig = Get-PSSqliteDBConfig -Path .\myModule\config\myModule.PSSqliteConfig.yml
# create the connection object
$conn = New-PSSqliteConnection -ConnectionString $dbconfig.ConnectionString
# Insert into the table cars, keepingthe connection opened
$null = New-PSSqliteRow -SqliteDBConfig $dbconfig -SqliteConnection $conn -TableName 'cars' -RowData @{make = 'Renault'; model = 'R5 e-tech'; year = 2025; colour = 'yellow'} -KeepAlive
<# look at the $conn object
$conn
Handle : SQLitePCL.sqlite3
ConnectionString : Data Source=C:\dev\SqlitExample\test.db;
Database : main
DataSource : C:\dev\SqlitExample\test.db
DefaultTimeout : 30
ServerVersion : 3.46.1
State : Open
ConnectionTimeout : 15
CanCreateBatch : False
Site :
Container :
#>
# Get the rows, without keeping the connection opened
Get-PSSqliteRow -SqliteConnection $conn -TableName 'cars' -SqliteDBConfig $dbconfig | FT -a
id make model colour year
-- ---- ----- ------ ----
1 Toyota Corolla Blue 2020
2 Toyota Corolla Yellow 2024
3 Renault R5 e-tech yellow 2025
4 Renault R5 e-tech yellow 2025
5 Renault R5 e-tech yellow 2025
# Checking on the connection
$conn.state
# ClosedWhat next ?
While these features will probably get you most of the way in a lot of cases, there’s a few idea I’d like to implement:
- Support bulk insert so that we can quickly insert many rows in one transaction (or rollback)
- Export an existing DB Schema to a yaml file
- Have a JSON schema for the configuration file validation and intellisense
But I know the priority now is to write more tests and uncover edge cases that users might bump into.
If you encounter issues or can think of improvements, feel free to share them in issues on the GitHub repo: https://github.com/SynEdgy/synedgy.pssqlite
Discover more from SynEdgy
Subscribe to get the latest posts sent to your email.