5 ms·

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 v

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.

phyrex 4y ago
SQLite has strict mode now
- exceptione 4y ago
  I think I mentioned that, or I don´t understand what you mean.
- krono 4y ago
  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/src/wiki/StrictMode https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
unemployable 4y ago
>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.
DemocracyFTW2 4y ago
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.

phyrex 4y ago

SQLite has strict mode now

exceptione 4y ago
I think I mentioned that, or I don´t understand what you mean.
krono 4y ago
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/src/wiki/StrictMode https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html

unemployable 4y ago

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

DemocracyFTW2 4y ago

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.