this is one of those beautiful yet terrifying aspects of SQLite. After all, you could stick another whole SQLite database into an attribute. Purposefully or by accident.
This true observation gets at a variation on the point of The Famous Article (TFA):
yes, but what reasonable level of effort shall be applied toward hypothetical 'problems'?
As you note, sticking a whole database into an attribute could be a design element for an application.
I'd always thought that stronger type systems had to do with performance and storage optimization, but TFA didn't delve into that.
> The presence of egregious errors (such as text in an integer column) is a convenient early warning signal that something is amiss.
We should be reporting or logging those errors. Accumulating them in our permanent storage will cause recurring bugs when reused until they are manually discovered and somehow repaired.
> Without flexible typing, such a table would need to be more complex, with separate columns for each possible type of data
That's what enums/unions are for.
> It has become a point of doctrine among many programmers that the best way to prevent application bugs is strict type enforcement. But I find no evidence in support of this.
I find it hard to believe that static typechecking is not advantageous compared to checking for every returned value whether it's undefined or null or a string instead of the expected array. Flexible typing turns many compiler errors into runtime errors. Only one of those is guaranteed to be caught.
There's a reason TypeScript got popular, and it's not the additional build step.
I don't think providing unions or algebraic data types are well supported in most RDBMS systems, they're such a pain to build.
> I find it hard to believe that static type checking
It's not static type checking. Errors happen in runtime, not compile time. In compile time there's no data yet.
If you need static type checking you're most likely to do it in your programming languages. If you want static type checking against the database schema you need languages with type providers like F#.
If you want to ensure business invariants, it's not enough to just use rigid tables. Plus those invariants are better to be enforced in the model because you can write better tests against them. There's no point to write it twice as there would be inconsistencies.
As a personal experience, being unable to express algebraic data type often leads to bad design/modeling, as developers don't have to tool to model things are they are, they have to use subpar workarounds.
Yup, the only practical difference between the behavior of SQLite and other databases is whether you get an error if you try to insert values that can't be converted to the declared column type. You'll need application logic to handle such errors in any case, so in principle I tend to agree with the SQLite devs that more rigid type checkin doesn't really buy you much.
Though in practice I think the more lax behavior makes it easier to skip or defer error handling. Also, devs need more awareness of if these (differences in) behaviors. It seems an alarming number of them don't.
I feel like you are talking about types in programming languages but Object–relational impedance mismatch is still a thing and I'm not sure if you want a solution (for the mismatch) at DB level.
> I find it hard to believe that static typechecking is not advantageous compared to checking for every returned value whether it's undefined or null or a string instead of the expected array.
+1. A simple way to think of this is that realize that strict type checking significantly reduced the number of possible codepaths, so the cognitive load on you is much smaller making it less likely for you to make mistakes.
With static typing, what you should also practice is using the strictest type possible, e.g., don't use a pointer if null has no meaning for you, don't use a vector if the size of the continer is fixed, etc.
I want to trust them because they’re known for really solid engineering. But, this sounds absolutely terrible to me, and their justification for it was truly weak.
> By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs.
I don't follow the logic here. If a more rigid type system produces an error, wouldn't that surface the error, not suppress it? What am I missing?
Author is talking about data errors vs type errors.
Data errors will always pass through regardless if it's invalid. Eg. an application bug that overflowed the int will still be happily accepted into an integer typed column even with a check contraint value > 0.
In the ANYTHING case you would have something like -205 in there instead.
For what it's worth I don't really agree with this post at all though.
Many databases benefit significantly from being able to optimize knowing that a column is a fixed-width integer, for instance (or a timestamptz--especially when it comes to sorting and indexing!). SQLite has not historically cared much about these kinds of optimizations because it does not compete particularly well on execution speed with other databases for anything but very simple queries.
Can't say I agree on the notion that dynamic languages doesn't gain anything with strict typing.
I have written a lot of PHP over the years and PHP has gradually introduced type hints for function parameters and return values. For me this has reduced the number of type errors I have to almost zero and with that data errors.
The thing is with type errors in dynamic languages is that it usually hides an error branch. Example, we open a file handle with fopen(), it can return a handle or false on fail.
If that would fail (false) with old style PHP you could continue pass that false file handle to other file functions like fread(), fwrite() etc without knowing they all failed. So in this case there would be no data at all either to be transformed, mangled or inserted, it wouldn't exist at all because it couldn't be written, that is also a form of data pollution (fopen() can swapped with sqlite equivalent for the same argument)
How about types and preventing data pollution? Take dates as a good example. In old MySQL you could insert zero dates (0000-00-00) or invalid dates (2004-04-31), with better sql modes you can prevent that for happening, a type enforcement.
Having non existing dates in your database will give trouble in the future, how should you handle them for calculating something like time periods for subscriptions? Do you really take into account when writing your SQL query that some dates are not valid dates at all?
Having inherited lots of old PHP project I can't say enjoy the non-strict typing in both code and database, database columns are usually filled with all sorts of garbage, like 0,1, -1, 2, true, false, null, "NULL", "Yes", "No" or even localized strings "Ja", "Nej". This will always lead to more complicated SQL queries or code logic down the line.
The article argues that you can easily spot data pollution in a column, that assumes that the developer actually looks at it, but if everything "works" why should they?
I want to do the opposite and create my own types, assume that we accept user input and to make sure that the user input doesn't contain garbage I can create my own types, like MultiByteText255, AsciiText64, HtmlFragment etc and that should be enforced both in code and storage. These types can both enforce the contents of it and also make sure to properly store and output it. And if I want I can inherit to specialized types like UserName extends MultiByteText255. Everyone using functions that takes a UserName type is forced to adhere to it.
Interesting concept, but a guess that a file system is at a lower abstraction level than a database.
Only argument that I can sort of understand for flexible types is that it is easier to get started or for smaller projects, like some batch script, but as soon as you have a large system you want strict control of what data you write to the database. Otherwise your schema migrations will be a nightmare.
I think what you are saying makes sense for a lot of projects. but point I was trying to make is that there are projects that have to deal with highly dynamic and complex data structures. I used file system as an example but any trees/graphs are pretty hard to express as a simple type.
I guess another scenario could be if you need to import massive amount of schemaless data, like csv imports, then types can be a problem if the sender does not have clear definition of what type the data has, common problem when dealing with non-engineers that hands you something from excel.
Thus too much of strict types can be a problem, if I compare PHP to Java, Java has a tendency to become type bureaucracy, where I find PHP to be a sane middle ground where you specify types on your functions , i.e. your api, but not within.
But writing data is not equivalent of passing data around in your application, as soon you have stored something it is hard to go back and change it because you have usually lost all context from when you wrote it, where as changing type in program code can almost always be done.
As a frequent user of sqlite I'm looking forward to try out STRICT tables, it will be interesting if that actually improves anything from an application perspective or if it becomes more cumbersome.
I have a hard time imagining that TCL would not have the same problem that PHP, Python and JavaScript have with types. If you're trying to add a string and a number, the language has to either throw an error or implicitly turn that into concatenation, propagating the string to more expressions that expected a number. At a cursory glance, it looks like TCL throws an error.
In that case, I can't imagine writing any significant amount of TCL and not having reached a point where you have a runtime error that would have been prevented had you had a static type system.
My guess is that Dr. Hipp’s (SQLite author) meticulous and prolific use of testing ferrets out so many bugs that the ones that are left wouldn’t be caught by static typing.
In contrast, folks who rely heavily on static type systems may not write so many tests. For example I put more energy into making sure that my Haskell types capture the problem I’m trying to solve, so that the compiler flags my mistakes and exposes poor assumptions.
I'd say the idea is that the implementation of a command/procedure needs to do the checking. In your example, arithmetic addition is usually provided by expr [0], which checks the expression it is fed before trying to calculate anything.
When you would like to concatenate strings (including numbers), you would use quoting or commands/procs made for that purpose instead.
I had to work in a very large python codebase before I started to realize how helpful strict typing can be. But for quick and small programs, I do enjoy pythons relatively loose typing.
I wonder what kind of application I need to try, before I realize the advantage of flexible typing in a database.
What happens when I take the mean of integers and strings? Won't I be surprised when I my sum of integers return a real number?
> It has become a point of doctrine among many programmers that the best way to prevent application bugs is strict type enforcement. But I find no evidence in support of this.
I am a fan of the SQLite authors for pioneering heavy-duty engineering, but this, like their hatred of threads [1], does not sound good to me and really puts me off.
It also sounds like a cop out; sure, they may not have found evidence, but did they really look?
Personally, with the success of Rust, TypeScript, and other stricter languages, and the fact that TDD seems to be about recreating the advantages of static typing for dynamically-typed languages, I think there is plenty of evidence.
Of course, "best" is doing a lot of the heavy lifting in the quote above, so I would define "best" as removing the most bugs per unit of effort. Static typing seems to fit that bill.
One of the HN threads I'm assuming they're referring to is the recent discussion of strict tables¹, which reads like the other side of this document. It may be interesting to read the comments on the other side directly.
Although, frankly every SQLite thread basically devolves in to the same thing as the TFA notes.
Why not let the users choose? Have a TEXT type that only allows you to store text, an INTEGER type that only allows you to store an integer, TEXT | INTEGER (or whatever) that allows you to store both, ANY that allows you to store anything, etc. ... Don't force your preference on the user!
28 comments
[ 2.4 ms ] story [ 77.1 ms ] threadAs you note, sticking a whole database into an attribute could be a design element for an application.
I'd always thought that stronger type systems had to do with performance and storage optimization, but TFA didn't delve into that.
We should be reporting or logging those errors. Accumulating them in our permanent storage will cause recurring bugs when reused until they are manually discovered and somehow repaired.
That's what enums/unions are for.
> It has become a point of doctrine among many programmers that the best way to prevent application bugs is strict type enforcement. But I find no evidence in support of this.
I find it hard to believe that static typechecking is not advantageous compared to checking for every returned value whether it's undefined or null or a string instead of the expected array. Flexible typing turns many compiler errors into runtime errors. Only one of those is guaranteed to be caught. There's a reason TypeScript got popular, and it's not the additional build step.
I don't think providing unions or algebraic data types are well supported in most RDBMS systems, they're such a pain to build.
> I find it hard to believe that static type checking
It's not static type checking. Errors happen in runtime, not compile time. In compile time there's no data yet.
If you need static type checking you're most likely to do it in your programming languages. If you want static type checking against the database schema you need languages with type providers like F#.
If you want to ensure business invariants, it's not enough to just use rigid tables. Plus those invariants are better to be enforced in the model because you can write better tests against them. There's no point to write it twice as there would be inconsistencies.
As a personal experience, being unable to express algebraic data type often leads to bad design/modeling, as developers don't have to tool to model things are they are, they have to use subpar workarounds.
Though in practice I think the more lax behavior makes it easier to skip or defer error handling. Also, devs need more awareness of if these (differences in) behaviors. It seems an alarming number of them don't.
+1. A simple way to think of this is that realize that strict type checking significantly reduced the number of possible codepaths, so the cognitive load on you is much smaller making it less likely for you to make mistakes.
With static typing, what you should also practice is using the strictest type possible, e.g., don't use a pointer if null has no meaning for you, don't use a vector if the size of the continer is fixed, etc.
I don't follow the logic here. If a more rigid type system produces an error, wouldn't that surface the error, not suppress it? What am I missing?
Data errors will always pass through regardless if it's invalid. Eg. an application bug that overflowed the int will still be happily accepted into an integer typed column even with a check contraint value > 0.
In the ANYTHING case you would have something like -205 in there instead.
For what it's worth I don't really agree with this post at all though.
> I'd always thought that stronger type systems had to do with performance and storage optimization, but TFA didn't delve into that.
I was also expecting/hoping to see this point addressed.
if stronger type systems will ensure performance, Haskell will beat C in benchmarks but that's not the case in most situations.
> In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end,
> after the closing ")", then strict typing rules apply to that table.
I'm guessing that addition is reason why this document was recently written and is a draft, I guess.
[0] https://www.sqlite.org/draft/flextypegood.html#if_you_insist...
[1] https://www.sqlite.org/draft/stricttables.html
I have written a lot of PHP over the years and PHP has gradually introduced type hints for function parameters and return values. For me this has reduced the number of type errors I have to almost zero and with that data errors.
The thing is with type errors in dynamic languages is that it usually hides an error branch. Example, we open a file handle with fopen(), it can return a handle or false on fail.
If that would fail (false) with old style PHP you could continue pass that false file handle to other file functions like fread(), fwrite() etc without knowing they all failed. So in this case there would be no data at all either to be transformed, mangled or inserted, it wouldn't exist at all because it couldn't be written, that is also a form of data pollution (fopen() can swapped with sqlite equivalent for the same argument)
How about types and preventing data pollution? Take dates as a good example. In old MySQL you could insert zero dates (0000-00-00) or invalid dates (2004-04-31), with better sql modes you can prevent that for happening, a type enforcement.
Having non existing dates in your database will give trouble in the future, how should you handle them for calculating something like time periods for subscriptions? Do you really take into account when writing your SQL query that some dates are not valid dates at all?
Having inherited lots of old PHP project I can't say enjoy the non-strict typing in both code and database, database columns are usually filled with all sorts of garbage, like 0,1, -1, 2, true, false, null, "NULL", "Yes", "No" or even localized strings "Ja", "Nej". This will always lead to more complicated SQL queries or code logic down the line.
The article argues that you can easily spot data pollution in a column, that assumes that the developer actually looks at it, but if everything "works" why should they?
I want to do the opposite and create my own types, assume that we accept user input and to make sure that the user input doesn't contain garbage I can create my own types, like MultiByteText255, AsciiText64, HtmlFragment etc and that should be enforced both in code and storage. These types can both enforce the contents of it and also make sure to properly store and output it. And if I want I can inherit to specialized types like UserName extends MultiByteText255. Everyone using functions that takes a UserName type is forced to adhere to it.
This folder can only contain JPEG and PNG files, this folder can contain only A or B ....etc.
useful in some cases but I don't think this is not what you want as a default.
Only argument that I can sort of understand for flexible types is that it is easier to get started or for smaller projects, like some batch script, but as soon as you have a large system you want strict control of what data you write to the database. Otherwise your schema migrations will be a nightmare.
Thus too much of strict types can be a problem, if I compare PHP to Java, Java has a tendency to become type bureaucracy, where I find PHP to be a sane middle ground where you specify types on your functions , i.e. your api, but not within.
But writing data is not equivalent of passing data around in your application, as soon you have stored something it is hard to go back and change it because you have usually lost all context from when you wrote it, where as changing type in program code can almost always be done.
As a frequent user of sqlite I'm looking forward to try out STRICT tables, it will be interesting if that actually improves anything from an application perspective or if it becomes more cumbersome.
In that case, I can't imagine writing any significant amount of TCL and not having reached a point where you have a runtime error that would have been prevented had you had a static type system.
Am I missing something?
In contrast, folks who rely heavily on static type systems may not write so many tests. For example I put more energy into making sure that my Haskell types capture the problem I’m trying to solve, so that the compiler flags my mistakes and exposes poor assumptions.
When you would like to concatenate strings (including numbers), you would use quoting or commands/procs made for that purpose instead.
[0] https://tcl.tk/man/tcl/TclCmd/expr.htm
I wonder what kind of application I need to try, before I realize the advantage of flexible typing in a database.
What happens when I take the mean of integers and strings? Won't I be surprised when I my sum of integers return a real number?
application that deals with highly dynamic data. lots of variants (OR type) in tree/graph. (photoshop's layers come to mind.)
I am a fan of the SQLite authors for pioneering heavy-duty engineering, but this, like their hatred of threads [1], does not sound good to me and really puts me off.
It also sounds like a cop out; sure, they may not have found evidence, but did they really look?
Personally, with the success of Rust, TypeScript, and other stricter languages, and the fact that TDD seems to be about recreating the advantages of static typing for dynamically-typed languages, I think there is plenty of evidence.
Of course, "best" is doing a lot of the heavy lifting in the quote above, so I would define "best" as removing the most bugs per unit of effort. Static typing seems to fit that bill.
[1]: https://sqlite.org/faq.html#q6
Although, frankly every SQLite thread basically devolves in to the same thing as the TFA notes.
¹ https://news.ycombinator.com/item?id=28259104