Database Migrations Without Heavy ORMs
Production databases evolve over time. Tracking a `schema_version` integer in a dedicated table lets you execute sequential SQL migration scripts reliably without massive ORM dependencies 🛠️.
import sqlite3
conn = sqlite3.connect(":memory:")
def apply_migrations(db):
db.execute("CREATE TABLE IF NOT EXISTS _meta (version INT)")
row = db.execute("SELECT version FROM _meta").fetchone()
current_ver = row[0] if row else 0
# Migration 1:
if current_ver < 1:
db.execute("CREATE TABLE users (id INT, email TEXT)")
db.execute("INSERT INTO _meta VALUES (1)")
print("Applied migration v1: created users table 🛠️")
apply_migrations(conn)
conn.close()
# 27. Database Migrations Without Heavy ORMs Production databases evolve over time. Tracking a `schema_version` integer in a dedicated table lets you execute sequential SQL migration scripts reliably without massive ORM dependencies 🛠️. ```python import sqlite3 conn = sqlite3.connect(":memory:") def apply_migrations(db): db.execute("CREATE TABLE IF NOT EXISTS _meta (version INT)") row = db.execute("SELECT version FROM _meta").fetchone() current_ver = row[0] if row else 0 # Migration 1: if current_ver < 1: db.execute("CREATE TABLE users (id INT, email TEXT)") db.execute("INSERT INTO _meta VALUES (1)") print("Applied migration v1: created users table 🛠️") apply_migrations(conn) conn.close() ``` Lightweight migration scripts keep your codebase independent of bloated ORMs while ensuring continuous deployment pipelines upgrade schemas seamlessly! 🛠️ --- ### Question Why should database schema changes be versioned in code rather than modified manually via GUI tools? 🧠 - [ ] a. Versioned migrations ensure all environments (dev, staging, production) apply identical changes deterministically 🛠️ - [ ] b. GUI database tools are deprecated in modern operating systems - [ ] c. Manual changes disable SQL query indexes - [ ] d. SQL commands cannot run without migration metadata tables --- ### Challenge Create table `meta (v INT)`. Write `migrate(db)` that checks if `v` exists; if table empty, insert `1` and create table `orders (id INT)`. Run `migrate` and verify `orders` exists. 🎯 <details> <summary>Solution</summary> ```python import sqlite3 conn = sqlite3.connect(":memory:") conn.execute("CREATE TABLE meta (v INT)") def migrate(db): row = db.execute("SELECT v FROM meta").fetchone() if not row: db.execute("CREATE TABLE orders (id INT)") db.execute("INSERT INTO meta VALUES (1)") db.commit() migrate(conn) tables = [r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()] print("orders" in tables) conn.close() ``` </details>