Database

At the core of our application is our database which stores all the data (posts and comments) and allows us to retrieve it later and update it. We will be using SQLite as our database engine as Python has built-in support through sqlite3.

Creating our Database

Creation and management of our database will be done in SQL which allows us to easily recreate and change our database whenever we want. The SQL tutorial can offer a good deep dive into the syntax.

What do we need?

With our simple blog we will need two tables, one for posts and one for comments. Our posts table will be able to store the title and body of the post as well as the date it was created. Our comments table will store the body of the comment, the date it was created and the post it belongs to.

Posts Table

Our posts table needs to include the post id, the date it was created, the title and the body of the post. The id will be the primary key and will be auto incremented. The created date will be set to the current time when the post is created. The title and body will be text fields that cannot be null.

id	created	title	body
1	2024-01-02 01:59:47	Post 1	great post
2	2024-01-02 01:59:47	Post 2	amazing post
3	2024-01-02 01:59:47	Post 3	awesome post

To create our table we can use the following SQL:

CREATE TABLE post (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);

Comments Table

The comments table will be similar to the posts table but will also include the post id that it belongs to. The id will be the primary key and will be auto incremented. The created date will be set to the current time when the comment is created. The body will be a text field that cannot be null. The post id will be an integer that cannot be null and will be a foreign key to the post table.

id	created	body	post_id
1	2024-01-02 01:59:47	great comment	1
2	2024-01-02 01:59:47	amazing comment	1
3	2024-01-02 01:59:47	awesome comment	2

To create our table we can use the following SQL:

CREATE TABLE comment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    body TEXT NOT NULL,
    post_id INTEGER NOT NULL,
    FOREIGN KEY (post_id) REFERENCES post (id)
);

Creation Script

As we know how to create our tables we can combine that into a creation script that can be run whenever we want a new database. This script will empty the current database and then recreate the new database. When changes are made to the database we can just update the script and run it which will recreate the database.

flaskapp/schema.sql

DROP TABLE IF EXISTS post;
CREATE TABLE post (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL,
    body TEXT NOT NULL
);
DROP TABLE IF EXISTS comments;
CREATE TABLE comment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    body TEXT NOT NULL,
    post_id INTEGER NOT NULL,
    FOREIGN KEY (post_id) REFERENCES post (id)
);

Core DB functionality

To make sure we can connect to our database and close it when we are done we will create a db.py file that will contain all the core functionality. This allows us to re-use all the code across our entire app.

flaskapp/db.py

"""DB functionality."""
import sqlite3

import click
from flask import current_app, g


def get_db():
    """Shared db object."""
    if "db" not in g:
        g.db = sqlite3.connect(
            current_app.config["DATABASE"],
            detect_types=sqlite3.PARSE_DECLTYPES,
        )
        g.db.row_factory = sqlite3.Row

    return g.db


def close_db(e=None):
    """Close the db."""
    db = g.pop("db", None)

    if db is not None:
        db.close()

1. One of the things that makes Flask nice is the g object. This is a global object that is unique for each request. We can use this to store our database connection so that we can re-use it across our entire app without importing anything.

2. We also need to close the database connection when we are done with it so that we don’t run out of connections.

Initialize the database

With our script we need a way to be able to initialise our database on command. We can use click which is included as part of flask to give us a command to initialise the database. This will allow us to run flask init-db to create the database.

flaskapp/db.py

def init_db():
    """Create and fill the db."""
    db = get_db()

    with current_app.open_resource("schema.sql") as f:
        db.executescript(f.read().decode("utf8"))


@click.command("init-db")
def init_db_command():
    """Clear the existing data and create new tables."""
    init_db()
    click.echo("Initialized the database.")

Integrating into our app

As we now have the ability to manage our db we need to integrate it into our app with adding the teardown and cli commands.

flaskapp/db.py

def init_app(app):
    """Add teardown and cli on app creation."""
    app.teardown_appcontext(close_db)
    app.cli.add_command(init_db_command)

Once we have this we can then import our db into our app and call the init_app function inside our create_app factory. This code should be at the end of the function to allow the application to be created first.

flaskapp/__init__.py

def create_app():
    app = ...
    # existing code omitted

    from . import db
    db.init_app(app)

    return app

Initialising the Database

As we have now integrated our database into our app we can now initialise it. To do this we can run the following command:

$ flask init-db
Initialized the database.

You should now be able to see a flaskapp.sqlite file in the instance folder in your project which is your newly created database.