SQLite in PowerShell: Migrating from CSV and JSON Workflows

Your PowerShell inventory script appends server details to a CSV every night. After a few months the file holds 10,000 rows, and you notice corrupted lines when two scheduled tasks write simultaneously. Filtering for servers checked in the last week means loading the entire file into memory and iterating with Where-Object. PowerShell SQLite replaces that fragile pattern with a single-file database that supports transactions, indexes, and SQL queries without installing a server.

SQLite stores everything in one .db file you can version-control, back up, or copy between machines. You get ACID transactions, foreign keys, and the ability to query historical data with a WHERE clause instead of scanning thousands of objects. For automation engineers managing state, logs, or inventory in scripts, it sits between CSV files and a full database server.

When CSV and JSON Files Break Down

CSV files work for one-off exports and small configuration data. They fail when multiple scripts write concurrently: Export-Csv -Append has no locking, so overlapping writes produce rows with missing columns or merged fields. A server inventory script that runs every hour on 50 machines will eventually corrupt a shared CSV on a file share. You find malformed rows weeks later when a report fails to parse them.

Large CSV files force you to load everything into memory. Import-Csv reads the entire file, then Where-Object filters it row by row. For 50,000 inventory records, a query like "show me servers updated in the last 24 hours" takes several seconds. Adding a new column means rewriting every row. There is no schema: a typo in a column name silently creates a new column instead of failing.

JSON files handle nested data better than CSV but suffer similar problems with concurrent writes. Appending to a JSON array requires reading the file, deserializing it, adding the new object, and writing the entire structure back. If two scripts do this simultaneously, one overwrites the other's changes. JSON also lacks indexing: finding a specific record by ID means deserializing the whole file and iterating. For append-heavy logs or time-series data, JSON becomes a performance bottleneck.

Use CSV for exporting reports or sharing data with Excel. Use JSON for configuration files that change infrequently and fit entirely in memory. When you need concurrent writes, historical queries, or datasets larger than a few thousand rows, SQLite provides the structure and performance you need without the overhead of a database server. The PowerShell 7.x and SQLite Fundamentals eBook covers advanced patterns once you outgrow basic file storage.

Installing the PSSQLite Module

PSSQLite is a community module that wraps SQLite's .NET libraries. It provides cmdlets for executing queries, inserting data, and managing transactions. The module works on both Windows PowerShell 5.1 and PowerShell 7+ on Windows and Linux, though the underlying libraries differ between versions.

Installing from the Gallery and Offline Options

Install the module from the PowerShell Gallery with Install-Module -Name PSSQLite -Scope CurrentUser. The -Scope CurrentUser flag avoids requiring administrator rights and installs to your profile directory. After installation, run Get-Command -Module PSSQLite to see the available cmdlets:

PS> Get-Command -Module PSSQLite

CommandType     Name                                Version    Source
-----------     ----                                -------    ------
Function        Invoke-SqliteBulkCopy               1.1.0      PSSQLite
Function        Invoke-SqliteQuery                  1.1.0      PSSQLite
Function        New-SqliteConnection                1.1.0      PSSQLite
Function        Out-DataTable                       1.1.0      PSSQLite

For air-gapped systems, use Save-Module -Name PSSQLite -Path C:\Temp\Modules on a machine with internet access, then copy the PSSQLite folder to the target machine's module path. Check your module paths with $env:PSModulePath -split ';'. On locked-down systems, you may need to unblock the downloaded files with Get-ChildItem -Recurse | Unblock-File and set the execution policy with Set-ExecutionPolicy RemoteSigned -Scope CurrentUser.

Verifying the SQLite DLL and PowerShell Core vs Windows PowerShell

PSSQLite bundles the SQLite libraries, so you do not need a separate SQLite installation. On Windows PowerShell 5.1, it uses System.Data.SQLite.dll. On PowerShell 7+, it uses Microsoft.Data.Sqlite.dll. The cmdlet syntax remains identical, but error messages differ slightly between the two. If a query fails with a message about "unable to open database file," check that the directory containing the .db file is writable by the account running the script.

The PSSQLite GitHub repository provides examples and tracks issues. The module has not seen frequent updates recently, but SQLite itself is stable and the cmdlets cover the common operations you need for automation workflows. For production scripts, test on both Windows PowerShell and PowerShell 7 if your environment uses both.

Creating Your First Database and Table

A SQLite database is a single file. You create it and a table in one step by running a CREATE TABLE statement against a file path. If the file does not exist, SQLite creates it. Use Invoke-SqliteQuery to execute SQL statements.

Choosing a File Path and Naming Conventions

Pick a path that your script account can write to and that will not be accidentally deleted. For a server inventory, use something like C:\Scripts\Data\ServerInventory.db. The directory must exist before you create the database, and both the file and its parent directory need write permissions. SQLite also creates a -wal and -shm file in the same directory when using write-ahead logging, so do not place the database in a read-only share.

$dbPath = "C:\Scripts\Data\ServerInventory.db"

# Create the directory if it doesn't exist
New-Item -ItemType Directory -Path (Split-Path $dbPath) -Force | Out-Null

Name the file with a .db or .sqlite extension. Avoid spaces in the path; if you must use them, wrap the path in quotes. Store the path in a variable at the top of your script so you can change it in one place.

Defining a Schema That Matches Your Current Data

If you are migrating from a CSV with columns like ServerName, IP, OS, and LastChecked, define a table with matching columns. SQLite supports TEXT, INTEGER, REAL, BLOB, and NUMERIC types. Declare a PRIMARY KEY to uniquely identify rows and speed up lookups.

$createTable = @"
CREATE TABLE IF NOT EXISTS Servers (
    ServerName TEXT PRIMARY KEY,
    IP TEXT NOT NULL,
    OS TEXT,
    LastChecked TEXT
)
"@

Invoke-SqliteQuery -DataSource $dbPath -Query $createTable

The IF NOT EXISTS clause prevents an error if the table already exists, making the script idempotent. SQLite is dynamically typed, so declaring TEXT does not strictly enforce it, but it helps with indexing and makes your intent clear. Store dates as TEXT in ISO 8601 format (YYYY-MM-DD HH:MM:SS) so SQLite's date functions work. The SQLite documentation explains type affinity in detail.

Check if a table exists by querying sqlite_master:

$tableCheck = @"
SELECT name FROM sqlite_master
WHERE type='table' AND name='Servers'
"@

$result = Invoke-SqliteQuery -DataSource $dbPath -Query $tableCheck
if ($result) {
    Write-Host "Table 'Servers' exists."
}

This query returns a row if the table exists, or nothing if it does not. Use it in migration scripts to verify the schema before inserting data.

Migrating Data from CSV to SQLite

Moving from CSV to SQLite means reading your existing file with Import-Csv, then inserting each row into the database. The naive approach loops over rows and executes one INSERT per row. For 10,000 rows, that takes minutes because each insert commits immediately. Wrapping inserts in a transaction reduces 10,000 disk writes to one, cutting the time from several minutes to a few seconds.

Reading the CSV and Batching Inserts

Import the CSV and insert rows inside a BEGIN TRANSACTION / COMMIT block. Use parameterized queries with -SqlParameters to handle special characters, quotes, and prevent SQL injection. Here is a complete migration script:

$dbPath = "C:\Scripts\Data\ServerInventory.db"
$csvPath = "C:\Scripts\Data\servers.csv"

# Read the CSV
$servers = Import-Csv -Path $csvPath

# Begin transaction
Invoke-SqliteQuery -DataSource $dbPath -Query "BEGIN TRANSACTION"

foreach ($server in $servers) {
    $insertQuery = @"
INSERT INTO Servers (ServerName, IP, OS, LastChecked)
VALUES (@ServerName, @IP, @OS, @LastChecked)
"@
    
    $params = @{
        ServerName  = $server.ServerName
        IP          = $server.IP
        OS          = $server.OS
        LastChecked = $server.LastChecked
    }
    
    Invoke-SqliteQuery -DataSource $dbPath -Query $insertQuery -SqlParameters $params
}

# Commit the transaction
Invoke-SqliteQuery -DataSource $dbPath -Query "COMMIT"

Without the transaction, inserting 10,000 rows takes 3-5 minutes. With the transaction, it takes 2-3 seconds. The transaction also ensures atomicity: if the script crashes halfway through, no partial data appears in the database.

Parameterized queries use @ParameterName placeholders in the SQL and a hashtable for -SqlParameters. This escapes values automatically, so a server name like SQL-SERVER'01 does not break the query or inject SQL. Never concatenate user data directly into SQL strings.

Handling Duplicates and Data Type Conversions

If you re-run the migration script, it fails when it tries to insert a row with a duplicate ServerName primary key. Use INSERT OR REPLACE to update existing rows or INSERT OR IGNORE to skip duplicates:

$insertQuery = @"
INSERT OR REPLACE INTO Servers (ServerName, IP, OS, LastChecked)
VALUES (@ServerName, @IP, @OS, @LastChecked)
"@

INSERT OR REPLACE deletes the old row and inserts the new one, so it resets any columns not in the VALUES clause. INSERT OR IGNORE silently skips the insert if the primary key exists, leaving the old row unchanged. Pick REPLACE when you want the latest data, IGNORE when you want to preserve the first occurrence.

Date formats in CSV files vary. If your CSV has dates like 1/15/2025 3:45 PM, convert them to ISO 8601 before inserting:

$lastChecked = [datetime]::ParseExact($server.LastChecked, "M/d/yyyy h:mm tt", $null)
$params['LastChecked'] = $lastChecked.ToString("yyyy-MM-dd HH:mm:ss")

Alternatively, store dates as Unix epoch integers (INTEGER column type) for easier math. Convert with ([datetime]$server.LastChecked - [datetime]'1970-01-01').TotalSeconds. SQLite's date functions like datetime() work with both ISO 8601 text and epoch integers.

Handle empty strings versus NULL explicitly. CSV imports treat blank cells as empty strings. If a column should be NULL when missing, check for empty strings and set the parameter to $null:

$params = @{
    ServerName  = $server.ServerName
    IP          = if ($server.IP) { $server.IP } else { $null }
    OS          = if ($server.OS) { $server.OS } else { $null }
    LastChecked = $lastChecked.ToString("yyyy-MM-dd HH:mm:ss")
}

This ensures NULL in the database rather than an empty string, which matters for queries with IS NULL or IS NOT NULL. Test your migration on a copy of the CSV first, then verify row counts match with SELECT COUNT(*) FROM Servers.

Replacing JSON State Files with SQLite

Many automation scripts use a JSON file to track state: the last time a job ran, which items have been processed, or counters for reporting. A typical pattern reads the file with Get-Content | ConvertFrom-Json, modifies properties, then writes it back with ConvertTo-Json | Set-Content. This breaks when two scripts run simultaneously because the second overwrites the first's changes. SQLite provides atomic updates and avoids the read-modify-write cycle.

Here is a JSON state file before migration:

{
    "LastRun": "2025-02-15T14:30:00",
    "ProcessedUsers": ["alice", "bob", "charlie"],
    "ErrorCount": 3
}

The script reads it, updates LastRun, adds a user to ProcessedUsers, and writes it back. If two instances run at once, one set of changes disappears.

Modeling Nested JSON as Relational Tables

For simple key-value state, create a table with key, value, and updated columns. Use INSERT OR REPLACE for upserts:

$createStateTable = @"
CREATE TABLE IF NOT EXISTS ScriptState (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated INTEGER
)
"@

Invoke-SqliteQuery -DataSource $dbPath -Query $createStateTable

# Update LastRun
$updateQuery = "INSERT OR REPLACE INTO ScriptState (key, value, updated) VALUES (@key, @value, @updated)"
$params = @{
    key     = "LastRun"
    value   = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    updated = [int](Get-Date -UFormat %s)
}
Invoke-SqliteQuery -DataSource $dbPath -Query $updateQuery -SqlParameters $params

For arrays like ProcessedUsers, flatten them into a separate table with one row per user. This lets you query individual users and avoids deserializing the entire array:

CREATE TABLE IF NOT EXISTS ProcessedUsers (
    username TEXT PRIMARY KEY,
    processed_at TEXT
)

Insert a user with INSERT OR IGNORE INTO ProcessedUsers (username, processed_at) VALUES (@username, @timestamp). Check if a user has been processed with SELECT 1 FROM ProcessedUsers WHERE username = @username. This scales better than searching a JSON array and supports queries like "show users processed in the last 24 hours."

For deeply nested JSON that does not map cleanly to tables, store it as a JSON blob in a TEXT column and use json_extract() to query fields (requires SQLite 3.38+):

CREATE TABLE IF NOT EXISTS Config (
    id INTEGER PRIMARY KEY,
    settings TEXT
)

-- Query a nested field
SELECT json_extract(settings, '$.database.timeout') FROM Config WHERE id = 1

This works when you need the flexibility of JSON but want transactional updates. For most automation state, flattening into relational tables provides better queryability. The PowerShell and JSON: From Beginner to Advanced eBook covers when to keep JSON versus when to normalize it.

Updating and Querying State Data

Reading state from SQLite is a single query instead of deserializing a file. Retrieve the last run time with:

$lastRun = Invoke-SqliteQuery -DataSource $dbPath -Query "SELECT value FROM ScriptState WHERE key = 'LastRun'"
if ($lastRun) {
    $lastRunTime = [datetime]$lastRun.value
    Write-Host "Last run: $lastRunTime"
}

Check if a user has been processed before running expensive operations:

$checkUser = "SELECT 1 FROM ProcessedUsers WHERE username = @username"
$result = Invoke-SqliteQuery -DataSource $dbPath -Query $checkUser -SqlParameters @{username = "alice"}
if (-not $result) {
    # Process the user
    # Then mark as processed
    $insertUser = "INSERT INTO ProcessedUsers (username, processed_at) VALUES (@username, @timestamp)"
    Invoke-SqliteQuery -DataSource $dbPath -Query $insertUser -SqlParameters @{
        username  = "alice"
        timestamp = (Get-Date).ToString("yyyy-MM-dd HH:mm:ss")
    }
}

This pattern avoids race conditions. If two scripts check the same user simultaneously, the second INSERT fails with a primary key violation, which you can catch with try/catch and treat as "already processed."

Querying SQLite in PowerShell Scripts

Once your data is in SQLite, you query it with SELECT statements instead of loading everything into memory and filtering with Where-Object. Invoke-SqliteQuery returns results as an array of PSCustomObject, so you can pipe them to other cmdlets or access properties directly.

Running SELECT Queries and Processing Results

Find all Windows servers with a parameterized LIKE query:

$query = "SELECT ServerName, IP, LastChecked FROM Servers WHERE OS LIKE @os ORDER BY LastChecked DESC"
$results = Invoke-SqliteQuery -DataSource $dbPath -Query $query -SqlParameters @{os = "Windows%"}

foreach ($server in $results) {
    Write-Host "$($server.ServerName) at $($server.IP) checked on $($server.LastChecked)"
}

The ORDER BY LastChecked DESC clause sorts newest first. Use LIMIT to return only the top N rows:

SELECT ServerName, LastChecked FROM Servers ORDER BY LastChecked DESC LIMIT 10

Aggregate functions count rows or find extremes without loading all data into memory. Count servers by OS:

$countQuery = "SELECT OS, COUNT(*) AS Total FROM Servers GROUP BY OS"
$counts = Invoke-SqliteQuery -DataSource $dbPath -Query $countQuery
$counts | Format-Table

Find servers not checked in the last 7 days using date arithmetic:

$staleServers = @"
SELECT ServerName, LastChecked
FROM Servers
WHERE datetime(LastChecked) < datetime('now', '-7 days')
ORDER BY LastChecked
"@

$results = Invoke-SqliteQuery -DataSource $dbPath -Query $staleServers

SQLite's datetime() function parses ISO 8601 strings and supports modifiers like '-7 days'. If you stored dates as Unix epoch integers, compare with WHERE LastChecked < strftime('%s', 'now', '-7 days').

Using Indexes to Speed Up Large Datasets

For tables with thousands of rows, queries that filter on non-primary-key columns perform full table scans. Add an index on columns used in WHERE clauses to speed them up:

CREATE INDEX IF NOT EXISTS idx_lastchecked ON Servers(LastChecked)

Check if a query uses an index with EXPLAIN QUERY PLAN:

$explainQuery = "EXPLAIN QUERY PLAN SELECT * FROM Servers WHERE LastChecked < '2025-01-01'"
Invoke-SqliteQuery -DataSource $dbPath -Query $explainQuery

Output shows SCAN TABLE Servers without an index or SEARCH TABLE Servers USING INDEX idx_lastchecked with one. For a table with 50,000 rows, the difference is milliseconds versus seconds. Index columns you filter or sort on frequently, but avoid indexing everything: each index slows down inserts and updates slightly and uses disk space.

Composite indexes cover multiple columns. If you often filter by OS and LastChecked together, create CREATE INDEX idx_os_checked ON Servers(OS, LastChecked). The column order matters: the index helps queries that filter on OS alone or OS and LastChecked, but not LastChecked alone. For most automation workflows, a single-column index on the timestamp column is sufficient.

Handling Concurrent Access and Locks

SQLite uses file-level locking: multiple scripts can read simultaneously, but only one can write at a time. When a second script tries to write while another holds the lock, it gets a "database is locked" error. This is not a failure of SQLite; it is the documented behavior for a single-file database. For automation workflows where scheduled tasks might overlap by a few seconds, you need retry logic and timeout settings.

SQLite Locking Modes and Write Serialization

The default locking mode blocks readers during a write transaction. If your inventory script runs every 5 minutes and takes 30 seconds to write, readers can query the database during the other 4 minutes and 30 seconds. Problems occur when two writers start simultaneously: the second waits for the lock, times out, and throws an error.

Write-ahead logging (WAL) mode improves concurrency by allowing readers to access the database while a writer commits. Enable it with a PRAGMA statement:

Invoke-SqliteQuery -DataSource $dbPath -Query "PRAGMA journal_mode=WAL"

This creates ServerInventory.db-wal and ServerInventory.db-shm files in the same directory. The -wal file holds uncommitted changes; the -shm file is shared memory for coordinating access. Do not delete these files while the database is in use. When you back up the database, copy all three files or use a method that handles WAL checkpoints. WAL mode persists in the database file, so you only need to set it once. For systems with many readers and occasional writers (a monitoring dashboard querying an inventory database updated hourly, for example) WAL removes most lock contention. The SQLite WAL documentation explains checkpointing and when to use it.

Retry Logic and Timeout Settings

If you cannot avoid concurrent writes, add retry logic. Set -QueryTimeout in seconds (default is 30) and wrap the query in a loop with Start-Sleep:

$maxRetries = 5
$retryDelay = 2

for ($i = 0; $i -lt $maxRetries; $i++) {
    try {
        Invoke-SqliteQuery -DataSource $dbPath -Query $insertQuery -SqlParameters $params -QueryTimeout 10
        break
    } catch {
        if ($_.Exception.Message -like "*database is locked*" -and $i -lt ($maxRetries - 1)) {
            Write-Warning "Database locked, retrying in $retryDelay seconds..."
            Start-Sleep -Seconds $retryDelay
        } else {
            throw
        }
    }
}

This retries up to 5 times with a 2-second delay between attempts. Increase -QueryTimeout if your writes take longer than 10 seconds. For scheduled tasks that might overlap, stagger their start times by a few minutes in Task Scheduler instead of relying solely on retries. A single script with sequential writes never encounters lock contention; locks only matter when multiple processes access the same database file simultaneously.

Production Patterns and Maintenance

SQLite databases grow over time but do not automatically reclaim space when you delete rows. A database that once held 50,000 rows still occupies the same disk space after you delete 40,000 of them. The VACUUM command rebuilds the database file and reclaims unused pages. Run it periodically, especially after bulk deletes or if you notice the file size growing without a corresponding increase in row count.

Vacuum, Integrity Check, and Backup

Check database integrity with PRAGMA integrity_check. This scans the entire database for corruption and returns "ok" if everything is valid:

$integrityCheck = Invoke-SqliteQuery -DataSource $dbPath -Query "PRAGMA integrity_check"
if ($integrityCheck.integrity_check -ne "ok") {
    Write-Error "Database integrity check failed: $($integrityCheck.integrity_check)"
}

Run this in a scheduled maintenance script, perhaps weekly. If it fails, restore from a backup immediately. Corruption is rare but can occur after a system crash or disk failure.

Reclaim space with VACUUM:

Invoke-SqliteQuery -DataSource $dbPath -Query "VACUUM"

VACUUM locks the database and rewrites it, so run it during low-activity periods. For a 500 MB database, it might take 10-20 seconds. If you delete thousands of rows nightly, vacuum weekly to keep the file size manageable. Monitor file size with (Get-Item $dbPath).Length / 1MB and alert if it exceeds a threshold.

Back up the database by copying the file, but copying a file mid-write can produce a corrupt backup. Use the SQLite command-line tool's .backup command for a consistent copy, or stop all writes before copying. If you have the sqlite3.exe binary, run:

sqlite3.exe $dbPath ".backup 'C:\Backups\ServerInventory_$(Get-Date -Format yyyyMMdd).db'"

Alternatively, export to SQL with PSSQLite, copy the file during a maintenance window, or use shadow copy on Windows. Never copy the .db file while a script holds a write lock; the backup may be unreadable. Test your backups by opening them with Invoke-SqliteQuery and running a query.

Rotating Databases and Retention Policies

For append-heavy logs or time-series data, rotate databases monthly to keep individual files small. At the start of each month, rename the current database and start a new one:

$dbPath = "C:\Scripts\Data\ServerInventory.db"
$archivePath = "C:\Scripts\Data\Archive\ServerInventory_$(Get-Date -Format yyyyMM).db"

if ((Get-Date).Day -eq 1 -and -not (Test-Path $archivePath)) {
    Move-Item -Path $dbPath -Destination $archivePath
    # The next query will create a new database file
}

This keeps the active database small and fast while preserving historical data in the archive. Query old data by opening the archived database file. Delete or compress archives older than your retention period:

Get-ChildItem "C:\Scripts\Data\Archive\*.db" | Where-Object {
    $_.LastWriteTime -lt (Get-Date).AddMonths(-12)
} | Remove-Item

For compliance or audit logs, compress old databases with Compress-Archive before deleting them. A sample monthly maintenance script might look like:

$dbPath = "C:\Scripts\Data\ServerInventory.db"

# Integrity check
$check = Invoke-SqliteQuery -DataSource $dbPath -Query "PRAGMA integrity_check"
if ($check.integrity_check -ne "ok") { throw "Integrity check failed" }

# Vacuum if file is large
$sizeMB = (Get-Item $dbPath).Length / 1MB
if ($sizeMB -gt 100) {
    Invoke-SqliteQuery -DataSource $dbPath -Query "VACUUM"
}

# Backup
$backupPath = "C:\Backups\ServerInventory_$(Get-Date -Format yyyyMMdd).db"
Copy-Item -Path $dbPath -Destination $backupPath

# Clean old backups
Get-ChildItem "C:\Backups\ServerInventory_*.db" | Where-Object {
    $_.LastWriteTime -lt (Get-Date).AddDays(-30)
} | Remove-Item

Run this as a scheduled task outside business hours. Monitor the script's success with exit codes or logging to a central system.

Migration Checklist and Next Steps

Before you migrate a production workflow from CSV or JSON to SQLite, verify these steps:

• Test the migration script on a copy of your data. Confirm row counts match with SELECT COUNT(*).
• Define a schema with appropriate types and a primary key. Add indexes on columns you filter or sort on frequently.
• Wrap bulk inserts in a transaction. Measure the time difference with and without BEGIN TRANSACTION / COMMIT.
• Enable WAL mode if you have concurrent readers: PRAGMA journal_mode=WAL.
• Add retry logic for writes if scheduled tasks might overlap. Set -QueryTimeout and use a loop with Start-Sleep.
• Schedule weekly integrity checks with PRAGMA integrity_check and monthly vacuums if you delete data regularly.
• Implement a backup strategy. Use sqlite3.exe .backup, copy the file during a maintenance window, or export to SQL.
• Document the database location, schema, and maintenance schedule for your team.

Once you have a working SQLite database for one script, migrate other workflows incrementally. Start with the most fragile CSV or JSON files: those that corrupt frequently or take longest to process. For advanced patterns like full-text search, JSON column queries, and integration with other PowerShell modules, the PowerShell 7.x and SQLite Fundamentals eBook provides detailed examples and production-ready scripts.

If you manage databases across multiple systems, consider centralizing inventory or log data in a shared SQLite file on a network path with WAL mode enabled, or replicate local databases to a central server for reporting. For workflows that outgrow a single file (hundreds of concurrent writers or terabytes of data) evaluate PostgreSQL or SQL Server, but most automation tasks stay well within SQLite's capabilities.

Frequently Asked Questions