9 ms·

I will re-consider it, once they have proper data types and data type checking.

jraph 6y ago
This aspect will probably never change: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing
- rini17 6y ago
  It is possible to create check constraints that do the type checking. Actually, if they shipped some predefined ones and said "if you want strong type checking do this", like, syntax to automatically populate the table columns with type check constraints, it would be IMO perfectly backward compatible.
- darksaints 6y ago
  At the very least we could get strict versions of data types, or some sort of key word used in the ddl to specify strict typing.
  - attilakun 6y ago
    This might help: https://dba.stackexchange.com/a/222271 https://dba.stackexchange.com/a/222271
RedShift1 6y ago
When I started a project with SQLite, the available data types struck me as odd (coming from MySQL), but as I learned I started asking the question: are there any more fundamental data types other than null, int, real, text and blob? For example dates are just a facade for an integer of some kind, JSON is really just text adhering to certain formatting rules, booleans are usually stored as some kind of byte anyway so why not drop that abstraction? With this limited set of datatypes it really makes you think harder about the data you are processing, because in the end all your data is one of these types anyway.
- hans_castorp 6y ago
  > are there any more fundamental data types other than null, int, real, text and blob? Those type names are just hints, they don't constrain the valid values in any way. https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
  - dietr1ch 6y ago
    I can't believe that runs. What a misfeature.
  - clon 6y ago
    You just put me off Sqlite for life. I can't believe someone thought this to be a good idea.
    - foepys 6y ago
      SQLite just does what JavaScript and other weakly typed languages do. The developer is responsible for what they input. If type checks are a must, SQLite is just not for you, easy as that.
      - LunaSea 6y ago
        But event JavaScript will reject data if it's considered not valid for a type. It won't throw but it will set an "invalid" / "NaN" / etc. value.
deleted 6y ago
[deleted]
shakkhar 6y ago
FWIW, we use https://cgsql.dev/ https://cgsql.dev/ to solve the type issue, among others. I totally understand that this is not for everyone.

This aspect will probably never change: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing

rini17 6y ago
It is possible to create check constraints that do the type checking. Actually, if they shipped some predefined ones and said "if you want strong type checking do this", like, syntax to automatically populate the table columns with type check constraints, it would be IMO perfectly backward compatible.
darksaints 6y ago
At the very least we could get strict versions of data types, or some sort of key word used in the ddl to specify strict typing.
- attilakun 6y ago
  This might help: https://dba.stackexchange.com/a/222271 https://dba.stackexchange.com/a/222271

RedShift1 6y ago

When I started a project with SQLite, the available data types struck me as odd (coming from MySQL), but as I learned I started asking the question: are there any more fundamental data types other than null, int, real, text and blob? For example dates are just a facade for an integer of some kind, JSON is really just text adhering to certain formatting rules, booleans are usually stored as some kind of byte anyway so why not drop that abstraction? With this limited set of datatypes it really makes you think harder about the data you are processing, because in the end all your data is one of these types anyway.

hans_castorp 6y ago
> are there any more fundamental data types other than null, int, real, text and blob? Those type names are just hints, they don't constrain the valid values in any way. https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
- dietr1ch 6y ago
  I can't believe that runs. What a misfeature.
- clon 6y ago
  You just put me off Sqlite for life. I can't believe someone thought this to be a good idea.
  - foepys 6y ago
    SQLite just does what JavaScript and other weakly typed languages do. The developer is responsible for what they input. If type checks are a must, SQLite is just not for you, easy as that.
    - LunaSea 6y ago
      But event JavaScript will reject data if it's considered not valid for a type. It won't throw but it will set an "invalid" / "NaN" / etc. value.

deleted 6y ago

[deleted]

shakkhar 6y ago

FWIW, we use https://cgsql.dev/ https://cgsql.dev/ to solve the type issue, among others. I totally understand that this is not for everyone.