FastAPI SQLite: Database Integration in FastAPI

Whether you are building a simple prototype, an internal tool, or a complex web application, integrating a database is key for storing and managing data. SQLite is a self-contained, serverless database engine that is convenient for development due to its lightweight nature, but powerful enough for production applications.

In this comprehensive guide, we will dive into integrating SQLite databases with FastAPI, the modern, high-performance Python web framework.

Why Use SQLite with FastAPI?

Here are some key reasons why SQLite and FastAPI work very well together:

• Simple and lightweight: SQLite databases are stored in a single file, requiring no separate server process, making them easy to integrate.
• Full-featured SQL: SQLite supports the vast majority of SQL commands and data types, giving a lot of flexibility.
• High performance: FastAPI and SQLite are both designed for speed and efficiency. Together, they can create very fast web applications and APIs.
• Great for prototyping: The simplicity of SQLite allows you to focus on rapidly building functional prototypes and internal tools.
• Production-ready: Many big sites use SQLite in production to store critical data. It scales quite well for many workloads.

Overall, by combining FastAPI and SQLite you get an uncomplicated yet powerful stack for building modern Python web apps of all sizes. Now let’s look at how to put them together.

Example Database and Models

For the examples in this guide, we will use a simple SQLite database containing a table of products with the following schema:

products
----------
id          INTEGER PRIMARY KEY
name        TEXT NOT NULL
description TEXT
price       REAL NOT NULL 

Here are some example rows we inserted with sample product data:

ID | Name       | Description            | Price
-----------------------------------------------  
1  | Basketball | Outdoor basketball     | 29.99
2  | Football   | Official size football | 19.99  

Now let’s look at how we would model this in Python code using Pydantic and SQLAlchemy…

Step 1: Define Pydantic Models

We start by defining Pydantic models to validate and document our product data:

from pydantic import BaseModel

class ProductBase(BaseModel):
    name: str
    description: str | None = None
    price: float

class ProductCreate(ProductBase):
    pass

class Product(ProductBase):
    id: int

    class Config:  
        orm_mode = True

Notes:

• Used standard Pydantic BaseModel
• Added optional description
• ProductCreate has just fields needed to create
• Product adds id field and orm_mode

The orm_mode is key to auto-convert SQLite data to Pydantic models later.

Step 2: Setup SQLAlchemy Models

Now let’s define equivalent SQLAlchemy models:

from sqlalchemy import Column, Integer, String, Text
from .database import Base

class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String)
    description = Column(Text)
    price = Column(Integer)

This maps very directly to the SQLite schema earlier.

Some key points:

• Imports SQLAlchemy types
• Inherits from Base class of database setup
• __tablename__ matches real table name
• Attributes match columns

With those models defined, we are ready to wire up the database integration into our FastAPI application…

Step 3: Connect Database

First, we initialize SQLAlchemy engine and session maker:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "sqlite:///./products.db"
engine = create_engine(SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False})

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

This establishes a connection pool for querying our SQLite database file products.db.

Step 4: Define Database Session Dependency

Next we create a FastAPI dependency to initialize a database session for each request:

# Dependency
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally: 
        db.close()

This will give a fresh SessionLocal instance per request to our path operation functions.

Step 5: Create Path Operations

With all the setup in place, we can now write standard FastAPI path operations that interface with the database:

from fastapi import Depends, FastAPI  

app = FastAPI()

# Define path operations  

@app.post("/products")
def create_product(product: ProductCreate, db: Session = Depends(get_db)):  
    db_product = Product(**product.dict())  
    db.add(db_product)  
    db.commit()  
    db.refresh(db_product)
    return db_product


@app.get("/products")
def list_products(db: Session = Depends(get_db)): 
     products = db.query(Product).all()  
     return products

And we are done! The path operations can create and read products leveraging the integrated SQLite database using sessions from our dependency.

Fetching Only Required Columns

One optimization we can make is to fetch only the required model columns instead of all of them.

For example, to return just name and price:

@app.get("/products")
def list_products(db: Session = Depends(get_db)):
     products = db.query(Product.name, Product.price).all()
     return products 

This avoids transferring unnecessary data over the network.

Leveraging Pydantic Validation

A major advantage of using the Pydantic models is input validation and output serialization handling for free from FastAPI.

For example, if we try to create a product with invalid data:

// POST /products body
{
   "name": "Basketball",
   "price": "twenty" // Invalid number  
}

We would get back a clear and useful validation error:

{
    "detail": [
        {
            "loc": [
                "body",
                "price"
            ],
            "msg": "value is not a valid float",
            "type": "type_error.float"
        }
    ]
}

The integration enables seamless data validation out of the box.

Comparison of Integration Approaches

There are a few popular ways to integrate SQLAlchemy with FastAPI – here is a quick comparison:

Generally the engine session approach is recommended unless you have a specific need met by the others.

Conclusion

Integrating SQLite databases with FastAPI provides a lightweight yet powerful data access layer for your Python web applications.

As we demonstrated, it only takes a few key steps to start persistently storing and querying data from SQLite:

1. Define Pydantic models for validation and serialization
2. Setup SQLAlchemy models that map to database schemas
3. Connect the database using an SQLAlchemy Engine
4. Create a dependency to manage database sessions
5. Write path operations that utilize the database

Following this simple recipe enables all the benefits of a relational database, while leveraging FastAPI’s stellar developer experience, performance, and scalability.

Compared to using an ORM alone, FastAPI combined with SQLite gives you automatic input validation and output serialization to/from clean Pydantic models – ensuring data integrity with minimal effort.

For many applications, the simple local file storage, full SQL support, battles-tested stability, and zero-configuration of SQLite makes it an ideal choice as a backing data store.

As your application grows, it is straightforward to switch to a more scalable production database like PostgreSQL or MySQL when needed, with very few code changes.

So if you are looking to persist, organize and query structured data in your next FastAPI project, SQLite is likely the right tool for the job. Give it a try today!