Migrate DB Model in Flask

Backend

Background

Django has the inspectdb command that automatically generates a model for a user. Unfortunately, Flask does not have such a feature and we need to use a package to achieve this. I'll be using the flask-sqlacodegen package and MySQL for this post.

Installation

# I used version 1.4.2.post1
pip3 install mysqlclient

# I used version 1.1.6.1
pip3 install flask-sqlacodegen

Create a models.py

Replace the username, password, DB HOST and DB NAME below with your info and run it in your terminal. This will generate a models.py file with your database information.

flask-sqlacodegen "mysql://username:password@DB_HOST/DB_NAME" --flask > models.py

Check your models.py file. It should look similar to this.

# coding: utf-8
from sqlalchemy import Column, DateTime, Integer, String
from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255, "utf8mb4_general_ci"))
    password = db.Column(db.String(255, "utf8mb4_general_ci"))
    email = db.Column(db.String(255, "utf8mb4_general_ci"))
    phone = db.Column(db.String(255, "utf8mb4_general_ci"))
    createdAt = db.Column(db.DateTime, nullable=False)
    updatedAt = db.Column(db.DateTime, nullable=False)

Import models to Flask app

from flask import Flask
from core import models # My path to models.py

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://username:password@DB_HOST/DB_NAME"
models.db.init_app(app)

if __name__ == "__main__":
    app.run()

And that's it! Don't forget to hide the SQLALCHEMY_DATABASE_URI in secrets before you push your code.

Conclusion

Using flask-sqlacodegen to automatically create ORM models was a painless process, and it worked flawlessly. However, it might be worth noting that the last update to this package was 2 years ago. On the other hand, Flask-Migrate seems to be the more popular package with frequent updates.

Reference

flask-sqlacodegen: Automatic model code generator for SQLAlchemy with Flask support

Flask-Migrate: SQLAlchemy database migrations for Flask applications using Alembic

GitHubGitHubLinkedin