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));
password_hash columns are the minimum
requirements for a login system. Note that a password hash and not
should be stored.
created column is useful as it allows you to track user growth
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
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 );
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 ); """, )
Whilst there isn't an autoformatter I'm aware of, I prefer to write SQL using the sqlstyle.guide guide.