2

I am writing a Flask application that uses Flask-SQLAlchemy and Flask-Migrate. Production has a PostgreSQL database, but for development, I was hoping to use SQLite instead of having to install full PostgreSQL on my development machine.

I set up Flask-Migrate as described in its docs. I can't help but notice, when I run flask db migrate on the dev box, these are the first two lines written to console:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

My question: Does this mean that the generated migration script is only suitable for SQLite?

I would have expected not, since you're supposed to commit the script to version control (after you give it a manual inspection) and use it for migration in production. And the author of Flask-Migrate himself admits one of the benefits of SQLAlchemy is you can use a different database engine in development and production (see his blog post). But then, why is it telling me about these assumptions? Is there a way to tell it not to assume a specific database engine?

Dominick Pastore
  • 4,177
  • 2
  • 17
  • 29

1 Answers1

1

The Context impl SQLiteImpl message that Alembic prints is actually a result of the database URL that is configured in your application. This URL is fed into Alembic, which allocates the proper driver for your chosen database. In your production environment you will see Context impl PostgresqlImpl instead.

Using different database in development and production is fine, but you have to be careful to make the contents of your migrations generic enough that work on both. You will not be able to use Postgres or SQLite specific features, obviously. I also recommend that add a staging server that is also based on Postgres where you can test your migrations before running them on production.

Miguel Grinberg
  • 65,299
  • 14
  • 133
  • 152
  • Thanks. I guess what isn't clear is what Postgres or SQLite specific features even make a difference in the script. For example, I have a table with an Enum column. SQLite doesn't have an Enum type, but Alembic still generated an Enum column in the migration script. So that's not a problem; it doesn't convert to the engine-specific type until the upgrade step. So if engine differences like that don't matter to Alembic, which ones do? – Dominick Pastore Dec 04 '19 at 15:32
  • I suppose what I'm really wondering is this: Should I interpret the context message to mean "We are trying to generate a generic script, but some artifacts of SQLite may creep in by necessity," or does it mean "We are optimizing the migration script for SQLite?" – Dominick Pastore Dec 04 '19 at 15:32
  • It just means that the migration scripts will be executed by the SQLite driver within Alembic. As I said in my answer, when working with different databases it's best to constantly test that you don't generate a migration that works in one but not the other by accident. The staging server is a must, in my opinion. – Miguel Grinberg Dec 05 '19 at 13:03
  • I was certainly planning on taking your advice on the staging server. My concern is that I'll overlook an issue when testing, since I'm still learning SQLAlchemy. So it's helpful to know, if the migration script seems to work with minimal "genericizing", is it more likely that I'm missing something, or that it's actually correct. Anyway, thanks, this was helpful. – Dominick Pastore Dec 05 '19 at 18:44
  • Actually, one other clarification, if you don't mind. When you said, "make the contents of your migrations generic enough," I assumed you meant I need to genericize the migration script. But did you just mean to make sure I'm only using SQLAlchemy features supported by both databases? – Dominick Pastore Dec 05 '19 at 19:05
  • 1
    You are probably worrying too much about what I said, maybe because I made it sound like it would be too easy to do something that breaks compatibility across databases. In my experience, as long as you stay with the primitive column types, you are likely to be fine. One thing to keep in mind though is that SQLite does not enforce column sizes, while other databases do. So if you define a column with a size that is too short for what you write in it, you will not notice until you run your code in Postgres. – Miguel Grinberg Dec 06 '19 at 11:34