Back
+20 XP
Concept

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()

# 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>

Checkpoint

Why should database schema changes be versioned in code rather than modified manually via GUI tools? 🧠

Practice

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. 🎯

Terminal
01
Tap code blocks below to assemble
Available Blocks