Hacker News Reader

Comment by flatline | Hacker News Reader

Sqlite is great but it is a poor substitute for a "real" database where you need any serious constraint enforcement at the database level. Yes, you can enable RI, but it's off by default. There is no field length constraint on text values, no native datetime, boolean, or guid types. You can't structurally alter tables without building a new one and copying the old.

If you actually need any of these things you can enforce them in code, by convention, but you're better off installing Postgres.

Also writes are serialized. That one really can't be worked around.

View on HN

> Sqlite is great but it is a poor substitute for a "real" database

This is true, and explicitly acknowledged by the author:

> SQLite does not compete with client/server databases. SQLite competes with fopen().

https://sqlite.org/whentouse.html

If you're deciding between Postgres and SQLite, in most cases you're doing it wrong (there are a few exceptions, such as serving a low-traffic almost-entirely-read dynamic website can be accomplished easily with either). Usually you want to choose between SQLite and "anything else that involves writing directly to the local filesystem".

I would disagree. The proper question is: "do I need a structured RDBMS or not?" Then if yes, which should I use? Just saying "SQLite!" starts to sound like the Regex quote from 1997: https://blog.codinghorror.com/regular-expressions-now-you-ha... A SQL system can do many wonderous things, but there's a wide range of pain and benefit across the different systems.

Think about requirements first, then tech/implementation approach.

But yeah, like everyone else, I often just start with SQLite anyway!

> There is no field length constraint on text values

SQLite has supported CHECK() for a while. So, for example, you can do:

    CHECK(LENGTH(col) IS BETWEEN 1 AND 16)

> Also writes are serialized.

Across multiple processes, but there is a shared cache and read uncommitted mode available otherwise.

Reply on news.ycombinator.com