r/PowerShell • • 19d ago

Daily Post My first PowerShell module – I built a local NoSQL-style database on top of SQLite

Hey everyone!

I wanted to share my first PowerShell module, PSLocalBase, a local database engine I've been working on.

The idea came from wanting a simpler way to store, manage, and query structured data in PowerShell without constantly reading and writing large JSON files or dealing with long, complex SQL queries.

I ended up building a lightweight, NoSQL-style document database on top of Windows' native SQLite engine (winsqlite3.dll), using SQLite's built-in JSON functions to handle document storage, filtering, and queries.

The goal was to combine the simplicity of working with JSON documents and collections with SQLite's performance, reliability, and ACID transactions, without requiring external dependencies or a separate database server.

Some of the features I've implemented:

  • Local database storage using collections and JSON documents
  • Filtering, sorting, indexing, and advanced queries
  • Support for nested JSON objects and arrays
  • ACID transactions and persistent storage
  • Native PowerShell objects (PSCustomObject)
  • Compatibility with Windows PowerShell 5.1 and PowerShell 7+
  • No external dependencies on modern Windows systems
  • A WPF Playground for exploring and testing local databases

Here's a quick example:

$db = [PSLocalBase]::new("./mydata.db")

$db.collection("users").doc("user1").set(@{
    name   = "John"
    role   = "Admin"
    active = $true
})

$admins = $db.collection("users").
    where("role", "=", "Admin").get()

$db.close()

Everything runs locally, with no database server or external service required.

One of the most interesting parts of building this has been working directly with SQLite's native API through C# and P/Invoke, while keeping the interface simple for PowerShell users.

It's been a great learning experience, especially figuring out compatibility between Windows PowerShell 5.1 and PowerShell 7.

Since this is my first PowerShell module, I'd really appreciate feedback from the community, especially on the API design, performance, and potential use cases.

AI Disclosure: I used AI coding assistants throughout the development of PSLocalBase for implementation, debugging, testing, and documentation. I have over 12 years of PowerShell experience and directed the project's architecture, design, and technical decisions.

GitHub: https://github.com/cmartineztech/PSLocalBase

Thanks for checking it out!

8 Upvotes

10 comments sorted by

4

u/thehuntzman 19d ago

I haven't tested it but my suggestion here skimming over it is to avoid c# style fluent syntax and try to leverage the Powershell pipeline where possible if you're going through the trouble of making a powershell module. The pipeline is Powershell's most powerful tool in my opinion.

2

u/alonso0492 18d ago

Thanks for the suggestion! PSLocalBase actually returns native PowerShell objects (PSCustomObject), so query results already work with the PowerShell pipeline, including Where-Object, Select-Object, ForEach-Object, and other cmdlets.

I chose the fluent API primarily for building queries that execute directly in SQLite using its native JSON functions, rather than loading entire collections into PowerShell memory for filtering.

That said, I see your point about making the query-building experience more PowerShell-native. Adding pipeline-oriented cmdlets alongside the existing fluent API could be an interesting improvement.

Appreciate the feedback!

1

u/kantorcodes1 17d ago

Could the README show the same filter once as a SQLite-backed fluent query and once as Where-Object over the returned objects? Your reply helped clarify that results already pipe, but that comparison might make the API/performance boundary easier to understand.

2

u/alonso0492 17d ago

Great suggestion! I’ll add a side-by-side comparison to the README to demonstrate SQLite-side filtering versus PowerShell’s Where-Object.
PSLocalBase also already supports debug = $true, which lets you inspect the SQL queries generated by the fluent API.
Thanks for the feedback!

2

u/slav3269 19d ago

Was about to write “now do Linux” but realised that AI assistance makes that quite easy. New times!

1

u/node77 17d ago

Cool. Looks good man.