5 ms·

So, I've heard SQLite described as "Postgres for people not paying attention", because it ignores data type errors. A lot of people in the HN community like ty

So, I've heard SQLite described as "Postgres for people not paying attention", because it ignores data type errors. A lot of people in the HN community like types. Me included. I really don't want my database to ignore type errors. I basically want Postgres but embedded like a library. I cannot think of any reason why that couldn't work. Perhaps just no one has built it yet.

js8 7y ago
I think it's a design choice. Ignoring types lets you focus on other features, in case of SQLite, things like crash resistance and reliability. In other words, typechecking is also a cost for language designer. Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.
- justinclift 7y ago
  Interestingly, it's amazing to me that many SQLite libraries in the Go ecosystem don't support these mixed/flexible data types in tables. If you read back a set of values and one of the fields in a row doesn't match the data type given by the column, the libraries will error out. One library that works correctly is github.com/gwenn/gosqlite, which checks the data type of each field of the result set (per row), so handles things gracefully.
- suyjuris 7y ago
  SQLite originated as a TCL extension, so it would make sense for it to share TCL's (notoriously lax) philosophy regarding types.
zokier 7y ago
I have recently been thinking if it were possible to marry pg query engine with sqlite storage layer and c api. Idea being that you could get all the rich types etc from pg with the embeddability of sqlite.
e12e 7y ago
Oh, that's a bit scary. Both for use as a file format (in case of attacker supplied db file) and more general. I see that even with "INTEGER" affinity, I can insert and read back text to columns :/ sqlite :memory: CREATE TABLE test( x INTEGER, y TEXT); INSERT INTO test VALUES("a", "b"); SELECT * FROM test; a|b https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
beatgammit 7y ago
It's really not a problem. I use statically typed languages (mostly Go and Rust), and everything just works as long as my database later retrieves and stores the right types. The only slight complexity is currency, which would be stored as an interger because there's no decimal type. Unless you have really strict requirements (generally not the case), SQLites storage classes (NULL, INTEGER, REAL, TEXT, BLOB) are sufficient. If it really bothers you, you can use extensions to provide whatever abstractions you like. If you're mostly doing CRUD, you really shouldn't have any issues.
thijsvandien 7y ago
Firebird (https://www.firebirdsql.org https://www.firebirdsql.org) can essentially work that way. It comes with two versions of the client library. One of them runs the database engine in-process (embedded mode), similar to SQLite. Later you can switch over to a separate server process, like with PostgreSQL. Though both those two set the bar pretty high, Firebird is a solid RDBMS that’s been around for a long time, with a well-balanced feature set (including strict data typing).

js8 7y ago

I think it's a design choice. Ignoring types lets you focus on other features, in case of SQLite, things like crash resistance and reliability. In other words, typechecking is also a cost for language designer. Even SQL itself has not particularly strong type system. Perhaps you're looking for something else entirely.

justinclift 7y ago
Interestingly, it's amazing to me that many SQLite libraries in the Go ecosystem don't support these mixed/flexible data types in tables. If you read back a set of values and one of the fields in a row doesn't match the data type given by the column, the libraries will error out. One library that works correctly is github.com/gwenn/gosqlite, which checks the data type of each field of the result set (per row), so handles things gracefully.
suyjuris 7y ago
SQLite originated as a TCL extension, so it would make sense for it to share TCL's (notoriously lax) philosophy regarding types.

zokier 7y ago

I have recently been thinking if it were possible to marry pg query engine with sqlite storage layer and c api. Idea being that you could get all the rich types etc from pg with the embeddability of sqlite.

e12e 7y ago

Oh, that's a bit scary. Both for use as a file format (in case of attacker supplied db file) and more general. I see that even with "INTEGER" affinity, I can insert and read back text to columns :/ sqlite :memory: CREATE TABLE test( x INTEGER, y TEXT); INSERT INTO test VALUES("a", "b"); SELECT * FROM test; a|b https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html

beatgammit 7y ago

It's really not a problem. I use statically typed languages (mostly Go and Rust), and everything just works as long as my database later retrieves and stores the right types. The only slight complexity is currency, which would be stored as an interger because there's no decimal type. Unless you have really strict requirements (generally not the case), SQLites storage classes (NULL, INTEGER, REAL, TEXT, BLOB) are sufficient. If it really bothers you, you can use extensions to provide whatever abstractions you like. If you're mostly doing CRUD, you really shouldn't have any issues.

thijsvandien 7y ago

Firebird (https://www.firebirdsql.org https://www.firebirdsql.org) can essentially work that way. It comes with two versions of the client library. One of them runs the database engine in-process (embedded mode), similar to SQLite. Later you can switch over to a separate server process, like with PostgreSQL. Though both those two set the bar pretty high, Firebird is a solid RDBMS that’s been around for a long time, with a well-balanced feature set (including strict data typing).