marcosschroh/yoyo-database-migrations

Database schema migration tool written in Python

Yoyo database migrations

This project has been clone from ollyc/yoyo. Thanks Ollyc!!

Why this repository?

• To improve project documentation
• To fix bugs related to different python versions
• I had different issues on Mac but not on Linux, so fix them
• To add full support for Python 3.6/3.7 (annotations, async/io databases drivers)
• To add new Features
• To add full code coverage

Yoyo is a database schema migration tool. You write database migrations as Python scripts containing raw SQL statements or Python functions.

Installation:

pip install yoyo-database-migrations

Documentation:

https://marcosschroh.github.io/yoyo-database-migrations/

What does yoyo-migrations do?

As your database application evolves, changes to the database schema may be required. Yoyo lets you write migration scripts in Python containing SQL statements to migrate your database schema to a new version.

A simple migration script looks like this:

# file: migrations/0001.create-foo.py
from yoyo import step
step(
    "CREATE TABLE foo (id INT, bar VARCHAR(20), PRIMARY KEY (id))",
    "DROP TABLE foo",
)

Yoyo manages these database migration scripts, gives you command line tools to apply and rollback migrations, and manages dependencies between migrations.

Database support

PostgreSQL, MySQL and SQLite databases are supported. ODBC and Oracle database backends are available (but unsupported).

Improvements

• Command yoyo showmigrations added

yoyo-database-migrations's Issues

MySQL and Postgres DDL transactionality in docs

I am trying to use yoyo 7.0.0 on Python 3.7 against Postgres 11 on Google Cloud SQL.

I propose there is a significant typo in the yoyo documentation about transactionality.

PostgreSQL
In PostgreSQL it is an error to run certain statements inside a transaction block. These include:

CREATE DATABASE ...
CREATE TABLE
ALTER TYPE ...
Using transactional = False allows you to run these within a migration

This seems false and backwards.

See the following references. They assert that Postgres does support transactional DDL, but MySQL does not.

• Reference 1.
• Reference 2

My hypothesis is that think someone accidentally wrote "Postgres" in the yoyo docs when they meant "MySQL"

I'm happy to submit a pull request with proposed changes if this makes any sense.

Create site documentation

Apply/Rollback N migrations from python code

Now is possible to apply and rollback all the migration from python code:

from yoyo import read_migrations
from yoyo import get_backend

backend = get_backend('postgres://myuser@localhost/mydatabase')
migrations = read_migrations('path/to/migrations')

with backend.lock():
    backend.apply_migrations(backend.to_apply(migrations))
    backend. rollback_migrations(backend. to_rollback(migrations))

Would be nice to specify the number of migrations that should be applied, for example:

So, if there are 10 migrations and 2 were applied, if we execute the command:

backend.apply_migrations(backend.to_apply(migrations), count=3)

The current head should be at 5, means that 5 migrations were applied and 5 are left. The same logic with rollback

backend. rollback_migrations(backend. to_rollback(migrations), count=1)

After that the head should be at 4.

Release package

I want a script to update the package version, release on pypi and deploy the site documentation.

Create full support for python 3.6/7

Add full support for Python 3.6/3.7 (annotations, async/io databases drivers). This will be a braeking change and only python 3.6/7 will be supported.

[Improvement] Yoyo automatic apply and rollback

What I have to do:
Application code and DB code should be separated and have a different release process. The application should know about the exact version of DB schema to apply migrations.

I have DB schema version 1 and the app expects DB version 1. I want to add a new feature to the app and change the DB schema. I do:

• Prepare a new DB migration version 2, release a new DB artifact (V2)
• Implement a new feature in the app, set an expected DB version 2

Rollout application:
Push the new version of the app with the latest version of the DB artifact.
The application knows that it needs DB V2; check the current DB version (1); apply migrations list till V2 from the latest DB schema artifact.

Rollback application (new version):
Push the prev (v1) version of the app with the latest version of the DB artifact.
The application knows that it needs DB V1; check the current DB version (2); rollback migrations (needed, current] ( means (v1, v2] from the latest DB schema artifact. (I always need the latest version of DB artifact!)

What I want to do:
I want to have the ability to store migrations with the application source code.
When yoyo applies migrations it should also store rollbacks in DB. And when I want to rollback migration from V2 to V1, it will apply rollback from the DB.

I have the app code with DB migrations in the same repo. I want to add a new feature to the app and change the DB schema. I do:

• Prepare a new DB migration version 2
• Implement a new feature in the app (DB and app changes in the same Pull-request)

Rollout application (new version):
The application has a full list of migrations to apply (till V2). Applies it on startup.

Rollback application:
The application has migrations till V1. But current DB version - V2. There are no v2 migrations and rollbacks with the app. Yoyo can fetch all rollbacks from DB till V1 and apply them to have a consistent DB version for the app.