PSLocalBase

PSLocalBase is a lightweight, zero-dependency embedded document (NoSQL / Key-Value) database engine for PowerShell, inspired by the JavaScript library Localbase.

Under the hood, it operates as a native NoSQL Document Store built on top of Windows' pre-installed SQLite engine (winsqlite3.dll) using its built-in JSON functions. It incurs zero overhead by running in-memory P/Invoke directly in C, returns native PowerShell objects ([PSCustomObject]) with seamless dot-notation access, and provides full support for arrays and nested document structures.

📖 Full Documentation: For step-by-step walkthroughs, full code examples, and advanced usage, see the Full Documentation (_DOCS/).

Installation & Getting Started
- Option A: Install from PowerShell Gallery
- Option B: Run from Source Code
Zero Dependencies Guarantee
System Requirements & Compatibility
Interactive GUI Playground (WPF)
Quick Start
Core API Reference
Test Suite & Quality Verification
- Independent Security & Quality Audit
Architecture & Performance
Full Documentation
Technical Specification
Security Policy
Changelog

Interactive GUI Playground (WPF)

Explore, query, test, and inspect your database visually in a single self-contained WPF application:

# Launch the interactive GUI Playground (PowerShell 7+ or Windows PowerShell 5.1):
pwsh -ExecutionPolicy Bypass -File .\Playground\Playground.ps1
# or
powershell.exe -ExecutionPolicy Bypass -File .\Playground\Playground.ps1

Supports 1-click Dark Mode & Light Mode, live multi-collection DataGrid inspection, interactive code tutorial cards, and an integrated execution console. The console runs any PowerShell you type with your own permissions; it is a local developer tool, not a sandbox. See the Playground Documentation.

Note

Repository Companion Tool: The Playground GUI is maintained in this GitHub repository as a visual development tool. The PowerShell Gallery package (Install-Module PSLocalBase) distributes the pure, lightweight database engine without GUI overhead. When PSLocalBase is installed on your machine, Playground.ps1 runs directly out of the box.

Zero Dependencies Guarantee

No External Packages: No NuGet packages, no PowerShell Gallery modules (System.Data.SQLite, Microsoft.Data.Sqlite), and no third-party binary downloads required.
Native OS Engine: Interacts with the native Windows library C:\Windows\System32\winsqlite3.dll using dynamic C# and P/Invoke compiled at runtime via Add-Type.
Pure Unicode UTF-16: Guaranteed non-corrupt storage and retrieval of special characters, accents, nested quotes, and multi-byte emojis (🚀🔥).

System Requirements & Compatibility

Platform Support Matrix

Tier	Operating System / Environment	Testing & Support Status
Verified in CI & Local Suites	Windows 10 / 11 (x64)	Tested on GitHub Actions (`windows-latest`) and local workstations across PowerShell 7.x (Core) and Windows PowerShell 5.1 (Desktop).
Supported by Design	Windows Server 2019, 2022, 2025 (x64); WinPE x64	Supported by design via native System32 `winsqlite3.dll` and .NET Framework 4.5+ / .NET Core runtime.
Unverified / Architectural Bounds	ARM64 architectures, UNC / SMB shares	ARM64 is not currently verified in CI. Remote network shares (`\\server\share`) are explicitly blocked by design for WAL database persistence.

Windows only: "PowerShell 7+" means PowerShell 7 on Windows. Importing on Linux or macOS throws PlatformNotSupportedException.
CI Test Suite: Continuous integration validates the manifest, runs PSScriptAnalyzer, executes the 19-suite test runner (_TESTS/run-tests.ps1) on both PowerShell 7 and Windows PowerShell 5.1, and verifies the Playground in both editions on every push and pull request.
Engine Dependency: Relies strictly on Microsoft's native C:\Windows\System32\winsqlite3.dll, pre-installed on modern Windows installations.

Installation & Getting Started

Option A: Install from PowerShell Gallery (Recommended)

PSLocalBase is published as an official module on the PowerShell Gallery. Install and import it in PowerShell 7+ or Windows PowerShell 5.1:

# Install for current user (no admin rights required):
Install-Module -Name PSLocalBase -Scope CurrentUser

# Or install for all users (requires Administrator privileges):
Install-Module -Name PSLocalBase -Scope AllUsers

# Import the module:
Import-Module PSLocalBase

To update to future versions:

Update-Module -Name PSLocalBase

Option B: Run from Source Code (Development / Local Clone)

If you are developing, customizing the engine, running tests, or launching the visual Playground from the repository:

# 1. Clone the repository:
git clone https://github.com/cmartineztech/PSLocalBase.git
cd PSLocalBase

# 2. Import directly from the local manifest:
Import-Module .\PSLocalBase.psd1 -Force

# 3. (Optional) Run the complete 19-suite test runner:
.\_TESTS\run-tests.ps1

# 4. (Optional) Launch the visual GUI Playground:
pwsh -ExecutionPolicy Bypass -File .\Playground\Playground.ps1

Quick Start

# 1. Install & import from PowerShell Gallery (or from source):
Install-Module -Name PSLocalBase -Scope CurrentUser
Import-Module PSLocalBase
# Or from cloned source: Import-Module .\PSLocalBase.psd1 -Force

# 2. Initialize database (disk file or in-memory) with optional configuration:
$db = [PSLocalBase]::new(".\my_database.db", @{
    wal         = $true   # Enable Write-Ahead Logging (default: $true)
    cacheSizeMB = 32      # SQLite memory cache in MB (default: 16)
    busyTimeout = 5000    # Lock retry timeout in ms (default: 5000)
    depth       = 20      # Max JSON serialization depth (default: 20)
})

# 3. Insert documents
$db.collection("users").doc("usr-100").set(@{
    name   = "Carlos Martinez"
    role   = "Admin"
    active = $true
    skills = @("PowerShell", "SQLite", "Cloud")
})

# 4. Read back single document
$user = $db.collection("users").doc("usr-100").get()
Write-Host "Name: $($user.name) | Role: $($user.role) | Active: $($user.active)"

# 5. Query collection with filtering and ordering
$admins = $db.collection("users").where("role", "=", "Admin").orderBy("name", "asc").get()
$admins | Format-Table name, role, active

# 6. Clean close
$db.close()

💡 For comprehensive step-by-step guides, pagination, bulk operations, and deep merge updates, see the Full Documentation (_DOCS/).

Core API Reference

Initialization

Standard File-Based Database

$db = [PSLocalBase]::new("C:\Data\app.db")
# Or using the helper cmdlet, optionally with configuration:
$db = New-LocalBase -Path "C:\Data\app.db" -Options @{ busyTimeout = 10000; cacheSizeMB = 32 }

Automatically creates parent directories if they do not exist.
Configures Write-Ahead Logging (WAL) mode, synchronous = NORMAL, and a 5000ms busy_timeout.
Opens with PRAGMA trusted_schema = OFF, so triggers and views stored in a database file cannot call unsafe functions.

Option	Default	Meaning
`wal`	`$true`	Write-Ahead Logging. Accepts booleans or `true`/`false`/`1`/`0`/`yes`/`no`; anything else throws.
`synchronous`	`NORMAL` (WAL) / `FULL`	`EXTRA`, `FULL`, `NORMAL`, `OFF` or `0`-`3`.
`busyTimeout`	`5000`	Milliseconds to wait for a lock held by another connection.
`cacheSizeMB`	`16`	SQLite page cache size.
`depth`	`20`	Maximum nesting when writing documents (1-100, validated at construction).
`maxDocumentBytes`	`16MB`	Documents larger than this fail to read with an error naming only the key.
`maxReadDepth`	`100`	Documents nested deeper than this fail to read with an error naming only the key.
`debug`	`$false`	Prints SQL text (never bound values) and includes SQL in error messages.

A closed instance cannot be reopened; create a new one.

In-Memory Database

$db = [PSLocalBase]::new(":memory:")
# Or:
$db = New-LocalBase

Collections & Documents

`collection([string]$name)`

Navigates to a collection (SQLite table). Only alphanumeric characters and underscores (\A[a-zA-Z0-9_]+\z) are accepted. Navigation is lazy: referencing a collection does not create empty/ghost tables in SQLite until documents are written, preventing unintentional table creation on typos or read queries.

$db.collection("orders")

`create()` / `createCollection([string]$name)`

Explicitly provisions the collection table in SQLite immediately without requiring initial documents, matching the counterpart .delete() lifecycle method.

# Fluent chaining:
$db.collection("audit_logs").create()

# Direct method:
$db.createCollection("audit_logs")

`collectionExists([string]$name)`

Checks whether a collection table already exists in the database.

if (-not $db.collectionExists("users")) {
    $db.createCollection("users")
}

`doc([object]$keyOrCriteria)`

Targets a specific document by its key or by an object/hashtable that names it with a key, id, or _key property (other properties are rejected rather than guessed). Targeting is lazy: the collection table is created by the first write (set, add, upsert, update), never by .doc() or a read.

# By string key
$db.collection("orders").doc("ord-001")

# By hashtable / criteria object
$db.collection("orders").doc(@{ id = "ord-001" })

Queries are immutable: writing through a reference does not change it, so $d = $db.collection("orders").doc("ord-001"); $d.set(...); $d.get() still reads ord-001.

`getCollections()` / `listCollections()`

Retrieves an array of all collection names present in the database.

$collections = $db.getCollections()
# Returns: @("orders", "users", "products")

Writing Data (`set`, `add`, `update`)

`set([object]$data, [hashtable]$options)`

On a Document: Overwrites or creates the targeted document.

$db.collection("products").doc("p1").set(@{
    name  = "Mechanical Keyboard"
    price = 120.00
    stock = 45
})

On a Collection: Replaces every document in the collection atomically with an array of objects. replaceAll() is the explicit name; set(@(...)) is kept as an alias.
```
$items = @(
    @{ id = "1"; name = "Item One" },
    @{ id = "2"; name = "Item Two" }
)
$db.collection("products").replaceAll($items)
```
Replacing is rejected with InvalidOperationException on a query that has where(), orderBy(), limit() or skip(), because it would delete documents outside the filter.

DateTime and DateTimeOffset values are stored as ISO-8601 UTC strings (for example 2026-01-02T03:04:05.0000000Z) in both editions, and are converted the same way when used as filter or query parameters. Property names that are empty or differ only in case are rejected, since they cannot be read back.

`add([object]$data, [string]$customKey = "")`

Generates a unique 32-character GUID (or uses $customKey if supplied), saves the document, and returns the key.

$genKey = $db.collection("audit_logs").add(@{
    action    = "UserLogin"
    timestamp = (Get-Date).ToString("o")
})
Write-Host "Generated audit record key: $genKey"

`update([object]$partialData)`

Performs a deep, recursive merge into the existing document. Unspecified fields remain untouched.

$db.collection("users").doc("usr-100").update(@{
    profile = @{
        avatarUrl = "https://example.com/avatar.png"
    }
})

💡 Looking for upsert() or storage schema validation? See the Full Documentation (_DOCS/) for single-statement atomic upserts (.upsert()) and schema integrity checks (.validate()).

Atomic Array Mutations (`ArrayUnion`, `ArrayRemove`)

Modify arrays inside documents atomically and idempotently without having to read-modify-write the full document.

`[PSLocalBase]::ArrayUnion([object[]]$values)`

Adds elements to an array only if they are not already present (set union).

# Add tags/roles without duplicates using static method
$db.collection("users").doc("usr-100").update(@{
    roles = [PSLocalBase]::ArrayUnion("Editor", "Admin")
})

# Or using descriptor hashtable syntax
$db.collection("users").doc("usr-100").update(@{
    roles = @{ '$arrayUnion' = @("Editor", "Admin") }
})

`[PSLocalBase]::ArrayRemove([object[]]$values)`

Removes all occurrences of specified elements from an array.

# Remove roles using static method
$db.collection("users").doc("usr-100").update(@{
    roles = [PSLocalBase]::ArrayRemove("Guest", "Editor")
})

# Or using descriptor hashtable syntax
$db.collection("users").doc("usr-100").update(@{
    roles = @{ '$arrayRemove' = @("Guest") }
})

Secondary Expression Indexes (`createIndex`, `dropIndex`)

Speed up high-volume JSON lookups or enforce data integrity (like unique emails or usernames) using SQLite JSON1 expression indexes.

`createIndex([string]$field, [hashtable]$options)`

Creates a B-Tree index on a JSON field. Supports unique = $true and order = "asc"|"desc".

# Create unique index on email
$db.collection("users").createIndex("email", @{ unique = $true })

# Create descending index on timestamp
$db.collection("orders").createIndex("createdAt", @{ order = "desc" })

`dropIndex([string]$field)`

Drops the secondary index associated with the specified JSON field.

$db.collection("users").dropIndex("email")

Querying & Filtering (`get`, `where`, `orderBy`, `limit`, `query`)

`where([string]$field, [string]$operator, [object]$value)`

Filters documents using SQLite's native json_extract and json_each. Conditions can be chained together.

Supported Operators:

Equality: =, ==, -eq
Inequality: !=, <>, -ne
Comparisons: >, <, >=, <=, -gt, -lt, -ge, -le
Pattern Matching: LIKE, -like, NOT LIKE, -notlike

LIKE/NOT LIKE use SQLite patterns. -like/-notlike use PowerShell's case-insensitive wildcard matcher inside a registered SQLite function, preserving complete strings including embedded NUL and Unicode. Bracket classes remain explicitly unsupported. SQL NULL values retain SQL predicate behavior. After upgrading the native bridge, start a fresh PowerShell session.

Membership: IN, -in (a $null member also matches documents where the field is null or missing)
Array Membership: array-contains, -contains (matches if document array contains the scalar value)
Array Intersection: array-contains-any (matches if document array contains ANY of the given values)
Exclusion: not-in, -notin (matches documents where the field is present, not null, and not in the set; $null members are ignored)
Array length and JSON type: length, length>, ... and type (null, integer, real, number, text, array, object, true, false, boolean), also available as whereLength() and whereType()

# Boolean filter
$activeUsers = $db.collection("users").where("active", "=", $true).get()

# Number comparison
$expensive = $db.collection("products").where("price", ">=", 100).get()

# IN operator
$filtered = $db.collection("users").where("role", "IN", @("Admin", "Manager")).get()

# Array contains (checks if array property contains 'powershell')
$tagMatch = $db.collection("articles").where("tags", "array-contains", "powershell").get()

# Array contains any (checks if tags intersect with any given category)
$multiTag = $db.collection("articles").where("tags", "array-contains-any", @("devops", "cloud")).get()

# Not-In filter (null safe)
$nonFruit = $db.collection("products").where("category", "not-in", @("fruit", "dairy")).get()

# Multiple chained filters (AND)
$result = $db.collection("products") `
    .where("category", "=", "Electronics") `
    .where("price", "<", 500) `
    .get()

`orderBy([string]$field, [string]$direction = "asc")`

Sorts the query results by a JSON field. Accepts "asc" or "desc".

$sorted = $db.collection("products").orderBy("price", "desc").get()

`limit([int]$count)`

Restricts the maximum number of documents returned.

$top3 = $db.collection("products").orderBy("price", "desc").limit(3).get()

`skip([int]$offset)`

Skips the specified number of records for offset-based pagination.

# Page 2 (skip first 10, return next 10)
$page2 = $db.collection("products").orderBy("price", "asc").skip(10).limit(10).get()

`count()`

Returns the total count of documents in the collection or matching .where() filters directly in SQL without loading rows into memory.

# Total count in collection
$total = $db.collection("users").count()

# Filtered count
$activeAdmins = $db.collection("users").where("active", "=", $true).where("role", "=", "Admin").count()

`get([hashtable]$options)`

Executes the query and returns data deserialized as [PSCustomObject].

# Standard retrieval (array of PSCustomObjects)
$docs = $db.collection("users").get()

# With document keys exposed (Localbase syntax)
$itemsWithKeys = $db.collection("users").get(@{ keys = $true })
foreach ($item in $itemsWithKeys) {
    Write-Host "Key: $($item.key) | User: $($item.data.name)"
}

Cross-Collection Queries & Custom SQL (`query`)

Perform relational JOINs, aggregations (COUNT, SUM), or complex multi-table queries across independent collections:

$sql = @'
SELECT 
    json_extract(u.doc, '$.name') AS Customer,
    json_extract(u.doc, '$.department') AS Department,
    json_extract(o.doc, '$.item') AS ItemPurchased,
    CAST(json_extract(o.doc, '$.price') AS REAL) AS Amount
FROM orders o
INNER JOIN users u ON json_extract(o.doc, '$.userId') = u.key
ORDER BY Amount DESC;
'@

$report = $db.query($sql)
$report | Format-Table -AutoSize

# Parameterized queries with strongly-typed binding and parameter count parity
$activeUsers = $db.query("SELECT key, json_extract(doc, '$.role') AS role FROM users WHERE json_extract(doc, '$.age') >= ?;", @(30))

query() executes exactly one statement; text after the first ; other than whitespace or comments is rejected. It is a trusted-code escape hatch: never build its SQL from user input, pass values through the parameter array.

💡 Advanced Query Operators & Aggregations: For selective field projections (.select()), first-item retrieval (.first()), instant boolean verification (.exists()), unique values (.distinct()), numeric aggregations (.sum(), .avg(), .min(), .max()), and array/type queries (whereLength(), whereType()), see the Full Documentation (_DOCS/).

Relational Lookups (`populate`)

Embed referenced documents from other collections directly into the retrieved document using SQL LEFT JOIN and SQLite JSON nesting, with zero N+1 query penalty and full null-safety.

# Populate 'customerId' reference with document from 'users' collection into property 'customer'
$order = $db.collection("orders").doc("ord-001").populate("customerId", "users", "customer").get()
Write-Host "Order amount: $($order.amount) | Customer: $($order.customer.name)"

# Populate during collection queries with orderBy/limit
$orders = $db.collection("orders") `
    .populate("customerId", @{ from = "users"; as = "customer" }) `
    .orderBy("amount", "desc") `
    .limit(10) `
    .get()

Compound Queries & Sharding (`union`, `unionAll`, `collectionGroup`)

Combine query results across different collections or query sharded collections matching a pattern.

`.union($otherQuery)` and `.unionAll($otherQuery)`

Merges results of two queries directly in SQL. union deduplicates identical records; unionAll keeps all records for maximum throughput.

Modifiers before composition belong to their branch. Modifiers after union()/unionAll() apply once to the combined result. Both branch plans are captured independently; a branch's existing pagination or projection is not reused as global state.

$qNorth = $db.collection("users_north").where("active", "==", $true)
$qSouth = $db.collection("users_south").where("active", "==", $true)

# Deduplicated union of both regions
$allUsers = $qNorth.union($qSouth).get()

# Fast union preserving duplicates
$allUsersWithDups = $qNorth.unionAll($qSouth).get()

# Total count across union
$totalActive = $qNorth.union($qSouth).count()

`.collectionGroup([string]$pattern)`

Queries across multiple collections matching a wildcard pattern (e.g. date-sharded collections or category tables). Matching tables are resolved each time the query runs. Each returned document includes a ._collection property indicating its origin table, which can also be used in where(). A collection group cannot be combined with union()/unionAll().

# Search all monthly log collections for critical errors
$errors = $db.collectionGroup("logs_2026_*") `
    .where("level", "==", "ERROR") `
    .orderBy("timestamp", "desc") `
    .limit(25) `
    .get()

foreach ($err in $errors) {
    Write-Host "[$($err._collection)] $($err.timestamp): $($err.message)"
}

Deleting Data (`drop`, `delete`, `deleteDatabase`)

Deleting a Document

$db.collection("users").doc("usr-100").delete()

Deleting Filtered Documents

$db.collection("logs").where("level", "=", "DEBUG").delete()

Dropping vs Truncating a Collection Table

# Drop the collection table cleanly (removes table schema and secondary indexes from sqlite_master)
$db.collection("temporary_data").drop()

# Or truncate all records from a collection (DELETE FROM [col], preserving schema and indexes)
$db.collection("temporary_data").delete()

Note

Accidental data loss protection: Calling .delete() with pagination (.limit(), .skip()) is rejected with an InvalidOperationException to guarantee targeted deletions never unintentionally purge an entire collection.
Collection-level deleteDatabase() guard: .deleteDatabase() must be invoked on the root $db instance ($db.deleteDatabase()). Invoking it on a child collection ($col.deleteDatabase()) is explicitly rejected with InvalidOperationException to prevent Windows file-lock conflicts.

Deleting the Entire Database File

Closes all connections and deletes the database file, along with any -wal, -shm and -journal files. The path is taken from SQLite itself, not from a property that could have been modified:

# Must be executed on the root instance:
$db.deleteDatabase()

ACID Transactions

Wrap multi-document operations in high-performance atomic transactions:

$db.beginTransaction()
try {
    $db.collection("accounts").doc("acc-1").update(@{ balance = 400 })
    $db.collection("accounts").doc("acc-2").update(@{ balance = 600 })
    $db.commit()
} catch {
    $db.rollback()
    throw
}

beginTransaction() issues BEGIN IMMEDIATE, taking the write lock up front so concurrent writers in other processes wait up to busyTimeout instead of failing on lock upgrade. Use beginTransaction('deferred') or beginTransaction('exclusive') for the other SQLite modes. If COMMIT fails, InTransaction stays $true so you can retry or roll back.

Resource Cleanup & Disposal

`close()`

Releases native unmanaged handles immediately so disk files can be safely moved, backed up, or removed without file lock issues.

$db.close()

`Dispose()`

Implements System.IDisposable for deterministic cleanup.

The native connection is owned by a SafeHandle, so an instance that is never closed is still released when the garbage collector finalizes it. Closing explicitly remains the way to release file locks at a known point.

Production Best Practices & Crash Resilience

Guaranteed Cleanup with `try / finally`

When writing production scripts, background jobs, or automation pipelines, always encapsulate database operations in a try / finally block. The finally block is guaranteed by PowerShell to execute even if an unhandled error or exception aborts the script, ensuring disk file locks are released instantly:

Import-Module .\PSLocalBase.psd1

$db = [PSLocalBase]::new(".\production.db")
try {
    # Business logic here
    $db.collection("sales").add(@{
        amount = 1500
        date   = (Get-Date).ToString("o")
    })
    
    # Even if an unexpected error occurs here, finally will run!
    # throw "Unexpected network or processing failure"
} finally {
    # Guaranteed cleanup: ensures connection closure and unlocks files
    $db.close()
}

What Happens if a Script Crashes Without Calling `.close()`?

Data Integrity & Zero Corruption: PSLocalBase operates in SQLite's Write-Ahead Logging (WAL) mode with full ACID compliance and synchronous = NORMAL. If a PowerShell process or script crashes mid-execution, uncommitted transactions are cleanly discarded upon recovery. With synchronous = NORMAL, SQLite syncs the WAL file during checkpoints, providing optimal write throughput while completely protecting against process crashes (for systems requiring strict power-loss durability across sudden physical outages, synchronous = FULL is available via configuration options).
Process Exit Cleanup: When a PowerShell process terminates (e.g. running via powershell -File script.ps1, GitHub Actions, or Windows Scheduled Tasks), the Windows kernel automatically reclaims all unmanaged handles and closes all file descriptors.
Interactive Sessions: If a script fails inside an interactive session (VS Code or PowerShell console), the handle stays open while a variable still references the instance. Invoke $db.close() to release the file lock immediately; unreferenced instances are closed by the finalizer.

Multi-Client Reading Over Network Shares (SMB)

Why Direct SMB Access is Blocked: SQLite WAL mode relies on shared memory (.db-shm), which cannot coordinate across separate machines over SMB. Direct UNC and mapped paths are rejected to prevent file corruption.
Writer Snapshot Export (VACUUM INTO): To share data over SMB without downtime, the writer process exports a consistent snapshot via $db.query("VACUUM INTO 'snapshot.db';") and publishes the completed file to the share.
Client Consumption (Local Temp): Readers copy the published snapshot to a unique $env:TEMP path and query at full local NVMe speed with zero network lock contention. See Section 11 in Full Documentation.

Test Suite & Quality Verification

PSLocalBase includes an automated test runner, _TESTS/run-tests.ps1, that executes 19 suites, each in its own process: 83 core acceptance cases (TC-01 to TC-83), the historical regression suites, the 2.0 hardening regressions, point-pagination and bridge-upgrade regressions, a seeded randomized comparison against a PowerShell model, native resource stress checks, and a multi-process WAL test in which Windows PowerShell 5.1 and PowerShell 7 write to the same file concurrently. All suites pass on both editions.

Running the Tests

# In Windows PowerShell 5.1
powershell -ExecutionPolicy Bypass -File .\_TESTS\run-tests.ps1

# In PowerShell 7+ (Core)
pwsh -ExecutionPolicy Bypass -File .\_TESTS\run-tests.ps1

For the complete breakdown of all 83 verified acceptance criteria, test categories, and validations, see the Test Suite Documentation.

Independent Security & Quality Audit (2.0.0 Hardened Release)

PSLocalBase 2.0.0 on branch hardening/v2 underwent an exhaustive audit across security, data integrity, concurrency, memory safety, and documentation, achieving a 95.5/100 (Production Ready) rating with 19 out of 19 automated suites passing on both PowerShell Core 7 and Windows PowerShell 5.1. For full details and defect resolution matrices, see the Final Verification Report.

Category	Score	Status
Security	98/100	✅ Exceptional (0 critical/high vulnerabilities, pre-compilation compound SQL isolation)
Code Quality	93/100	✅ Very High (Zero PSScriptAnalyzer warnings, SafeHandle lifecycle)
Functionality	96/100	✅ High (Full NoSQL API, terminal parity, atomic array mutations)
Architecture	92/100	✅ Solid (Zero dependencies, direct winsqlite3.dll integration)
Documentation	96/100	✅ Excellent (17-vector security policy, full specifications)
Test Coverage	98/100	✅ Exceptional (19 suites, 280+ tests, multi-process WAL concurrency)
Overall Score	95.5/100	✅ Production Ready (Release Candidate 2.0.0)

Architecture & Performance

flowchart TD
    Host["PowerShell Host<br/>(Windows PowerShell 5.1 / PowerShell 7+)"]
    API["PSLocalBase Class<br/>(Fluent NoSQL API: collection, doc, set, add, update, get)"]
    Interop["WinSqlite3 Interop Layer<br/>(In-Memory C# P/Invoke with UTF-16 Bindings)"]
    Native["C:\Windows\System32\winsqlite3.dll<br/>(Zero-Dependency Native Windows SQLite)"]
    DB[("database.db<br/>(Primary Key-Value JSON Store)")]
    WAL[("database.db-wal<br/>(Write-Ahead Logging / Concurrency)")]

    Host --> API
    API --> Interop
    Interop --> Native
    Native --> DB
    Native --> WAL

Native SQLite 3 & NoSQL Architecture Highlights

1. Embedded NoSQL Document Model

Dedicated Collection Tables: Every collection is an isolated table structured as (key TEXT PRIMARY KEY, doc TEXT).
Zero-Schema Overhead: Documents are stored as standard JSON payloads, allowing arbitrary schema evolution without migrations.
Instant Key Access: Single document queries (.doc("id").get()) leverage SQLite's B-Tree primary key index for $O(1)$ / $O(\log N)$ sub-millisecond retrieval.

2. SQLite JSON1 Engine (`json_extract`)

Pushdown Query Execution: Filters created with .where() evaluate properties inside the SQLite engine via json_extract(doc, '$.field'), completely bypassing the overhead of deserializing unneeded rows into PowerShell memory.
Deep Path Resolution: Full support for nested properties (e.g. .where("billing.address.city", "=", "Miami") translates to $.billing.address.city).
Direct JSON Sorting: .orderBy("price", "desc") executes in native C via ORDER BY json_extract(...).
Type-Aware Evaluations: Translates PowerShell booleans ($true/$false) to SQLite JSON integer representations (1/0), supports SQL IN array operators, string LIKE wildcards, and NULL checks.

3. Native Concurrency & PRAGMA Tuning

PRAGMA journal_mode = WAL; Activates Write-Ahead Logging. Readers never block writers, and writers never block readers across processes.
PRAGMA synchronous = NORMAL; Optimizes I/O throughput by eliminating redundant disk sync flushes while maintaining complete ACID crash-safety.
PRAGMA busy_timeout = 5000; Automatically waits up to 5,000 milliseconds when multiple threads or processes write concurrently, avoiding SQLITE_BUSY errors.
PRAGMA cache_size = -16000; Reserves a 16 MB in-memory page cache for high-throughput query execution.

4. Low-Level C UTF-16 Interoperability

Interacts directly with C:\Windows\System32\winsqlite3.dll via UTF-16 APIs (sqlite3_open16, sqlite3_prepare16_v2, sqlite3_bind_text16, sqlite3_column_text16, sqlite3_errmsg16).
100% immune to ANSI/UTF-8 conversion data corruption or multi-byte emoji truncation.
Fully parameterized SQL queries prevent SQL injection attacks.

Author

Created and maintained by Carlos Martinez (@cmartineztech).

License

This project is licensed under the MIT License - see the LICENSE file for details.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
Playground		Playground
_DOCS		_DOCS
_TESTS		_TESTS
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
InitWinSqlite3Native.ps1		InitWinSqlite3Native.ps1
LICENSE		LICENSE
PSLocalBase.psd1		PSLocalBase.psd1
PSLocalBase.psm1		PSLocalBase.psm1
README.md		README.md
SECURITY.md		SECURITY.md

Latest commit

History

Folders and files

Repository files navigation

PSLocalBase

Table of Contents

Interactive GUI Playground (WPF)

Zero Dependencies Guarantee

System Requirements & Compatibility

Platform Support Matrix

Installation & Getting Started

Option A: Install from PowerShell Gallery (Recommended)

Option B: Run from Source Code (Development / Local Clone)

Quick Start

Core API Reference

Initialization

Standard File-Based Database

In-Memory Database

Collections & Documents

collection([string]$name)

create() / createCollection([string]$name)

collectionExists([string]$name)

doc([object]$keyOrCriteria)

getCollections() / listCollections()

Writing Data (set, add, update)

set([object]$data, [hashtable]$options)

add([object]$data, [string]$customKey = "")

update([object]$partialData)

Atomic Array Mutations (ArrayUnion, ArrayRemove)

[PSLocalBase]::ArrayUnion([object[]]$values)

[PSLocalBase]::ArrayRemove([object[]]$values)

Secondary Expression Indexes (createIndex, dropIndex)

createIndex([string]$field, [hashtable]$options)

dropIndex([string]$field)

Querying & Filtering (get, where, orderBy, limit, query)

where([string]$field, [string]$operator, [object]$value)

orderBy([string]$field, [string]$direction = "asc")

limit([int]$count)

skip([int]$offset)

count()

get([hashtable]$options)

Cross-Collection Queries & Custom SQL (query)

Relational Lookups (populate)

Compound Queries & Sharding (union, unionAll, collectionGroup)

.union($otherQuery) and .unionAll($otherQuery)

.collectionGroup([string]$pattern)

Deleting Data (drop, delete, deleteDatabase)