Full Width [alt+shift+f] Shortcuts [alt+shift+k]
Sign Up [alt+shift+s] Log In [alt+shift+l]
1

Prefer STRICT tables in SQLite

from Evan Hahn (dot com) [alt+shift+b] in technology

In short: I prefer strict tables in SQLite because they avoid some datatype problems, such as putting text in number columns. SQLite has a feature that I think is underrated: strict tables. Strict tables help enforce rigid typing, preventing mistakes like putting text into integer columns. I like them, and wrote this post to promote their use! To make a strict table, add STRICT to the end of its definition. Like this: -CREATE TABLE people (name TEXT); +CREATE TABLE people (name TEXT) STRICT; That’s it! But what does it do? Advantages of strict tables Broadly, strict tables help enforce rigid types, like other SQL engines do. Prevents type mismatches on insert/update Most significantly, strict tables keep you from inserting the wrong type into a column. For example, SQLite normally lets you put text into an INTEGER column, but not with strict tables. -- Non-strict tables let you put anything anywhere. CREATE TABLE people_nonstrict (age INTEGER); INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- => works fine -- Strict tables don't allow that, which I prefer. CREATE TABLE people_strict (age INTEGER) STRICT; INSERT INTO people_strict (age) VALUES ('garbage'); -- => error: cannot store TEXT value in INTEGER column Personally, I think it’s a mistake to try to put text in an integer column, or vice-versa. I don’t want SQLite to let me make this error! The same validation happens for UPDATEs, too. Notably, if a value can be losslessly converted, it will still be accepted. For example, the string '123' can be perfectly converted to an integer, so it’s allowed. These two lines are equivalent, even for a strict table: INSERT INTO people_strict (age) VALUES ('123'); INSERT INTO people_strict (age) VALUES (123); Prevents bogus column types on table creation By default, you can create columns with bogus types. For example, all of these work even though they aren’t valid SQLite datatypes: -- SQLite doesn't support these types, but this is all accepted. CREATE TABLE...
11th Jul 2026

Stay updated

Get a weekly newsletter with the top 5 articles worth reading every week.

More from Evan Hahn (dot com)

Anecdotally, programmers dislike "reduce"

In short: from my experience, people like map and filter, but not reduce. I use functions like map and filter all the time. When I put that code up for review, my peers rarely complain. I get plenty of feedback about other decisions, but not about my use of map and filter. I cannot say the same for reduce. Often, when I’ve submitted a patch with reduce inside, I get a comment like, “this part is hard to read.” And I see reduce way less than map, filter, some, and so on. Anecdotally, I have come to believe that programmers don’t like reduce as much. I don’t know why, but I have a few theories: reduce is harder to read. reduce is less familiar. reduce can have worse performance compared to other options. reduce is less elegant in languages I use, like JavaScript, Python, and Swift. In my blissful stint as a Clojure developer, I did not get this feedback. I’m wrong, and I’m seeing a trend that’s not real. I usually just change reduce to something else and move on. Even though I prefer it, I don’t usually care much. But it’s a little social phenomenon I’ve observed, and I thought I’d document it. I’ve also noticed this less recently, possibly because code review is less thorough nowadays. Do you notice this? Do you like reduce? Please tell me.

3 weeks ago • 1 votes
Vim's UserGettingBored autocmd

In short: Vim has a joke autocmd called UserGettingBored that doesn’t do anything. Vim’s automatic commands feature, usually shortened to “autocmd”, lets you run code when various events occur. For example, you could implement an auto-save feature by binding the TextChanged event to the :w command. Vim has over 100 events, from “buffer was created” to “file was saved”. But one of them sticks out to me: UserGettingBored. Here’s the documentation: UserGettingBored: When the user presses the same key 42 times. Just kidding! :-) When I saw this, I was busy doing something else and it completely derailed me. “I must know more,” I thought. Here’s what I found: Unfortunately, it doesn’t do anything. It only exists in the documentation (and some tests). If you try to use it with somethig like autocmd UserGettingBored ..., you’ll get a “no such group or event” error. It’s present in Vim, Neovim, and Vim Classic. It was first added by Bram Moolenaar in July 2000, over a year before Vim 6.0 was released. The original description was, “When the user hits CTRL-C. Just kidding!” And it didn’t do anything back then, so I don’t think it’s ever been real. In August 2001, he added the smiley face to the documentation. It then read, “When the user hits CTRL-C. Just kidding! :-)” Twelve years later, in 2013, the description changed to its current iteration: “When the user presses the same key 42 times. Just kidding! :-)” In 2022, developer Mike Smith created an unofficial plugin inspired by this joke autocmd. If you press the same key 42 times in Insert mode, a picture of Samuel L. Jackson appears. 22 years later, it’s finally real.

22nd Aug 2026 • 1 votes
"Sixteenth of a year", a 1.8 KiB art piece

As I write this, we’re about 7 sixteenths through 2026, and it’s about 14 sixteenths through the day. For the sixteenth issue of the Taper online magazine, I split time into sixteenths to think about its passage in a different way. The code, which had to be under 2048 bytes, isn’t terribly complex. It does some date math and uses a Go server for minification. If you want, here’s the unminified source code. Go check out all the other entries from this issue! My favorites include "[SIC]", “Desperate Measures from a Dying Regime”, and "(un)done". See also: my previous Taper entry.

3rd Jun 2026 • 1 votes
Offline command line translation with TranslateGemma + Ollama

I wrote a simple script that translates text at the command line, completely offline. Here’s an example of how it works on my computer: echo '¿Cómo estás?' | translate # => How are you? It combines a few tools: TranslateGemma, a special-purpose language model for translation Ollama, a tool for running language models locally Efficient Language Detector, a library that detects the language for a piece of text Here’s the pseudocode of how it works: source = read_stdin() # Uses Efficient Language Detector source_language = detect_language(source) # Uses JavaScript's `navigator.language` target_language = get_system_language() # Uses Ollama + TranslateGemma return translate(source, source_language, target_language) I built this because I couldn’t find anyone else who had done it. It’s written in Deno for my specific needs—for example, it only translates text into your system’s language—but could easily be adapted if you need something else. I like that I can do offline, private, automatic translation. It’s imperfect, but useful for me! Here’s the source code.

1st May 2026 • 1 votes

More in technology

You Won’t Hear About These, Even In Myths (Atlassian Jira, Confluence (and more) Pre-Auth Arbitrary File Read CVE-2026-21589)

Welcome back to yet another episode of "security was taken seriously". Being who we are (and constantly being exposed to what we see…), we recognize we have been doomed to eternal damnation as we keep on watching security best practices crumble behind “secure by design”

2 hours ago • 1 votes
Beeper MCP

Fellow Automattician Job Thomas, down in Cape Town, South Africa, has a lovely story about using Claude and the Beeper MCP to wrangle all the group chats for his daughter’s sports.

13 hours ago • 1 votes
Traction Heroes Ep. 46: Tribal Psychology

We keep reading scary reports of swarms of AI agents breaking into computer systems and doing other untoward and perhaps illegal things. To what degree do these agents influence each other? And is there anything we can learn from human behavior that would help mitigate these problems? That’s the heady backstory behind [episode 46](https://www.tractionheroes.com/2439976/episodes/19905275-tribal-psychology) of [_Traction Heroes_](https://www.tractionheroes.com/). Harry brought a short reading from David McRaney’s [_How Minds Change_](https://www.amazon.com/How-Minds-Change-Surprising-Persuasion-ebook/dp/B093R2CP2V/) about the impact of our groups on our thinking and behavior. Here’s the key fragment: > the latest evidence coming out of social science is clear. Humans value being good members of their groups much more than they value being right. So much so that as long as the group satisfies those needs, we will choose to be wrong if it keeps us in good standing with our peers. We never lack evidence for this: just tune in to the news. People do stupid things to remain in good standing with their milieu _all the time_. (I know I have!) And apparently, so do AIs. (Although as I pointed out in the show, we mustn’t anthropomorphize technology.) But more importantly, knowing about the perils of tribal psychology can help us be more effective when working in groups — and therefore, to gain traction. [_Traction Heroes episode 46: Tribal Psychology_](https://www.tractionheroes.com/2439976/episodes/19905275-tribal-psychology)

yesterday • 1 votes
Zenith Data Systems, Part I

The quality goes in before the name goes on

2 days ago • 1 votes
+ Gone too far

There's a chance, slim as it might be, that things won't go the way the've gone the last 30 years with digitization the moving to rental models for everyhing.

2 days ago • 1 votes
📚 BoredReading

You seem to be enjoying this.

Join free to unlock everything.

Create free account

Already have an account? Sign in