single file migration script without any libraries in Python

migrate.py

"""This is the demo to describe how to migrate SQLite3 database in just single file migration script. 
Should be useful for minimal Python environments to run these scripts robust.
"""

import sqlite3


conn = sqlite3.connect("myapp.db")

# Migraiton class. It is okay to use tuple, but it is readable to declare such class.
class Migration:
    def __init__(self, name: str, sql: str):
        self.name = name
        self.sql = sql


MIGRATIONS = [
    Migration(
        "001_initialize_tables",
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            created_at DATETIME,
            name VARCHAR(255),
            is_deleted BOOLEAN
        )
        """,
    ),
]



def migrate_latest():
    cur = conn.cursor()
    # Ensure the migration table exists
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS migrations (
            name VARCHAR(255) PRIMARY KEY
        )
        """
    )
    
    # Execute migrations
    for migration in MIGRATIONS:
        already_migrated = cur.execute(
            """
            select * from migrations where name = ?
            """,
            (migration.name,),
        ).fetchone()
        if not already_migrated:
            cur.execute(migration.sql)
            cur.execute("insert into migrations values (?)", (migration.name,))
            print(f"Migrated: {migration.name}")
    conn.commit()
    conn.close()
    print("Migration done!")


if __name__ == "__main__":
    migrate_latest()