Using a LiteDB portable database with your PowerShell project

I was working on a PowerShell project recently where I needed to reference a large number of items – more than I wanted to add as an array or hash table in script, or even an external file like an XML document. So I searched for a portable, standalone database that I could distribute with the script and happened across LiteDB, a lightweight, single-file NoSQL database. It seemed the ideal solution as there’s a .Net code library for it, and so far I’m pretty impressed.

In this blog I’ll cover some of the basics for working with a LiteDB database using PowerShell.

First you need to download the package from NuGet, then extract it. Grab the LiteDB.dll file from ..\lib\net45 folder and add it to your project.

Add the library to your PowerShell session:

Add-Type -Path "C:\Temp\LiteDB.dll"

Create a database (this can also be used to open the same database):

$database = [LiteDB.LiteDatabase]::new("C:\Temp\CountryCodeDatabase.db")

You can also open a database in read-only mode. This can be useful if the database will be placed in a location where the user may not have write access, such as ProgramFiles or ProgramData.

$database = [LiteDB.LiteDatabase]::new("Filename='C:\Temp\CountryCodeDatabase.db';ReadOnly=$true")

Create (or open) a collection (think table). In this case, I’m adding an automatically generated Id at the same time:

$Collection = $database.GetCollection("CountryCodes",[LiteDB.BsonAutoId]::Int64)

Now you can add a record (or document in NoSQL speak) using a BsonDocument:

$BsonDocument = [LiteDB.BsonDocument]::new()
$BsonDocument["Country"] = "United Kingdom"
$BsonDocument["Code"] = "44"
$null = $Collection.Insert($BsonDocument)

Or you can insert many records at once:

$Hash = @{
    "United States" = 1
    "Brazil" = 55
    "Netherlands" = 31
    "Lebanon" = 961
    "Iceland" = 354
}
Foreach ($Country in $Hash.Keys)
{
    $BsonDocument = [LiteDB.BsonDocument]::new()
    $BsonDocument["Country"] = $Country
    $BsonDocument["Code"] = $Hash["$Country"]
    $null = $Collection.Insert($BsonDocument)
}

To view the entries in the database file you can use LiteDB.Studio. Connect to the database file, double-click the collection and Run the select statement:

You can query the database directly in the UI:

You can also query in PowerShell, there are several ways to do that, for example:

$Collection.Find("Code = '44'")
$Collection.Query().Where("Code = '44'").ToDocuments()
$Collection.FindById(1)

All the above queries return the same result:

If you want to reference or retrieve a specific value from the result, it depends how you queried it, for example

$Result = $Collection.Find("Code = 354")
$Result.RawValue["Country"].RawValue

$result = $Collection.Query().Where("Code = 354")
$Result.First()["Country"].RawValue

$Result = $Collection.FindById(3)
$Result["Country"].RawValue

You could also view an entire collection, for example:

($Collection.FindAll()).ToList() | foreach {
    [pscustomobject]@{
        Id = $_["_id"].RawValue
        Country = $_["Country"].RawValue
        Code = $_["Code"].RawValue
    }
}

If you want to update a record with new values:

$Result = $Collection.FindById(1)
$Result["Code"] = "16"
$Collection.Update($result)

To delete a record:

$Collection.Delete(2)

View the changes in LiteDB Studio:

Finally, dispose the database object to release it and commit the records:

$database.Dispose()

Pretty simple really. LiteDB is a handy way to store a large number of items such as settings or properties that you need to distribute with a PowerShell script or project.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.