r/sqlite • • 2d 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 • • 2d ago

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

Thumbnail
github.com
1 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 • • 3d 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 • • 3d 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 • • 4d ago

An SQLite cheat sheet that could be useful to beginners

Thumbnail
tms-outsource.com
12 Upvotes

r/sqlite • • 4d 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

parse_number() as a SQLite extension: C vs Rust

Thumbnail simonwillison.net
2 Upvotes

r/sqlite • • 5d 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 • • 6d 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 • • 6d 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 • • 7d 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 • • 10d 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 • • 10d ago

WinSQLite Editor in your Browser

Post image
1 Upvotes

r/sqlite • • 10d ago

WinSQLite Editor in your Browser

Post image
0 Upvotes

link in comment


r/sqlite • • 10d 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 • • 13d 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 • • 14d ago

GitHub - srdzank/WinSQLite-Editor: SQLite-Editor

Thumbnail
github.com
2 Upvotes

r/sqlite • • 16d 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!


r/sqlite • • 20d ago

GitHub - srdzank/WinSQLite-Editor: SQLite-Editor sqlite_vec

Thumbnail
github.com
4 Upvotes

r/sqlite • • 20d ago

WAL file grew to 4GB because our metrics scraper held a read txn open for the whole scrape

10 Upvotes

We buffer counter samples into a local SQLite file per host and a Prometheus exporter reads them every 15s. The exporter was opening a transaction, iterating a cursor, and formatting the text exposition inline, so the read txn stayed open for ~2-3 seconds while the HTTP response was written out — long enough that the writer's auto-checkpoints kept bailing and the WAL never truncated. Fix was dumb and obvious in hindsight: read rows into memory, commit, then serialize, plus a `wal_checkpoint(TRUNCATE)` after the nightly delete of old samples. What I'm still unsure about is whether I should set `journal_size_limit` as a safety net or if that just hides the next version of this bug — does anyone rely on it in production, or treat an oversized WAL as a signal worth alerting on?


r/sqlite • • 21d ago

Soft delete vs hard delete: the case for each, and what would decide it for me

7 Upvotes

A decision that looks small and tends to stick for the life of a system. Here are both sides, written as fairly as I can.

The case for soft delete (a deleted_at column instead of removing the row)

  • Undo is easy: restore the row, clear the column.

  • Foreign keys from other tables stay valid, so history like “this order belonged to a customer who later left” still resolves.

  • You keep an audit trail without a separate mechanism.

  • Support teams like being able to see what was removed and when.

The case against

  • Every query has to remember WHERE deleted_at IS NULL. One forgotten filter and removed data leaks into a screen, a report or an export.

  • Unique constraints get awkward. If a user deletes their account and signs up again with the same email, a plain unique index on email blocks it. In Postgres you can use a partial unique index, WHERE deleted_at IS NULL; other databases handle this differently, so check yours.

  • Tables only grow, and indexes carry rows nobody queries.

  • Real deletion may eventually be required (privacy or retention rules), so “soft” deletion can turn into a second deletion project later.

  • Cascading is ambiguous: when a parent is soft-deleted, what happens to its children?

What hard delete plus something else looks like

  • Delete the row, and write the old version to an audit or archive table, or emit an event, if you need history.

  • Cleaner queries and constraints, at the cost of building and maintaining the history mechanism.

What would decide it for me:

  • Do users or staff actually need undo, or is it only “just in case”?

  • Are there legal or contractual limits on how long deleted data can be kept?

  • How many places query this table? More places means more chances to miss the filter.

  • Will other systems (analytics, search, caches) need to learn about deletes?

I don’t think either is wrong by default. If I had to pick a starting point without knowing the system, I’d lean toward hard delete with an audit trail where retention rules are strict, and soft delete where undo is a real feature. I’m not certain about that split.

Where do you land, and has either choice caused you trouble later?


r/sqlite • • 24d ago

JDER — un editor gratuito per i tuoi diagrammi di informatica

Thumbnail
reddit.com
4 Upvotes

r/sqlite • • 25d ago

How do you orchestrate zero-downtime schema migrations across 100+ isolated databases (Database-per-Tenant) while in active beta?

Thumbnail reddit.com
10 Upvotes

r/sqlite • • 25d ago

I built a free Mac database client because I wanted to stop reaching for the mouse

Thumbnail reddit.com
3 Upvotes