logoalt Hacker News

petilontoday at 7:14 PM3 repliesview on HN

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.


Replies

e2letoday at 7:34 PM

> 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

show 3 replies
Ciantictoday at 7:21 PM

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

masklinntoday at 7:34 PM

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

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

show 1 reply