[exceptione]

For me an important caveat is the typing. With all respect for the original author of SQLite -- he has done an outstanding job-- I think he underestimates the value of a good typing system. I have seen some databases that had all kinds of messy data. Back in the day MySQL was also quite loose with regards to checking data. Undoing the damage is in most cases not possible. For a business data is more important than code, so be strict up front.

I know, SQLite has added the option to enforce type checking. The authors still don´t believe in the value of it and the available types are quite limited and thus loose. I think this is something that pgsql got quite right, where you can have your domain types on the database level.

On the other hand, if you keep this as a replacement for your config file ( I thought this was the original purpose?), then yeah, you get an awesome deal. I wouldn't dare to build my business on it, just like I don´t believe in MongoDb and any untyped language for serious purposes.

[DemocracyFTW2]

As others have pointed out, there's the strict mode now which is still quite restricted (pun intended), but what you most often don't hear is that you can also use check constraints, as in

    sqlite> create table t ( id integer primary key, n integer check ( typeof( n ) = 'integer' ) );
    sqlite> insert into t ( n ) values ( 1 );
    sqlite> insert into t ( n ) values ( '1' );
    sqlite> insert into t ( n ) values ( true );
    sqlite> insert into t ( n ) values ( 'x' );
    Runtime error: CHECK constraint failed: typeof( n ) = 'integer' (19)
    sqlite> select * from t;
    ┌────┬───┐
    │ id │ n │
    ├────┼───┤
    │ 1  │ 1 │
    │ 2  │ 1 │
    │ 3  │ 1 │
    └────┴───┘
    sqlite> select ( select n from t where id = 1 ) = ( select n from t where id = 2 );
    1 // i.e. true

Check constraints do have the advantage over more classical types that additional constraints can be declared such as valid ranges for numerical types etc.

[unemployable]

>I think this is something that pgsql got quite right

I don't think so. For example, pgsql had an array type before it got JSON, so the drivers can't automatically convert arrays that you want to insert into JSON. With my SQLite ORM, you can just insert arrays and objects and it knows to convert them automatically to JSON.

I like that SQLite just has a few primitive types. My ORM will be able to build on top of them. For example, JavaScript will soon be adding new date types (Temporal), and I will create new types for that, which will be stored as text ultimately.

[phyrex]

SQLite has strict mode now

[krono]

Which is quite limited in scope and does not allow for boolean (faux-boolean, of course) or json columns. It also affects certain operations in ways that might not be immediately obvious.

Not sure if this has received any further work since its release.

https://sqlite.org/src/wiki/StrictMode

https://sqlite.org/stricttables.html

[exceptione]

I think I mentioned that, or I don´t understand what you mean.