Simple SQL Schema Migration

I'm working on an application to manage my streaming media for my Roku player using sqlite3 and Python for everything. One thing I've learned over the years is that your SQL schema always changes. Once your code is in production you always have something you need to change about it, whether it's adding a new column, changing a type or tables to support new features. I wanted a way to automatically update the database schema when a new version of the code is installed. I don't want to jump into the complexity of using SQLAlchemy and migrate so I came up with this simple method. The database has a table named schema with a single row with the current schema version in it. This class checks the current version and executes any missing commands, bringing it up to the latest version.

class DbSchema(object):
    """
    Database schema creation and modification
    """

    # Schema revisions, rev[0], etc. is a list of SQL operations to run to
    # bring the database up to date.
    sql = ["""  create table users(id INTEGER PRIMARY KEY, username UNIQUE, password, email);
                create table sources(id INTEGER PRIMARY KEY, name, type, path UNIQUE);

                create table schema(version);
                insert into schema(version) values(1);
            """,
            # Add default values
            """ insert into users(username, password) values("admin","badpassword");

                update schema set version=2;
            """,
            # Add media table
            """ create table media(
                    id INTEGER PRIMARY KEY,
                    name,
                    path
                );

                update schema set version=3;
            """,
        ]

    def __init__(self, database):
        self.database = database

    def upgrade(self):
        """
        Upgrade the database to the current schema version
        """
        # Get the current schema version number
        conn = sqlite3.connect(self.database)
        conn.row_factory = sqlite3.Row
        cur = conn.cursor()
        try:
            cur.execute("select version from schema")
            version = cur.fetchone()['version']
        except:
            version = 0

        if len(self.sql) > version:
            for update in self.sql[version:]:
                cur.executescript(update)
        cur.close()
        conn.close()

Initialize it with the path to your database, and then call the upgrade() method on it.

schema = DbSchema("./library.db")
schema.upgrade()

Add new tables, changes to old tables, default data, etc. to new strings in the sql list. Make sure to update the schema table's version as the last step for each revision. As long as you only make database changes via this class you will ensure that the tables are ready for your new code when each new revision is released. This version is being used with sqlite3, but works just as well with mysql or postgresql.