Create: 2025-12-18, Update: 2025-12-18
Recently, with the release of htmx 4.0, I decided to once again, rewrite one of my little service.
Not only do I want to upgrade to htmx 4, but I reconsidered my whole stack. One of the cornerstone in my stack is PostgreSQL. I am very familiar with it, it runs fine.
However, my service only lives on one machine. PostgreSQL is overkill.
I decided to switch to SQLite.
Since SQLite does not have a dedicated type for time. It fakes time as INTEGER, REAL or TEXT. That is not a big deal. I can store the time in INTEGER and parse it in Go.
Consider the following schema.
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
permission INTEGER NOT NULL,
created_at INTEGER NOT NULL DEFAULT (datetime('now')),
updated_at INTEGER NOT NULL DEFAULT (datetime('now')),
version INTEGER NOT NULL DEFAULT 1
);
As a normal person would think, created and updated is of type INTEGER, right?
No.
The datetime() function in SQLite returns a string, formatted as YYYY-MM-DD HH:MM:SS.
But the type of created is INTEGER, of course SQLite would convert the datetime string into INTEGER, right?
Wrong!
SQLite would store the created value as TEXT.
I use scany to scan the query result rows into struct. It keeps reporting an error of unmatch type, string cannot be converted to int64.
I was suspecting it was a bug or some unimplemented features in the go SQLite driver, (the go SQLite driver is another messy story). The driver I chose was modernc.org/sqlite. A CGO free library which makes cross compiling for my Raspberry Pi a lot easier.
I switch between a lot of drivers. Did not help.
Then I come across this stackoverflow answer in 2011. Static rigid typing in SQLite.
Long story short, the type I specify in the schema is like a suggestion to SQLite, if the inserted data can fit in the type I wrote, it would be the correct type, otherwise, it will still insert to the column with whatever the type it is.
In this case, SQLite inserted the datetime() as a TEXT, which is a string.
After scratching my hair for hours, once the issue is identified, the solution is very simple. Just find a function that returns unix time as INTEGER in SQLite.
So the new schema is like this.
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
permission INTEGER NOT NULL,
created_at INTEGER NOT NULL DEFAULT (unixepoch('now')),
updated_at INTEGER NOT NULL DEFAULT (unixepoch('now')),
version INTEGER NOT NULL DEFAULT 1
);
unixepoch() ensures the return type to be INTEGER.
Thank you SQLite!