Hacker News Reader

Comment by petilon | Hacker News Reader

The downside of strict tables is that some data types are not available, such as Date.

Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else.

On the other hand, the main use case for SQLite is embedded databases. And that means only one application is using the database. In that scenario being able to evolve the schema (as opposed to creating a new database and copying the data over) can be seen as an advantage. The application's code knows what to expect in each column--including mixed data types.

View on HN

> The downside of strict tables is that some data types are not available, such as Date.

There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.

https://sqlite.org/datatype3.html

numeric is not a type it’s an affinity, the underlying types are real and integer. That is why numeric is not valid on strict tables.

The downside is that you loose the place to store the metadata that a column is supposed to store a date.

Which is why I prefer not to use them.

A comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify.

Domain types would be the best fix, but I do not think legacy tables are the second best.

A comment is not something I can get programmatically through `sqlite3_column_decltype` or `sqlite3_table_column_metadata`.

I can use either API to trigger bool and time handling in my Go driver.

https://github.com/ncruces/go-sqlite3/blob/main/driver/drive...

Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.

Yeah the parse rules for strict tables are annoying but it doesn't change the underlying semantics:

  sqlite> create table test (
    id integer primary key,
    created_at text default current_timestamp,
    flag integer not null default 0 check (flag in (0, 1))
  ) strict;
  sqlite> insert into test (flag) values (1);

  sqlite> select * from test;
  ╭────┬─────────────────────┬──────╮
  │ id │     created_at      │ flag │
  ╞════╪═════════════════════╪══════╡
  │  1 │ 2026-07-12 04:03:22 │    1 │
  ╰────┴─────────────────────┴──────╯

I don't understand what this has to do with strict mode? Yes those types should exist, but the workaround is to use an integer column for bool and a text column for date, whether or not you're using strict mode.

The point of strict mode is that the RDBMS performs data type validation when inserting/updating data. If you use a text column for storing dates, you can store any string in the column, not just valid dates. That's not strict.

SQLite has no date data type. Also SQLite has no way to call EXPLAIN on query and get the dummy type name either for arbitrary SELECT query, so you can't even infer it, if you were to use the dummy type name "DATE" or "DATETIME".

> some data types are not available, such as Date.

That’s not a type, you just get a numeric-affinity column.

Right, and that's a serious limitation when in strict mode.

The serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused.

You can use comments to preserve intent in strict mode, and that’s strictly more useful than fuzzy mode: it is richer, it is clearer, and it is no less reliable.

Reply on news.ycombinator.com