SQLite

SQLite allows multiple writers. The constraint is that only one of how writers can be actively writing at any moment in time. If there are multiple processes wanting to write, they take turns. SQLite prevents two or more writes from running concurrently, so there is nothing the application needs to do to implement this, other than responding to SQLITE_BUSY replies from failed (concurrent) write attempts and retrying after a short delay.

Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.

At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.

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.

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...

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.

Minor correction: SQLite is not closed to contributions. It just has an unusually high bar to accepting contributions. The project does not commonly accept pull requests from random passers-by on the internet. But SQLite does accept outside contributed code from time to time. Key gates include that paperwork is in place to verify that the contributed code is in the public domain and that the code meets certain high quality standards.

As long as you are not using indexes on expressions where the expression value is a floating point number that is computed using one or more text->binary conversions, then you should be fine.

You do not recall correctly. There is more than 500K SLOC of test code in the public source tree. If you "make releasetest" from the public source tarball on Linux, it runs more than 15 million test cases.

It is true that the half-million lines of test code found in the public source tree are not the entirety of the SQLite test suite. There are other parts that are not open-source. But the part that is public is a big chunk of the total.

The Common Table Expression feature of SQL is very good at walking graphs. See, for example <https://sqlite.org/lang_with.html#queries_against_a_graph>.

If an I/O error happens with read()/write(), you get back an error code, which SQLite can deal with and pass back up to the application, perhaps accompanied by a reasonable error message. But if you get an I/O error with mmap, you get a signal. SQLite itself ought not be setting signal handlers, as that is the domain of the application and SQLite is just a lowly library. And even if SQLite could set signal handlers, it would be difficult to associate a signal with a particular I/O operation. So there isn't a good way to deal with I/O errors when using mmap(). With mmap(), you just have to assume that the filesystem/mass-storage works flawlessly and never runs out of space.

SQLite can use mmap(). That is a tested and supported capability. But we don't advocate it because of the inability to precisely identify I/O errors and report them back up into the application.

That depends on the query. SQLite tries to use LIMIT to restrict the amount of reading that it does. It is often successful at that. But some queries, by their very nature, logically require reading the whole input in order to compute the correct answer, regardless of whether or not there is a LIMIT clause.

Website updated with more precise wording. Sorry for the confusion.

No, Simon, we don't "refuse". We are just very selective and there is a lot of paperwork involved to confirm the contribution is in the public domain and does not contaminate the SQLite core with licensed code. Please put the false narrative that "SQLite refuses outside contributions" to rest. The bar is high to get there, but the SQLite code base does contain contributed code.

The story of Fossil:

Something better than CVS was needed. (I'm not being critical of CVS. I had to use the VCSes that can before, and CVS was amazing compared to them.) Monochrome gave me the idea of doing a distributed VCS and storing content in SQLite, but Monochrome didn't support sync over HTTP, which I definitely wanted. Git had just appeared, and was really bad back in those early years. (It still isn't great, IMO, though people who have never used anything other than Git are quick to dispute that claim.) Mercurial was... Mercurial. So I decided to write my own DVCS.

This turned out to be a good thing, though not in the way I expected. Since Fossil is built on top of SQLite, Fossil became a test platform for SQLite. Furthermore, when I work on Fossil, I see SQLite from the point of view of an application developer using SQLite, rather than in my usual role of a developer of SQLite. That change in perspective has helps me to make SQLite better. Being the primary developer of the DVCS for SQLite in addition to SQLite itself also give me the freedom to adapt the DVCS to the specific needs of the SQLite project, which I have done on many occasions. People make fun of me for writing my own DVCS for SQLite, but in the balance it was a good move.

Note that Fossil is like Git in that it stores check-ins an a directed acyclic graph (DAG), though the details of each node are different. The key difference is that Fossil stores the DAG in a relational database (SQLite) whereas Git uses a custom "packfile" key/value store. Since the content is in a relational database, it is really easy to add features like tickets, and wiki, and a forum, and chat - you've got an RDBMS sitting there, so why not use it? Even without those bonus features, you also have the benefit of being about to query the DAG using SQL to get useful information that is difficult to obtain from Git. "Detached heads" are not possible in Fossil, for example. Tags are not limited by filesystem filename restrictions. You can tag multiple check-ins with the same tag (ex: all releases are tagged "release".) If you reference an older check-in in the check-in comment of a newer check-in, then go back and look at the older check-in (perhaps you bisected there), it will give a forward reference to the newer one. And so forth.

SQLite runs about 5 times faster compiled with GCC (13.3.0) than it does when compiled with FIL-C. And the resulting compiled binary from GCC is 13 times smaller.

The top-level routine is here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6767-6818>. Small (32-bit) integer literals are compared numerically, here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6526>. They don't have to exactly match. So if you say "x=0x123" in the WHERE clause of the partial index and "x=291" in the WHERE clause of the query, and that will still work. However, 64-bit integer literals and floating-point literals are compared using strcmp(), here: <https://sqlite.org/src/info/aae36a5fbd17?ln=6570>, so they do need to match exactly, at least in the current implementation. Maybe that is something I should work on...

Yeah, but who ever writes "x=0.9" as a constraint on a partial index? Really? Don't you know you aren't suppose to compare floating point quantities for equality?

If P is the expression on the partial index and Q is the WHERE clause of the query, then the partial index is only usable if Q implies P for all possible assignments of variables. A theorem prover is needed to establish this. Every RDBMS has one. The one inside SQLite is not terribly bright, true enough. It leans toward usually little memory and few CPU cycles. It does not do a good job if P contains "x=0.9". On the other hand, SQLite's theorem prover is decent if P contains "x IS NOT NULL", because in actual practice, probably about 90% of partial index WHERE clauses are some variation on "x IS NOT NULL".

The partial index expression does not always have to be exactly the same as what is in the WHERE clause of the query. SQLite will always find the match if P is a subset of Q; if Q can be rewritten as "R AND P". But if P is "x IS NOT NULL" and Q does anything that restricts x from being NULL, for example if Q contains "x>0", then SQLite's theorem prover will find that match too, even if "IS NOT NULL" never appears in Q.

Will the theorem prover in SQLite get better someday? Perhaps. It has gotten better over the years. The question becomes, how much more code space and query-planning CPU cycles are you willing to spend to get a slightly better query planner? This trade-off is different for a client/server database engine. With SQLite being embedded, the trade-off tends to fall more on the side of "keep it simple". If you have followed SQLite over many years, you might have noticed it is shifting toward more complex decision making as memory becomes cheaper and CPUs get faster. It's a tricky balancing act to find the sweet spot.

I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that allows keywords to be used as identifiers as long as the identifier usage does not occur in a context where the keyword is allowed.

So, for example, you can used MATERIALIZED as a keyword in a common-table expression ("WITH xyzzy(a,b) AS MATERIALIZED (...)") but MATERIALIZED can also be used as a column or table name. Hence, the following SQL actually works in SQLite:

   WITH xyz(MATERIALIZED) AS MATERIALIZED(
     VALUES(1),(2),(3)
   )
   SELECT * FROM xyz;

Is there an SQL injection attack vulnerability there?

No, at least not if you put the SQL inside of {...}, which IIRC the documentation strongly recommends.

The $uid is passed down into SQLite. It is a single token recognized by the SQL parser itself. It does not get expanded by TCL. The $uid token serves the same roll as a "?" or ":abc" token would in some other SQL implementations. It is a placeholder for a value. The tclsqlite3.c interface first parses the SQL, then asks for the names of all of the placeholder tokens. Then it binds the values in TCL variables of the same name to those placeholders.

Indeed, this whole mechanism is specifically designed to make it easy to write SQL-injection-free code. As long as you put your SQL inside of {...}, you are completely safe from SQL injections.

If your TCL script includes SQL text inside of "...", then TCL will do the expansion and SQL injection is possible. But as long as the SQL text is inside of {...}, SQL injection is not possible.

The paper is from 2017. Fossil got chat support in 2021 and the developers now use Fossil-chat. https://fossil-scm.org/home/doc/trunk/www/chat.md

Fossil chat has the advantages that (1) it is fully encrypted and (2) it works from any web-browser, including on mobile phones.

Why not just have the value be the base 2 logarithm of the page size, i.e. a value between 9 and 16?

Yes, that would have been a better choice. Originally, the file format only supported page sizes between 512 and 32768, though, and so it just seemed natural to stuff the actual number into a 2-byte integer. The 65536 page size capability was added years later (at the request of a client) and so I had to implement the 65536 page size in a backwards compatible way. The design is not ideal for human readability, but there are no performance issues nor unreasonable code complications.

The page size value is not the only oddity. There other details in the file format that could have been done better. But with trillions of databases in circulation, it seems best to leave these minor quirks as they are rather than to try to create a new, more perfect, but also incompatible format.

Dr. Hipp has said several times that nobody expected a weakly-typed database to achieve the pervasiveness that is observed with SQLite.

I don't remember ever saying that. Rather, see https://sqlite.org/flextypegood.html for detailed explanation of why I think flexible typing ("weak typing" is a purgative and inaccurate label) is a useful and innovative feature, not a limitation or a bug. I am surprised at how successful SQLite has become, but if anything, the flexible typing system is a partial explanation for that success, not a cause of puzzlement.

sqlite in its default (journal_mode = DELETE) is not durable.

Not true. In its default configuration, SQLite is durable.

If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.

This behavior is what most applications want. You'll never get a corrupt database, even on a power loss or similar. You might lose a transaction that happened within the past second or so. So if you cat trips over the power cord a few milliseconds after you set a bookmark in Chrome, that bookmark might not be there after you reboot. Most people don't care. Most people would rather have the extra day-to-day performance and reduced SSD wear. But if you have some application where preserving the last moment of work is vital, then SQLite provides that option, at run-time, or at compile-time.

When WAL mode was originally introduced, it was guaranteed durable by default, just like DELETE mode. But people complained that they would rather have increased performance and didn't really care if a recent transaction rolled back after a power-loss, just as long as the database didn't go corrupt. So we changed the default. I'm sorry if that choice offends you. You can easily restore the original behavior at compile-time if you prefer.

Update: This enhancement is now on trunk and will be included in the 3.50.0 release of SQLite due out in about four weeks.

I just checked in an experimental change to sqlite3_rsync that allows it to work on non-WAL-mode database files, as long as you do not use the --wal-only command-line option. The downside of this is that the origin database will block all writers while the sync is going on, and the replicate database will block both reads and writers during the sync, because to do otherwise requires WAL-mode. Nevertheless, being able to sync DELETE-mode databases might well be useful, as you observe.

If you are able, please try out this enhancement and let me know if it solves your problem. See <https://sqlite.org/src/info/2025-05-01T16:07Z> for the patch.

I claim the edit is neither a hardening nor a softening but rather a clarification and an attempt to better explain the original intent.

SQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.

Seeing this pop up on HN prompted me to review the 13-year-old document, and bring it up to date with the latest enhancements to SQLite. Specifically, three ways for safely backing up a live SQLite database are now listed in section 1.2. See the latest draft at https://sqlite.org/draft/howtocorrupt.html

Author here:

My title was imprecise and unclear. I didn't mean that you should raise errors if CRLF is used as a line terminator in (for example) HTTP, only that a bare NL should be allowed as an acceptable line terminator. RFC2616 recommends as much (section 19.3 paragraph 3) but doesn't require it. The text of my proposal does say that CRLF should continue to be accepted, for backwards compatibility, just not required and not generated by default. I failed to make that point clear.

My initial experiments suggested that this idea would work fine and that few people would even notice. Initially, it appeared that when systems only generate NL instead of CRLF, everything would just keep working seamlessly and without problems. But, alas, there are more systems in circulation that are unable to deal with bare NLs than I knew. And I didn't sell my idea very well. So there was breakage and push-back.

I have revised the document accordingly and reverted the various systems that I control to generate CRLFs again. The revolution is over. Our grandchildren will have to continue dealing with CRLFs, it seems. Bummer.

Thanks to everyone who participated in my experiment. I'm sorry it didn't work out.

Correct.

"Secure delete" is sufficient to prevent forensic traces from persisting in the database file itself. So you could, for example, send the database file over a TCP/IP link after a secure delete and no deleted data would be transmitted.

"Secure delete" is sufficient to remove traces from the database file itself, but it is not sufficient to remove deleted data from the underlying SSD. That was known from the beginning and should be mentioned in the documentation; if it is not then the documentation needs to be enhanced.

Just to clear up the error in the parent post: SQLite has native blobs, floats, and integers, not just strings. It doesn't have a bunch of other types for things like dates and JSON - you just represent those things using the native times of integer, float, string or blob. But it is not limited to only strings. This has been true for 20 years.