How to Use FastCRUD with Multiple Databases: PostgreSQL, MySQL, and SQLite

FastCRUD supports PostgreSQL, MySQL/MariaDB, and SQLite through SQLAlchemy 2.0 async engines, automatically detecting the database dialect at runtime to handle differences in upsert syntax while exposing a unified API for all CRUD operations.

FastCRUD is a SQLAlchemy 2.0-based CRUD generator designed for FastAPI applications that works seamlessly across multiple database backends. Whether you are connecting to PostgreSQL, MySQL/MariaDB, or SQLite, the library abstracts dialect-specific implementations so you can use identical code patterns across all supported databases. This guide explains how to configure FastCRUD for each database backend and leverage its automatic dialect detection for portable database operations.

How FastCRUD Handles Multiple Database Backends

FastCRUD is built on SQLAlchemy 2.0's async ORM capabilities, which provides native support for PostgreSQL, MySQL, and SQLite through different async driver packages.

SQLAlchemy 2.0 Foundation

The library expects an AsyncSession provider regardless of the underlying database. In fastcrud/crud/fast_crud.py, all CRUD operations—including create, get, get_multi, update, delete, and upsert—are implemented using SQLAlchemy's core and ORM APIs that translate to dialect-specific SQL at execution time.

Automatic Dialect Detection

When performing database-specific operations like bulk upserts, FastCRUD detects the dialect at runtime using db.bind.dialect.name. This detection occurs in fastcrud/crud/database_specific.py, where the library routes to the appropriate implementation:

if db.bind.dialect.name == "postgresql":
    statement, params = await upsert_multi_postgresql(...)
elif db.bind.dialect.name == "sqlite":
    statement, params = await upsert_multi_sqlite(...)
elif db.bind.dialect.name in ["mysql", "mariadb"]:
    statement, params = await upsert_multi_mysql(...)

Database-Specific Upsert Logic

The upsert implementations handle the syntactic differences between databases:

• PostgreSQL: Uses postgresql.insert(...).on_conflict_do_update(...) with full RETURNING clause support.
• SQLite: Uses sqlite.insert(...).on_conflict_do_update(...) with RETURNING clause support.
• MySQL/MariaDB: Uses mysql.insert(...).on_duplicate_key_update(...). This dialect does not support the RETURNING clause, so FastCRUD disables returning data for MySQL upserts.

Configuring Database Connections

To use FastCRUD with different databases, you only need to change the connection URL when creating the async engine.

PostgreSQL Setup

Use the postgresql+asyncpg driver scheme:

DATABASE_URL = "postgresql+asyncpg://user:password@localhost:5432/dbname"
engine = create_async_engine(DATABASE_URL, echo=True)

MySQL/MariaDB Setup

Use the mysql+aiomysql driver scheme:

DATABASE_URL = "mysql+aiomysql://user:password@localhost:3306/dbname"
engine = create_async_engine(DATABASE_URL, echo=True)

SQLite Setup

Use the sqlite+aiosqlite driver scheme for async support:

DATABASE_URL = "sqlite+aiosqlite:///./test.db"
engine = create_async_engine(DATABASE_URL, echo=True)

Environment-Based Configuration

Store the database URL in an environment variable to switch backends without code changes:

import os
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

DATABASE_URL = os.getenv("DATABASE_URL", "sqlite+aiosqlite:///./default.db")
engine = create_async_engine(DATABASE_URL, echo=True)
async_session = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_session():
    async with async_session() as session:
        yield session

Creating Database-Agnostic CRUD Endpoints

The crud_router function in fastcrud/endpoint/crud_router.py generates FastAPI endpoints without requiring database-specific configuration.

Using crud_router

Pass the async session dependency and your SQLAlchemy or SQLModel class:

from fastapi import FastAPI
from fastcrud import crud_router
from myapp.models import Item
from myapp.schemas import CreateItemSchema, UpdateItemSchema, ReadItemSchema
from myapp.db import get_session

app = FastAPI()

item_router = crud_router(
    session=get_session,
    model=Item,
    create_schema=CreateItemSchema,
    update_schema=UpdateItemSchema,
    path="/items",
    tags=["Items"],
    select_schema=ReadItemSchema,
)

app.include_router(item_router)

The router works identically whether get_session provides a PostgreSQL, MySQL, or SQLite connection. All generated endpoints (POST /items, GET /items/{id}, PATCH /items/{id}, etc.) issue the appropriate SQL for the current dialect.

Handling Upserts Across Different Databases

When performing bulk upserts with upsert_multi, FastCRUD automatically adapts to database capabilities.

PostgreSQL and SQLite RETURNING Support

Both PostgreSQL and SQLite support the RETURNING clause, allowing FastCRUD to return the inserted or updated rows:

from fastcrud.examples.item.schemas import ReadItemSchema

items_to_upsert = [
    CreateItemSchema(name="Widget", price=9.99),
    CreateItemSchema(name="Gadget", price=19.99),
]

result = await item_crud.upsert_multi(
    db,
    instances=items_to_upsert,
    schema_to_select=ReadItemSchema,
    return_as_model=True,
)

# Returns: {"data": {"created": [ReadItemSchema(...)], "updated": []}}

MySQL Limitations

MySQL and MariaDB do not support RETURNING on insert operations. When using upsert_multi with MySQL, FastCRUD disables the returning functionality:

# MySQL: Operation succeeds but returns None for data

result = await item_crud.upsert_multi(
    db,
    instances=items,
    schema_to_select=ReadItemSchema,  # Ignored for MySQL

    return_as_model=True,
)

# Returns: {"data": None} or empty response

This behavior is handled transparently in fastcrud/crud/database_specific.py, where the MySQL implementation uses mysql.insert(...).on_duplicate_key_update(...) without requesting return data.

Summary

• FastCRUD supports PostgreSQL, MySQL/MariaDB, and SQLite through SQLAlchemy 2.0 async engines.
• The library detects the database dialect at runtime using db.bind.dialect.name to route operations correctly.
• Configure your database by changing the connection URL scheme (postgresql+asyncpg, mysql+aiomysql, sqlite+aiosqlite).
• All standard CRUD operations work identically across databases; only bulk upserts require dialect-specific handling in fastcrud/crud/database_specific.py.
• PostgreSQL and SQLite support RETURNING clauses for upserts; MySQL does not, so upsert operations return data only for the former two.

Frequently Asked Questions

Does FastCRUD require different code for PostgreSQL versus MySQL?

No. FastCRUD abstracts database differences through SQLAlchemy 2.0. You use the same FastCRUD class and crud_router function regardless of whether you connect to PostgreSQL, MySQL, or SQLite. The library detects the dialect at runtime and routes database-specific operations like upserts to the appropriate implementation in fastcrud/crud/database_specific.py.

Which async drivers does FastCRUD support?

FastCRUD works with any SQLAlchemy 2.0 compatible async driver. The recommended configurations are:

• PostgreSQL: postgresql+asyncpg
• MySQL/MariaDB: mysql+aiomysql
• SQLite: sqlite+aiosqlite

You specify the driver in your DATABASE_URL connection string when creating the async engine with create_async_engine.

Why do upsert operations return data for PostgreSQL but not MySQL?

PostgreSQL and SQLite support the SQL RETURNING clause, which allows the database to send back the inserted or updated rows after an upsert operation. MySQL and MariaDB do not support RETURNING on insert operations. FastCRUD handles this transparently: when using upsert_multi with MySQL, the operation completes successfully but returns None for the data field, whereas PostgreSQL and SQLite return the created or updated records.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}