Persistent web server with sqlite3 Link to heading

Today, we will look into how to create a web-server with persistent data. That is, the web-server will log its data into a database file, and even in scenarios when the web server needs to restart due to crash or update, the database will remain intact and the server will append to the existing database, rather than overwrite.

There is really nothing magical about this. We are just going to plug in a database to our stateless server to make it stateful — that’s it. This article is intended to the audience with little or no experience in connecting a database with a server.

Dependencies Link to heading

  • Fastapi: we will write a server using FastAPI, just because it is simple and easy to, but you are welcome to use other framework, such as Flask.
  • SQLite3: this is the main ingredient. Though there are a load of other databases options out there, we are going to stick with SQLite3 as this is built-into virtually all *nix environment.

Within your Python environment, install Fastapi

pip install "fastapi[standard]"

High-level overview Link to heading

What we want to achieve is very simple. We have a server that is running, listening to a POST request for a log entry. Upon a successful request, it will insert the log message into the database. For convenience, we will also let the server listen for a GET request, upon which it dispatches all the log entries in the table.

The meat of the exercise is that database must be persistent. That is, the server may crash or update, but the database must remain intact. In other words, all the items in the table shall remain even after the server restarts.

Implementation Link to heading

Let’s first create a simple config file where we specify the database file, as in

# config.py
DB_PATH = 'log.db'

Note that the database file must be stored in a stateful storage. If you are running the server inside a container, the database path must not be within the container, because a container is stateless and the database file will be gone when the container restarts.

Next, let’s write the server. It should be fairly simple.

# serve.py
from fastapi import FastAPI, Depends
from pydantic import BaseModel
import sqlite3
from contextlib import asynccontextmanager
import config
import uvicorn


class LogEntry(BaseModel):
    message: str


@asynccontextmanager
async def lifespan(app: FastAPI):
    # Initialize the database
    conn = sqlite3.connect(config.DB_PATH)
    cursor = conn.cursor()
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS logs
                      (id INTEGER PRIMARY KEY AUTOINCREMENT,
                       message TEXT,
                       timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)
        """
    )
    conn.commit()
    conn.close()
    yield
    # Cleanup (if needed)


app = FastAPI(lifespan=lifespan)


def get_db():
    conn = sqlite3.connect(config.DB_PATH, check_same_thread=False)
    try:
        yield conn
    finally:
        conn.close()


@app.post("/log")
async def create_log(log: LogEntry, db: sqlite3.Connection = Depends(get_db)):
    cursor = db.cursor()
    cursor.execute("INSERT INTO logs (message) VALUES (?)", (log.message,))
    db.commit()
    return {"status": "Log entry created"}


@app.get("/logs")
async def get_logs(db: sqlite3.Connection = Depends(get_db)):
    cursor = db.cursor()
    cursor.execute("SELECT * FROM logs")
    logs = cursor.fetchall()
    return {"logs": logs}

We are creating a new table only if it does not exist. Otherwise, we load it and append items. Note that we are setting check_same_thread parameter to False in connect() method. According to this post, this is safe. Without the flag, we will encounter an error from SQLite3.

Before we test our server, let’s write a simple helper script to print out the database.

# print.py
import sqlite3
from tabulate import tabulate
import config


def print_db_contents():
    try:
        # Connect to the SQLite database
        conn = sqlite3.connect(config.DB_PATH)
        cursor = conn.cursor()

        # Execute a SELECT query to fetch all rows from the table
        cursor.execute("SELECT * FROM logs ORDER BY timestamp ASC")
        rows = cursor.fetchall()

        if not rows:
            print("The database is empty.")
        else:
            # Get the column names
            column_names = [description[0] for description in cursor.description]

            # Print the data in a tabular format
            print(tabulate(rows, headers=column_names, tablefmt="grid"))

        # Close the database connection
        conn.close()

    except sqlite3.Error as e:
        print(f"An error occurred: {e}")


if __name__ == "__main__":
    print_db_contents()

Testing Link to heading

Let’s start the server. To prove that it is thread-safe, we are going to spawn multiple processes with --workers option

fastapi run --workers=4 server.py

Open up a new terminal, and run the following to monitor the database.

watch "python print.py | tail"

Open up yet another terminal, and fire up the following to execute a POST request

# create post_data.json containing the POST content
echo '{"message": "a message to log"}' > post_data.json

# send the request
curl -X POST "http://localhost:8000/log" -H "Content-Type: application/json" -d @post_data.json
{"status":"Log entry created"}

The message should appear in the monitoring terminal window. Now, let’s send a bunch of concurrent requests to make sure the database does not fail. For that we will use ab command.

# send 1000 requests, up to 100 concurrently at any given time
ab -n 1000 -c 100 -p post_data.json -T application/json http://localhost:8000/log

Now, let’s stop the server with control-C and restart. Is the database still intact? We can easily test by sending a GET request

curl -X GET "http://localhost:8000/logs"

If the database is persistent, it should return all log entries so far!

References Link to heading

SQLite Home Page
Small. Fast. Reliable.Choose any three. SQLite is a C-language library that implements a small, fast, self-contained…
www.sqlite.org

FastAPI
FastAPI framework, high performance, easy to learn, fast to code, ready for production
fastapi.tiangolo.com

Python, SQLite, and thread safety
If you have ever used the built-in sqlite3 module in a multithreaded Python application, you may have seen this…
ricardoanderegg.com