Ekekenta Odionyenfe I am a software engineer and technical writer who is proficient in server-side scripting and database setup.

Server-side rendering with FastAPI and MySQL

9 min read 2762

Server Side Rendering FastAPI Mysql

FastAPI is a modern and performant web framework for building APIs, a task that typically requires using a frontend tool to handle the client side. Based on Pydantic and Starlette, FastAPI includes server-side rendering features and type hints for Python ≥ v3.6.0, supporting both the client side and server side. Additionally, FastAPI includes the following features:

  • Comparable speed to Node.js and Go
  • Reduced possibility of human-caused errors
  • Excellent support for code editors
  • Eliminates code duplication
  • Standards-based

Unlike other Python frameworks, like Flask, FastAPI is integrated with SQLAlchemy, which supports database integrations with MySQL, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server.

In this tutorial, we’ll explore server-side rendering with FastAPI by building a simple database for adding and removing movie titles. You can follow along by cloning the GitHub repository for this project. Let’s get started!

Setting up our project

Let’s start by setting up our application. Our project uses the following structure:

 ┣ static
 ┃ ┣ css
 ┃ ┃ ┣ overview.css
 ┃ ┃ ┗ style.css
 ┃ ┗ js
 ┃ ┃ ┗ script.js
 ┣ templates
 ┃ ┣ index.html
 ┃ ┗ overview.html
 ┣ .gitignore
 ┣ database.py
 ┣ main.py
 ┣ model.py
 ┣ requirements.txt
 ┗ schema.py

In the static directory, we’ll store static files. templates is the directory for our HTML pages, and database.py is a file for our database connections. The model.py file is for our database models, and the schema.py file is for our database schema.

Set up a virtual environment

It’s good practice to create isolated Python environments for your Python project. To ensure that you have virtualenv installed, run the command below:

pip install virtualenv

Now, create a new directory called server-side-rendering-with-fastapi. Navigate to it and use the command below to create a virtual environment:

python3 -m venv env

To activate the virtual environment we just created, run the command below:

source env/bin/activate

Install dependencies

Now, let’s install the necessary packages for our project. We will use Uvicorn as our ASGI development server, Jinja2 as our template engine, and python-multipart to receive form fields from the client:

pip install fastapi uvicorn jinja2 python-multipart

Creating the FastAPI server

With our project set up, we can create our FastAPI server. Create a main.py file in the project’s root directory and add the following code to it:

from fastapi import FastAPI
app = FastAPI()

@app.get("/")
def read_root():
    return {"message": "welcome to FastAPI!"}

Then, run the server with the command below:

uvicorn main:app --reload

The --reload flag tells Uvicorn to reload the server whenever new code is added to the application. Next, open your browser and navigate to http://127.0.0.1:8000, where you’ll see a JSON response similar to the following message:

{"message": "welcome to FastAPI!"}

Setting up SQLAlchemy

Now that we’ve set up our FastAPI server, let’s get started with the SQLAlchemy ORM (Object Relational Mapper) and create a database. Let’s install SQLAlchemy and MySQL Connector/Python:

pip install sqlalchemy mysql-connector-python

In your terminal, run the following command to create a database in your MySQL database:

//Login to MySQL
mysql -u root -p

//Create database named serversiderendering
CREATE DATABASE serversiderendering;

In your project’s root directory, create a database.py file. We’ll import SQLAlchemy create_engine, declarative_base, and sessionmaker. We’re using MySQLConnector to connect to our database, so our connection string will look like the following code:

>DATABASE_URL = "mysql+mysqlconnector://[email protected]:3306/serversiderendering"

We can connect to our database using the create_engine function we just imported from SQLAlchemy. We’ll also import the sessionmaker function, which creates a session for eliminating security issues in our application.

However, the session will not be created until a Sessionlocal class instance is created from the sessionmaker. We’ll disable autocommit and autoflush, then bind the database engine to the session.

The declarative_base class, which we’ll use to create our application’s database model, is also required for our database connection. Add the code below to database.py:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
DATABASE_URL = "mysql+mysqlconnector://[email protected]:3306/serversiderendering"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

Creating a database model

Let’s build a SQLAlchemy database model now that our application is connected to our MySQL database. To begin, create a model.py file in your project’s root directory. Each attribute of our database is represented by a Column in SQLAlchemy. We’ll import Column and pass a SQLAlchemy class type, like Integer, String, Text, or Boolean as an argument defining the type in the database.

To create the SQLAlchemy models, we’ll import and use the Base class from our database.py file. Then, add the __tablename__ attribute to the Movie class, telling SQLAlchemy what name to use in the database for our model.

To receive unique data, we add the unique parameter to our name field, make ID the primary_key, and index it. Add the code below to model.py:

from sqlalchemy.schema import Column
from sqlalchemy.types import String, Integer, Text
from database import Base
class Movie(Base):
    __tablename__ = "Movie"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(20), unique=True)
    desc = Column(Text())
    type = Column(String(20))
    url = Column(String(100))
    rating = Column(Integer)

Creating the database schema

Now, our database model has been configured, but we still need to create a schema for our model, which will read data and return it from the API. To achieve this, we’ll create a Pydantic schema for our model.

First, we’ll define our model validations, ensuring that the data coming from the client side is the same data type as the field we defined. Next, Pydantic’s orm_mode will instruct the Pydantic model to read the data as a dictionary and as an attribute.

Create a schema.py file in your project’s root directory and paste the code below into it:

from datetime import date
from pydantic import BaseModel
class Movie(BaseModel):
    id = int
    name = str
    desc = str
    type = str
    url = str
    rating = str

    class Config:
        orm_mode = True

Now, let’s go back to our main.py file and import the database, schema, SessionLocal variable, database engine, and model:

import schema
from database import SessionLocal, engine
import model

Then, we’ll create our table by calling the model.Base.metadata.create_all() function and bind our database engine to it:

model.Base.metadata.create_all(bind=engine)

Finally, we’ll create a get_database_session() function in main.py, which will create and close the session in all of our routes:

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

Creating our templates

Now, we’ll create HTML pages and render them to the client. First, we’ll create a template folder in our project’s root directory to store our templates.

To render the pages, we’ll need to add some configurations to our server. In main.py, we’ll import Jinja2Templates from FastAPI to configure our template and StaticFiles to configure our static files:

from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates

We’ll use the FastAPI mount method to add the static files to our application, which requires the file path, directory, and a name for the static files:

app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")

Then, create a static/css/style.css file in your project’s root directory and add the following custom styles to it:

body{
    background-color:rgb(236, 234, 232);
}
img {
   width: 100%;
   border-radius: 10px;
}
.image{
    width: 30%;
}
.details{
    width: 70%;
    margin: 12px
}
.card {
    border-radius: 20px;
}

You can also save your JavaScript image folders and files in the static folder to render them to the client. Lastly, let’s create index.html and overview.html files in the templates directory. Add your HTML document to these files.

Rendering our templates

Now that we’ve created and configured our templates, let’s render them to the client. To begin, we must create a route for the index and overview pages. In main.py, we’ll import Depends and Request from FastAPI, Session from SQLAlchemy, and HTMLResponse from FastAPI responses:

from fastapi import FastAPI, Depends, Request
from sqlalchemy.orm import Session
from fastapi.responses import HTMLResponse

You have to pass the request as part of the key-value pairs in the context for Jinja2 in your request handler function, along with the database session, which will be dependent on the get_database_session() function we created to manage our session across our routes. Then, we’ll query our database to retrieve our movies and render them with our index.html template.



The overview route accepts a request parameter, which is used to query the database for the specific data using the filter method. Then, it returns the first occurrence of the queried items. You can learn more about database queries in the FastAPI documentation.

When a request is made to these endpoints, we return a TemplateResponse to the client with the movie object. Update your main.py file with the code below:

@app.get("/movie", response_class=HTMLResponse)
async def read_movies(request: Request, db: Session = Depends(get_database_session)):
    records = db.query(Movie).all()
    return templates.TemplateResponse("index.html", {"request": request, "data": records})

@app.get("/movie/{name}", response_class=HTMLResponse)
def read_movie(request: Request, name: schema.Movie.name, db: Session = Depends(get_database_session)):
    item = db.query(Movie).filter(Movie.id==name).first()
    print(item)
    return templates.TemplateResponse("overview.html", {"request": request, "movie": item})

In our index.html file, we’ll load our static file and display our data using Jinga2. We use url_for to load our static file with a Jinja tag, passing the static file name and the path to the file:

<link href="{{ url_for('static', path='/style.css') }}" rel="stylesheet">

Then, we’ll loop through our movie objects and display them on our HTML page. Currently, we haven’t added any movies yet:

  <div class="col-md-4 mb-2">
    <div class="card">
      <div class="card-body d-flex flex-row justify-content-between align-items-center">
         <div class="image mb-2">
              <img src="{{movie.url}}" alt="">
         </div&gt;
         <div class="details">
            <a href="/movie/{{movie.id}}"> <h4>{{movie.name}}</h4></a>
            <div class="footer d-flex flex-row justify-content-between align-items-center">
               <h6>{{movie.type}}</h6>
               <h6>{{movie.rating}}</h6>        
           </div>
        </div>
      </div>
    </div>
  </div&gt;

Creating a movie form

Now that we’ve successfully rendered our template on the client side, let’s create an HTML form that enables us to save movies to the database. First, we need to create an HTML form in index.html:

<form action="/movie/" method="POST" enctype="application/x-www-form-urlencoded">
    <div class="row">
        <div class="col-md-6">
            <label for="">Movie Name:</label>
            <input type="text" class="form-control" id="email" name="name" />
        </div>
        <div class="col-md-6">
            <label for="">Image URL:</label>
            <input type="text" class="form-control" name="url" />
        </div>
        <div class="col-md-6">
            <label for="">Type:</label>
            <select name="type" id="" class="form-control">
                <option value=""></option>
                <option value="movie">movie</option>
                <option value="series">series</option>
            </select>
        </div>
        <div class="col-md-6">
            <label for="">Rating:</label>
            <input type="number" class="form-control" name="rate" min="18" />
        </div>
        <div class="col-md-12">
            <label for="">Description:</label>
            <textarea name="desc" rows="5" class="form-control"></textarea>
        </div>
        <div class="col-md-6">
            <button type="submit" class="btn btn-info mt-4">Save</button>
        </div>
    </div>
</form>

When sending data to FastAPI, always encode your HTML form with the application/x-www-form-urlencoded.

Before we can use the form in our application, we’ll need to import Form from FastAPI and RedirectResponse from Starlette into our main.py file:

from fastapi import Depends, FastAPI, Request, Form
from starlette.responses import RedirectResponse

Next, we’ll create a request handle, configure our form, and validate it with our database schema. Then, we’ll create an instance of our movie model, passing the data from the user to the model. Finally, we’ll add and save records to the database using the db.add and db.commit methods.

We’ll redirect the user back to the root route of our application using the FastAPI RedirectResponse function, which accepts a URL and a status code as parameters:

@app.post("/movie/")
async def create_movie(db: Session = Depends(get_database_session), name: schema.Movie.name = Form(...), url: schema.Movie.url = Form(...), rate: schema.Movie.rating = Form(...), type: schema.Movie.type = Form(...), desc: schema.Movie.desc = Form(...)):
    movie = Movie(name=name, url=url, rating=rate, type=type, desc=desc)
    db.add(movie)
    db.commit()
    response = RedirectResponse('/', status_code=303)
    return response

When redirecting a user from a POST route to a GET route, always include the 303 status code.

Updating movies

We need to create a route to enable users to update movies. HTML forms support only GET and POST requests, so we’ll configure our update route to accept JSON data. First, we need to import JSONResponse from fastapi.responses into main.py:

from starlette.responses import `JSONResponse

Next, we’ll create a patch route that will accept the movie ID as a parameter. Then, we get the user’s input from the request.json() method and search the database for a movie with the specific ID.

We can update the movie’s name and description, refresh our database, convert the movie object to JSON, and return it to the client as a response:

@app.patch("/movie/{id}")
async def update_movie(request: Request, id: int, db: Session = Depends(get_database_session)):
    requestBody = await request.json()
    movie = db.query(Movie).get(id)
    movie.name = requestBody['name']
    movie.desc = requestBody['desc']
    db.commit()
    db.refresh(movie)
    newMovie = jsonable_encoder(movie)
    return JSONResponse(status_code=200, content={
        "status_code": 200,
        "message": "success",
        "movie": newMovie
    })

Now open the overview.html file and add the update form:

&lt;form method="POST" id="updateForm">
  <div class="row">
    <div class="col-md-12">
      <label for="">Movie Name:</label>
      <input type="text" class="form-control" id="name" />
    </div>
    <div class="col-md-12">
      <label for="">Description:</label>
      <textarea id="desc" rows="5" class="form-control"></textarea>
    </div>
    <input type="hidden" id="id" value="{{movie.id}}" />
    <div class="col-md-6">
      <button type="submit" class="btn btn-info mt-4">Update</button>
    </div>
  </div>
</form>

Next, we will use the JavaScript Fetch API to send a Patch request to the server to update the movies. Add the code below to script.js:

form = document.getElementById("updateForm");
function updateMovie(id, name, desc) {
  fetch("/movie/" + id, {
    method: "PATCH",
    body: JSON.stringify({
      name,
      desc,
    }),
  }).then((response) => response.json());
  window.location.reload();
}
form.addEventListener("submit", (e) => {
  e.preventDefault();
  const name = document.getElementById("name").value;
  const des = document.getElementById("desc").value;
  const id = document.getElementById("id").value;
  updateMovie(id, name, des);
});

Delete movies

We also need the option to delete a movie from our database. To do so, we’ll need to create a delete route that accepts the movie ID as a parameter in our main.py folder. We’ll search the database for the movie with that specific ID, then delete it using the db.delete() method. When the movie is successfully deleted, we’ll send a null value to the client:

@app.delete("/movie/{id}")
async def delete_movie(request: Request, id: int, db: Session = Depends(get_database_session)):
    movie = db.query(Movie).get(id)
    db.delete(movie)
    db.commit()
    return JSONResponse(status_code=200, content={
        "status_code": 200,
        "message": "success",
        "movie": None
    })

Next, in scripts.js, we’ll send a Patch request to the server to update the movies using the JavaScript Fetch API:

async function deleteMovie(id) {
  const res = await fetch("/movie/" + id, {
    method: "DELETE",
  }).then((response) => response.json());
  console.log(res);
}

Conclusion

In this tutorial, we explored FastAPI by building a simple movie database application. FastAPI is a great, modern framework for creating REST APIs. With FastAPI’s server-side rendering features, you can build and manage small-scale applications like websites and blogs without using any frontend framework.

Get setup with LogRocket's modern error tracking in minutes:

  1. Visit https://logrocket.com/signup/ to get an app ID.
  2. Install LogRocket via NPM or script tag. LogRocket.init() must be called client-side, not server-side.
  3. $ npm i --save logrocket 

    // Code:

    import LogRocket from 'logrocket';
    LogRocket.init('app/id');
    Add to your HTML:

    <script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script>
    <script>window.LogRocket && window.LogRocket.init('app/id');</script>
  4. (Optional) Install plugins for deeper integrations with your stack:
    • Redux middleware
    • ngrx middleware
    • Vuex plugin
Get started now
Ekekenta Odionyenfe I am a software engineer and technical writer who is proficient in server-side scripting and database setup.

One Reply to “Server-side rendering with FastAPI and MySQL”

  1. Hi, thanks a tone for your super detailed tutorial!

    Unfortunately, When I start the uvicorn server, I cannot connect to mysql because of an error as following

    > sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

    Seems like I need to give my password in order to connect to mysql successfully.
    Can you help fixing this? Thanks a lot!

Leave a Reply