Hacker News

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 a day ago [ - ]

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

chrismorgan 14 hours ago [ - ]

I don’t like strict tables, because it conflates two concerns, with one somewhat good and one distinctly bad effect (in my assessments).

The somewhat good: it gets rid of most of the weak typing. It still coerces, in line with other SQL databases, but at least a column will only store values of one type. Personally I’d prefer to opt out of the coercion. And I don’t think most ways of writing SQL (in applications especially, but also manually) will ever actually trigger the strict differences. So it doesn’t feel like it’s actually particularly useful.

The distinctly bad: you’re limited to six datatype names. You may well now want external documentation or load-bearing comments in your schema, and your application code may be hobbled, if it liked to infer types based on the datatype name. For example, in sqlx, SQLite datatype BOOLEAN can automatically map to Rust type bool <https://github.com/transact-rs/sqlx/blob/75bc0487eb661da811b...>. Without that, you have to resort to a variety of less-pleasant techniques, such as selecting `done as "done: bool"` or overriding things in sqlx.toml.

I really, really wish they’d implement some form of CREATE TYPE and let that work with strict tables. If I could `CREATE TYPE BOOLEAN FROM INTEGER` and such, I’d be all in on strict tables.

pseudalopex 21 hours ago [ - ]

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

simonw 21 hours ago [ - ]

  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 19 hours ago [ - ]

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.

19 hours ago [ - ]

[deleted]

zimmi 21 hours ago [ - ]

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 21 hours ago [ - ]

"feels like using a 30years old database"

RivieraKid a day ago [ - ]

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

21 hours ago [ - ]

[deleted]

a day ago [ - ]

[deleted]

formerly_proven 21 hours ago [ - ]

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 18 hours ago [ - ]

The price of compatibility could be a pragma.

formerly_proven 9 hours ago [ - ]

It literally is? Changing the defaults shown in the PRAGMAs above would break backwards compatibility. SQLite is strictly semantically versioned and does not break backwards compatibility.

https://sqlite.org/versionnumbers.html

pseudalopex 3 hours ago [ - ]

Their complaint was SQLite's type system was poor. You said this was the price of compatibility. And the documentation of your recommended pragmas said nothing of types. They seemed unrelated helpful advice seemingly.

New types would break forward compatibility in SQLite's terms. 3.7.0 added WAL mode was their example of a forward compatibility break.[1] 3.y.0 could add better type system mode.

[1] https://sqlite.org/formatchng.html

ThatMedicIsASpy a day ago [ - ]

it's a single file.

IshKebab 21 hours ago [ - ]

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.

nickpeterson 6 hours ago [ - ]

A lot of oltp databases have modeling conventions for making read only reporting tables. Do tabular dbs have an inverse for transaction heavy data, that later gets batched to a read optimized structure? I kind of think most databases (even oltp workloads) really are read dominated. I feel like DuckDB is really close to working as the ‘main db’ for such systems, but my lack of knowledge for how to handle quick mutations bothers me. It feels like some form of temporal data modeling would solve it but I don’t know.

grodes a day ago [ - ]

Read their docs