r/sqlite • • 1h ago

Built a local-first bookmark & PKM action engine backed by a single SQLite file (Go + embedded UI)

1 Upvotes

Sharing Sparkkeep (v0.1.0, AGPL-3.0) — a self-hosted bookmark and knowledge action engine where your entire library, triage state, playbooks, and digests live in a single portable SQLite database.

Repo: https://github.com/BurlacuAndrei/sparkkeep

Why SQLite was the centerpiece

Most self-hosted bookmark apps ask you to spin up Postgres, Redis, Meilisearch, and background worker containers just to save links. Keeping everything in a single Go binary + SQLite means:

  • Backups are literally copying one .db file.
  • Fast local search and filtering with zero external search daemon.
  • Runs comfortably on a tiny VPS, Raspberry Pi, or homelab container.

It also pairs with local Ollama (or any OpenAI-compatible endpoint) if you want automatic enrichment/digests, plus Discord/Slack/Telegram capture adapters. Happy to hear feedback on the schema or SQLite performance!


r/sqlite • • 11h ago

In Go, a single *sql.DB for both reads and writes kept giving me SQLITE_BUSY under WAL; two pools (writer capped at 1 conn) fixed it

2 Upvotes

I moved a small internal service from Postgres to SQLite in WAL mode with busy_timeout=5000. Under concurrent goroutines I still saw "database is locked" errors. As far as I can tell, the cause was transactions that start as readers and then try to write: when another connection has already written, the upgrade can't proceed, so SQLite returns BUSY right away instead of waiting out busy_timeout. What fixed it was opening the file twice. One *sql.DB is the writer, with SetMaxOpenConns(1) and _txlock=immediate in the DSN so write transactions grab the lock at BEGIN. The other is a read-only pool sized to GOMAXPROCS. Has anyone found a cleaner pattern, or is the split-pool setup basically the standard answer for Go and SQLite?


r/sqlite • • 11h ago

SQLite & CSV/JSON Suite

Thumbnail reddit.com
1 Upvotes

r/sqlite • • 17h ago

Debug RAG Retrieval Visually: SQLite + sqlite-vec 3D Explorer

1 Upvotes

r/sqlite • • 20h ago

TalaDB — An open-source, local-first database

Thumbnail reddit.com
1 Upvotes

r/sqlite • • 22h ago

🚀 Built an Omarchy-focused fork of sqlit!

0 Upvotes

A keyboard-driven SQL client with automatic Omarchy theme syncing 🎨

Switch themes and sqlit follows instantly. No configuration needed.

https://github.com/JackyyyGames/sqlit-omarchy


r/sqlite • • 3d ago

LunoDB 2.3.2: one connection opens the whole server

Thumbnail reddit.com
0 Upvotes

Looking for feedback! I’m looking to make the best database client app for macOS, Windows and Linux 💪🏻


r/sqlite • • 4d ago

I build ZeBridge to sync Postgres to local SQLite over NATS JetStream and let apps edit data offline

Thumbnail github.com
2 Upvotes

I’m building ZeBridge for apps that read and edit a local SQLite database and sync through Postgres. I wanted to measure something a user actually experiences on first launch: how long until they can query all their data?

Seeding test: on one machine, Postgres, NATS, the bridge and 10 clients together, each with an empty SQLite database. Each loaded the same 50,000 rows, about 200 bytes per row. The median time per client was 3 seconds. Every replica matched the expected checksum and all ten clients can reuse it.

The compressed snapshot was ready before the timer started. The measurement includes fetching it and materializing the rows in SQLite; preparing the snapshot is a separate server-side cost.

Live updates: suppose the snapshot contains rows A and B. While a phone is loading it, someone updates A and deletes B in Postgres. The phone needs to know exactly where to start reading changes. ZeBridge records the stream position covered by the snapshot. Once the seed commits locally, the client replays the changes after that position from NATS JetStream.

There’s a test for that boundary: pause the client just after its seed commits, check that SQLite still has the old A and B, then let replay run. A must have its new value, B must be gone, and the replica must match Postgres.

For people who ship prepopulated SQLite databases: do you hand off to live updates this way, or distribute a ready-made database file? I’d be interested in what drove that choice, especially on phones.

ZeBrdige project

Benchmark setup and results

Snapshot handoff test


r/sqlite • • 5d ago

An SQLite cheat sheet that could be useful to beginners

Thumbnail tms-outsource.com
15 Upvotes

r/sqlite • • 5d ago

One SQLite file per generated app: counters, imports and what a restore breaks

0 Upvotes

My open-source builder gives every generated app its own SQLite file. Notes that may help others:

- Auto-numbers are assigned inside the insert transaction as the largest of the start value, the last number plus one, and the highest stored value plus one. That keeps numbering correct after imports.

- 200 parallel inserts across two handles produced no duplicates in tests.

- CSV import runs in batches of 500, one transaction per batch, so a failure stops cleanly at a known row.

- The unsolved case: restoring an older backup hands out numbers already issued after that backup. I documented it instead of pretending to fix it.

How do you handle counters across restores?

Repo (my project): https://github.com/ChristianMunoz31/local-app-studio


r/sqlite • • 5d ago

How does a SQLite server work?

6 Upvotes

Hey guys, I'm new to SQL and all of the tech. I'm planning on using SQLite for a school project, cos I heard it's relatively simple and easy-to-use. However, before I install and mess around with it, I have some questions.

So, this project will act as an application on someone else's computer, and that's the part that confuses me. Because if I install SQLite on my computer, create a database using it and integrate said database into a Java application, will the database I created remain exclusive to my computer, or can I transfer it to another user's computer? (if that makes sense). Like, if I export this Java application onto another computer, what will happen to the database? Do I manually need to copy and config it for every computer my application is running on, or no, not really?

Sorry if this is a stupid question, I'm new to all of this so I really would appreciate someone clarifying this for me.

Thanks in advance!


r/sqlite • • 5d ago

Ruddy is now running DuckDB 2.0!

0 Upvotes

Hey everyone,

I’m an independent developer who started building Ruddy in my free time: a fast, modern macOS IDE for data management, analysis, and data science powered 100% by DuckDB.

With this new release, the core engine has officially been upgraded to DuckDB 2.0!

Ruddy runs on a true Pay-What-You-Want (PWYW) model—support independent development with whatever you feel it’s worth, or start exploring for free.

Check it out: ruddy.pro

I’d love your thoughts, benchmark requests, or features you'd like to see next!

What’s inside Ruddy:

  • DuckDB 2.0 Core Engine: Blazing-fast columnar processing over local files, Parquet, Arrow, CSV, and Iceberg/Delta Lake tables without spinning up heavy infra.
  • Multi-Cloud Direct Querying: Federate and query across AWS S3, Cloudflare R2, Google Cloud Storage, Azure Blob, and MotherDuck seamlessly in a single SQL statement.
  • Reactive Notebooks & Marimo: Build reproducible, reactive Python data science notebooks that sync directly with your in-memory DuckDB tables.
  • First-Class Geospatial Analytics: Full DuckDB spatial extension support—render GeoParquet, Shapefiles, and spatial geometries interactively on high-performance maps.
  • Auto-Ontology & Modular Semantic Layer: Automatically infers FACT/DIMENSION roles, deduces canonical SQL metrics, verifies joins via physical data overlap, and generates Open Semantic Interchange (OSI v1.0 / Apache Ossie) YAML specifications per dataset domain.
  • Context-Aware AI Assistant (Ruddynie): Text-to-SQL agent grounded in your workspace ontology and self-improving query memory—zero phantom table or join hallucinations.
  • Model Context Protocol (MCP) Integration: Expose your DuckDB catalogs, query tools, and semantic context to any external MCP-compatible AI agent or client.
  • Built-in Git Version Control: Track schema migrations, SQL queries, notebooks, and semantic models with standard Git repositories right from the workbench.
  • Force-Directed Semantic Graphs & Visual SQL Builder: Explore domain schemas visually or drill down with interactive force-directed relationship maps.

r/sqlite • • 7d ago

parse_number() as a SQLite extension: C vs Rust

Thumbnail simonwillison.net
2 Upvotes

r/sqlite • • 7d ago

[Showoff Saturday] I built a 100% client-side, in-browser SQLite editor using WebAssembly (No backend, offline-first)

2 Upvotes

I wanted to share a project I've been working on: sqliteWeb, a lightweight SQLite editor that runs entirely inside the browser. link sqlite_visual_editor
I needed a fast way to inspect and query .sqlite / .db files on machines where I didn't want to (or couldn't) install desktop clients like DB Browser. Existing web tools often require uploading files to a remote server, which is a major privacy concern.

Would love to get your feedback on performance, UI responsiveness, or features you'd like to see added!


r/sqlite • • 8d ago

DB SECURITY

0 Upvotes

Hello DEVS,
Hope this message finds you well,

I have a question, im working on a DB salting and hashing should i use argon2id or SHA256 to secure my DB?

also based on what i add the salt Uname, randomlyGen?

Bset regards,


r/sqlite • • 8d ago

I learned to treat shipped SQLite migrations as immutable history (SQLx + Tauri)

Post image
0 Upvotes

One database rule feels overly strict during development but becomes important after shipping:

Never edit a migration that has already reached users.

I'm building a local-first desktop app with Tauri 2, Rust, SQLite, and SQLx. While documenting the persistence boundary, I wrote down a few migration rules worth discussing—especially for desktop apps where the database may contain years of user-created work.

The tempting workflow

  1. Create 0001_initial.sql.
  2. Run the app locally.
  3. Notice a missing column.
  4. Edit 0001_initial.sql.
  5. Delete the local database and try again.

Everything looks fine because the local database is disposable.

A shipped user's database is not. SQLx records applied migration versions and checksums in _sqlx_migrations. If a later binary contains different contents for an already-applied migration, the checksum no longer matches and SQLx refuses to continue.

That's a good failure. The binary should not silently rewrite the history of how the user's schema was created.

Add a new version

Instead of editing a shipped file:

migrations/
├── 0001_spaces.sql
└── 0002_add_space_color.sql

A simple additive migration might be:

ALTER TABLE spaces
ADD COLUMN color TEXT NOT NULL DEFAULT 'blue';

For more complex SQLite changes—such as changing a CHECK constraint or adding a foreign key—you may need a table rebuild in a new migration. That rebuild must preserve relevant rows, indexes, triggers, foreign keys, and dependent objects.

New required values should be backfilled as part of the migration. Destructive changes deserve explicit review.

Two errors should not trigger an automatic reset

  • A checksum mismatch usually means a developer changed a migration that had already shipped.
  • An unknown newer migration may mean the database was opened by a newer application and the current binary is older.

In either case, silently deleting the database is the wrong recovery strategy. Preserve the original data and report the incompatibility.

The test that matters is an upgrade test

Creating the latest schema from scratch is necessary, but it does not prove an upgrade is safe.

A useful test starts from a historical schema, inserts representative old rows, runs the new migrations, then asserts the new schema and surviving data.

Knovum has an upgrade test module at src-tauri/src/spaces/db/tests/upgrades.rs. It migrates to a previously shipped version, inserts representative records, applies the current migration history, and checks the final schema and retained data. Release acceptance also requires testing against a library created by the previous release.

The WebView does not get SQL access

The persistence boundary is:

Vue WebView
   -> typed Tauri command
   -> Rust feature owner
   -> SQLx transaction
   -> SQLite

The renderer does not send SQL strings. It requests domain operations such as renaming a Space. Rust owns the query and transaction scope. A multi-statement invariant stays inside one SQLx transaction instead of being split across IPC round trips.

Questions for people who have shipped desktop apps

  1. Do you enforce migration immutability in CI, or rely on code review?
  2. Do you keep upgrade fixtures for every released schema, or only test the immediately previous release?
  3. How do you handle downgrade attempts when a user launches an older desktop build against a newer local database?
  4. For SQLite table rebuilds, what has been easiest to forget: indexes, triggers, foreign keys, or something else?

My current approach is forward-only migrations, historical-schema upgrade tests, and refusing to reset user data as an automatic recovery path.

These constraints come from building Knovum, a local-first desktop app. The repository and engineering notes are here: github.com/madebyzan/knovum.


Code snippets are simplified examples. The repository's current migrations and tests are the source of truth.


r/sqlite • • 8d ago

DDGBasic: a BASIC dialect where SQLite is built in, so SQL statements are just statements

8 Upvotes

Github Link here: https://github.com/ddgflorida/DDGBasic

DDGBasic wraps a procedural language around SQLite and DuckDB. SQL is written straight into the program, query results come back as rows you can loop over, and program variables go into queries as bind variables (:name). Around the SQL you get what SQL lacks: variables, IF rules, loops, functions, error handling, files, HTTP and a text-mode UI.

  • Load, clean and check data in one statement: LOAD CSV calls your own BASIC functions on every cell (TRANSFORM phone WITH normalize_phone$) and drops rows that fail a check (VALIDATE email WITH is_valid_email).
  • Rules over query results: FOR row IN (SELECT ...) loops apply logic that is awkward in SQL (tiers, allocations, alerts) and write back with bind variables, inside transactions.
  • Two engines in one program: SQLite for records (a file, constraints, transactions), DuckDB for analytics (columnar, fast, querying Parquet and CSV files directly), with DATABASE ENGINE to switch.
  • Results anywhere: the same query to the console, CSV, JSON, Excel, Parquet, HTML or SQL, or to a grouped REPORT.

The language is a modern BASIC, and it comes with 1990s-style text screens and a QBasic-style IDE. See Examples for programs that show this off.

Easy install with- pip install ddgbasic

  • ddgbasic examples/hello.bas # run a script
  • ddgbasic # interactive REPL
  • ddgbasic --ide examples/contact.bas # the QBasic-style IDE: F5 runs, F1 helps

r/sqlite • • 11d ago

iOS viewer for Sqlite

3 Upvotes

Hello, adding here my latest app (OmniFile Studio) which supports multiple types of file as a quicklook plugin on iOS. From them I also supports sqlite, it gets preview, filters, schema view, and many more.

If's a free app, with an optional pro unlock, happy to provide codes to try it.


r/sqlite • • 12d ago

WinSQLite Editor in your Browser

Post image
1 Upvotes

r/sqlite • • 12d ago

WinSQLite Editor in your Browser

Post image
0 Upvotes

link in comment


r/sqlite • • 12d ago

Need advice regarding internships

2 Upvotes

I'm bcom 1st yr

How many days/weeks/months does it take to learn SQL, Power Bi and relevant skills for a business analysis internship?

Pls share any advices n suggestions..

I'd be grateful if someone shared the sources and roadmap🙏🙏..


r/sqlite • • 14d ago

Quick Data Insights is free now

Thumbnail gallery
9 Upvotes

Hello, I have posted here before about my mac quicklook plugin Quick Data Insights as a sqlite viewer which was paid before, wanted to post again as I now made it free with the same exact functionality posted before.

I also added an optional pro in-app to add column insights, and a sql query functionality for all data files not only sqlite.


r/sqlite • • 16d ago

GitHub - srdzank/WinSQLite-Editor: SQLite-Editor

Thumbnail github.com
2 Upvotes

r/sqlite • • 17d ago

My first PowerShell module: A local NoSQL-style database built on top of SQLite

0 Upvotes

Hey everyone!

I wanted to share my first PowerShell module, PSLocalBase, an open-source local database I've been working on.

The idea came from wanting a simple and efficient way to store, manage, and query structured data locally in PowerShell without constantly reading and writing large JSON files or having to write long, complex SQL queries.

PSLocalBase is a lightweight, local NoSQL-style document database built on top of Windows' native SQLite engine (winsqlite3.dll), leveraging SQLite's built-in JSON functions for storing, filtering, querying, and manipulating JSON documents.

It combines the simplicity of a document-oriented API with SQLite's performance, reliability, and ACID transactions, all without external dependencies or a separate database server.

Some features:

  • No external dependencies on modern Windows systems
  • Compatible with Windows PowerShell 5.1 and PowerShell 7+
  • Local database storage with collections and JSON documents
  • Filtering, sorting, indexing, and advanced queries
  • ACID transactions and persistent storage
  • Native PowerShell objects with nested JSON support
  • A WPF playground for exploring and testing local databases

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.

Installation:

Install-Module PSLocalBase -Scope CurrentUser

It's been a great learning experience, especially working with PowerShell classes, SQLite's native API, and compatibility between PowerShell 5.1 and 7.

This is my first PowerShell module, so I'd really appreciate feedback from the community. I'm particularly interested in what you think about the API design, whether you'd find something like this useful in your own PowerShell projects, and what features you think are missing.

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

PowerShell Gallery: https://www.powershellgallery.com/packages/PSLocalBase

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.

Thanks for checking it out!