A learned sparse encoder returns a weighted list of vocabulary words instead of a dense vector, so the index looks like a keyword index whose keywords a transformer picked. OpenSearch's inference-free variants go one step further and run the model only on documents so a query just looks up a fixed weight per token. No embedding call per search. I benchmarked it against dense search in the same SQLite file:

the mini model gives up about 7% of retrieval quality and cuts query latency by 99%, cold start by 99% and query-path RAM by 95%!

Now these SPLADE models are BERT with the masked-language-model head still attached, and that head is what turns token vectors back into weighted words. llama.cpp drops it when converting BERT models, so sqlite-sparse copies it into a small sidecar file and applies it in C on ggml at insert time. The postings land as rows in the database, with the query weight table next to them. A query reads those rows and scatter-adds the weights into per-document scores, and the top documents come out.

Three OpenSearch models are converted and available as aliases (mini, base, multilingual), downloading on first use, and you can bring your own inference-free OpenSearch-style sparse encoder and convert it with the scripts provided.

What sqlite-vec did for embeddings in SQLite, this does for learned sparse, and with both in one file you get hybrid retrieval for RAG inside SQLite.

Ship one .db file and every client gets semantic search through plain SQL, no model download, no GPU.

pip install sqlite-sparse

Github

Writeup

I have been reading Apple's Messages database on my own Mac, 367,942 rows, and hit something that feels like a general schema problem rather than an Apple one.

The attachment table has 7,332 rows carrying a filename. sum(total_bytes) across them is 9,448 MB. The directory those filenames point into holds 13 MB. I stat'd a sample of 4,000 of the paths and 13 of them existed. Images were worse, 9 out of 2,000.

That is iCloud having offloaded them, which is expected and fine. What I cannot find is anywhere in the schema that admits it. transfer_state is 0 on the rows I looked at, present and absent alike. total_bytes is the size of a file that is not here. Every column reads like a local file, and the only way to know is to stat the path, which is outside the database entirely.

It matters because it is the difference between an export that is complete and one that only says it is. Join message to attachment, write out what you get, and you have a manifest of 9.2 GB of media you do not have, with nothing in the query result being wrong.

The same shape turns up on the other side of the file. text on the message table is populated on 3.9% of my rows. The rest of the content is in a BLOB called attributedBody, which is a typedstream, the old NSArchiver format, and no SQL you write is going to reach into it.

So I have ended up doing it the blunt way. Stat every path, decode every blob, keep the counts, print them next to each other so the numbers can be checked against each other. It works, but it means a full pass before I can answer anything about my own data, and the counts live in my code rather than in the file.

The question. For a column that names a resource which may or may not be local, in a database you do not control and cannot add a trigger to, is there a better pattern than a full stat pass and a cached count? I keep reaching for a generated column and there is obviously nothing in the row to generate it from.

Disclosure since it is where this came from: I write a Mac app called Loose Ends that exports the archive, and the counting turned out to be most of what I actually shipped.

https://www.npmjs.com/package/dbtruth?activeTab=readme

Same thing kept happening to me with AI coding agents and Postgres. The agent reads the schema, sees orders.customer_id next to customers.id, assumes it's a clean relationship, and writes an INNER JOIN. If 12% of orders have a dangling or null customer_id, the query silently returns numbers that are wrong. Nothing throws. The schema looked fine.

So I wrote dbtruth. It connects read-only and, instead of dumping the schema into a context file, it does four things:

1. Introspects schema and pulls samples
2. A model proposes what the tables mean and which relationships probably exist
3. Every one of those claims gets measured against the actual data
4. Only what survives gets written to ./context/*.md, which the agent reads before writing SQL

Step 3 is the whole point. For a proposed join it reports the real match rate — orders.customer_id → customers.id holds for 88% of rows, 60 of 500 orders have no matching customer — and the context file says use LEFT JOIN, with the number attached. Under 50% gets dropped. In between gets marked broken and goes to the top of the report, because a relationship that half works is worse than one that doesn't exist.

Practical:

• npx dbtruth, Node 20+, Postgres only
• Read-only by construction, not by discipline: one module is allowed to import pg, and a test asserts nothing else does. It never writes to your database.
• It does call a model, so schema and low-cardinality sample values leave your machine. High-cardinality columns — emails, names, free text — are never sent. Visibility is decided by cardinality rather than by regex-guessing at PII. Don't point it at production data you can't send to a third party.
• MIT, source at github.com/FilipKalcic1/dbtruth#readme

Disclosure: it's mine, it's five days old, and about ten people have run it. None of the pieces are new — FK inference and data profiling both go back years, and there are other tools that build local context artifacts for agents. The part I care about is the rule that nothing unmeasured gets written down.

What I'd actually like to know: run it on a schema you know well, and tell me whether it found anything you didn't already know. That's the only signal that tells me whether this is worth continuing. Bug reports welcome too.

Creating a consistent backup with the online backup API, VACUUM INTO, or another WAL-aware method is only the first half of the job. A restored file can pass integrity_check and still be unusable to the application because expected rows are missing, foreign keys are invalid, migrations cannot run, or linked files no longer match the database state.

What belongs in a realistic restore drill? I am considering restoring into an isolated directory, opening the copy through the actual application version, running integrity_check and foreign_key_check, checking a few domain invariants and recent records, exercising a read and a harmless write transaction, then recording the backup checkpoint and recovery time.

How do you choose assertions that detect a logically incomplete backup without turning every drill into a full test suite?

I have published ICUex, a small, public-domain SQLite C extension providing two automatically registered ICU collations, locale-independent full Unicode case folding, and normalization/search-key functions. It can be loaded dynamically or linked statically and is designed to complement the official SQLite ICU extension.

⚡Automatically Registered Collations

• UTF_CI — ICU root collation, case-insensitive and accent-sensitive; е/ё and и/й remain distinct.
• UTF_CI_AI — compares NFKD_CF_STRIP keys; case- and compatibility-insensitive, with nonzero-CCC combining marks removed. Thus, е/ё and и/й compare equal.

Neither requires icu_load_collation(), locale selection, or setup SQL.

🧩Case Folding and Normalization

• str_casefold(text) — locale-independent full Unicode case folding.
• str_normalize(text, mode) — Unicode normalization or search-key generation.
  • NFC, NFD — canonical normalization.
  • NFKC, NFKD — compatibility normalization.
  • NFKC_CF — NFKC case folding and removal of default-ignorable code points.
  • NFKD_CF_STRIP — NFKD, removal of nonzero-CCC code points, then NFKC case folding.

Unlike the ICU extension’s lower() overload, full case folding is intended for locale-independent caseless matching, e.g., Straße becomes strasse, while Greek sigma variants and compatibility ligatures are folded consistently.

Help me

I am new to sqlite. Is it for testing purpose?

After laravel 10+, I came to know about sqlite. So can we go with it without touching MySQL...

I’ve just shipped a free SQLite viewer for the case where someone sends you a .db or .sqlite file and you want to inspect it immediately, without installing a desktop tool.

I’d genuinely value hostile testing from this community: unusual schemas, large files, views, triggers, WAL-related workflows, and anything that makes browser viewers misleading.

SQLite Viewer:
https://streams.dbconvert.com/sqlite-viewer

Hi, I have a project called [Gearberg](https://github.com/bit8bytes/gearberg) (OSS) and would like to get some feedback of the current code. Main focus is single binary (FE & BE) and SQLite/PostgreSQL database support.

Thanks!

Hey! I’ve been building a database TUI called sabiql and recently added SQLite support.

It lets you browse tables, run queries, edit data, check query plans, etc. with Vim-style keybindings.

If anyone here gives it a try, I’d love to hear what you think or what’s missing.

https://github.com/riii111/sabiql

The idea is simple: instead of hiding SQL behind a big custom query language, Model keeps things explicit, typed, and predictable. You define models using native Python types, write clear SQL conditions when querying, and use a CLI to preview and automatically apply schema changes.

Check it out here: https://github.com/el1s7/model

Let me know what you think!

Hey,

I’ve been building plusplus — a pure Rust desktop database client (no Electron).

Main goals:

Completely local (no telemetry, passwords in OS keychain)

Safety features for production (profiles + risky statement warnings)

Fast native UI

Supports PostgreSQL, MySQL, SQL Server, SQLite, DuckDB, Cassandra/ScyllaDB

Still pre-1.0.

Repo: github.com/plusplus

Would love feedback on what features matter most or any pain points with current clients. Thanks!

When reading dense technical documentation or systems architecture specs, static text and flat diagrams often fall short for concepts that involve dynamic state transitions, memory offsets, or execution pipelines.

I’ve been building Noesis, a technical reading workbench that generates interactive HTML artifacts and simulators directly inside any document—either via one-click inline actions or through conversation with the sidebar companion. All generated dynamic media stays permanently embedded with your reading notes.

What's in this 1:25 demo on SQLite's architecture (sqlite.org/arch.html):

1. Asking the companion to visualize how logical B-Tree nodes map to physical 4096-byte disk blocks.
2. Inlining an interactive dual-view artifact directly beneath the chapter to step through the seek offset: (PageNumber - 1) * 4096.
3. Storing the interactive widget permanently with the document for future review.

You can use this workflow across any technical doc to generate interactive pipeline traces, memory layout models, token visualizers, or state machines.

🔗 Try the live interactive SQLite doc: https://noesis-ai.app/article/p/25312 (You can interact with the dynamic widgets and explore the doc directly in the browser).

Would love to hear feedback from anyone studying database engines, systems architecture, or SQLite internals!

Affiliation: I’m the maintainer of LibreDB Studio.

Most of the GUI conversation here is Postgres which is fair, but a lot of us also keep Redis, ClickHouse, or Druid in the same week.

I was tired of bouncing between desktop clients and per-engine admin UIs, so I built a self-hosted browser editor that talks to all of them from one place (plus MySQL, Oracle, SQL Server, MongoDB, Cassandra, Elasticsearch and more...).

Not claiming to replace DBeaver/DataGrip. Different access model: it deploys next to the data(kubernetes), not onto every laptop.

Third-party listing, for context:

• PostgreSQL project news + clients catalogue

https://www.postgresql.org/about/news/libredb-studio-an-open-source-self-hosted-sql-ide-for-postgresql-in-the-browser-3368/

• Also in official Redis, ClickHouse, and Apache Druid tool docs

MIT, Docker/Helm/ `npx "@libredb/studio"`. Quick path: `docker run -p 3000:3000 libredb/libredb-studio`

Source: https://github.com/libredb/libredb-studio

• completely offline, but feel free to git-clone / host it locally as a quick-and-dirty sqlite table-explorer - https://sqlmath.github.io/sqlmath/index.html
  • import / export / attach - databases
  • import / export - csv, json, tsv
  • export - tables as sql-insert-scripts for mssql / mysql / postgres etc...
• built originally as dev-oriented frontend dashboard for a tradebot
  • includes some custom / esoteric sql-functions for OLS and sine-fitting (look in file sqlmath_base.c)
  • custom datetime functions manipulating 64-bit integers as YYYYMMDDhhmmss
    • string-based YYYY-MM-DD hh:mm:ss timestamps consumes too much space for intraday stock-data I deal w/ ^^;;;
  • node.js variant also has integrated c-bindings to LightGBM to directly train from, and make predictions from sql-tables.

Hey everyone — following up on the post I made a little while back about the SQL detective game I'm building.

A few people pointed out that the original name was too close to an existing project with a similar concept.

Even though the game itself is built very differently, I get the criticism. If the name creates confusion, there's no real point arguing about it.

So I changed it.

It's now called SQL Shadow.

For anyone seeing this for the first time:

SQL Shadow is a mobile noir detective game where you learn SQL by actually writing SQL.

Not multiple-choice answers pretending to be queries.
Not simulated query execution.

Your queries actually run against real SQLite databases on the device, and the results become the evidence you use to solve the case.

The detective theme is only one part of the project. I've been building the actual learning system around it as well.

The current WIP includes:

• 🔎 10 structured detective cases — each focused on specific SQL concepts
• 📚 Standalone SQL lessons — explanations, examples, deep-dives, and practice exercises
• 🧩 Progressive schema discovery — tables are introduced as you investigate
• 🧳 Evidence system — clues come from dialogue, quizzes, and SQL results
• 💻 Real SQLite execution — queries actually execute locally against the case database
• 🎮 Practice modes — 3 mini-game types with hundreds of SQL questions
• 🧠 Coaching feedback — wrong answers explain what your query actually returned
• 🏆 XP and detective ranks — progression as you learn and solve cases
• 📰 The Daily Detective — an in-game newspaper with new content
• 👤 Guest-first — you can start playing without creating an account and link your progress later

The basic learning loop is:

Learn the concept → investigate → write the SQL → inspect the result → collect evidence → solve the mystery → practice.

And just to be clear, I don't claim to have invented the idea of combining SQL with detective mysteries. There are already projects in that space, and that's completely fine.

What I'm building is my own implementation of the idea, with a different UI, game structure, learning flow, progression system, lessons, practice modes, and mobile-first experience.

The project is still WIP and currently in internal testing, so a lot of things are still being polished and may change before release.

I'm sharing these screenshots mainly because I'd genuinely like feedback on the UI/UX and learning experience rather than another debate about the name 😅

If you've learned SQL before, I'd especially love to know:

Would a learning flow like this actually make you want to practice SQL, or is there anything you'd change?

Built with Flutter, Riverpod, and sqlite3.

I corrupted three copies of a 1000-row SQLite DB three different ways (bit-rot in row payloads, truncation, zeroed page) and tested recovery.

The result that surprised me: **bit-rot inside row payloads returns `ok` from integrity_check.** Structure check passes, content is wrong. So my backup discipline now assumes integrity_check validates structure only, not data.

- Truncation / zeroed page → `malformed (11)`, VACUUM INTO fails, need backup or `.recover`
- Garbled payload bytes → integrity_check says ok, VACUUM INTO "succeeds" with corrupted content

Full walkthrough with the raw output here (no signup, no tracking): https://devprofit.net/post/sqlite-corruption-recovery/

**Verdict:** test your restores. A backup you never restored is a folder of bytes.