17 ms·
Quirks, Caveats, and Gotchas in SQLite
- dehrmann 4y agoOnce I worked with a codebase that used SQLite to mock MySQL for tests. It mostly worked, but they're different enough that things sliped by around nasty edge cases.
- speedgoose 4y agoWas the codebase supposed to be generic, or did someone wanted more weird challenges in his life?
- christophilus 4y agoThe group by behavior is useful. I wish Postgres did that.
- unterdenlinden 4y agoYou can do that with window functions in Postgres: SELECT MAX(salary) OVER (), first_name, last_name FROM employee;
- systems 4y agoexactly, SQL being a declarative language, you should always prefer explicit over implicit SQL bugs are very hard to detect, when the query return a result that looks right, and because the language is declarative its easy to do those mistakes
- deathanatos 4y agoUse of that group by behavior is a bug, nearly every time I've seen it. (Though usually I'm seeing it in MySQL.) Columns that are not part of the group, an aggregate of the group, or a functional dependency of the group aren't stable — there are multiple rows that could supply the value, and they needn't have the same value.
- kwoff 4y agoYeah, I've seen that bug in a monitoring graph that was showing data for say every 5 minutes but the (MySQL) database table had data per minute; so you'd see a spike in the graph, but refresh a minute later and voila no spike anymore.
- dspillett 4y agoMost DBs don't (and won't ever) support that because the results become undefined. You could get different values out for the same query in different versions of the DB (due to query planner changes) or even within the same version and the same database over time (as the relative sizes of your tables change so the planner choses a different route, or index options are changed, so the planner is forced to go with a different route or has the option of a better one). Though in a world where “eventual consistency” is accepted, may be “eeny, meeny, miny, moe” will be more generally considered OK at some point :) At least SQLite tries to be consistent (taking the value from the row where the aggregate found its value) where that is possible (which it often isn't) which mySQL (which also allows non-grouped non-aggregated columns in the projection list) does not.
- herge 4y agoI hit annoyances when using sqlite when dealing with some corner-cases. I wanted to implement a lock and share some small amount of data (who did what when) between two different linux users on the same machine. I figured "sqlite is better than fopen, let's use that!", but between directory permissions, all the WAL files, probably the sqlite3 python lib and Diskcache (https://pypi.org/project/diskcache/ https://pypi.org/project/diskcache/) not helping things, it was a real pain, where regularly under different race conditions, we would get permission denied errors. I managed to paper it over with retries on each side, but I still wonder if there was a missing option or setting I should have used.
- m_st 4y agoI feel you! I had an ASP.NET Core Web API using a SQLite file for data storage. It was working exceptionally fine in development. When we released it, as soon as more than 10 users were throwing request upon it, it got so deadly slow. Obviously caching and some tweaking helped a lot. But it was far from easy to find, understand and test (!) all required options and optimizations. Other than that I still love SQLite.
- dspillett 4y ago> I managed to paper it over with retries on each side You'll hit that elsewhere as well. “Papering over with retries” is standard MS advice for Azure SQL and a number of other services: https://learn.microsoft.com/en-us/azure/architecture/best-practices/retry-service-specific https://learn.microsoft.com/en-us/azure/architecture/best-pr...
- drej 4y agoAnyone can share their experience with the somewhat new STRICT mode? Does it help? I tend to use Postgres when available, primarily for the added strictness, but I'd surely prefer SQLite in more scenarios as it's easier to operate.
- torbica 4y agoIt does not really help in our scenario. I really hope that this feature will evolve more in next versions.
- bob1029 4y agoWe have extremely heavy SQLite usage throughout. Strict mode would only cause trouble for us. Most of our SQLite access is managed through a domain-specific C# library we developed. It handles all of the parsing & conversion internally. We don't touch the databases directly in most cases.
- masklinn 4y agoI use strict tables. I’m now realising it did help when I migrated tables from one data type to an other, because I’d missed updating some of the code. I didn’t realise because it was just what I’d expected, but without strict tables I’d have had to debug strange errors on retrieval rather than the type error on insertion I got.
- rch 4y agoI've run into situations where I need a db, but all I have is Python, so the embedded SQLite is my best option. It would be ideal if the interface was fully compatible with postgres (or a practical subset), even if SQLite was handling storage under the hood.
- dicroce 4y agoI encountered a case a few years ago where a query failed because the database had been opened read only but it needed to recover a journal. The solution was to just never open the database read only... but this seems less than optimal. Anyone else seen this?
- ajkjk 4y ago> The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of "INTEGER" and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts '1234' into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like 'wxyz' into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column. wtf. who would ever want that?
- at_a_remove 4y agoMe! I'm often exploring data where either there's no defined standard or the use of the data has drifted from the standard. Now, I could go over this line-by-line, but instead my go-to has been "Hey, let's throw this into SQLite, then run stats on it!" See what shakes out. SQLite kindly and obediently takes all of this mystery data, which ends up being nothing like what I was told, and just accepting it. Then I can begin prodding it and see what is actually going on. This is something that has come up for me for at least a decade: chuck it in SQLite, then figure out what the real standard is.
- throw10920 4y agoWere SQLite not such a stellar piece of software, I would complain, and ask that this kind of flexibility be disabled by default, with an option to explicitly enable it if necessary.
- tehbeard 4y agoWhile I doubt it will ever be enabled by default due to backwards compatibility, there is a strict tables[1] feature now. [1] https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- silvestrov 4y ago
- deathanatos 4y ago> NUL Characters Are Allowed In Text Strings Any raw byte sequence is allowed in text strings.
- dspillett 4y agoWhich seems logical to me. Though there is an issue that some of sqlite's own functions are unaware of this and will end early if a NUL is encountered: https://www.sqlite.org/nulinstr.html https://www.sqlite.org/nulinstr.html
- deathanatos 4y ago> Which seems logical to me. I expect text to be text (i.e., Unicode, these days).
- dspillett 4y ago\u0000 (NUL) is a perfectly valid unicode character. Though I can understand people from mostly C/C-alike background where NUL termination is the norm for strings less uncomfortable with that.
- bmacho 4y agoI think it was against "Any raw byte sequence is allowed in text strings.", and not against NUL.
- thfuran 4y agoBut aren't there extended ASCII encodings that have 256 8-bit characters?
- deathanatos 4y agoThere are other encodings, yes, where any byte string is valid within that encoding. The Unicode encodings (UTF-*) don't have that property, however. The SQLite docs say this about text values or columns, though it's a bit muddy which is which. (But it doesn't really matter.) > TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). But it's the best reference I have for "what are the set of values that a `text` value can have". E.g., sqlite> PRAGMA encoding = 'UTF-8'; sqlite> CREATE TABLE test (a text); sqlite> INSERT INTO test (a) VALUES ('a' || X'FF' || 'a'); sqlite> SELECT typeof(a), a from test; text|a�a Here we have a table storing a "text" item, whose value appears to be the byte sequence `b"a\xffa"`¹. That's not valid UTF-8, or any other Unicode. The replacement character here ("�") is the terminal saying "I can't display this". Presumably for this reason, the Rust bindings I use have Text value carrying a [u8], i.e., raw bytes. Its easy enough to turn that into a String, in practice. (But it is fallible, and the example above would fail. In practice, it gets rolled into all the other "it's not what I expect" cases, like if the value was an int. But having a language have a text type that's more or less just a bytestring is still regrettable.) ¹borrowing Rust or Python's syntax for byte strings.
- deleted 4y ago[deleted]
- npilk 4y agoSetting aside some of the technical choices, I wish we saw more product builders publish transparent perspectives on the 'shortcomings' of their product. SQLite also has a list of reasons why you would or wouldn't want to use SQLite that I've always enjoyed: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- donatj 4y agoI'd say it's a lot easier to do when you've already got mass adoption.
- chasil 4y agoLike Oracle, SQLite lacks boolean (bit) columns, but it does support bitwise operators, so multiple boolean values can be packed into an integer with powers of 2. Setting a value with an update is a bitwise or, and checking a value is a bitwise and. $ sqlite3 SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 2 | 1; 3 sqlite> select 3 & 1; 1 Oracle only has a "bitand" function, but "bitor" has been posted on Oracle user sites: create or replace function bitor(p_dec1 number, p_dec2 number) return number is begin if p_dec1 is null then return p_dec2; else return p_dec1-bitand(p_dec1,p_dec2)+p_dec2; end if; end; / That isn't necessary in SQLite. Searches on these will likely require full table scans, a definite performance disadvantage.
- dekhn 4y ago"Similarly, SQLite allows you to store a 2000-character string into a column of type VARCHAR(50). Other SQL implementations would either throw an error or truncate the string. SQLite stores the entire 2000-character string with no loss of information and without complaint." That's not flexible typing, that's user-hostile behavior.
- NicolaiS 4y agoThere is nothing called "VARCHAR(n)" in SQLite. It is mapped to `CHAR(ACTER) => TEXT` which is limited to 2^31 - 1 characters. [1] https://www.sqlite.org/datatype3.html#3.1.1 https://www.sqlite.org/datatype3.html#3.1.1 [2] https://www.sqlite.org/limits.html#max_length https://www.sqlite.org/limits.html#max_length
- dekhn 4y agoThats user hostile behavior- VARCHAR is a critical optimization for large databases.
- masklinn 4y agoWhat part of varchar is a “critical optimisation” exactly? If it’s ensuring that the data is never larger than X, then you can use a CHECK constraint. It has no impact on storage in sqlite, or in postgres for that matter.
- Ndymium 4y agoIn PostgreSQL a varchar is also a text type column but with an added length check. So it's actually just slower to insert and not more space efficient, as far as I know. So SQLite is not alone in this.
- chasil 4y agoThese are the native SQLite datatypes, that are also used as column affinities. TEXT - text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). BLOB - blob of data, stored exactly as it was input. NUMERIC - generic number, attempts to devolve to integer or real. INTEGER - signed, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. REAL - floating point value, stored as an 8-byte IEEE-754 format. Any CHAR variant is really text. If you really need it to be 50 characters, then a suitable CHECK constraint must be in place. You can also create tables with unknown types: CREATE TABLE foo(bar razzamataz); With some gymnastics, you can see the affinity assigned to this column: create table bar as select * from foo where 1=0; sqlite> .dump bar PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE bar( bar NUM ); COMMIT;
- masklinn 4y ago> Foreign Key Enforcement Is Off By Default That is by far my biggest annoyance with sqlite. Not only that, but the FK enforcement must be enabled on a per-connection basis. Unlike the WAL, you can’t just set it once and know that your FKs will be checked, every client must set the pragma on every connection. Such a pain in the ass. The error reporting on FK constraint errors is also not great (at least when using implicit constraints via REFERENCES sqlite just reports that an FK constraint failed, no mention of which or why, good luck, have fun). More generally, I find sqlite to have worse error messages than postgres when feeding it invalid SQL.
- jeffreyrogers 4y agoCan't you just wrap the connection handling code with some logic that automatically executes the pragma?
- masklinn 4y agoSure. The problem is that you have to carefully remember to do that in every project, and if other applications need write access that they do so as well.
- massysett 4y agoThere is a compile-time option to always enable foreign-key constraints.
- clcaev 4y agoThe lack of a datetime data type is a significant burden on applications.
- gurjeet 4y agoImportant bit to pay attention to: <SQLite's behaviour is> "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. But lately, people have come to realize that it is sometimes better to be strict in what you accept, so as to more easily find errors in the input.
- tritiy 4y agoNumber 6 was really surprising: SELECT max(salary), first_name, last_name FROM employee; This returns one row! AFAIK all other databases would return one row per record in the table where first_name, last_name would be from the row while max(salary) would be the value from the row with max salary. Is this SQL ANSI compatible?