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

▲ zimmi 4 hours ago | parent | next [-]

You can use strict tables: https://sqlite.org/stricttables.html

▲ pseudalopex 3 hours ago | parent [-]

This could enforce dates are strings. They wanted to enforce dates are dates I thought.

▲ simonw 3 hours ago | parent | next [-]

  create table events (
    id integer primary key,
    name text not null,
    event_date text not null check (
      -- YYYY-MM-DD
      event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      and date(event_date) is not null
      and date(event_date) = event_date
    )
  );

In Python that raises this error if the date is invalid:

  sqlite3.IntegrityError: CHECK constraint failed:
    event_date glob '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'

	▲	pseudalopex an hour ago \| parent \| next [-]
		I see. The strict tables page did not mention the date and time functions. Python would show the 1st line always? Or the failed part? This is unreasonable for a very common type I think.
	▲	an hour ago \| parent \| prev [-]
		[deleted]

▲ zimmi 3 hours ago | parent | prev [-]

Storing dates as INTEGER (year * 10000 + month * 100 + day, e.g. 20260530) is not so bad. Proper date / timestamp types would be great though.

	▲	mollerhoj 3 hours ago \| parent [-]
		"feels like using a 30years old database"

▲ RivieraKid 4 hours ago | parent | prev | next [-]

Yes, this is basically my only issue with SQLite. SQLite with a strict type system would be great.

	▲	3 hours ago \| parent [-]
		[deleted]

▲ 4 hours ago | parent | prev | next [-]

[deleted]

▲ formerly_proven 3 hours ago | parent | prev | next [-]

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

You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-c...

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

The IS NOT NULL is needed because date returns NULL for invalid dates; the other check because it also accepts Julian days (date('2026') is sometime during year 4707 BC).

	▲	pseudalopex 24 minutes ago \| parent [-]
		The price of compatibility could be a pragma.

▲ ThatMedicIsASpy 4 hours ago | parent | prev | next [-]

it's a single file.

▲ IshKebab 3 hours ago | parent | prev | next [-]

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.

▲ grodes 4 hours ago | parent | prev [-]

Read their docs