[HN Gopher] Modern SQLite: Features You Didn't Know It Had
___________________________________________________________________
Modern SQLite: Features You Didn't Know It Had
Author : thunderbong
Score : 166 points
Date : 2026-04-02 16:34 UTC (6 hours ago)
(HTM) web link (slicker.me)
(TXT) w3m dump (slicker.me)
| subhobroto wrote:
| None of these are news to the HN community. Write-ahead logging
| and concurrency PRAGMAs have been a given for a decade now. IIRC,
| FTS5 doesn't often come baked in and you have to compile the
| SQLite amalgamation to get it. If you do need better typing, you
| should really use PostgreSQL.
|
| However, I will concede, and the article doesn't mention at all,
| far less are aware that you can build HA, cross region replicated
| SQLite using purely OSS software provided you architect your
| software around it. Now _that_ would be a really good `Modern
| SQLite: Features You Didn 't Know It Had` article!
|
| Another interesting discussion point is how far self hosted
| PostgreSQL and pgBackRest can get you to a near-zero data loss
| high RPO, RTO setup. Its simply amazing we can self host all
| this.
| happytoexplain wrote:
| There are plenty of people in the HN community who don't know
| much about SQLite. Tech is a big, huge, enormous, gigantic
| domain.
| sgbeal wrote:
| > Write-ahead logging and concurrency PRAGMAs have been a given
| for a decade now.
|
| All of the listed features except for strict tables and
| generated columns have been in SQLite for 10+ years, and those
| two are certainly not new. The JSON APIs were not made part of
| the standard distribution until 3.38 (2022-02) but were added
| in 3.9 (2015-10) and widely used long before they were upgraded
| from an optional extension to a core feature.
|
| - Generated columns: 3.31 (2020-01)
|
| - Strict tables: 3.37 (2021-11)
| subhobroto wrote:
| > All of the listed features except for strict tables and
| generated columns have been in SQLite for 10+ years, and
| those two are certainly not new
|
| Correct.
|
| As I mentioned in my GP, these features have been around a
| long, long, long time and in the current age of AI that would
| happily tell you these features exist if you remotely even
| hint at it, I would assume one would really have to go out of
| their way to be ignorant of them.
|
| It doesn't hurt to have an article like this reiterate all
| that information though, I just would have loved the same
| level of effort put into something that's not as easily
| available yet.
|
| A gap exists, and the article doesn't mention at all, about
| actually configuring solutions like litestream, rqlite,
| dqlite (not the same!) to build HA, cross region replicated
| SQLite using purely OSS software provided you architect your
| software around it. Now that would be a really good "Modern
| SQLite: Features You Didn't Know It Had" article!
|
| Another interesting discussion point is how far self hosted
| PostgreSQL and pgBackRest can get you to a near-zero data
| loss high RPO, RTO setup. It just blows my mind that we can
| self host all this on consumer grade hardware without having
| to touch a DC or "the cloud" at all.
| 123abcdef wrote:
| I'm afraid you overestimated my knowledge
| esafak wrote:
| https://www.explainxkcd.com/wiki/index.php/2501:_Average_Fam...
| faizshah wrote:
| Theres also spellfix1 which is an extension you can enable to get
| fuzzy search.
|
| And ON CONFLICT which can help dedupe among other things in a
| simple and performant way.
| somat wrote:
| I was trying to port a small program I wrote from postgres to a
| sqlite backend(mainly to make it easier to install) and was
| pleased to find out sqlite supported "on conflict" I was less
| pleased to find out that apperently I abuse CTE's to insert
| foreign keys all the time and sqlite was not happy doing that.
| with thing_key as ( insert into item(key, description)
| values('thing', 'a thing') on conflict do nothing )
| insert into user_note(uid, key, note) values (123, 'thing', 'I
| like this thing') on conflict (uid, thing) do update set note =
| 'I like this thing');
| FooBarWidget wrote:
| I've found FTSE5 not useful for serious fuzzy or subword full
| text search. For example I have documents saying "DaemonSet". But
| if the user searches for "Daemon" then there will be no results.
| nikisweeting wrote:
| I have found this as well, FTSE5 is convenient to have as an
| option, but it's not as versatile as postgres or sonic or other
| full-text search solutions.
|
| Does anyone have any other favorite modern bloom-filter-based
| search solutions that dont need to store copies of all the
| documents in the search db? Ideally something that can run in
| WASM too so we can ship a tiny search index to the browser. I
| found https://github.com/tinysearch/tinysearch but haven't
| tried it yet.
| yomismoaqui wrote:
| Doesn't this work ok?
|
| https://www.sqlite.org/fts5.html#fts5_prefix_queries
| ers35 wrote:
| Use the trigram tokenizer:
| https://www.sqlite.org/fts5.html#the_trigram_tokenizer
| krylon wrote:
| STRICT tables are something I appreciate _very much_ , even
| though I cannot recall running into a problem that would have
| prevented by its presence in the before-time. But it's good to
| have all the same.
|
| I don't think I've ever done much with SQLite's JSON functions,
| but I have on one or two occasions used a constraint to enforce a
| TEXT column contains valid JSON, which would have been very
| tedious to do otherwise.
| crazygringo wrote:
| > _even though I cannot recall running into a problem that
| would have prevented by its presence in the before-time_
|
| I very, very much did. I was using a Python package that used a
| lot of NumPy internally, and sometimes its return values would
| be Python integers, and sometimes they'd be NumPy integers.
|
| The Python integers would get written to SQLite as SQLite
| integers. The NumPy integers would get written to SQLite as
| SQLite binary blobs. Preventing you from doing simple things
| like even comparing for equal values.
|
| Setting to STRICT caused an error whenever my code tried to
| insert a binary blob into an integer column, so I knew where in
| the code I needed to explicitly convert the values to Python
| integers when necessary.
| nikisweeting wrote:
| Surprised no one has mentioned Turso yet!
|
| They recently landed multi-writer support for their rust SQLite
| re-implementation, which is personally the biggest issue I've had
| with using SQLite for high concurrency applications.
|
| `PRAGMA journal_mode = 'mvcc';`
|
| https://docs.turso.tech/tursodb/concurrent-writes
|
| Very excited to see if SQLite responds by adding native support,
| I'm hoping competition here will spur improvements on both sides.
| ncruces wrote:
| Incredible that a database company writes that page and doesn't
| document the isolation level of the feature.
| malkia wrote:
| In the past I've used the backup API -
| https://sqlite.org/backup.html - in order to load in memory a
| copy of sqlite db, and have another live one. I would do this
| after certain user action, and then by doing a diff, I would know
| what changed... I guess poor way of implementing PostgreSQL
| events... but it worked!
|
| Granted it was small DB (few megabytes), I also wanted to avoid
| collecting changes one by one, I simply wanted a diff over last
| time.
| tombert wrote:
| For a long time I absolutely hated SQLite because of how terribly
| it was implemented in Emby, which made it so you couldn't load
| balance an Emby server because they kept a global lock on the
| database for exactly one process, but at this point I've grown a
| kind of begrudging respect for it, simply because it is the
| easiest way to shoehorn something (roughly) like journaling in
| terrible filesystems like exFAT.
|
| I did this recently for a fork of the main MiSTer executable
| because of a few disagreements with how Sorg runs the project,
| and it was actually pretty easy to change out the save file
| features to use SQLite, and now things are a little more
| resistant to crashes and sudden power loss than you'd get with
| the terrible raw-dogged writing that it was doing before.
|
| It's fast, well documented, and easy to use, and yeah it has a
| lot more features than people realize.
| 101008 wrote:
| Not sure if people interested, but since I use sqlite in a lot of
| my own projects, I am working on a lightweight monitoring and
| safety layer for production SQLite. The idea is pretty simple:
| SQLite is amazing, but once it's running in production you
| basically have zero observability. If something weird happens
| (unexpected writes, schema changes, background jobs touching
| tables, etc.) you only find out after the fact. It tries to solve
| that without touching application code. It's a Rust agent that
| runs next to your sqlite file, and connects to the server where
| everything is logged in. My current challenge right now is
| encryption and trust, mostly.
|
| Curious if others here are running SQLite in production and if
| you would be interested in something like this.
| mandeepj wrote:
| checkout https://newrelic.com/instant-observability/sqlite
| kherud wrote:
| SQLite seems very powerful for building FTS (user enters free
| text, expects high precision/recall results). Still, I feel like
| it's non-trivial to get good search quality.
|
| I think the naive approach is to tokenize the input and append
| "*" for prefix matching. I'm not too experienced and this can
| probably be improved a lot. There are many settings like
| different tokenizers, stemming, etc. Additionally, a lot can be
| built on top like weighting, boosting exact matches, etc.
|
| Does anyone know good resources for this to learn and draw
| inspiration from?
| fizx wrote:
| I mean you can use sqlite as an index and then rebuild all of
| Lucene on top of it. It's non-trivial to build search quality
| on top of actual search libraries too.
|
| O'Reilly's "Relevant Search" isn't the worst here, but you'll
| be porting/writing a bit yourself.
| subhobroto wrote:
| > Does anyone know good resources for this to learn and draw
| inspiration from?
|
| Is there a reason why something more custom built, like
| ParadeDB Community edition won't meet your needs?
|
| I understand you're speaking about SQLite, while ParadeDB is
| PostgreSQL but as you know, it's non-trivial to get good search
| quality, so I'm trying to understand your situation and needs.
| momo_dev wrote:
| the JSON functions are genuinely useful even for simple apps. i
| use sqlite as a dev database and being able to query JSON columns
| without a preprocessing step saves a lot of time. STRICT tables
| are also great, caught a bug where I was accidentally inserting
| the wrong type and it just silently worked in regular mode
| subhobroto wrote:
| > caught a bug where I was accidentally inserting the wrong
| type and it just silently worked in regular mode
|
| Typically one would design their "DTO"s to catch such errors
| right in the application layer, way before it even made it into
| the DB.
|
| Different people call this serialization-deserialization layer
| different names (DTO being the most ubiquitous I think) but in
| general, one programs the serialization-deserialization layer
| to catch structural issues (age is "O" instead of 0).
|
| The DB is then delegated to catch unchanging ground truths and
| domain consistency issues (people who have a DOB into the
| future are signing up or humans who don't have any email or
| address at all when the business needs it).
|
| In your case, it's great the DB caught gaps in the application
| layer but one would handle it way before it even made it there.
|
| The way I think about DB types are:
|
| 1. unexpected gaps/bugs: "What did I miss in my application
| layer?"
|
| 2. expected and unchanging constraints: "What are some
| unchanging ground truth constraints in my business?", "What are
| the absolute physical limits of my data?" - while I check for a
| negative age in my DTOs to provide fast errors to the user, I
| put these constraints in the DB because it's an unchanging rule
| of reality.
|
| Crucially, by keeping volatile business rules out of the
| database and restricting it only to these ground truths, I
| avoid being dragged down by constant DB migrations in a fast-
| evolving business
| QuadrupleA wrote:
| Love SQLite and most of these features.
|
| On the STRICT mode, I've asked this elsewhere and never gotten an
| answer: does anyone have a loose-typing example application where
| SQLite's non-strict, different-type-allowed-for-each-row has been
| a big benefit? I love the simplicity of SQLite's small number of
| column types, but the any-type-allowed-anywhere design always
| seemed a little strange.
| lateforwork wrote:
| When your application's design changes, you may need to store a
| slightly different type of data. Relational databases
| traditionally require explicit schema changes for this, whereas
| NoSQL databases allow more flexible, schema-less data. SQLite
| sits somewhere in between: it remains a relational database,
| but its dynamic typing allows you to store different types of
| values in a column without immediately migrating data to a new
| table.
|
| This flexibility is convenient when only one application reads
| and writes to the table. But if multiple applications access
| the same tables, the lack of a strictly enforced schema becomes
| a liability. The same is true when using generic tools to
| process data in SQLite tables, because such tools don't know
| what type of data to expect. The column type may be X but the
| actual data may be of type Y.
| irq-1 wrote:
| > the any-type-allowed-anywhere design always seemed a little
| strange.
|
| Sqlite came from TCL which is all strings. https://www.tcl-
| lang.org/
|
| An example of where this would be a benefit is if you stored
| date/times in different formats (changing as an app evolved.)
| SQLite wrote:
| Flexible typing works really well with JSON, which is also
| flexibly typed. Are you familiar with the ->> operator that
| extracts a value from JSON object or array? If jjj is a column
| that holds a JSON object, then jjj->>'xyz' is the value of the
| "xyz" field of that object.
|
| I copied the idea for the ->> operator from PostgreSQL. But in
| PostgreSQL, the ->> operator always returns a text rendering of
| the value from the JSON, even if the value is really an integer
| or floating point number. PG is rigidly typed, so that's all it
| can do. But SQLite is flexibly typed, so the ->> operator can
| return anything - text, integer, floating-point, NULL -
| whatever value if finds in the JSON.
| ncruces wrote:
| Not necessarily, but being able to specify types beyond those
| allowed by STRICT tables is useful.
|
| Ideally, I'd like to be able to specify the stored type (or at
| least, side step numeric affinity), and give the type a name
| (for introspection, documentation).
|
| Specifying that a column is a DATETIME, a JSON, or a DECIMAL is
| useful, IMO.
|
| Alas, neither STRICT nor non-STRICT tables allow this.
| mpyne wrote:
| I actually needed that exact window function example earlier this
| week when I needed to figure out why our shared YNAB budget
| somehow got out of balance with the bank. SQLite to load the
| different CSVs and lay out the bank's view of the world against
| YNAB's with running totals was what I turned to.
| aaviator42 wrote:
| SQLite is insanely robust. I have developed websites serving
| hundreds of thousands of daily users where the storage layer is
| entirely handle by SQLite, via an abstraction layer I built that
| gives you a handy key-value interface so I don't have to craft
| queries when I just need data storage/retrieval:
| https://github.com/aaviator42/StorX
| andrewstuart wrote:
| Disturbing.
|
| I did not know SQLite allows writing data that does not match the
| column type. Yuck. Now I need to review anything I built and fix
| it.
|
| I understand why they wouldn't, but STRICT should be the default.
| lateforwork wrote:
| STRICT has severe limitations, for example it does not have
| date data type.
|
| Why is it a problem that it allows data that does not match the
| column type? SQLite is intended for embedded databases, where
| only your application reads and writes from the tables. In this
| scenario, as long as you write data that matches the column's
| data type, data in the table does match the column type.
| subhobroto wrote:
| > Why is it a problem that it allows data that does not match
| the column type? SQLite is intended for embedded databases
|
| I'm afraid people forget that SQLite is (or was?) designed to
| be a superior `open()` replacement.
|
| It's great that modern SQLite has all these nice features,
| but if Dr. Hipp was reading this thread, I would assume he
| would be having very mixed feelings about the ways people
| mention using SQLite here.
| SQLite wrote:
| No, I think that people can use SQLite anyway they want.
| I'm glad people find it useful.
|
| I do remain perplexed, though, about how people continue to
| think that rigid typing helps reliability in a scripting
| language (like SQL or JSON) where all values are subclasses
| of a single superclass. I have never seen that in my own
| practice. I don't know of any objective research that
| supports the idea that rigid typing is helpful in that
| context. Maybe I missed something...
| lateforwork wrote:
| > _where all values are subclasses of a single
| superclass_
|
| I don't understand this. By values do you mean a row (in
| database terms)? I don't understand what that has to do
| with rigid typing.
|
| Lack of rigid typing has two issues, in my opinion:
| First, when two or more applications have to read data
| from a single database, lack of an agreed-upon-and-
| enforced schema is a limitation. Second, when you use
| generic tools to process data, the tools have no idea
| what type of data to expect in a column, if they can't
| rely on the table schema.
| andrewstuart wrote:
| >> but if Dr. Hipp was reading this thread
|
| He is.
| andrewstuart wrote:
| >> Why is it a problem that it allows data that does not
| match the column type?
|
| "Developers should program it right" is less effective than a
| system that ensures it must be done right.
|
| Read the comments in this thread for examples of subtle bugs
| described by developers.
| lateforwork wrote:
| > _"Developers should program it right" is less effective
| than a system that ensures it must be done right._
|
| You're right, of course. But this must be balanced with the
| fact that applications evolve, and often need to change the
| type of data they store. How would you manage that if this
| is an iOS app? If SQLite didn't allow you to store a
| different type of value than the column type, you would
| have to create a new table and migrate data to a new table.
| Or create a new column and abandon the old column. Your app
| updates will appear to not be smooth to users. So it is a
| tradeoff. The choice SQLite made is pragmatic, even if it
| makes some of us that are used to the guarantees offered by
| traditional RDBMSs queasy.
| subhobroto wrote:
| > I understand why they wouldn't, but STRICT should be the
| default.
|
| No wait, what do you mean?
|
| As I mentioned at https://news.ycombinator.com/item?id=47619982
| - your application layer should be validating the data on its
| way in and out. I mention the two reasons I use for DB fall
| back
| SQLite wrote:
| Checking the datatype is not the same as validating. There is
| lots of data out there that is invalid, and yet still has the
| correct type. In fact, that is the common case.
|
| I dare say you will be hard pressed to find a dataset of
| significant size that doesn't have at least one invalid entry
| somewhere. Increasingly strict type rules will not fix that.
| kristianp wrote:
| There's table valued functions over json as well, as mentioned by
| [1].
|
| https://sqlite.org/json1.html#table_valued_functions_for_par...
|
| [1] https://news.ycombinator.com/item?id=47618597
| captn3m0 wrote:
| You can shorten your JSON queries using arrow notation in sqlite.
| SELECT settings -> '$.languages' languages FROM
| user_settings WHERE settings ->> '$.languages'
| LIKE '%"en"%';
|
| I use them heavily with my jekyll-sqlite projects. See
| https://github.com/blr-today/website/blob/main/_config.yml#L...
| for example.
___________________________________________________________________
(page generated 2026-04-02 23:01 UTC)