DEV Community

Cover image for Forget about migrations and use SQLSugar

Posted on

Forget about migrations and use SQLSugar

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.



pip install sqlsugar
Enter fullscreen mode Exit fullscreen mode

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

    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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode


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.

Top comments (0)