5 ms·

It's not a toy database, but it is a good idea to be familiar with its Quirks, Gotchas, and Caveats: https://www.sqlite.org/quirks.html https://www.sqlite.org/

It's not a toy database, but it is a good idea to be familiar with its Quirks, Gotchas, and Caveats: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html

sgarland 2y ago
This part never fails to make me twitch: > Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and even pejorative. We prefer to say that SQLite is "flexibly typed" and that other SQL database engines are "rigidly typed". A database, relational or otherwise, should have strict typing, full stop. It may have transparent casting on retrieval, but if you try to store an INT in a TEXT column, it should throw an error. To do otherwise is to have questionable data integrity. I like SQLite for small projects that don’t need a remote DB, and I like that Python has a native library for it. I do not, however, like its many gotchas, like the above, or how you have to explicitly enable foreign keys.
- strken 2y ago
  It's hard to know what to do about SQLite's dangerous defaults. Changing them breaks backwards compatibility, while leaving them means some users are unintentionally using their database without referential integrity or strict tables. Maybe you wait until v4? But v3 was 20 years ago and there's no obvious reason to break backwards compatibility or change the API yet, so that's just kicking the can down the road.
- bad_username 2y ago
  Moreover, later in the page: > SQLite says that the following query returns false: > SELECT 1='1'; > It does this because an integer is not a string. Every other major SQL database engine says this is true, for reasons that the creator of SQLite does not understand. The mind that allowed strings be accepted into integer columns should not find it hard to understand.

5 ms·

It's not a toy database, but it is a good idea to be familiar with its Quirks, Gotchas, and Caveats: https://www.sqlite.org/quirks.html https://www.sqlite.org/

by neverartful 2y ago

It's not a toy database, but it is a good idea to be familiar with its Quirks, Gotchas, and Caveats: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html

sgarland 2y ago
This part never fails to make me twitch: > Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and even pejorative. We prefer to say that SQLite is "flexibly typed" and that other SQL database engines are "rigidly typed". A database, relational or otherwise, should have strict typing, full stop. It may have transparent casting on retrieval, but if you try to store an INT in a TEXT column, it should throw an error. To do otherwise is to have questionable data integrity. I like SQLite for small projects that don’t need a remote DB, and I like that Python has a native library for it. I do not, however, like its many gotchas, like the above, or how you have to explicitly enable foreign keys.
- strken 2y ago
  It's hard to know what to do about SQLite's dangerous defaults. Changing them breaks backwards compatibility, while leaving them means some users are unintentionally using their database without referential integrity or strict tables. Maybe you wait until v4? But v3 was 20 years ago and there's no obvious reason to break backwards compatibility or change the API yet, so that's just kicking the can down the road.
- bad_username 2y ago
  Moreover, later in the page: > SQLite says that the following query returns false: > SELECT 1='1'; > It does this because an integer is not a string. Every other major SQL database engine says this is true, for reasons that the creator of SQLite does not understand. The mind that allowed strings be accepted into integer columns should not find it hard to understand.