SQLite on the Box vs a Managed Postgres for a Vibe-Coded App

The community garden app ran on SQLite for fourteen months without a single database problem. That is the first thing I want on the record, because the rest of this article is about the week I spent moving it to Postgres, and it would be easy to read that as a verdict against SQLite. It is not. SQLite was the right choice for most of those fourteen months. It stopped being the right choice for reasons that had very little to do with performance and a lot to do with how the app had been written.

The app itself is small. Forty-two raised beds, a waitlist of about ninety people, a booking system for the shared tools in the shed, and a noticeboard. I described it to a coding agent over two evenings, and the agent picked the stack: a Node framework, an ORM, and SQLite in a file called garden.db. I deployed it to a small VPS, set up a nightly backup of the file, and forgot about it.

If you are staring at a freshly generated app right now, with a DATABASE_URL="file:./dev.db" in the .env and a README that says “switch to Postgres for production,” this is what I wish I had known before deciding.

Why the agent reached for SQLite, and why it was fine

Agents default to SQLite for the same reason tutorials do. There is nothing to install, nothing to configure, and nothing to connect to. The database is a file next to the code. For a first run on a laptop, that is ideal, and plenty of agent-built apps never need anything else.

On a single VPS, SQLite is not a toy. With write-ahead logging turned on, readers do not block the writer, and a small app with a few writes per minute will never notice there is only one writer at a time. Queries are fast because there is no network hop. Backups are a copy of one file, as long as you take them properly: SQLite’s online backup command or VACUUM INTO, not a raw cp of a file that might be mid-write. I also ran Litestream for a while, which streams changes to object storage continuously, so the worst case was losing a few seconds rather than a day.

For the garden, that meant zero database cost, zero connection problems, and restores I actually tested twice by copying the backup onto my laptop and opening the app against it. Managed Postgres would have added a monthly bill, a network dependency, and a connection string to protect, in exchange for capabilities the app did not use.

What changed

Three things happened in the same spring.

The garden committee asked for a second, separate instance for a sister garden across town, sharing the waitlist. The treasurer wanted to connect a spreadsheet tool directly to the booking data for reports. And I wanted to move the web front end onto a serverless platform, because a volunteer who knew nothing about servers was taking over maintenance and I did not want to hand them an SSH key.

Each of those is a textbook reason to leave SQLite. Two app instances cannot safely share one SQLite file over a network. An external reporting tool needs a network database to connect to. And a serverless platform has no persistent disk for garden.db to live on. SQLite’s whole design is that the database and the app share a machine. All three requests broke that assumption at once.

None of this was about scale. The garden still had ninety people on the waitlist. It was about shape: more than one thing now needed to reach the data, and not all of those things lived on the box.

The migration was harder than the data volume suggested

I expected the move to take an evening. The data was a few thousand rows. It took most of a week, and almost none of that time was spent moving rows. It was spent discovering what SQLite had quietly allowed.

Dates in four formats. SQLite does not have a real date type. It stores dates as text, numbers, or whatever you give it. Over fourteen months, the agent had written features in different sessions, and the booking table had dates stored as ISO strings, as Unix timestamps in seconds, as timestamps in milliseconds, and in one early feature as "2025-04-03 18:00" with no time zone. SQLite compared them as best it could. Postgres refused to put 1712167200 into a timestamptz column.

Booleans that were not booleans. Most rows had 0 and 1. A handful, from an import script the agent wrote for the original waitlist spreadsheet, had the strings "true" and "false". SQLite had accepted them into an integer column without complaint, because by default it applies type affinity rather than enforcing types.

Case-insensitive searches that were not written as such. The member search used LIKE. In SQLite, LIKE is case-insensitive for ASCII by default. In Postgres, it is case-sensitive. After the migration, searching for “maria” no longer found “Maria.” The fix was trivial, but it was a silent behaviour change nobody would have noticed until a committee member complained.

Migrations that had rebuilt tables. SQLite’s ALTER TABLE is limited, so when the agent changed a column type or dropped a constraint, the ORM’s migration tool did what it has to on SQLite: create a new table, copy everything across, drop the old one, and rename. That works, but it meant the migration history was full of SQLite-specific steps that could not be replayed against Postgres. I had to generate a fresh baseline migration for Postgres and treat the old history as archaeology.

Every one of these was invisible while the app ran on SQLite. The app worked. The data was, loosely speaking, correct. It was just much less strict than I had assumed, and the strictness only showed up when I moved to a database that enforces it.

What I would do differently on day one

I do not think the answer is “start with Postgres.” For many vibe-coded apps, that adds cost and moving parts for a future that may never come. But there are a few things I now do on day one that would have turned my week into an evening.

Use STRICT tables, or the equivalent. SQLite has supported STRICT tables since version 3.37. They enforce column types the way other databases do. If your ORM does not create them by default, a one-line instruction to the agent to add them, or a check constraint on each column, stops the four-date-formats problem before it starts.

Tell the agent how to store time. I now put one rule in the project instructions: all timestamps are stored as UTC ISO 8601 strings, with no exceptions and no numeric timestamps. That rule would have saved two days.

Keep the SQL portable where it is cheap. Use the ORM’s case-insensitive search helper instead of relying on LIKE behaviour. Avoid SQLite-only functions unless you have a reason. The agent will do this if asked.

Run the test suite against Postgres occasionally, even if production is SQLite. A CI job that spins up Postgres in a container and runs the tests once a week would have flagged every one of these problems months before they mattered.

How I decide now

Here is the rule I use when someone asks me whether to keep the agent’s SQLite default or switch to a managed Postgres before launch.

Keep SQLite on the box when:

• The app runs as one process on one machine with a persistent disk
• Only the app itself needs to reach the data
• Writes are modest; dozens per second is fine, thousands is not
• You have a tested backup, whether that is a nightly online backup, Litestream, or both
• You are happy to be the one who restores it

Move to a managed Postgres when:

• The app runs on a serverless platform, or on more than one instance
• Something other than the app needs a network connection to the data: a reporting tool, a second service, a background worker on another machine
• You need someone else to handle backups, point-in-time recovery, and upgrades
• You are handing the app to someone who should never have to think about a file on a disk

If you are in the first list today but can see the second list coming, stay on SQLite and make it strict. You get the simplicity now and a cheap migration later.

Where the garden ended up

The garden now runs on a small managed Postgres on a free-ish tier. The web app is on a serverless platform, the sister garden reads from the same waitlist, and the treasurer’s spreadsheet connects with a read-only user. The new maintainer has never seen a terminal and does not need to. The monthly cost went from zero to roughly the price of a bag of compost.

I do not regret the SQLite year. It was simple, fast, and cheap, and it let the app exist before anyone knew whether the committee would actually use it. What I regret is letting the database be loose for fourteen months because nothing forced it to be strict. SQLite was never the problem. The problem was that it was so forgiving that neither I nor the agent ever had to decide what a date was.