🚀 Step 3 of 10
← Previous: Step 2 — HTML + Jinja2 Templates — Your First Page
→
SQLite + SQLAlchemy — Store Users
Data must survive between requests. Define a User class that maps to a DB table, create a /register route that saves a new user.
🎯 Goal — what you'll achieve in this step
Fill a register form, submit, and see the user saved in the SQLite database file (openbenchml.db).
📖 The basic concept
Learn this first. It's the pure Python/HTML idea, stripped of all app complexity.
# The basic concept: an ORM maps a Python class to a DB table
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String, unique=True)
engine = create_engine('sqlite:///app.db')
Base.metadata.create_all(engine) # runs CREATE TABLE
Session = sessionmaker(bind=engine)
db = Session()
# Create
user = User(username='ada')
db.add(user)
db.commit() # writes to disk
print(user.id) # 1 (auto-assigned by DB)
# Read
all_users = db.query(User).all()
print([u.username for u in all_users]) # ['ada']
🧮 Apply it to OpenBenchML
database.py
python
# Step 3: Database setup
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
username = Column(String, unique=True, nullable=False)
password_hash = Column(String, nullable=False) # plain text for now; step 4 fixes this
engine = create_engine('sqlite:///openbenchml.db', connect_args={'check_same_thread': False})
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(bind=engine)
main.py
python
# Step 3: Add /register on top of steps 1-2
from fastapi import FastAPI, Request, Form, Depends
from fastapi.responses import HTMLResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy.orm import Session
import uvicorn
from database import User, SessionLocal
app = FastAPI(title='OpenBenchML')
templates = Jinja2Templates(directory='templates')
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
@app.get('/health')
async def health():
return {'status': 'ok'}
@app.get('/', response_class=HTMLResponse)
async def landing(request: Request):
return templates.TemplateResponse('landing.html', {
'request': request, 'app_name': 'OpenBenchML',
})
@app.get('/register', response_class=HTMLResponse)
async def register_form(request: Request):
return templates.TemplateResponse('register.html', {'request': request})
@app.post('/register')
async def register(
request: Request,
username: str = Form(...),
password: str = Form(...),
db: Session = Depends(get_db),
):
# Check if username exists
existing = db.query(User).filter(User.username == username).first()
if existing:
return {'error': 'username taken'}
# Save the new user
user = User(username=username, password_hash=password) # step 4: hash this
db.add(user)
db.commit()
return RedirectResponse(url='/', status_code=303)
if __name__ == '__main__':
uvicorn.run(app, host='0.0.0.0', port=8000)
templates/register.html
html
{% extends 'base.html' %}
{% block title %}Register{% endblock %}
{% block content %}
<h1>Register</h1>
<form method='post' action='/register'>
<input type='text' name='username' placeholder='Username' required>
<input type='password' name='password' placeholder='Password' required>
<button type='submit'>Sign Up</button>
</form>
{% endblock %}
How it works
A database is the difference between a toy app and a real app. Without one, every restart wipes your users. SQLAlchemy is an ORM (Object-Relational Mapper) — you write a Python class with Column-typed attributes, and it generates the CREATE TABLE SQL + translates .query()/.add()/.commit() into SQL under the hood. The User class maps to a `users` table with id, username, password_hash columns. create_all(engine) inspects the model and runs CREATE TABLE if it doesn't exist. The get_db() dependency gives each request its own Session and closes it automatically — sessions are not thread-safe, so one-per-request is mandatory. The /register route receives the form fields via Form(...), creates a User object, db.add() stages it, db.commit() writes it to disk. In step 4 we'll hash the password (storing plaintext is a security disaster).
✏️ Try yourself
Add an `email` column to the User model. Add an email field to the register form. After registering a user, add a @app.get('/users') route that returns `[{u.id, u.username, u.email} for u in db.query(User).all()]` as JSON.
⚠️ Common mistakes to avoid
- Forgetting `db.commit()` — the user is staged in memory but never written to disk. The row disappears when the session closes.
- Not calling `create_all(engine)` — the table doesn't exist, so INSERT fails with `no such table: users`.
- Sharing one Session across requests — sessions are not thread-safe. Use `Depends(get_db)` so each request gets its own.
- Using `connect_args={'check_same_thread': False}` without understanding it — it's required for SQLite + FastAPI because FastAPI uses threads. In production with Postgres, you don't need it.
🔍 Where this is used in OpenBenchML
app/database/models.py defines User, Model, Dataset, BenchmarkJob, LeaderboardEntry, Competition, Comment. app/database/db.py creates the engine + SessionLocal. app/routes/auth.py uses get_db for register/login.
Next — Step 4
Forms + Password Hashing + JWT — Login