FastAPI SQLAlchemy Migrations Guide¶

In the world of web development, FastAPI stands out as a powerful and efficient Python web framework. When building complex applications, it's essential to organize and manage data effectively. SQLAlchemy provides a robust Object-Relational Mapping (ORM) system, while Alembic handles database migrations. In this beginner-friendly guide, we'll explore how to create models using SQLAlchemy and perform migrations using Alembic with an SQLite database in FastAPI.

Prerequisites¶

Before we begin, ensure you have the following prerequisites installed on your machine:

• Python (version 3.7 or higher)
• FastAPI and SQLAlchemy
• Alembic
• SQLite database (included in Python's standard library)

Step 1: Setting Up the Environment¶

Let's start by creating a new directory for our FastAPI project. Open your terminal and run the following commands:

mkdir fastapi_project
cd fastapi_project

python -m venv venv
source venv/bin/activate  # On Windows, use "venv\Scripts\activate"

Step 2: Installing Required Packages¶

Now that we have our virtual environment activated, let's install the necessary packages using pip:

pip install fastapi[all] sqlalchemy alembic

Step 3: Define SQLAlchemy Model¶

In FastAPI, we create SQLAlchemy models to represent database tables. Create a new Python file called models.py in your project directory. Let's define a simple model representing a user:

# models.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    email = Column(String, unique=True, index=True)
    full_name = Column(String)
    hashed_password = Column(String)

Step 4: Creating the Database¶

Now that we have defined our model, let's create the SQLite database and tables.

alembic init alembic

This command will create an alembic directory with necessary configuration files.

4.1. Auto Migration¶

If you want alembic handles migrations follow this method: In the alembic folder edit env.py and find target_metadata line and edit like the following

# NOTE we added
from models import Base
target_metadata = Base.metadata

4.2. Manual Migration¶

If you want to manually create tables you can follow this method. Open interactive shell by typing python

(venv) $ python

>>> from sqlalchemy import create_engine
>>> from models import Base
>>>
>>> DATABASE_URL = "sqlite:///./test.db"
>>> engine = create_engine(DATABASE_URL)
>>> Base.metadata.create_all(bind=engine)

Step 5: Configuring Alembic¶

Open the alembic.ini file in the alembic directory and make the following changes:

# alembic.ini
[alembic]
script_location = alembic

[alembic.ext]
sourceless = false

sqlalchemy.url = sqlite:///./test.db  # Replace with your database URL if different

Step 6: Generating a Migration¶

Now, let's create an initial migration for our User model. In the terminal, run the following command:

alembic revision --autogenerate -m "Initial migration"

Alembic will analyze the changes in the model and generate a migration script in the alembic/versions directory. You can see the migration files in the alembic/versions directory.

Note that if you do not specify a column as nullable=False, by default the fields will be nullable=True except primary keys.

# ...
sa.Column('full_name', sa.String(), nullable=True),
# ...

Step 7: Applying the Migration¶

To apply the migration and create the users table in the database, run the following command:

alembic upgrade head

Congratulations! You've successfully created models using SQLAlchemy and performed migrations using Alembic with an SQLite database in FastAPI.

Step 8: Editing Models¶

If you've added a new field or column to a model, you'll need to perform the following steps to ensure your database is up-to-date and reflects the changes:

Step 1: Update the Model¶

Open the models.py file and add the new field/column to your model class. For example, let's say we want to add a phone_number field to the User model:

# models.py
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String, unique=True, index=True)
    email = Column(String, unique=True, index=True)
    full_name = Column(String)
    hashed_password = Column(String)
    phone_number = Column(String)  # New field

Step 2: Generate a New Migration¶

With the model updated, we need to generate a new migration to capture the changes. In your terminal, run the following command:

alembic revision --autogenerate -m "Add phone_number field to User model"

Step 3: Apply the Migration¶

Finally, apply the migration to the database to make the changes take effect. Run the following command:

alembic upgrade head

This command will execute the new migration and update the database schema to include the new phone_number field in the users table.

After completing these steps, your model will be synchronized with the database, and your FastAPI application will be ready to use the new field.

Remember that when performing migrations on a production system, it's essential to take necessary precautions to avoid data loss or corruption. Always make backups before running migrations on a production database. Additionally, consider using version control to track your migrations and make collaboration with other developers more manageable.

server_default¶

In SQLAlchemy, the server_default parameter is used to specify a default value for a column that will be set by the database server itself when a new row is inserted and no explicit value is provided for that column. This is especially useful when you have a NOT NULL column and want to provide a default value if the client does not provide one.

Let's demonstrate how to define a NOT NULL field with a default value using a String column in SQLAlchemy:

Step 1: Import Required Modules¶

First, make sure you have the necessary modules imported at the beginning of your models.py file:

from sqlalchemy import Column, Integer, String, text
from sqlalchemy.ext.declarative import declarative_base

Step 2: Edit the Model¶

with server_default Now, let's modify our User model to include a new column role, which will be of type String and will have a default value of 'user'. This means that if the client does not provide a value for the role field when creating a new user, the database will automatically set it to 'user':

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    # previous fields
    role = Column(String, server_default='user', nullable=False)

In the role column definition, we set the server_default parameter to 'user', which is the default value that will be used if no value is explicitly provided during insertion.

Step 3: Creating the Database¶

with the New Model Since we've made changes to our model, we need to create a new migration and apply it to the database to add the new role column with its default value.

Generate a new migration using alembic revision --autogenerate -m "Add role field to User model" Apply the migration with alembic upgrade head Now, when you create a new user without providing a value for the role field, the database will automatically set it to 'user'.

Remember that server_default is just one of the many features SQLAlchemy provides to customize your database schema. As a beginner, you can gradually explore more options to enhance your database models and make your FastAPI application more powerful and flexible.

Conclusion¶

In this article, we've learned the basics of using SQLAlchemy to create models and Alembic for database migrations in FastAPI. This is just the tip of the iceberg; you can explore more advanced features and build more complex applications using these tools. Happy coding!