alt Hacker NewsI started using SQLite for a home project after years of reading about it, I was shocked at the poor type system coming from Postgres. It is really inferior, not sure why it gets so much praise.
https://sqlite.org/datatype3.html
https://www.postgresql.org/docs/current/datatype.html
Working with date/time feels like using a 30years old database, nothing is enforced at insert. Really someone needs to explain why so many people like it.
Replies
Yes, this is basically my only issue with SQLite. SQLite with a strict type system would be great.
This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:
PRAGMA journal_mode = WAL
PRAGMA foreign_keys = ON
# Something non-null
PRAGMA busy_timeout = 1000
# This is fine for most applications, but see the manual
PRAGMA synchronous = NORMAL
# If you use it as a file format
PRAGMA trusted_schema = OFF
Also use strict tables. https://www.sqlite.org/stricttables.html
While it has bad ergonomics, you can also use CHECK constraints. For example, using sqlite's built in date support, it's possible but awkward:
CHECK (
date(my_date_col) IS NOT NULL
AND my_date_col = date(my_date_col)
)
it's a single file.
It gets praise because of stuff other than the type system.
I agree it is disappointing, especially before strict tables.
You should check out DuckDB which is basically SQLite but with proper types. Although it is also OLAP (struct of arrays) rather than OLTP (array of structs) which may have worse performance for typical SQLite loads. In practice I doubt it matters if you have an application where you're considering either.
Read their docs
You can use strict tables: https://sqlite.org/stricttables.html