Forget about migrations and use SQLSugar

Teemu — Sat, 10 Dec 2022 14:41:43 +0000

Managing your database can be a laborious and time-consuming chore for a developer working on a small project or prototype. To aid with this, I created a tiny project called SQLSugar.

SQLSugar is a library that makes it easy to use a real database without spending time on generating migrations. This might be extremely helpful for running quick prototypes for ideas or tiny side projects. You can easily add new tables, columns, and indexes to your database without worrying about managing migrations.

How it works?

To use SQLSugar, you simply define your database schema using SQLAlchemy's models, and then pass that schema to SQLSugar's migrate function. SQLSugar will then use Alembic to autogenerate the difference between your defined schema and the actual database, and execute the necessary operations to make the two match.

For example, if SQLSugar finds a new column in your code that is missing from your database, it will create the column automatically. This saves you the time and effort of manually creating and running migrations, allowing you to focus on building your project.

Usage

Installing:

pip install sqlsugar

First, define models with SQLAlchemy:

import logging

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import Session, declarative_base

from sqlsugar import migrate

logging.basicConfig(
    format="%(asctime)s - %(name)s - %(levelname)s - %(message)s", level=logging.INFO
)

Base = declarative_base()

class Cat(Base):  # type: ignore
    __tablename__ = "cat"

    id = Column(Integer, primary_key=True)
    name = Column(String)

You can then use the SQLSugar's migrate command to automatically get your database up-to-speed.

from sqlsugar import migrate

# You only need this command to handle creating tables & running migrations
migrate(engine.connect(), Base.metadata)

Limitations

While my project is a great solution for rapidly prototyping ideas, it does have limitations. It does not support renaming columns, or dropping columns. As your project grows, you should switch to using more mature solution like Alembic.

DEV Community: Teemu

Forget about migrations and use SQLSugar

How it works?

Usage

Limitations