Quirks, Caveats, and Gotchas in SQLite

&gt; 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 &quot;INTEGER&quot; 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 &#x27;1234&#x27; into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like &#x27;wxyz&#x27; into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.<p>wtf. who would ever want that?

Setting aside some of the technical choices, I wish we saw more product builders publish transparent perspectives on the &#x27;shortcomings&#x27; of their product. SQLite also has a list of reasons why you would or wouldn&#x27;t want to use SQLite that I&#x27;ve always enjoyed: <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;whentouse.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;whentouse.html</a>

I 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.<p>I figured &quot;sqlite is better than fopen, let&#x27;s use that!&quot;, but between directory permissions, all the WAL files, probably the sqlite3 python lib and Diskcache (<a href="https:&#x2F;&#x2F;pypi.org&#x2F;project&#x2F;diskcache&#x2F;" rel="nofollow">https:&#x2F;&#x2F;pypi.org&#x2F;project&#x2F;diskcache&#x2F;</a>) 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.

&gt; Foreign Key Enforcement Is Off By Default<p>That is by far my biggest annoyance with sqlite.<p>Not only that, but the FK enforcement must be enabled <i>on a per-connection basis</i>. 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.<p>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).<p>More generally, I find sqlite to have worse error messages than postgres when feeding it invalid SQL.

Anyone 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&#x27;d surely prefer SQLite in more scenarios as it&#x27;s easier to operate.

Like 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.<p>Setting a value with an update is a bitwise or, and checking a value is a bitwise and.<p><pre><code> $ sqlite3 SQLite version 3.36.0 2021-06-18 18:36:39 Enter &quot;.help&quot; for usage hints. Connected to a transient in-memory database. Use &quot;.open FILENAME&quot; to reopen on a persistent database. sqlite&gt; select 2 | 1; 3 sqlite&gt; select 3 &amp; 1; 1 </code></pre> Oracle only has a &quot;bitand&quot; function, but &quot;bitor&quot; has been posted on Oracle user sites:<p><pre><code> 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; &#x2F; </code></pre> That isn&#x27;t necessary in SQLite.<p>Searches on these will likely require full table scans, a definite performance disadvantage.

The group by behavior is useful. I wish Postgres did that.

Number 6 was really surprising:<p>SELECT max(salary), first_name, last_name FROM employee;<p>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?

Important bit to pay attention to:<p><pre><code> &lt;SQLite&#x27;s behaviour is&gt; &quot;Be liberal in what you accept&quot;. 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.</code></pre>

I 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?

The lack of a datetime data type is a significant burden on applications.

Once I worked with a codebase that used SQLite to mock MySQL for tests. It mostly worked, but they&#x27;re different enough that things sliped by around nasty edge cases.

&gt; <i>NUL Characters Are Allowed In Text Strings</i><p><i>Any</i> raw byte sequence is allowed in text strings.

&quot;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.&quot;<p>That&#x27;s not flexible typing, that&#x27;s user-hostile behavior.