Using Powershell to Get Objects from Sqlite3

As discussed in SQLite3 and Powershell Redirection, we do not need to add any special Powershell tools to access sqlite3 if we utilize piping.

Versioning on my system:

SQLite version 3.24.0 2018-06-04 19:24:41
PSVersion 5.1.14393.2791

Note: I create an Alias for sqlite3:

PS A:\sqlite_to_csv> Set-Alias -Name sqlite3 -Value A:\sqlite3\sqlite3.exe


Creating an Object in Powershell is generally the best way to utilize data, and in my case I need an easy way to generate JSON files, which Powershell can be quite helpful at.

First, let's make a sample database:

PS A:\sqlite_to_csv> sqlite3 sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> twitter TEXT,
   ...> blog TEXT
   ...> );
sqlite> INSERT INTO sample
   ...> (name,twitter,blog)
   ...> VALUES
   ...> ('David Steimle','dbsteimle','rhymeswithtimely.blogspot.com');
sqlite> SELECT * FROM sample;
1|David Steimle|dbsteimle|rhymeswithtimely.blogspot.com


Now we have something to query.

First, we want to create the header, which will provide output in CSV mode with headers. For this I use a Here-String.

PS A:\sqlite_to_csv> $QuerySetup = @"
>> .mode csv
>> .headers on
>>
>> "@


Then we can define our query; we will do a simple SELECT only, for now, and combine it with our query setup.

PS A:\sqlite_to_csv> $Query = "SELECT * FROM sample;"
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT * FROM sample;


We then assign our result to an Object with pipes:

PS A:\sqlite_to_csv> $MyObject = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject | Format-List

id      : 1
name    : David Steimle
twitter : dbsteimle
blog    : rhymeswithtimely.blogspot.com


This Object is now usable in all the normal ways, such as turning it into JSON (I will output to a file, as that would be my end goal):

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com"
}


While in a basic sense this works for me, I do need the ability to have sub-objects and arrays in my JSON. This means additional tables. The obvious place to start in this example would be with Social Media. First, we will make a table of social media sites:

sqlite> CREATE TABLE socialmedium (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> url TEXT
   ...> );
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Twitter','https://twitter.com/');
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Instagram','https://instagram.com/');
sqlite> SELECT * FROM socialmedium;
id          name        url
----------  ----------  --------------------
1           Twitter     https://twitter.com/
2           Instagram   https://instagram.co


Then a lookup table with user details:

sqlite> CREATE TABLE socialuser (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> user INTEGER,
   ...> site INTEGER,
   ...> username TEXT
   ...> );
sqlite> INSERT INTO socialuser (user,site,username) VALUES
...> (1,1,'dbsteimle');
sqlite> SELECT * FROM socialuser;
id user site username
---------- ---------- ---------- ----------
1 1 1 dbsteimle

These tables use the PRIMARY KEY from the sample table as a reference point. To work with out current Object, we need to add a new Property, and assign it as an empty hash table:

PS A:\sqlite_to_csv> $MyObject | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
PS A:\sqlite_to_csv> $MyObject

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : {}


We need to use an INNER JOIN in this scenario, since we are looking up values in two lookup tables:

PS A:\sqlite_to_csv> $Query = @"
>> SELECT user,name,url,username
>> FROM socialuser
>> INNER JOIN socialmedium ON socialmedium.id=socialuser.site
>> WHERE user=$($MyObject.id);
>> "@
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=1;


Now, pipe through sqlite3:

PS A:\sqlite_to_csv> $MyObject.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject.SocialMedia

user name    url                  username
---- ----    ---                  --------
1    Twitter https://twitter.com/ dbsteimle


And Conversion to JSON:

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}


To best utilize this, we would want a function where we could utilize a known value as a parameter. We may not know that id 1 matches name 'David Steimle', but if we know the database, we might know to look for name="David Steimle"

function Get-MyObject {
param( [string]$Name )

$QuerySetup = @"
.mode csv
.headers on

"@

$Query = "SELECT * FROM sample WHERE name='$Name';"
$MySql = "$QuerySetup$Query"
$Temp = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}

$Query = @"
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=$($Temp.id);
"@
$MySql = "$QuerySetup$Query"
$Temp.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | ConvertTo-Json | Set-Content ".\$Name.json"

return $Temp
}

PS A:\sqlite_to_csv> Get-MyObject -Name "David Steimle"

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}

PS A:\sqlite_to_csv> Get-Content '.\David Steimle.json'
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}

Or, assign the result to a variable:

PS A:\sqlite_to_csv> $DavidSteimle = Get-MyObject -Name 'David Steimle'
PS A:\sqlite_to_csv> $DavidSteimle

id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}