# Evolutions
The evolutions package provides a facility similar to
[play-evolutions](https://www.playframework.com/documentation/2.7.x/Evolutions)
([source](https://github.com/playframework/playframework/tree/master/persistence/play-jdbc-evolutions/src/main/scala/play/api/db/evolutions))
for use with Python. It differs from other similar tools in its simple,
lightweight philosophy and its pure SQL orientation. It enforces a linear
history in schema development with no branching or dependencies. Migrations
are contained in SQL files and run automatically as needed, with no code
embedding or hand-tuned invocations needed. Evolutions plays well with source
control as well as differences between development and production
environments. Finally, it's not tied to any application framework so can be
used anywhere that Python 3 and the target database are available, regardless
of whether the using application is itself implemented in Python.
## Ups
The basic idea is that, as the database schema is developed incrementally, the
changes are placed in *ups* files named `1.sql` (the original schema),
`2.sql`, `3.sql`, etc.. When the tool is invoked (usually just before the app
starts up, see Usage), a table is checked in the database to see which
of these scripts have been run, and any missing are run in sequence. The
scripts should contain both schema changes and data migration. This way, the
app and the schema can be developed together, without worrying about manual
database updates, legacy data support, or schema-code synchronization.
### Example
An application is deployed with its schema in a file `1.sql`,
containing a lot of `CREATE TABLE` statements. Some development is done and
`2.sql` with some `ALTER TABLE` statements is written. When this is deployed
and the evolutions tool run before application startup, it detects that
`1.sql` has already been run, but `2.sql` has not, so it runs that. Later on,
`3.sql` and `4.sql` are developed but reach deployment together. When the
evolutions tool is run, it runs first `3.sql` then `4.sql` in sequence.
## Downs
In addition to the *ups*, a set of corresponding *downs* files `1-downs.sql`,
`2-downs.sql`, `3-downs.sql` are created during development, which undo the
effects of the corresponding ups files. If the tool detects that a change has
been made to an ups file, then it will rerun it. However, its former version,
as well as any later ups that have been run "on top" of it, must be undone
first. So the tool first runs these in reverse, using versions cached in the
database itself in case *those* have changed as well, then runs back up the
sequence starting from the first modified file. This process allows tweaking
changes during development, without needing to manually adjust the database
each time. It also enables collaboration, so that, for example, a single
`#.sql` file can be used by everyone for an entire sprint (merging in changes
to a shared git branch).
### Example
The application in the first example continues being developed. In addition to
the ups files mentioned earlier, `1-downs.sql` (a bunch of `DROP TABLE`
statements), `2-downs.sql` (`ALTER TABLE` statements undoing the alterations
in `2.sql`), `3-downs.sql`, and `4-downs.sql` were all written. At one point,
someone realizes there was a mistake in `3.sql` and it should be fixed. They
modify this file, as well as `3-downs.sql`, then deploy the application.
When the evolutions tool runs, it detects that `3.sql` has changed. It then
runs `4-downs.sql` and `3-downs.sql` (in that order) to get to a point where
it can correctly apply the new version of `3.sql`. Then it runs that script,
and finally `4.sql` to bring the database fully up to date.
## Production
In general, although running downs will ensure a consistent database schema,
some data loss is often unavoidable, simply because elements of the schema are
lost through downgrade, meaning the data cannot be preserved. **For this
reason, you should not run downs in production. Ever.** Once you deploy an
evolution stage to production, you should freeze it, so evolutions will not
run it again. In addition, you can and should enable a safety check by adding
the `--prod` switch when invoking (see Usage). If the tool encounters an ups
script change when this switch is active, it will abort rather than run any
downs, so you can address the situation manually.
In this case, you will end up in a situation where the database is out of sync
with the evolutions scripts. This can also occur if evolutions is only taken
into use. This can be remedied by using the `--skip=<indices>` switch to the
tool, which will cause the tool to assume the scripts with the given
comma-separated indices have already been run, and mark them so in the
database, without actually running them.
### Example
The deployment mentioned in the preceding examples as assumed to be on a
development environment, where it's OK to lose data in the case *downs*
scripts need to be run. However, in the production deployment, the evolutions
tool is invoked with the `--prod` argument. Hence, in the situation described
in the previous example where `3.sql` has changed, it will abort and return an
error code. The user will then need to address the situation manually.
Possibly they will apply the new change needed from `3.sql` manually, taking
care to preserve data.
Then they can invoke the tool using `--skip=3`. This will cause it to mark
`3.sql` has having been "run" in its new modified version, *without actually
running it*. Then, after noting that `4.sql` has not changed, the database is
now considered to be up to date. (Subsequent invocations do not need the
`--skip` argument and will not trigger an abort, unless there has been a new,
different change of an already-run script.)
# Database Support
MySQL, PostgreSQL, and Sqlite are supported. The database is accessed through
a combination of the command line clients and
[DB-API2](https://www.python.org/dev/peps/pep-0249/), using
[mysql-connector-python](https://dev.mysql.com/doc/connector-python/en/),
[PsycoPg2](http://initd.org/psycopg/docs/), and
[sqlite3](https://docs.python.org/3.8/library/sqlite3.html). (These are not
listed as dependencies of this package, so you should install the one needed
for your own case yourself.)
## Transactions
The evolutions scripts themselves are run by invoking the command line client
for the database being used (e.g., `psql` for Postgres). Transactionality is
therefore under control of the script itself.
When using MySQL or PostgreSQL (but not Sqlite), the evolutions table is
locked when the script starts, so any parallel instances of the script started
up will wait until the first one completes, and then will find the evolutions
already run and do nothing.
# Install
pip3 install evolutions
-or- to get development version:
pip3 install https://github.com/arobertn/evolutions/archive/master.tar.gz
# Usage
The tool is invoked via a Python 3 command line script, and should be called
just before or as part of starting the application, or, in an auto-deploy
environment, whenever the schema files have been changed.
./evolutions.py <db_url> <db_user> <db_pass> <evolutions_dir> [--skip=<stages>] [--prod]
--skip=<stages> = comma-separated indices to assume already run
--prod = abort if downs need to be run (for production)
- *db\_url:* e.g.: `mysql://localhost:3306/dbname`,
`postgresql://localhost:5432/dbname`,
`sqlite:/absolute/path/to/database.db`
- *db\_user, db\_pass:* db\_pass is ignored for Postgres (must use .pgpass
file), and both db\_user and db\_pass are ignored for Sqlite; pass empty
strings ("")
- *evolutions_dir:* directory containing the #.sql/#-downs.sql files; can be
relative or absolute path
- *--skip:* comma-separated list of stage indices to skip running if you have
a database which has already had one or more of the #.sql files run on it;
will insert rows to the `evolutions` table to make it up to date, but will
not actually run the scripts
- *--prod:* if given, tool will abort immediately if it determines any downs would
need to be run; database is not touched
# Implementation
The evolutions tool operates by collecting the SHA1 hash of each ups and downs
script in the evolutions directory, and storing these values, together with
the script contents themselves, in a dedicated table (named `evolutions`) in
the database. Decisions on which ups and downs scripts to run are made by
comparing the database record and the scripts found in the directory, and
updates are made according to the runs.
# Development
On [GitHub](https://github.com/arobertn/evolutions).
Raw data
{
"_id": null,
"home_page": "https://github.com/arobertn/evolutions",
"name": "evolutions",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3",
"maintainer_email": "",
"keywords": "database migration sql python python3 evolutions",
"author": "Adrian Robert",
"author_email": "arobert@interstitiality.net",
"download_url": "https://files.pythonhosted.org/packages/74/50/15674dbdda8d6f2cb66ec3e3083e6dfa034a25a132dfe5d36b14d22c6d7b/evolutions-0.8.3.tar.gz",
"platform": "any",
"description": "\n# Evolutions\n\nThe evolutions package provides a facility similar to\n[play-evolutions](https://www.playframework.com/documentation/2.7.x/Evolutions)\n([source](https://github.com/playframework/playframework/tree/master/persistence/play-jdbc-evolutions/src/main/scala/play/api/db/evolutions))\nfor use with Python. It differs from other similar tools in its simple,\nlightweight philosophy and its pure SQL orientation. It enforces a linear\nhistory in schema development with no branching or dependencies. Migrations\nare contained in SQL files and run automatically as needed, with no code\nembedding or hand-tuned invocations needed. Evolutions plays well with source\ncontrol as well as differences between development and production\nenvironments. Finally, it's not tied to any application framework so can be\nused anywhere that Python 3 and the target database are available, regardless\nof whether the using application is itself implemented in Python.\n\n## Ups\n\nThe basic idea is that, as the database schema is developed incrementally, the\nchanges are placed in *ups* files named `1.sql` (the original schema),\n`2.sql`, `3.sql`, etc.. When the tool is invoked (usually just before the app\nstarts up, see Usage), a table is checked in the database to see which\nof these scripts have been run, and any missing are run in sequence. The\nscripts should contain both schema changes and data migration. This way, the\napp and the schema can be developed together, without worrying about manual\ndatabase updates, legacy data support, or schema-code synchronization.\n\n### Example\n\nAn application is deployed with its schema in a file `1.sql`,\ncontaining a lot of `CREATE TABLE` statements. Some development is done and\n`2.sql` with some `ALTER TABLE` statements is written. When this is deployed\nand the evolutions tool run before application startup, it detects that\n`1.sql` has already been run, but `2.sql` has not, so it runs that. Later on,\n`3.sql` and `4.sql` are developed but reach deployment together. When the\nevolutions tool is run, it runs first `3.sql` then `4.sql` in sequence.\n\n## Downs\n\nIn addition to the *ups*, a set of corresponding *downs* files `1-downs.sql`,\n`2-downs.sql`, `3-downs.sql` are created during development, which undo the\neffects of the corresponding ups files. If the tool detects that a change has\nbeen made to an ups file, then it will rerun it. However, its former version,\nas well as any later ups that have been run \"on top\" of it, must be undone\nfirst. So the tool first runs these in reverse, using versions cached in the\ndatabase itself in case *those* have changed as well, then runs back up the\nsequence starting from the first modified file. This process allows tweaking\nchanges during development, without needing to manually adjust the database\neach time. It also enables collaboration, so that, for example, a single\n`#.sql` file can be used by everyone for an entire sprint (merging in changes\nto a shared git branch).\n\n### Example\n\nThe application in the first example continues being developed. In addition to\nthe ups files mentioned earlier, `1-downs.sql` (a bunch of `DROP TABLE`\nstatements), `2-downs.sql` (`ALTER TABLE` statements undoing the alterations\nin `2.sql`), `3-downs.sql`, and `4-downs.sql` were all written. At one point,\nsomeone realizes there was a mistake in `3.sql` and it should be fixed. They\nmodify this file, as well as `3-downs.sql`, then deploy the application.\n\nWhen the evolutions tool runs, it detects that `3.sql` has changed. It then\nruns `4-downs.sql` and `3-downs.sql` (in that order) to get to a point where\nit can correctly apply the new version of `3.sql`. Then it runs that script,\nand finally `4.sql` to bring the database fully up to date.\n\n## Production\n\nIn general, although running downs will ensure a consistent database schema,\nsome data loss is often unavoidable, simply because elements of the schema are\nlost through downgrade, meaning the data cannot be preserved. **For this\nreason, you should not run downs in production. Ever.** Once you deploy an\nevolution stage to production, you should freeze it, so evolutions will not\nrun it again. In addition, you can and should enable a safety check by adding\nthe `--prod` switch when invoking (see Usage). If the tool encounters an ups\nscript change when this switch is active, it will abort rather than run any\ndowns, so you can address the situation manually.\n\nIn this case, you will end up in a situation where the database is out of sync\nwith the evolutions scripts. This can also occur if evolutions is only taken\ninto use. This can be remedied by using the `--skip=<indices>` switch to the\ntool, which will cause the tool to assume the scripts with the given\ncomma-separated indices have already been run, and mark them so in the\ndatabase, without actually running them.\n\n### Example\n\nThe deployment mentioned in the preceding examples as assumed to be on a\ndevelopment environment, where it's OK to lose data in the case *downs*\nscripts need to be run. However, in the production deployment, the evolutions\ntool is invoked with the `--prod` argument. Hence, in the situation described\nin the previous example where `3.sql` has changed, it will abort and return an\nerror code. The user will then need to address the situation manually.\nPossibly they will apply the new change needed from `3.sql` manually, taking\ncare to preserve data.\n\nThen they can invoke the tool using `--skip=3`. This will cause it to mark\n`3.sql` has having been \"run\" in its new modified version, *without actually\nrunning it*. Then, after noting that `4.sql` has not changed, the database is\nnow considered to be up to date. (Subsequent invocations do not need the\n`--skip` argument and will not trigger an abort, unless there has been a new,\ndifferent change of an already-run script.)\n\n\n# Database Support\n\nMySQL, PostgreSQL, and Sqlite are supported. The database is accessed through\na combination of the command line clients and\n[DB-API2](https://www.python.org/dev/peps/pep-0249/), using\n[mysql-connector-python](https://dev.mysql.com/doc/connector-python/en/),\n[PsycoPg2](http://initd.org/psycopg/docs/), and\n[sqlite3](https://docs.python.org/3.8/library/sqlite3.html). (These are not\nlisted as dependencies of this package, so you should install the one needed\nfor your own case yourself.)\n\n\n## Transactions\n\nThe evolutions scripts themselves are run by invoking the command line client\nfor the database being used (e.g., `psql` for Postgres). Transactionality is\ntherefore under control of the script itself.\n\nWhen using MySQL or PostgreSQL (but not Sqlite), the evolutions table is\nlocked when the script starts, so any parallel instances of the script started\nup will wait until the first one completes, and then will find the evolutions\nalready run and do nothing.\n\n\n# Install\n\n pip3 install evolutions\n\n-or- to get development version:\n\n pip3 install https://github.com/arobertn/evolutions/archive/master.tar.gz\n\n\n# Usage\n\nThe tool is invoked via a Python 3 command line script, and should be called\njust before or as part of starting the application, or, in an auto-deploy\nenvironment, whenever the schema files have been changed.\n\n ./evolutions.py <db_url> <db_user> <db_pass> <evolutions_dir> [--skip=<stages>] [--prod]\n --skip=<stages> = comma-separated indices to assume already run\n --prod = abort if downs need to be run (for production)\n\n- *db\\_url:* e.g.: `mysql://localhost:3306/dbname`,\n `postgresql://localhost:5432/dbname`,\n `sqlite:/absolute/path/to/database.db`\n- *db\\_user, db\\_pass:* db\\_pass is ignored for Postgres (must use .pgpass\n file), and both db\\_user and db\\_pass are ignored for Sqlite; pass empty\n strings (\"\")\n- *evolutions_dir:* directory containing the #.sql/#-downs.sql files; can be\n relative or absolute path\n- *--skip:* comma-separated list of stage indices to skip running if you have\n a database which has already had one or more of the #.sql files run on it;\n will insert rows to the `evolutions` table to make it up to date, but will\n not actually run the scripts\n- *--prod:* if given, tool will abort immediately if it determines any downs would\n need to be run; database is not touched\n\n\n# Implementation\n\nThe evolutions tool operates by collecting the SHA1 hash of each ups and downs\nscript in the evolutions directory, and storing these values, together with\nthe script contents themselves, in a dedicated table (named `evolutions`) in\nthe database. Decisions on which ups and downs scripts to run are made by\ncomparing the database record and the scripts found in the directory, and\nupdates are made according to the runs.\n\n\n# Development\n\nOn [GitHub](https://github.com/arobertn/evolutions).\n",
"bugtrack_url": null,
"license": "BSD-3-Clause",
"summary": "App framework agnostic pure SQL incremental database migrations tool modeled after play-evolutions",
"version": "0.8.3",
"split_keywords": [
"database",
"migration",
"sql",
"python",
"python3",
"evolutions"
],
"urls": [
{
"comment_text": "",
"digests": {
"md5": "032b321407632aad7125494c49836ca1",
"sha256": "5dbdf46f45ea1d8579e15b4582e163381ab7ea475afbcc6cb7e4bf2a1859c627"
},
"downloads": -1,
"filename": "evolutions-0.8.3-py3-none-any.whl",
"has_sig": false,
"md5_digest": "032b321407632aad7125494c49836ca1",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3",
"size": 15178,
"upload_time": "2022-12-27T09:58:25",
"upload_time_iso_8601": "2022-12-27T09:58:25.383001Z",
"url": "https://files.pythonhosted.org/packages/60/ac/99fcb12ad4de6214380d8b822e14ae1e8282517b10d7fa86794e447a36d6/evolutions-0.8.3-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"md5": "e9fd7bb14957fc3c423d83419d82f154",
"sha256": "9502bbdc6a8d60209eacf5e755c97c6207d430995cb8df8ce32335e4532ce709"
},
"downloads": -1,
"filename": "evolutions-0.8.3.tar.gz",
"has_sig": false,
"md5_digest": "e9fd7bb14957fc3c423d83419d82f154",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3",
"size": 10778,
"upload_time": "2022-12-27T09:58:27",
"upload_time_iso_8601": "2022-12-27T09:58:27.124241Z",
"url": "https://files.pythonhosted.org/packages/74/50/15674dbdda8d6f2cb66ec3e3083e6dfa034a25a132dfe5d36b14d22c6d7b/evolutions-0.8.3.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2022-12-27 09:58:27",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "arobertn",
"github_project": "evolutions",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "evolutions"
}
JSON
