Create an API for DataTables with FastAPI

DataTables is a popular jQuery plugin that offers features like pagination, searching, and sorting, making it easy to handle large datasets. This article will demonstrate how to create a FastAPI back-end to work with the DataTables. What are the parameters that DataTables sends to our API and the requirements of the data that DataTables needs.

To deal with DataTables, you need to understand the information that DataTables will send to the API through the query string.

draw = 1
columns[0][data] = id
columns[0][name] = 
columns[0][searchable] = true
columns[0][orderable] = true
columns[0][search][value] = 
columns[0][search][regex] = false
columns[1][data] = name
columns[1][name] = 
columns[1][searchable] = true
columns[1][orderable] = true
columns[1][search][value] = 
columns[1][search][regex] = false
columns[2][data] = price
columns[2][name] = 
columns[2][searchable] = true
columns[2][orderable] = true
columns[2][search][value] = 
columns[2][search][regex] = false
order[0][column] = 0
order[0][dir] = asc
order[0][name] = 
start = 0
length = 10
search[value] = 
search[regex] = false

• draw the request ID that is used to synchronize between the client and server.
• columns[x][data] the column's field name that we define on the client-side.
• order[0] the sorting information.
• start the start index of the record.
• length the length per page (page size).
• search[value] the search value information.

The DataTables expected data will require these information.

• draw DataTables sends this ID to us, and we just send it back.
• recordsTotal Total records number before filtering.
• recordsFiltered Total records number after filtering.
• data The records data.

Prerequisites

• Python 3.10
• MySQL

Setup project

pip install fastapi sqlalchemy pymysql uvicorn python-dotenv

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ .env
└─ app
   ├─ db.py
   ├─ main.py
   ├─ models
   │  └─ product.py
   ├─ routers
   │  └─ product.py
   ├─ static
   │  └─ index.html
   └─ __init__.py

__init__.py is used to mark the directory as a Python package.

Project files

.env

DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=example
DB_USER=root
DB_PASSWORD=

This file contains the database connection information.

db.py

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

load_dotenv()
url = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_DATABASE')}"
engine = create_engine(url)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

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

The db.py module sets up the database connection for a FastAPI application using SQLAlchemy. It loads database credentials from environment variables, creates a SQLAlchemy engine for a MySQL database, and establishes a session factory for database interactions. The get_db function provides a database session that can be used in dependency injection, ensuring proper session management by closing the session after use.

models\product.py

from sqlalchemy import *
from app.db import Base

class Product(Base):
    __tablename__ = "Product"
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR)
    price = Column(DECIMAL)

The models\product.py module defines a SQLAlchemy model for the Product table, mapping its columns for the product's ID, name, and price. It extends the Base class from the db module, enabling easy interaction with product data in the database.

routers\product.py

from fastapi import APIRouter, Request, Depends
from sqlalchemy.orm import Session
from sqlalchemy.sql import select, asc, desc, func
from app.db import get_db
from app.models.product import Product

router = APIRouter()

@router.get("/products")
def index(request: Request, start: int, length: int = 10, db: Session = Depends(get_db)):
    params = request.query_params.get
    order = "id"
    if params("order[0][column]"):
        order = params("columns[" + params("order[0][column]") + "][data]")
    direction = params("order[0][dir]", "asc")
    sort_direction = asc if direction == "asc" else desc
    query = db.query(Product)
    recordsTotal = query.count()
    search = params("search[value]")
    if search:
        query = query.filter(Product.name.like(f"%{search}%"))
    recordsFiltered = query.count()
    products = (
        query
        .order_by(sort_direction(getattr(Product, order)))
        .offset(start)
        .limit(length)
        .all()
    )
    return {
        "draw": params("draw"),
        "recordsTotal": recordsTotal,
        "recordsFiltered": recordsFiltered,
        "data": products
    }

This product.py file defines a FastAPI endpoint for fetching product data optimized for DataTables. It supports server-side pagination, sorting, and search functionality, allowing dynamic ordering and filtering of products. SQLAlchemy ORM is used to query the Product model, returning total records, filtered records, and product data, ensuring efficient data handling for large datasets.

main.py

import uvicorn
from fastapi import FastAPI
from fastapi.responses import FileResponse
from app.routers.product import router

app = FastAPI()
app.include_router(router, prefix="/api")
@app.get("/")
async def index():
    return FileResponse("app/static/index.html")

if __name__ == " __main__":
    uvicorn.run(app, host="127.0.0.1")

The main.py module initializes a FastAPI application and includes a router for handling product-related API requests under the /api prefix. It also serves a static index.html file from the app/static directory at the root URL (/). When run as the main program, it starts the FastAPI server using Uvicorn, listening on 127.0.0.1. This setup provides the structure for both API endpoints and static file delivery in the application.

index.html

<!DOCTYPE html>
<head>
    <link rel="stylesheet" href="https://cdn.datatables.net/2.0.7/css/dataTables.dataTables.min.css">
</head>
<body>
    <table id="table" class="display">
        <thead>
            <td>id</td>
            <th>name</th>
            <th>price</th>
        </thead>
    </table>
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/2.0.7/js/dataTables.min.js"></script>
    <script>
        new DataTable('#table', {
            ajax: '/api/products',
            processing: true,
            serverSide: true,
            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'price' }
            ]
        })
    </script>
</body>
</html>

This index.html file sets up DataTables with HTML and JavaScript to interact with our API. The processing: true option displays a loading indicator during data requests, while serverSide: true ensures that all DataTables operations, such as pagination, sorting, and searching, are handled via server-side API calls.

Run project

uvicorn app.main:app

Open the web browser and goto http://localhost:8000

You will find this test page.

Testing

Page size test

Change page size by selecting 25 from the "entries per page" drop-down. You will get 25 records per page, and the last page will change from 10 to 4.

Sorting test

Click on the header of the first column. You will see that the id column will be sorted in descending order.

Search test

Enter "no" in the search text-box, and you will see the filtered result data.

Conclusion

In this article, you have learned how to create a FastAPI back-end to work with DataTables. You now understand the various parameters DataTables sends to the API and how to use them to generate and return the appropriate data. Additionally, you have learned how to set up DataTables on the client-side using HTML and JavaScript. I hope this article helps you integrate these features into your next project.

Source code: https://github.com/stackpuz/Example-DataTables-FastAPI

Create a CRUD Web App in Minutes: https://stackpuz.com