CAST(x AS STRING) casts to integer in SQLite

Plus TIL: Diátaxis: Systematic technical documentation

This newsletter is a round-up of content I’ve posted on lalitm.com over the past week or so.

In this edition:

• CAST(x AS STRING) casts to integer in SQLite

• TIL: Diátaxis: Systematic technical documentation

Also, if you want to be informed of posts as they happen, you can also follow me on Bluesky where I post links as they’re published!

---

CAST(x AS STRING) casts to integer in SQLite - 2025-11-03

As an “SQLite consultant” for my local area of Google, I often have people come to me having written SQL like:

SELECT CAST(bar AS STRING) AS baz
FROM foo

and ask me “Why is baz always an integer?! Have I hit an SQLite bug?”.

I have to again reach for my list of “odd quirks that SQLite has that people don’t know about”. Because this is not a bug, at least according to the SQLite manual.

Instead, the correct way to write the above query is:

SELECT CAST(bar AS TEXT) AS baz
FROM foo

The reason for this? Quoting from “Determination of Column Affinity”

For tables not declared as STRICT, the affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

1. If the declared type contains the string “INT” then it is assigned INTEGER affinity.

2. If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity. Notice that the type VARCHAR contains the string “CHAR” and is thus assigned TEXT affinity.

3. If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity BLOB.

4. If the declared type for a column contains any of the strings “REAL”, “FLOA”, or “DOUB” then the column has REAL affinity.

Otherwise, the affinity is NUMERIC.

STRING does not match any of the numbered rules and so fallback to NUMERIC affinity which, in the general case, means integer.

Due to SQLite’s staunch stance of being “backwards compatible” there’s very little chance of this paper-cut ever going away. But at least next time someone comes to me with this issue, I’ll be able to link to this post instead of writing the same thing for the nth time :)

---

TIL: Diátaxis: Systematic technical documentation - 2025-11-02

A few weeks ago, I wrote about “The Documentation System” and how valuable I found it. As I dug deeper into researching how best to apply the principles outlined there, I came across Diátaxis. Written by the same author after they left Divio, Diátaxis is a distillation of all the principles with much more guidance in how to apply the framework (e.g. giving more examples), diving more into the philosophy and in general being a more comprehensive view into how to write great technical docs.

I’m blown away by how well the framework (and Diátaxis makes clear it is a framework not a rigid set of rules) is explained. It’s patently obvious that the author really understands technical documentation and they truly have given a gift to the industry by writing it up.

Finding Diátaxis has only made me more motivated to deeply absorb its principles and see how to best apply it to all technical documentation I refactor and/or write going forward.