SQLKata with SQLite minimal example (Powershell)

I have a sqlite database say c:\myDb.sqlite

I have figured out how to build a query to this db in SQLKata:

$query = New-Object SqlKata.Query("myTable")
$compiler = New-Object SqlKata.Compilers.SqliteCompiler
$query.Where("myColumn", "1")
$result = $compiler.Compile($query)

But I have no clue at all how to submit this to my Sqlite database.

Can anyone help?

Thanks,

Alex

Solution

Getting this to work from PowerShell is hampered by two difficulties:

Loading the assemblies related to NuGet packages in general and the Microsoft.Data.Sqlite NuGet package in particular often requires extra, non-obvious work in PowerShell.
PowerShell generally doesn't surface extension methods as such - e.g. .Get() on query instances - necessitating explicit calls to the static methods of [SqlKata.Execution.QueryExtensions] instead.

Specifically, using NuGet packages from PowerShell requires the following steps, which are neither convenient nor obvious:

Merely installing NuGet packages with Install-Package or trying to use them from the local cache created by .NET SDK projects in $HOME/.nuget/packages is often not enough, because any assemblies they depend on aren't then present in the same directory, which is what Add-Type requires.
They must also be unpacked in a platform-appropriate manner via an auxiliary .NET SDK project to a single target folder (per package or combined), as outlined in this answer.
Additionally, for the Microsoft.Data.Sqlite package, the platform-appropriate native library (e.g., win-x64\native\*.dll from the "runtimes" folder subtree of the .NET SDK project's publish folder) must be copied directly to the target folder in PowerShell (Core), but curiously not in Windows PowerShell, at least as of package version 5.0.9

The following sample code uses the Add-NuGetType helper function, available from this MIT-licensed Gist, which automates all of the steps above:

Note:

Assuming you have looked at the linked code to ensure that it is safe (which I can personally assure you of, but you should always check), you can install Add-NuGetType directly as follows (instructions for how to make the function available in future sessions or to convert it to a script will be displayed):
```
irm https://gist.github.com/mklement0/7436c9e4b2f73d7256498f959f0d5a7c/raw/Add-NuGetType.ps1 | iex
```
When first run, the function downloads and installs a private copy of the .NET SDK embedded inside the folder in which NuGet packages downloaded later are cached. This initial installation takes a while, and the -Verbose switch used below reports its progress.
Add-NuGetType is not meant for production use, but for experimentation with NuGet packages; run help Add-NuGetType for more information.

# Reference the relevant namespaces.
using namespace SqlKata
using namespace SqlKata.Compilers
using namespace SqlKata.Execution
using namespace Microsoft.Data.Sqlite

# Load the SqlKata and Sqlite asssemblies.
# See the comments above for how to install the Add-NuGetType function.
# Note: On first call, a private copy of the .NET SDK is downloaded
#       on demand, which takes a while.
Add-NuGetType -Verbose SqlKata, SqlKata.Execution, Microsoft.Data.Sqlite

# First, create sample database './sample.db' with table 'sample_table'
@'
create table sample_table (Name string, Age int); 
insert into sample_table (Name, Age) values ("JDoe", 42), ("JRoe", 43);
.save ./sample.db
'@ | sqlite3

# Create a [SqliteConnection] instance...
$connection = [SqliteConnection]::new("Data Source=$PWD/sample.db")
# ... and create a query factory for it.
$sqliteDb = [QueryFactory]::new($connection, [SqlServerCompiler]::new())

# Create and execute a sample query.
$query = $sqliteDb.Query("sample_table").Where("Name", "JRoe")
# Note the need to use the static methods of [SqlKata.Execution.QueryExtensions],
# because PowerShell doesn't make *extension methods* automatically available.
[SqlKata.Execution.QueryExtensions]::Get($query) # outputs [Dapper.SqlMapper+DapperRow] instances