12

Is there any library or tool to perform database migrations in python without SQLAlchemy? I am using a PostgreSQL database and the queries are raw SQL. I use psycopg2 for the database connection.

Improve this question
8
  • you mean migrations without using flask-migrate ? Commented Mar 20, 2019 at 8:39
  • 1
    you will have to use alembic and run the migration scripts by yourself manually. Commented Mar 20, 2019 at 9:29
  • 1
    alembic generates the migration scripts for you, it hides the enormous amount of complexity of updating a schema for an existing table in the database. Updating schema of existing tables in the database is a very complex operation and requires careful planning for tables with large number of columns and imagine if you have to do such thing frequently it is even cumbersome. alembic in combination with flask-migrate makes this task very easy. Commented Mar 20, 2019 at 10:18
  • 1
    If your application uses pure SQL to query the database, wouldn't it make sense to also write your migrations in pure SQL? It is probably still a good idea to create a table to keep track of which migrations have already run and to make a Python-wrapper to check which migrations should be run and actually run only those migrations, but this seemss reasonably trivial to write. The migrations themselves can be bare SQL files. Commented May 6, 2019 at 8:53
  • 1
    @Zeust the Unoobian That's exactly what I ended up doing and it went well. Thanks for the advice. Commented May 7, 2019 at 12:43

3 Answers 3

Reset to default
15

There are several tools that can be used to version your database with migrations in Python, outside of SQLAlchemy. I can't speak for any of them in particular, so I looked for ones that seem to have some adoption as well as active maintainence.

(as of Apr 14, 2023)

Tool GitHub Stars Maintained Notes
pgmigrate yandex/pgmigrate 562 Feb 3 2023 Postgres only
dbmate amacneil/dbmate 3.3k Mar 2023
Yoyo coobas/yoyo-migrations 0 Mar 2020
Migrate golang/migrate 11.2k Apr 2023 go-lang library & CLI tool

Other resources I've found seem to be tied to one framework or another. Searching the package index turns up a lot of older resources.

If one finds pgmigrate or dbmate to be undesirable, then from there it might be worth asking whether the migration tool really needs to be Python-specific. Typically migrations are a part of the build process and (in my opinion) ideally should require as little coding and configuration as possible.

Juan D. Vega's post, Language and framework agnostic database migrations, highlights the golang/migrate CLI tool, which attempts to offer a way to manage your migrations irrespective of the particulars of the application that the database supports.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

"Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python."
@Abdur-RahmaanJanhangeer So can Alembic be used without SQLAlchemy ?
@codingbruh No ...
This is a top result on Google, suggest when answering StackOverflow questions you don't just point people back to Google.
1

If you can bring yourself to do your db migrations outside of the CLR, I'd strongly recommend considering FlyWay. It has been around forever, it's supported by RedGate, and the community version is free and open source. It has built-in support for all the major db platforms and installs like any other utility you'd use. For a Python app you'd do all of your db migrations via shell scripts or process calls instead of directly in the code.

My team settled on this tech because:

  1. Yoyo is pretty immature and buggy for us
  2. We don't use SQLAlchemy's OR framework
  3. Alembic's syntax and framework seems pretty heavyweight for us
  4. Under FlyWay's model we just write SQL scripts and check them into a directory. It's really lightweight.
  5. It's been proven to work nicely in clustered environments

Give it a look.

Improve this answer

Comments

1

There is flask-migratepg for this purpose, where SQL files are simply placed under a database/migrations directory in alphanumerical order and migrations executed with flask migrate execute. It is based on Psycopg 3.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.