4 ms·

The limited type system and loose data validation always prevented me of using SQLite. It's a nice data store but a poor database in my opinion.

hans_castorp 6y ago
> The limited type system and loose data validation always prevented me of using SQLite. This. "limited type system" is actually an exaggeration in my opinion. The "data types" seem to only serve documentation purposes. You can always store any value in any column regardless of the "data type"
- tpetry 6y ago
  Wait? Your can for example put an unicode string into integer or number columns? That can‘t be true? I mean, really? ...
  - ludwigschubert 6y ago
    Yeah. Execute program has a short and sweet explanation with in-browser demonstration: https://www.executeprogram.com/courses/sql/lessons/no-type-enforcement-in-sqlite https://www.executeprogram.com/courses/sql/lessons/no-type-e...
  - IshKebab 6y ago
    It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types. Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
  - hans_castorp 6y ago
    > Wait? Your can for example put an unicode string into integer or number columns? Yes https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
  - IshKebab 6y ago
    It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types like `BLIB` or whatever. Foreign key constraints are also not enforced by default but there is at least an option to turn that on. I'm keeping a close eye on DuckDB which looks like a modern saner version of SQLite.
ludwigschubert 6y ago
Is anyone aware of efforts to build a a “strict mode”-type of setting, or a SQLite competitor? I’d love to use a single file as db with stricter type enforcement.
- attilakun 6y ago
  It's already possible: https://dba.stackexchange.com/questions/203220/sqlite-what-is-the-use-of-specifying-data-types/222271#222271 https://dba.stackexchange.com/questions/203220/sqlite-what-i...
nojito 6y ago
Why wouldn't you validate before inserting it into a database?
- colonwqbang 6y ago
  If a tool can do something automatically (type checking, other constraints) then it is often nicer to let the tool do it for you, instead of doing it manually. Sqlite is very unusual in that it uses static type declarations, but it doesn't actually enforce them. That can be quite unexpected. See also: "Why wouldn't you check the type of your variable before passing it to a function?"
  - nojito 6y ago
    This doesn't make any sense. Why do you want to fail on insertion and not fail during the acquisition step itself? You have to write tons of wrappers and boilerplate to handle those insertion failures instead of cleaning up your data during acquisition. >See also: "Why wouldn't you check the type of your variable before passing it to a function?" Completely different workflow. The types of data is known during acquisition.
    - LunaSea 6y ago
      But programming language types are different than SQLite types so that's already a mismatch that could introduce errors. Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database. Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation.
      - nojito 6y ago
        >But programming language types are different than SQLite types so that's already a mismatch that could introduce errors. No...because they can be boiled down to the sqlite types and again it doesn't matter if you properly validate the data on insertion which you should be doing. >Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database. No...you don't validate queries..you validate insertion >Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation. You don't need to depend on it...because you validated on insertion.
coliveira 6y ago
If you use a typed language (like Java), this is a non-issue. The data will be written in the correct format. But if you use a non-typed language (like Python), this is also not a problem, by the very nature of dynamic languages. So I don't see where the problem is, unless you use SQLite from the console. Moreover, they have constraints that can be added to the db.

hans_castorp 6y ago

> The limited type system and loose data validation always prevented me of using SQLite. This. "limited type system" is actually an exaggeration in my opinion. The "data types" seem to only serve documentation purposes. You can always store any value in any column regardless of the "data type"

tpetry 6y ago
Wait? Your can for example put an unicode string into integer or number columns? That can‘t be true? I mean, really? ...
- ludwigschubert 6y ago
  Yeah. Execute program has a short and sweet explanation with in-browser demonstration: https://www.executeprogram.com/courses/sql/lessons/no-type-enforcement-in-sqlite https://www.executeprogram.com/courses/sql/lessons/no-type-e...
- IshKebab 6y ago
  It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types. Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
- hans_castorp 6y ago
  > Wait? Your can for example put an unicode string into integer or number columns? Yes https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
- IshKebab 6y ago
  It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types like `BLIB` or whatever. Foreign key constraints are also not enforced by default but there is at least an option to turn that on. I'm keeping a close eye on DuckDB which looks like a modern saner version of SQLite.

ludwigschubert 6y ago

Is anyone aware of efforts to build a a “strict mode”-type of setting, or a SQLite competitor? I’d love to use a single file as db with stricter type enforcement.

attilakun 6y ago
It's already possible: https://dba.stackexchange.com/questions/203220/sqlite-what-is-the-use-of-specifying-data-types/222271#222271 https://dba.stackexchange.com/questions/203220/sqlite-what-i...

nojito 6y ago

Why wouldn't you validate before inserting it into a database?

colonwqbang 6y ago
If a tool can do something automatically (type checking, other constraints) then it is often nicer to let the tool do it for you, instead of doing it manually. Sqlite is very unusual in that it uses static type declarations, but it doesn't actually enforce them. That can be quite unexpected. See also: "Why wouldn't you check the type of your variable before passing it to a function?"
- nojito 6y ago
  This doesn't make any sense. Why do you want to fail on insertion and not fail during the acquisition step itself? You have to write tons of wrappers and boilerplate to handle those insertion failures instead of cleaning up your data during acquisition. >See also: "Why wouldn't you check the type of your variable before passing it to a function?" Completely different workflow. The types of data is known during acquisition.
  - LunaSea 6y ago
    But programming language types are different than SQLite types so that's already a mismatch that could introduce errors. Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database. Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation.
    - nojito 6y ago
      >But programming language types are different than SQLite types so that's already a mismatch that could introduce errors. No...because they can be boiled down to the sqlite types and again it doesn't matter if you properly validate the data on insertion which you should be doing. >Also, there is much more boilerplate in man-in-the-middle-ing each database query to validate data rather than have a single entry point which validate the data types which is the database. No...you don't validate queries..you validate insertion >Also, using any in-database feature like functions or triggers should also have this security baked in but won't due to SQLite's missing validation. You don't need to depend on it...because you validated on insertion.

coliveira 6y ago

If you use a typed language (like Java), this is a non-issue. The data will be written in the correct format. But if you use a non-typed language (like Python), this is also not a problem, by the very nature of dynamic languages. So I don't see where the problem is, unless you use SQLite from the console. Moreover, they have constraints that can be added to the db.