Create an API for AG-Grid with FastAPI
AG-Grid is a powerful JavaScript data grid library, perfect for building dynamic, high-performance tables with advanced features like sorting, filtering, and pagination. In this article, we’ll implement an API in FastAPI to support AG-Grid, allowing for efficient server-side data operations like filtering, sorting, and pagination. By combining AG-Grid with FastAPI, we’ll create a responsive solution that ensures smooth handling of large datasets.
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(page: int = 1, size: int = 10, order = "id", direction = "asc", search = "", db: Session = Depends(get_db)):
offset = (page - 1) * size
sort_direction = asc if direction == "asc" else desc
query = db.query(Product)
if search:
query = query.filter(Product.name.like(f"%{search}%"))
count = query.count()
products = (
query
.order_by(sort_direction(getattr(Product, order)))
.offset(offset)
.limit(size)
.all()
)
return {
"data": products,
"count": count
}
The product.py file defines an API router for handling product-related requests in a FastAPI application, featuring an index function that retrieves a paginated list of products based on query parameters for pagination (page and size), sorting (order and direction), and optional searching by product name. It calculates the pagination offset, determines the sorting direction, and constructs a SQLAlchemy query to fetch products from the database, applying a filter if a search term is provided. The function then counts the total matching products, retrieves the relevant product records, and returns a JSON response containing both the product data and the total count, facilitating integration with frontend applications.
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>
<script src="https://cdn.jsdelivr.net/npm/ag-grid-community/dist/ag-grid-community.min.js"></script>
</head>
<body>
<div id="grid" class="ag-theme-alpine" style="height: 400px; width: 640px; margin: 1em"></div>
<script>
function getQuery(params) {
let query = new URLSearchParams()
let size = params.endRow - params.startRow
let page = Math.floor(params.startRow / size) + 1
query.append('page', page)
query.append('size', size)
if (params.sortModel.length) {
let sort = params.sortModel[0]
query.append('order', sort.colId)
query.append('direction', sort.sort)
}
if (params.filterModel.name) {
query.append('search', params.filterModel.name.filter)
}
return query.toString()
}
let columns = [
{ headerName: 'ID', field: 'id', sortable: true },
{
headerName: 'Name', field: 'name', sortable: true, filter: true,
filterParams: {
filterOptions: ['contains'],
maxNumConditions: 1,
}
},
{ headerName: 'Price', field: 'price', sortable: true }
]
let gridOptions = {
columnDefs: columns,
rowModelType: 'infinite',
pagination: true,
paginationPageSize: 20,
cacheBlockSize: 20,
datasource: {
getRows: function (params) {
let query = getQuery(params)
fetch(`/api/products?${query}`)
.then(response => response.json())
.then(json => {
params.successCallback(json.data, json.count)
})
}
}
}
document.addEventListener('DOMContentLoaded', () => {
agGrid.createGrid(document.querySelector('#grid'), gridOptions)
})
</script>
</body>
</html>
The index.html file sets up a web page that uses the AG-Grid library to display a dynamic data grid for products. It includes a grid styled with the AG-Grid theme and a JavaScript section that constructs query parameters for pagination, sorting, and filtering. The grid is configured with columns for ID, Name, and Price, and it fetches product data from an API endpoint based on user interactions. Upon loading, the grid is initialized, allowing users to view and manipulate the product list effectively.
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 50 from the "Page Size" drop-down. You will get 50 records per page, and the last page will change from 5 to 2.
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 of the "Name" column, and you will see the filtered result data.
Conclusion
In conclusion, we’ve successfully integrated AG-Grid with FastAPI to create a powerful and efficient data grid solution. By leveraging FastAPI's capabilities on the backend, we enabled AG-Grid to manage server-side filtering, sorting, and pagination, ensuring smooth performance even with large datasets. This integration not only streamlines data management but also enhances the user experience with dynamic, responsive tables on the frontend. With AG-Grid and FastAPI working seamlessly together, we’ve built a scalable and high-performance grid system that is ready for real-world applications.
Source code: https://github.com/stackpuz/Example-AG-Grid-FastAPI
Create a CRUD Web App in Minutes: https://stackpuz.com
