Database schema
Members table
The members table stores the login credentials for the users that can use the app. The SQL to create this database is,
CREATE TABLE members(
id SERIAL PRIMARY KEY,
created TIMESTAMP NOT NULL DEFAULT now(),
email TEXT NOT NULL,
email_verified TIMESTAMP,
password_hash TEXT NOT NULL
);
CREATE UNIQUE INDEX members_unique_email_idx on members (LOWER(email));
The id
, email
, and password_hash
columns are the minimum
requirements for a login system. Note that a password hash and not
the raw
password
should be stored.
The created
column is useful as it allows you to track user growth
over time.
The email_verified
column indicates if the user has verified that
they control the email, which is a crucial thing to check before
sending the user emails. This could be a boolean, but storing the
timestamp helps understand the user's actions.
See a note with the models that explains why the unique
index is used with LOWER
.
Todo table
The todos table stores all the todos, linked to the user,
CREATE TABLE todos(
id BIGSERIAL PRIMARY KEY,
complete BOOLEAN NOT NULL DEFAULT FALSE,
due TIMESTAMPTZ,
member_id INT NOT NULL REFERENCES members(id),
task TEXT NOT NULL
);
Complete migration
The initial setup of the database should be contained in
backend/db/0.py
, which should be,
from databases import Database
async def migrate(db: Database) -> None:
await db.execute(
"""CREATE TABLE members(
id SERIAL PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
password TEXT NOT NULL
)
""",
)
await db.execute(
"""CREATE TABLE todos(
id BIGSERIAL PRIMARY KEY,
complete BOOLEAN NOT NULL DEFAULT FALSE,
due TIMESTAMPTZ,
member_id INT NOT NULL UNIQUE REFERENCES members(id),
task TEXT NOT NULL
);
""",
)
Note
Whilst there isn't an autoformatter I'm aware of, I prefer to write SQL using the sqlstyle.guide guide.