SQLite vs PostgreSQL
Dialect differences, Python integration, and choosing the right database
Skills taught: sql.sqlite-vs-postgresql, sql.python-sqlite3, sql.python-psycopg2, sql.choosing-database
Key dialect differences
-- ─────────────────────────────────────────
-- SQLite PostgreSQL
-- ─────────────────────────────────────────
-- AUTO-INCREMENT
-- SQLite: INTEGER PRIMARY KEY AUTOINCREMENT
-- PostgreSQL: id SERIAL PRIMARY KEY
-- id INTEGER GENERATED ALWAYS AS IDENTITY
-- CURRENT TIMESTAMP
-- SQLite: DATETIME('now'), DATE('now')
-- PostgreSQL: NOW(), CURRENT_TIMESTAMP, CURRENT_DATE
-- BOOLEANS (SQLite has no BOOLEAN type)
-- SQLite: INTEGER (0 = false, 1 = true)
-- PostgreSQL: BOOLEAN (TRUE/FALSE)
-- STRING functions
-- SQLite: || for concatenation, instr(), substr()
-- PostgreSQL: || or CONCAT(), position(), substring()
-- JSON
-- SQLite: json() (basic), json_extract()
-- PostgreSQL: JSON, JSONB (binary, indexed), -> and ->> operators
-- FULL TEXT SEARCH
-- SQLite: FTS5 extension
-- PostgreSQL: tsvector, tsquery, @@ operator, GIN indexes
-- ENABLE FOREIGN KEYS (SQLite only)
PRAGMA foreign_keys = ON;
Python integration
# ── SQLite (stdlib, no install needed) ──
import sqlite3
# Connect to a file (creates it if it doesn't exist)
conn = sqlite3.connect('mydb.db')
conn.row_factory = sqlite3.Row # rows as dict-like objects
# Enable foreign keys
conn.execute('PRAGMA foreign_keys = ON')
cur = conn.cursor()
# Parameterized query — use ? placeholders
cur.execute('SELECT * FROM users WHERE city = ?', ('Delhi',))
for row in cur.fetchall():
print(row['name'], row['email'])
# Insert with transaction
with conn: # auto-commits or rolls back on exception
conn.execute(
'INSERT INTO users (name, email, city, joined_at) VALUES (?,?,?,?)',
('New User', 'new@example.com', 'Pune', '2024-07-01')
)
row_id = conn.execute('SELECT last_insert_rowid()').fetchone()[0]
print('Inserted ID:', row_id)
conn.close()
# ── PostgreSQL (install: pip install psycopg2-binary) ──
import psycopg2
from psycopg2.extras import RealDictCursor
# Connection string
conn = psycopg2.connect(
host='localhost',
port=5432,
dbname='mydb',
user='postgres',
password='secret'
# or: dsn='postgresql://postgres:secret@localhost:5432/mydb'
)
conn.autocommit = False # explicit transactions
# Use %s placeholders (not ?) for PostgreSQL
with conn.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute('SELECT * FROM users WHERE city = %s', ('Delhi',))
for row in cur.fetchall():
print(row['name'], row['email'])
# Commit / rollback
try:
with conn.cursor() as cur:
cur.execute(
'INSERT INTO users (name, email, city) VALUES (%s, %s, %s) RETURNING id',
('New User', 'new@example.com', 'Pune')
)
new_id = cur.fetchone()[0]
conn.commit()
except Exception:
conn.rollback()
raise
finally:
conn.close()
SQLAlchemy — one codebase for both
# pip install sqlalchemy
from sqlalchemy import create_engine, text
# SQLite
engine = create_engine('sqlite:///mydb.db')
# PostgreSQL
# engine = create_engine('postgresql+psycopg2://user:pass@localhost/mydb')
# Same code works for both!
with engine.connect() as conn:
result = conn.execute(text('SELECT * FROM users WHERE city = :city'),
{'city': 'Delhi'})
for row in result:
print(row.name, row.email)
Frequently Asked Questions
When should I use SQLite vs PostgreSQL?
SQLite: local/desktop apps, CLI tools, development databases, mobile apps, single-user or low-concurrency scenarios. Its main limit is single-writer — only one INSERT/UPDATE/DELETE at a time. PostgreSQL: any web application with concurrent users, when you need JSON, full-text search, robust role-based access, logical replication, or extension ecosystem. Start with SQLite for development — its schema is valid PostgreSQL SQL with minor adjustments.
How do I migrate from SQLite to PostgreSQL?
The SQL is largely compatible with small changes: AUTOINCREMENT → SERIAL or GENERATED ALWAYS AS IDENTITY, DATE('now') → NOW(), INTEGER boolean columns → BOOLEAN TRUE/FALSE. Use SQLAlchemy with Alembic migrations to manage schema changes across both databases. The pgloader tool can migrate SQLite data directly to PostgreSQL.