Set up Alembic for database migrations

Message ID 20200222213126.GA934040@tsubame.mg0.fr
State New
Headers show
Series
  • Set up Alembic for database migrations
Related show

Commit Message

Frédéric Mangano-Tarumi Feb. 22, 2020, 9:31 p.m. UTC
---
 INSTALL                   |  4 +-
 TESTING                   |  3 +-
 alembic.ini               | 86 +++++++++++++++++++++++++++++++++++++++
 aurweb/initdb.py          |  9 ++++
 aurweb/schema.py          |  8 ++++
 migrations/README         | 48 ++++++++++++++++++++++
 migrations/env.py         | 73 +++++++++++++++++++++++++++++++++
 migrations/script.py.mako | 24 +++++++++++
 8 files changed, 252 insertions(+), 3 deletions(-)
 create mode 100644 alembic.ini
 create mode 100644 migrations/README
 create mode 100644 migrations/env.py
 create mode 100644 migrations/script.py.mako

Comments

Frédéric Mangano-Tarumi Feb. 23, 2020, 6:33 p.m. UTC | #1
Frédéric Mangano-Tarumi [2020-02-22 22:31:26 +0100]
> diff --git a/aurweb/initdb.py b/aurweb/initdb.py
> index e3e96503..c02fb961 100644
> --- a/aurweb/initdb.py
> +++ b/aurweb/initdb.py
> @@ -31,10 +33,17 @@ def feed_initial_data(conn):
>  
>  
>  def run(args):
> +    # Ensure Alembic is fine before we do the real work, in order not to fail at
> +    # the last step and leave the database in an inconsistent state. The
> +    # configuration is loaded lazily, so we query it to force its loading.
> +    alembic_config = alembic.config.Config('alembic.ini')
> +    alembic_config.get_main_option('script_location')
> +
>      engine = sqlalchemy.create_engine(aurweb.db.get_sqlalchemy_url(),
>                                        echo=(args.verbose >= 1))
>      aurweb.schema.metadata.create_all(engine)
>      feed_initial_data(engine.connect())
> +    alembic.command.stamp(alembic_config, 'head')
>  
>  
>  if __name__ == '__main__':

I’ve realized this chunk breaks the test suite because it requires
initdb to be run from the top-level directory. The test suite doesn’t
need Alembic so I’ll add a flag to initdb to disable it.
Lukas Fleischer Feb. 26, 2020, 1 p.m. UTC | #2
On Sat, 22 Feb 2020 at 22:31:26, Frédéric Mangano-Tarumi wrote:
> ---
>  INSTALL                   |  4 +-
>  TESTING                   |  3 +-
>  alembic.ini               | 86 +++++++++++++++++++++++++++++++++++++++
>  aurweb/initdb.py          |  9 ++++
>  aurweb/schema.py          |  8 ++++
>  migrations/README         | 48 ++++++++++++++++++++++
>  migrations/env.py         | 73 +++++++++++++++++++++++++++++++++
>  migrations/script.py.mako | 24 +++++++++++
>  8 files changed, 252 insertions(+), 3 deletions(-)
>  create mode 100644 alembic.ini
>  create mode 100644 migrations/README
>  create mode 100644 migrations/env.py
>  create mode 100644 migrations/script.py.mako

Looks great, thanks! I merged this patch and the two followup patches
into the pu branch. I will do some more testing and merge them to master
if there are no issues.

We should also think about phasing out the text files in the upgrading/
subdirectory. Maybe we should simply keep them for one or two releases
and remove the directory entirely later? If anybody ever still needs
them after they have been removed, they can still be recovered from the
Git history...
Frédéric Mangano-Tarumi Feb. 26, 2020, 9:10 p.m. UTC | #3
Lukas Fleischer [2020-02-26 14:00:51 +0100]
> We should also think about phasing out the text files in the upgrading/
> subdirectory. Maybe we should simply keep them for one or two releases
> and remove the directory entirely later? If anybody ever still needs
> them after they have been removed, they can still be recovered from the
> Git history...

I don’t know who reads the upgrading instructions, but how about having
a 4.x.0-and-later.txt saying that SQL migrations are now handled by
Alembic, and referring to migrations/README?

Let’s also keep in mind that some upgrades might require manual
intervention anyway. In my opinion these breaking changes should be
documented in commit messages and synthesized in release notes, but
whatever the AUR team is used to should be fine too.
Lukas Fleischer Feb. 27, 2020, 11:28 a.m. UTC | #4
On Wed, 26 Feb 2020 at 22:10:09, Frédéric Mangano-Tarumi wrote:
> Lukas Fleischer [2020-02-26 14:00:51 +0100]
> > We should also think about phasing out the text files in the upgrading/
> > subdirectory. Maybe we should simply keep them for one or two releases
> > and remove the directory entirely later? If anybody ever still needs
> > them after they have been removed, they can still be recovered from the
> > Git history...
> 
> I don\u2019t know who reads the upgrading instructions, but how about having
> a 4.x.0-and-later.txt saying that SQL migrations are now handled by
> Alembic, and referring to migrations/README?
> 
> Let\u2019s also keep in mind that some upgrades might require manual
> intervention anyway. In my opinion these breaking changes should be
> documented in commit messages and synthesized in release notes, but
> whatever the AUR team is used to should be fine too.

I like that suggestion.

In the past, we did not have any release notes, except for announcement
emails sent to the mailing lists. I am not sure how easy it would be to
collect those emails, compile a CHANGES.md file and add it to the source
tree, alongside with upgrade instructions extracted from the upgrading/
subdirectory. This would allow us to get rid of the directory entirely.

Patch

diff --git a/INSTALL b/INSTALL
index 68fe5dcd..7087aca2 100644
--- a/INSTALL
+++ b/INSTALL
@@ -47,8 +47,8 @@  read the instructions below.
 
 4) Install Python modules and dependencies:
 
-    # pacman -S python-mysql-connector python-pygit2 python-srcinfo python-sqlalchemy
-    # pacman -S python-bleach python-markdown
+    # pacman -S python-mysql-connector python-pygit2 python-srcinfo python-sqlalchemy \
+                python-bleach python-markdown python-alembic
     # python3 setup.py install
 
 5) Create a new MySQL database and a user and import the aurweb SQL schema:
diff --git a/TESTING b/TESTING
index 190043f9..4a1e6f4c 100644
--- a/TESTING
+++ b/TESTING
@@ -11,7 +11,8 @@  INSTALL.
 
 2) Install the necessary packages:
 
-    # pacman -S --needed php php-sqlite sqlite words fortune-mod python python-sqlalchemy
+    # pacman -S --needed php php-sqlite sqlite words fortune-mod \
+                         python python-sqlalchemy python-alembic
 
    Ensure to enable the pdo_sqlite extension in php.ini.
 
diff --git a/alembic.ini b/alembic.ini
new file mode 100644
index 00000000..6d3a3929
--- /dev/null
+++ b/alembic.ini
@@ -0,0 +1,86 @@ 
+# A generic, single database configuration.
+
+[alembic]
+# path to migration scripts
+script_location = migrations
+
+# template used to generate migration files
+# file_template = %%(rev)s_%%(slug)s
+
+# timezone to use when rendering the date
+# within the migration file as well as the filename.
+# string value is passed to dateutil.tz.gettz()
+# leave blank for localtime
+# timezone =
+
+# max length of characters to apply to the
+# "slug" field
+# truncate_slug_length = 40
+
+# set to 'true' to run the environment during
+# the 'revision' command, regardless of autogenerate
+# revision_environment = false
+
+# set to 'true' to allow .pyc and .pyo files without
+# a source .py file to be detected as revisions in the
+# versions/ directory
+# sourceless = false
+
+# version location specification; this defaults
+# to alembic/versions.  When using multiple version
+# directories, initial revisions must be specified with --version-path
+# version_locations = %(here)s/bar %(here)s/bat alembic/versions
+
+# the output encoding used when revision files
+# are written from script.py.mako
+# output_encoding = utf-8
+
+# the database URL is generated in env.py
+# sqlalchemy.url = driver://user:pass@localhost/dbname
+
+
+[post_write_hooks]
+# post_write_hooks defines scripts or Python functions that are run
+# on newly generated revision scripts.  See the documentation for further
+# detail and examples
+
+# format using "black" - use the console_scripts runner, against the "black" entrypoint
+# hooks=black
+# black.type=console_scripts
+# black.entrypoint=black
+# black.options=-l 79
+
+# Logging configuration
+[loggers]
+keys = root,sqlalchemy,alembic
+
+[handlers]
+keys = console
+
+[formatters]
+keys = generic
+
+[logger_root]
+level = WARN
+handlers = console
+qualname =
+
+[logger_sqlalchemy]
+level = WARN
+handlers =
+qualname = sqlalchemy.engine
+
+[logger_alembic]
+level = INFO
+handlers =
+qualname = alembic
+
+[handler_console]
+class = StreamHandler
+args = (sys.stderr,)
+level = NOTSET
+formatter = generic
+
+[formatter_generic]
+format = %(levelname)-5.5s [%(name)s] %(message)s
+datefmt = %H:%M:%S
diff --git a/aurweb/initdb.py b/aurweb/initdb.py
index e3e96503..c02fb961 100644
--- a/aurweb/initdb.py
+++ b/aurweb/initdb.py
@@ -1,6 +1,8 @@ 
 import aurweb.db
 import aurweb.schema
 
+import alembic.command
+import alembic.config
 import argparse
 import sqlalchemy
 
@@ -31,10 +33,17 @@  def feed_initial_data(conn):
 
 
 def run(args):
+    # Ensure Alembic is fine before we do the real work, in order not to fail at
+    # the last step and leave the database in an inconsistent state. The
+    # configuration is loaded lazily, so we query it to force its loading.
+    alembic_config = alembic.config.Config('alembic.ini')
+    alembic_config.get_main_option('script_location')
+
     engine = sqlalchemy.create_engine(aurweb.db.get_sqlalchemy_url(),
                                       echo=(args.verbose >= 1))
     aurweb.schema.metadata.create_all(engine)
     feed_initial_data(engine.connect())
+    alembic.command.stamp(alembic_config, 'head')
 
 
 if __name__ == '__main__':
diff --git a/aurweb/schema.py b/aurweb/schema.py
index b1261e86..fde6512f 100644
--- a/aurweb/schema.py
+++ b/aurweb/schema.py
@@ -1,3 +1,11 @@ 
+"""
+Schema of aurweb's database.
+
+Changes here should always be accompanied by an Alembic migration, which can be
+usually be automatically generated. See `migrations/README` for details.
+"""
+
+
 from sqlalchemy import CHAR, Column, ForeignKey, Index, MetaData, String, TIMESTAMP, Table, Text, text
 from sqlalchemy.dialects.mysql import BIGINT, DECIMAL, INTEGER, TINYINT
 from sqlalchemy.ext.compiler import compiles
diff --git a/migrations/README b/migrations/README
new file mode 100644
index 00000000..5c032d9b
--- /dev/null
+++ b/migrations/README
@@ -0,0 +1,48 @@ 
+This directory contains Alembic’s environment for managing database migrations.
+
+From Alembic’s documentation: Alembic is a lightweight database migration tool
+for usage with the SQLAlchemy Database Toolkit for Python.
+https://alembic.sqlalchemy.org/en/latest/index.html
+
+
+Upgrading to the latest version
+-------------------------------
+
+Simply run `alembic upgrade head` from aurweb’s root.
+
+
+Creating new migrations
+-----------------------
+
+When working with Alembic and SQLAlchemy, you should never edit the database
+schema manually. Please proceed like this instead:
+
+1. Edit `aurweb/schema.py` to your liking.
+2. Run `alembic revision --autogenerate -m "your message"`
+3. Proofread the generated migration.
+4. Run `alembic upgrade head` to apply the changes to the database.
+5. Commit the new migration.
+
+To revert a migration, you may run `alembic downgrade -1` and then manually
+delete the migration file. Note that SQLite is limited and that it’s sometimes
+easier to recreate the database.
+
+For anything more complicated, please read Alembic’s documentation.
+
+
+Troubleshooting
+---------------
+
+- `ModuleNotFoundError: No module named 'aurweb'`
+
+  You may either install the aurweb module with pip, or set PYTHONPATH to your
+  aurweb repository. Since alembic must be run from the aurweb root, you may
+  simply use: `PYTHONPATH=. alembic …`.
+
+- `FAILED: No config file 'alembic.ini' found, or file has no '[alembic]' section`
+
+  You need to run Alembic from the project’s root, and not from `migrations/`.
+
+- `configparser.NoSectionError: No section: 'database'`
+
+  You need to set AUR_CONFIG, as explained in `TESTING`.
diff --git a/migrations/env.py b/migrations/env.py
new file mode 100644
index 00000000..1627e693
--- /dev/null
+++ b/migrations/env.py
@@ -0,0 +1,73 @@ 
+import aurweb.db
+import aurweb.schema
+
+from alembic import context
+import logging.config
+import sqlalchemy
+
+
+# this is the Alembic Config object, which provides
+# access to the values within the .ini file in use.
+config = context.config
+
+# Interpret the config file for Python logging.
+# This line sets up loggers basically.
+logging.config.fileConfig(config.config_file_name)
+
+# model MetaData for autogenerating migrations
+target_metadata = aurweb.schema.metadata
+
+# other values from the config, defined by the needs of env.py,
+# can be acquired:
+# my_important_option = config.get_main_option("my_important_option")
+# ... etc.
+
+
+def run_migrations_offline():
+    """Run migrations in 'offline' mode.
+
+    This configures the context with just a URL
+    and not an Engine, though an Engine is acceptable
+    here as well.  By skipping the Engine creation
+    we don't even need a DBAPI to be available.
+
+    Calls to context.execute() here emit the given string to the
+    script output.
+
+    """
+    context.configure(
+        url=aurweb.db.get_sqlalchemy_url(),
+        target_metadata=target_metadata,
+        literal_binds=True,
+        dialect_opts={"paramstyle": "named"},
+    )
+
+    with context.begin_transaction():
+        context.run_migrations()
+
+
+def run_migrations_online():
+    """Run migrations in 'online' mode.
+
+    In this scenario we need to create an Engine
+    and associate a connection with the context.
+
+    """
+    connectable = sqlalchemy.create_engine(
+        aurweb.db.get_sqlalchemy_url(),
+        poolclass=sqlalchemy.pool.NullPool,
+    )
+
+    with connectable.connect() as connection:
+        context.configure(
+            connection=connection, target_metadata=target_metadata
+        )
+
+        with context.begin_transaction():
+            context.run_migrations()
+
+
+if context.is_offline_mode():
+    run_migrations_offline()
+else:
+    run_migrations_online()
diff --git a/migrations/script.py.mako b/migrations/script.py.mako
new file mode 100644
index 00000000..2c015630
--- /dev/null
+++ b/migrations/script.py.mako
@@ -0,0 +1,24 @@ 
+"""${message}
+
+Revision ID: ${up_revision}
+Revises: ${down_revision | comma,n}
+Create Date: ${create_date}
+
+"""
+from alembic import op
+import sqlalchemy as sa
+${imports if imports else ""}
+
+# revision identifiers, used by Alembic.
+revision = ${repr(up_revision)}
+down_revision = ${repr(down_revision)}
+branch_labels = ${repr(branch_labels)}
+depends_on = ${repr(depends_on)}
+
+
+def upgrade():
+    ${upgrades if upgrades else "pass"}
+
+
+def downgrade():
+    ${downgrades if downgrades else "pass"}