[exceptione]

SQLite has no real types. Use SQLite if data quality is of no concern. The applicability domain of SQLite is far smaller than people think.

If you want to improve on JSON, you would have to go into an other direction. Maybe something like postgis would be helpful for extremely large canvasses.

JSON Schema is pretty powerful by the way. Checkout the documentation. SQLite is absolutely no match there. What Sqlite could bring is speed, but I dont see how in the contxt of canvas it would be of any help here.

[asa400]

SQLite's types are quite loose by default, no doubt.

There is a somewhat recent STRICT mode that strengthens them: https://www.sqlite.org/stricttables.html

[samatman]

Exactly so. SQLite also has a rich collection of CHECK constraints which can raise errors if data is not to your liking in some fashion, this includes validating JSON. Not a JSON schema, admittedly, although (just like for Postgres) this is available as an extension. https://github.com/asg017/sqlite-jsonschema

Saying that SQLite doesn't have "real types" is simply false. If one doesn't want to learn how to use a tool, blaming it for that failure is poor form.

[exceptione]

The author of SQLite is quite open about it. The lack of typing has been part of the design from the beginning. Sqlite has hardly any types:

    INT
    INTEGER
    REAL
    TEXT
    BLOB
    ANY 

Of course one can program all kind of check constraints, like one can program all kinds of value validations in javascript.

Unfortunately, that is not the same as typing. Sqlite lacks typing because, as the sqlite author explains in the docs, flexibility is the goal. He continues with "But other developers are aghast", and so strict tables where born, but you can clearly see this cannot overcome real concerns. Try to look for the DATETIME datetype in that list.

Deep bow to sqlite, its design goal was to be the ini file replacement and it has outperformed itself on that one.

Thanks for the extension link. Although constraints are not reuable type definitions, they would still be helpful in this context. Pity that json doesn't have a type for dates, one has to rely on string formats: https://json-schema.org/understanding-json-schema/reference/...

[samatman]

> Of course one can program all kind of check constraints, like one can program all kinds of value validations in javascript.

I don't consider this a valid distinction where databases are concerned.

If you define a datatype in an ordinary SQL database, and try to pass it invalid data, it will fail at runtime. How else could it work? There's no compile-time interaction between the value and the database.

If you define a field as BOOLEAN in Postgres, then the value must be 0 or 1, or the database will refuse to write it and return an error. In SQLite this is spelled INTEGER NOT NULL CHECK (col_name = 0 or col_name = 1). More verbose? Yes. Identical semantics? Also yes.

It would certainly be nice if SQLite had a datetime validator that could be used as a check constraint! Hold, up, I got you fam: CHECK(date IS strftime('%Y-%m-%d', date)). If you need a different format, those are,, available.

I guess it has a date type after all! Learn something new every day.

[exceptione]

Types are a contract, that both parties can understand. This notion is not captured by operational semantics.

Parties that do not read the database contract will get caught by runtime validation. However, any code that targets the database contract could make use of that contract, e.g. with scaffolding. This might enable tighter integration with type checking in the client program.

What you propose is not a contract, your code doesn't understand it, so now you introduce a new problem. (I think that is why the sqlite author doesn't seem to be too enthusiastic about bolting on strictness checks, as its potential is really limited and it contradicts its design).

[wizzwizz4]

What are operational semantics to you, if not a contract?

[asa400]

Heh, I haven't seen that particular datetime constraint before, thanks for that!

[samatman]

If there's a risk of properly-formatted but nonetheless invalid dates, like 2024-03-34, one can do this: DATE(date_column, '+0 days') IS date_column). The '+0 days' causes 2024-03-34 to normalize to 2024-04-03 (this is part of the ISO standard!) and therefore the check fails.

Admittedly these sorts of tricks are obscure, if by 'obscure' we mean "you have to feed a search engine a string like 'Validate SQLite datetime' and read some sources". But to reiterate my point slightly differently, the verbosity of these CHECK constraints doesn't indicate that they're doing anything different from a "typed database".

Out of curiosity, I asked ChatGPT, which got the "well formed" version, when I pointed out it would accept 2023-03-34, it gave a correct explanation of what SQLite would do with that date, and suggested `CHECK(date_column = strftime('%Y-%m-%d', date_column))`, which is more satisfying than the other one, and has the same effect. Really gotta keep an eye on the chatbot.

[wizzwizz4]

Aren't these the types?

  NULL
  INT(EGER)
  REAL
  TEXT
  BLOB
  (INTEGER PRIMARY KEY)

[dolmen]

https://sqlite.org/flextypegood.html