3 ms·

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 R

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.

akira2501 9y ago
> 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.
zackelan 9y ago
> 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 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".
- mwexler 9y ago
  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-have-two-problems/ 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!

akira2501 9y ago

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

zackelan 9y ago

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

mwexler 9y ago
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-have-two-problems/ 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!