SQLite to PostgreSQL: Migrating Your Flask App

SQLite works great when you're hacking alone, but once you go multi-user or deploy to a server, you need PostgreSQL. Here's how to migrate without losing data.

Why PostgreSQL?

• True concurrent writes (SQLite has table-level locking)
• Full ACID transactions
• Advanced indexes (GIN, GiST)
• Native JSON columns
• Better tooling: pgAdmin, pg_dump, etc.

Install psycopg2

pip install psycopg2-binary

Update Your Connection Code

Before (SQLite):

import sqlite3

def get_db():
    return sqlite3.connect('app.db')

After (PostgreSQL):

import psycopg2
import psycopg2.extras
import os

DATABASE_URL = os.environ['DATABASE_URL']

def get_db():
    conn = psycopg2.connect(DATABASE_URL)
    conn.autocommit = False
    return conn

SQL Syntax Differences

Migrating Your Schema

-- PostgreSQL version
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   TEXT NOT NULL UNIQUE,
    email      TEXT NOT NULL UNIQUE,
    password   TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Migrating Data

# migrate.py
import sqlite3
import psycopg2

src  = sqlite3.connect('old.db')
src.row_factory = sqlite3.Row
dest = psycopg2.connect(os.environ['DATABASE_URL'])

cur_src  = src.cursor()
cur_dest = dest.cursor()

cur_src.execute('SELECT * FROM users')
for row in cur_src.fetchall():
    cur_dest.execute(
        'INSERT INTO users (id, username, email, password) VALUES (%s,%s,%s,%s)',
        (row['id'], row['username'], row['email'], row['password'])
    )

dest.commit()
print("Migration complete!")

Environment Variables

# .env
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb

# load in app.py
from dotenv import load_dotenv
load_dotenv()

Congratulations — your app is now production-grade.