Migrate an AI-Built App From SQLite to Postgres
SQLite types are suggestions, Postgres types are rules. Fix the schema before you move a row, and reset your sequences after.
Migrate an AI-Built App From SQLite to Postgres
To migrate an AI-built app from SQLite to Postgres, fix the schema differences before you move a single row. SQLite stores types loosely, has no real boolean or timestamp type, and will happily hold a string in an integer column. Postgres will not. A straight data copy usually appears to work, then produces wrong results weeks later on the rows where the types were never what the schema claimed. The data move is the easy half.
When you actually need to move
SQLite is a serious database and most AI-built apps ship on it longer than their authors expect. Move when you hit one of these, not before:
Concurrent writes. SQLite serialises writers. One long write blocks the others, and under load you start seeing database is locked.
More than one application server. SQLite lives on one filesystem. Two instances need shared storage, which is where the real pain starts.
A platform that resets the filesystem on deploy. Several hosts do this. Your database disappears on the next push.
Features you need and SQLite lacks: full-text ranking at scale, JSON indexing, concurrent schema changes, row-level security.
Notably absent from that list: size. SQLite handles databases into the hundreds of gigabytes without complaint, and read-heavy apps run on it beautifully. If your only reason is that Postgres feels more professional, that is not a reason. The deeper trade-offs are in choosing a database for an AI-built app.
The four differences that break the migration quietly
These are the ones that pass every smoke test and surface later:
SQLite behaviour | Postgres behaviour | What breaks |
|---|---|---|
Booleans stored as 0 and 1 integers | Real boolean type | Queries comparing to 0 or 1 stop matching |
Datetimes stored as text in whatever format was written | timestamptz with real parsing | Mixed formats in one column fail the copy, or land with no timezone |
INTEGER PRIMARY KEY AUTOINCREMENT | Sequences or identity columns | Sequence starts at 1 and collides with every imported row |
Dynamic typing: any value in any column | Strict typing enforced on insert | Rows that were always malformed finally get rejected |
The sequence one is the most common production incident from this migration and the easiest to prevent. After importing rows with explicit ids, the Postgres sequence still thinks the next id is 1, so your first new insert collides with an existing row. Reset every sequence to the maximum id in the table immediately after the import, before the app writes anything.
The datetime one is the most insidious. If your app wrote timestamps through more than one code path, and AI-built apps very often do, you will have ISO strings, Unix epochs, and locale-formatted dates in the same column. Audit it before migrating with a query grouping by string length, which surfaces the variants in about ten seconds.
Migrate an AI-built app from SQLite to Postgres, step by step
Freeze schema changes. Do not ship anything that alters tables while the migration is in flight.
Audit the data: for every column, check that the stored values match the declared type. Booleans, dates and numerics first.
Write the Postgres schema by hand, or have the agent generate it and then read every line. Do not let a tool infer it from SQLite, because it will faithfully reproduce the looseness.
Normalise the data in SQLite first, in place. Convert all datetimes to ISO 8601 UTC. Coerce booleans. Fix or delete rows that cannot be coerced.
Export to CSV per table, import with COPY. It is far faster than row-by-row inserts and it fails loudly on type violations, which you want.
Reset every sequence to max(id).
Add foreign keys, indexes and constraints after the import, not before. Constraints during a bulk load are slow and produce confusing ordering failures.
Run a row count and a checksum comparison per table. Counts alone will not catch silently coerced values.
Point a staging copy of the app at the new database and run your test suite against it.
Step 3 is where an AI coding agent earns its keep and also where it needs supervision. Ask it to generate the Postgres DDL from your SQLite schema and then ask it separately to list every place the two schemas differ in semantics, not just syntax. Those are different questions and it will answer the second one poorly unless you ask it explicitly. The general caution in letting an agent run database migrations applies at full strength here.
The cutover
For an app with real users, the simplest safe cutover is a short maintenance window, and you should not be embarrassed by that. A five minute window at a quiet hour costs you almost nothing and removes an entire class of dual-write bugs.
Put the app in read-only mode. Most frameworks can do this with a middleware that rejects non-GET requests.
Run the final export and import. You have already rehearsed it, so you know how long it takes.
Switch the connection string. Keep the old SQLite file untouched, not deleted.
Take the app out of read-only mode and watch error rates for an hour.
Keep the SQLite file for at least a month. It is a few megabytes and it is the only real rollback you have. The temptation to delete it once things look fine is how people discover, in week three, that one table imported with a truncated column.
If your app is already handling money or anything else you cannot reconstruct, rehearse the entire cutover against a copy first and time it. The rehearsal is also where you find out that your ORM's connection pooling settings, which never mattered on SQLite, now matter quite a lot. Related reading on the operational side: staging and production for an AI-built app.
What changes in your code afterwards
Expect to touch more application code than you planned. Common ones:
Raw SQL with SQLite-specific functions: strftime, datetime(now), group_concat. Postgres equivalents differ in both name and behaviour.
Case sensitivity. SQLite LIKE is case-insensitive for ASCII by default, Postgres LIKE is not. Every search feature built on LIKE changes behaviour.
Boolean comparisons against 0 and 1 in queries or ORM filters.
Anything relying on rowid.
Connection handling. A single file handle becomes a pool with limits you can exhaust.
The case sensitivity one is worth a specific test, because it usually degrades silently into a search box that stops finding things rather than an error anyone notices. If search is a real feature of your app, adding search to an AI-built app is worth revisiting after the move, since Postgres gives you options SQLite did not. And if any of this leaves the app misbehaving in ways you cannot place, what to do when your AI-built app breaks in production covers the triage, and the guide to building an app with AI is the starting point if you are earlier in the process than this.
Frequently asked questions
How do I migrate an AI-built app from SQLite to Postgres safely?
Normalise the data inside SQLite first, write the Postgres schema explicitly rather than inferring it, import with COPY so type violations fail loudly, then reset every sequence to the maximum existing id before the app writes anything. A short read-only window during cutover removes most of the remaining risk.
When should I move off SQLite?
When you need concurrent writers, more than one application server, or a host that does not give you a persistent filesystem. Database size is rarely the reason, since SQLite handles very large read-heavy databases without difficulty.
Why do my inserts fail with a duplicate key after migrating to Postgres?
Because the sequence behind your primary key was not advanced after importing rows with explicit ids. It still believes the next value is 1. Set each sequence to the current maximum id in its table immediately after the import and before any application writes.
Will my dates survive the migration from SQLite to Postgres?
Only if they were written consistently, which in an AI-built app is worth checking rather than assuming. SQLite stores datetimes as text, so a column can hold several formats at once. Convert them all to ISO 8601 in UTC before exporting.
Can an AI coding agent do this migration for me?
It can do most of the mechanical work well, especially the DDL translation and the export scripts. What it does poorly unless asked directly is identify semantic differences between the two schemas, so ask for that as a separate question and read the answer carefully.
How did this land?
About the author
Developer Advocate
Steve builds something with Swarmz every week and writes up what worked, what broke, and what he'd do differently. Tutorials and hands-on guides are his lane.
