6 ms·

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 a

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.
      - foepys 6y ago
        I don't see how having "NaN" or "undefined" in a number field makes any difference to just putting the value there. If you are using a strongly typed language, this will not be a problem. SQLite made a design choice in favor of simplicity. It's also missing basic date functions all together. The only way to compare dates is by using Unix Epoch.
        samatman 6y ago
        I use ISO 8601 full timestamps in UTC. They were carefully designed so that collation order is identical to temporal order. Which is convenient! If you need interval logic, though, SQLite won't help you, and epoch is the better choice. It's possible to solve some queries with a regex, but you won't love it.
  - OskarS 6y ago
    Chill out. This is a design choice to make it more viable to be used in dynamically typed languages. It's maybe not the design choice everyone would make, but it's not, like, insane: this is how all variables in Python/JavaScript/Ruby/whatever works. If you really care about this, adding strongly typed columns is trivial: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a70cb9567cb18200ba0b https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a7...
    - deleted 6y ago
      [deleted]
    - deleted 6y ago
      [deleted]
- RedShift1 6y ago
  That is... interesting...
- deleted 6y ago
  [deleted]
- fctorial 6y ago
  It's a feature: https://stackoverflow.com/questions/29476818/how-to-avoid-inserting-the-wrong-data-type-in-sqlite-tables https://stackoverflow.com/questions/29476818/how-to-avoid-in...
- yread 6y ago
  I don't really see it as such a problem. When interacting with the database you either use an ORM with your strongly-typed business objects or you write the SQL and use parameters (again strongly typed)
  - LunaSea 6y ago
    SQL parameters aren't strongly typed though and in any case SQLLite doesn't have stored procedures.
  - hans_castorp 6y ago
    > When interacting with the database you either use an ORM with your strongly-typed business objects If you have strongly-type business objects then why not have a strongly typed storage? If you code can control the constraints that a correct data type ensures, then why have "strongly type business objects" to begin with? Why not store everything in your code as strings as well? I see this misconception all the time. The database (and its data) lives way longer than most applications. And it's also a wrong to assume that there is always only one application accessing the database. Bulk loads are a typical case of secondary applications. Not choosing the proper data type in a relational database is a really bad decision and we see question on stackoverflow and similar sites on a weekly (if not daily) basis asking how to fix invalid data in those "un-typed" columns.
    - yread 6y ago
      I'm not saying it's useless to have type constraints in a database. Just that with a bit of care it's not such a big deal. In the end there will always be some business rules that are not constrained by the database. So, you always have to be a bit careful about what you store in it. Indeed not being careful and hoping that your types, constraints and triggers are going to save you is more risky > Not choosing the proper data type in a relational database is a really bad decision Well, then rejoice, you can't make this bad decision in SQLite because everything is +/- a number or a string
giantDinosaur 6y ago
I mean, you can represent everything under the sun as a string (or a very big int) but that's a level of operating where I find my brain starts to get fatigued for absolutely no real gain at all.
magicalhippo 6y ago
We need to store weights in kg with three decimal places. If we sum to get a total, the total should be correct according to those three digits. I'm sure we could get it to work in SQLite, but it sounds like we would have to have some layer of manual fudging that we couldn't forget about. In our current database we just use "numeric(16, 3)" and no worries.
- RedShift1 6y ago
  You could store it in grams as an integer.
- em500 6y ago
  At the risk of sounding glib, you could store everything as integer grams?
  - magicalhippo 6y ago
    Yes of course. That's why I said I'm sure we could get it to work. But then it has to be converted to kg for actual usage. Either directly in the queries or views. Having a fixed-point decimal type allows us to not think about once the table is created. Same issue with money. Most of the time we need to store monetary values with two decimal places (cents).
    - dmurray 6y ago
      What kind of application are you developing where there is so little middleware between the database storage layer and the user that converting to/from kilos adds significant additional complexity, but your users aren't technical enough to just deal with having weights in grams?
      - magicalhippo 6y ago
        These are electronic versions of official forms. The user interface and reports should show what is actually being sent or has been sent. Sure we could deal with it, but there are quite a number of tables due to different forms and messages, and then there's all the reports. Many custom ones thanks to to local officials wanting data from a certain customer in a certain way...
darksaints 6y ago
With this sort of perspective, why use a database at all? You can store literally any digital object as some sequence of bytes, and that's what file systems are for. At some point, you actually like for the software you use to actually have meaningful features.
- aembleton 6y ago
  Because sqlite let's you index and query the data without having to write your own layer to do that.
  - darksaints 6y ago
    Having rich data types allows a lot of things without having to write your own layer. Spatial indexes are extremely efficient, and I can't query geometries efficiently when I'm storing geometries as a blob. The same goes for dates, json, XML, ranges, etc. The problem with reserving specialized logic for the application layer is that it limits you to simplistic indexing schemes and you end up doing excessive IO and filtering in memory to get what you actually want. The idea that databases shouldn't have specialized datatypes is really only an idea that works in simplistic crud apps. The world is much bigger than that.
- isbvhodnvemrwvn 6y ago
  Querying filesystems is extremely primitive when compared to what sqlite can do.
  - darksaints 6y ago
    So is storing dates as integers, and json as strings.
    - RedShift1 6y ago
      But dates are integers (all databases store dates as an integer that is an offset from some fixed date), and JSONs are strings. SQLite has the functions to work with these datatypes (dates, JSON, etc...) but fundamentally they are not different from integers or strings.

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.
    - foepys 6y ago
      I don't see how having "NaN" or "undefined" in a number field makes any difference to just putting the value there. If you are using a strongly typed language, this will not be a problem. SQLite made a design choice in favor of simplicity. It's also missing basic date functions all together. The only way to compare dates is by using Unix Epoch.
      - samatman 6y ago
        I use ISO 8601 full timestamps in UTC. They were carefully designed so that collation order is identical to temporal order. Which is convenient! If you need interval logic, though, SQLite won't help you, and epoch is the better choice. It's possible to solve some queries with a regex, but you won't love it.
- OskarS 6y ago
  Chill out. This is a design choice to make it more viable to be used in dynamically typed languages. It's maybe not the design choice everyone would make, but it's not, like, insane: this is how all variables in Python/JavaScript/Ruby/whatever works. If you really care about this, adding strongly typed columns is trivial: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a70cb9567cb18200ba0b https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=9baffa184672a7...
  - deleted 6y ago
    [deleted]
  - deleted 6y ago
    [deleted]
RedShift1 6y ago
That is... interesting...
deleted 6y ago
[deleted]
fctorial 6y ago
It's a feature: https://stackoverflow.com/questions/29476818/how-to-avoid-inserting-the-wrong-data-type-in-sqlite-tables https://stackoverflow.com/questions/29476818/how-to-avoid-in...
yread 6y ago
I don't really see it as such a problem. When interacting with the database you either use an ORM with your strongly-typed business objects or you write the SQL and use parameters (again strongly typed)
- LunaSea 6y ago
  SQL parameters aren't strongly typed though and in any case SQLLite doesn't have stored procedures.
- hans_castorp 6y ago
  > When interacting with the database you either use an ORM with your strongly-typed business objects If you have strongly-type business objects then why not have a strongly typed storage? If you code can control the constraints that a correct data type ensures, then why have "strongly type business objects" to begin with? Why not store everything in your code as strings as well? I see this misconception all the time. The database (and its data) lives way longer than most applications. And it's also a wrong to assume that there is always only one application accessing the database. Bulk loads are a typical case of secondary applications. Not choosing the proper data type in a relational database is a really bad decision and we see question on stackoverflow and similar sites on a weekly (if not daily) basis asking how to fix invalid data in those "un-typed" columns.
  - yread 6y ago
    I'm not saying it's useless to have type constraints in a database. Just that with a bit of care it's not such a big deal. In the end there will always be some business rules that are not constrained by the database. So, you always have to be a bit careful about what you store in it. Indeed not being careful and hoping that your types, constraints and triggers are going to save you is more risky > Not choosing the proper data type in a relational database is a really bad decision Well, then rejoice, you can't make this bad decision in SQLite because everything is +/- a number or a string

giantDinosaur 6y ago

I mean, you can represent everything under the sun as a string (or a very big int) but that's a level of operating where I find my brain starts to get fatigued for absolutely no real gain at all.

magicalhippo 6y ago

We need to store weights in kg with three decimal places. If we sum to get a total, the total should be correct according to those three digits. I'm sure we could get it to work in SQLite, but it sounds like we would have to have some layer of manual fudging that we couldn't forget about. In our current database we just use "numeric(16, 3)" and no worries.

RedShift1 6y ago
You could store it in grams as an integer.
em500 6y ago
At the risk of sounding glib, you could store everything as integer grams?
- magicalhippo 6y ago
  Yes of course. That's why I said I'm sure we could get it to work. But then it has to be converted to kg for actual usage. Either directly in the queries or views. Having a fixed-point decimal type allows us to not think about once the table is created. Same issue with money. Most of the time we need to store monetary values with two decimal places (cents).
  - dmurray 6y ago
    What kind of application are you developing where there is so little middleware between the database storage layer and the user that converting to/from kilos adds significant additional complexity, but your users aren't technical enough to just deal with having weights in grams?
    - magicalhippo 6y ago
      These are electronic versions of official forms. The user interface and reports should show what is actually being sent or has been sent. Sure we could deal with it, but there are quite a number of tables due to different forms and messages, and then there's all the reports. Many custom ones thanks to to local officials wanting data from a certain customer in a certain way...

darksaints 6y ago

With this sort of perspective, why use a database at all? You can store literally any digital object as some sequence of bytes, and that's what file systems are for. At some point, you actually like for the software you use to actually have meaningful features.

aembleton 6y ago
Because sqlite let's you index and query the data without having to write your own layer to do that.
- darksaints 6y ago
  Having rich data types allows a lot of things without having to write your own layer. Spatial indexes are extremely efficient, and I can't query geometries efficiently when I'm storing geometries as a blob. The same goes for dates, json, XML, ranges, etc. The problem with reserving specialized logic for the application layer is that it limits you to simplistic indexing schemes and you end up doing excessive IO and filtering in memory to get what you actually want. The idea that databases shouldn't have specialized datatypes is really only an idea that works in simplistic crud apps. The world is much bigger than that.
isbvhodnvemrwvn 6y ago
Querying filesystems is extremely primitive when compared to what sqlite can do.
- darksaints 6y ago
  So is storing dates as integers, and json as strings.
  - RedShift1 6y ago
    But dates are integers (all databases store dates as an integer that is an offset from some fixed date), and JSONs are strings. SQLite has the functions to work with these datatypes (dates, JSON, etc...) but fundamentally they are not different from integers or strings.