How do I move an existing app from SQLite to PostgreSQL?

Moving an app from SQLite to PostgreSQL is usually a mix of schema changes, data migration, and app-code updates. The exact steps depend on your stack, but here’s the practical path most teams follow.

1) Check for SQLite-specific assumptions

Before migrating, look for features or behaviors that differ:

• Typing is looser in SQLite than PostgreSQL
• SQLite often allows:
  • storing any type in any column
  • implicit conversions that PostgreSQL won’t accept
• Common SQLite-specific SQL patterns:
  • INSERT OR REPLACE
  • AUTOINCREMENT
  • datetime('now')
  • LIKE/collation quirks
• SQLite allows more relaxed schema definitions, which PostgreSQL will reject

2) Update your schema for PostgreSQL

You’ll likely need to adjust:

• Primary keys
  • SQLite: INTEGER PRIMARY KEY AUTOINCREMENT
  • PostgreSQL: GENERATED ALWAYS AS IDENTITY or SERIAL (older style)
• Boolean fields
  • SQLite often stores booleans as 0/1
  • PostgreSQL has a real BOOLEAN type
• Date/time fields
  • Use TIMESTAMP, TIMESTAMPTZ, or DATE
• Text vs numeric
  • PostgreSQL is strict about types, so data must match the column type

Example:

-- SQLite style
id INTEGER PRIMARY KEY AUTOINCREMENT,
is_active INTEGER NOT NULL DEFAULT 1

-- PostgreSQL style
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
is_active BOOLEAN NOT NULL DEFAULT TRUE

3) Choose a migration strategy

Common options:

A. Dump and transform

• Export SQLite data
• Convert schema and data
• Import into PostgreSQL

Good for smaller apps or one-time migrations.

B. Use a migration tool

Depending on your stack:

• Django: change DB settings, run migrations, then use data export/import
• Rails: switch adapter, adjust migrations, move data
• Node.js: use Knex/Sequelize/TypeORM migrations
• Python: Alembic, Django ORM, etc.

C. Use a conversion tool

Some tools can help convert SQLite dumps to PostgreSQL-compatible SQL, but usually you still need manual cleanup.

4) Export data from SQLite

A simple way is to export tables to CSV or SQL.

Example with sqlite3:

sqlite3 app.db .dump > sqlite_dump.sql

But note: .dump output is SQLite syntax, so it won’t run directly on PostgreSQL without edits.

For CSV export:

sqlite3 -header -csv app.db "SELECT * FROM users;" > users.csv

5) Create the PostgreSQL database and schema

Create the target database, then apply your PostgreSQL schema first.

Example:

createdb myapp
psql myapp < schema.sql

You may need to:

• rename types
• remove SQLite-specific syntax
• create indexes and constraints explicitly

6) Import the data

For CSV import, PostgreSQL’s COPY is commonly used:

COPY users FROM '/path/to/users.csv' DELIMITER ',' CSV HEADER;

If you’re importing through an app or script, use parameterized inserts or bulk copy methods.

7) Fix sequences/identity values

If you imported IDs manually, make sure PostgreSQL’s sequence is updated so future inserts don’t collide.

Example:

SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE(MAX(id), 1)
) FROM users;

8) Update the application connection/config

Change the database connection string from SQLite to PostgreSQL.

Examples:

• SQLite: file-based path like sqlite:///app.db
• PostgreSQL: host/user/password/dbname like postgresql://user:pass@host:5432/dbname

Also update:

• pooling settings
• SSL settings if needed
• environment variables
• ORM dialect/driver package

9) Test carefully

Verify:

• all tables and relations exist
• CRUD operations work
• transactions behave as expected
• queries still return correct results
• pagination, sorting, and filtering work
• unique constraints and foreign keys are enforced

10) Watch out for query differences

PostgreSQL is stricter and more standards-compliant. Common fixes:

• LIMIT/OFFSET generally works in both
• string concatenation:
  • SQLite may use || similarly, but null handling can differ
• INSERT OR REPLACE:
  • in PostgreSQL use INSERT ... ON CONFLICT ... DO UPDATE
• case-insensitive search:
  • SQLite behavior differs from PostgreSQL
  • consider ILIKE in PostgreSQL
• date/time functions differ

11) Deploy with a rollback plan

Before switching production:

• take a backup of the SQLite DB
• test the migration on staging
• verify app behavior under real load
• keep a rollback path in case something fails

---

Typical migration workflow

1. Backup SQLite database
2. Audit SQLite-specific SQL and schema
3. Convert schema to PostgreSQL
4. Export data from SQLite
5. Import into PostgreSQL
6. Fix sequences/indexes/constraints
7. Update app config and ORM driver
8. Test thoroughly
9. Deploy

---

If you want, I can also give you:

• a step-by-step migration for Django / Rails / Node / Python
• a script to convert SQLite dumps to PostgreSQL
• or help you migrate a specific schema if you paste it here.