Organising SQLAlchemy Base and Models in FastAPI Projects

Introduction

When working with FastAPI and SQLAlchemy, you may encounter an issue where your tables are not created in the database even though your code seems to be correctly set up. A common reason for this is having multiple instances of declarative_base instead of a single shared instance. In this blog post, we’ll walk you through the proper way to organise your SQLAlchemy Base and models in a FastAPI project.

Step 1: Create a separate file for the declarative base

Create a file named base.py in your project directory to hold the Base instance. This file will contain the following code:

from sqlalchemy.orm import declarative_base

Base = declarative_base()

Step 2: Import Base in your models

When defining your models, make sure to import the Base instance from base.py. This ensures that all your models share the same Base instance. Here’s an example model:

from base import Base
from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = "users"

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

Step 3: Import Base in your init_db function

In the file containing your init_db function, import the Base instance from base.py and use it when calling Base.metadata.create_all():

from base import Base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

def init_db(config):
    # Your existing code here
    Base.metadata.create_all(engine_instance)
    # Rest of the code

By following this structure, you ensure that the same Base instance is used throughout the application, and SQLAlchemy can correctly create the necessary tables in the database.

Conclusion

Organising your SQLAlchemy Base and models in a FastAPI project is crucial for the proper functioning of your application. By following the steps outlined in this blog post, you can avoid potential issues with table creation and ensure a smooth development experience.

Remember always to import the Base instance from the base.py file in both your models and the init_db function. This way, you maintain a single shared Base instance, allowing SQLAlchemy to recognize all the tables defined in your models and create them in the database as needed.