Database

JsWeb provides a robust database layer built on top of SQLAlchemy. It simplifies session management and provides a convenient base class for your models with helpful CRUD (Create, Read, Update, Delete) methods.

Setup

The database connection is configured via the DATABASE_URL in your config.py file. The database is initialized automatically when the application starts.

# config.py
DATABASE_URL = "sqlite:///my_app.db"
# Or for PostgreSQL:
# DATABASE_URL = "postgresql://user:password@host/dbname"

Session Management

JsWeb uses a thread-local session object called db_session. This object is the standard way to interact with the database. You can import it from jsweb.database.

The session's lifecycle (creation, commit, rollback, and closing) is managed automatically by the DBSessionMiddleware, so you typically don't need to worry about it in your view functions.

from jsweb.database import db_session
from .models import User

@app.route('/users')
def list_users(req, res):
    all_users = db_session.query(User).all()
    # ...

Defining Models with ModelBase

To create your database models, you should inherit from ModelBase. This base class provides all the standard SQLAlchemy declarative functionality plus several helper methods.

# models.py
from jsweb.database import ModelBase, Column, Integer, String, ForeignKey, relationship

class User(ModelBase):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(100), unique=True, nullable=False)
    posts = relationship("Post", back_populates="author")

class Post(ModelBase):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    title = Column(String(100), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    author = relationship("User", back_populates="posts")

CRUD Operations with ModelBase

ModelBase makes common database operations simple and clean.

Model.create(**kwargs)

Creates and saves a new model instance in a single step.

@app.route('/create-user', methods=['POST'])
def create_user(req, res):
    new_user = User.create(username=req.form['username'])
    return f"User {new_user.username} created with ID {new_user.id}"

instance.update(**kwargs)

Updates attributes of an existing model instance and saves the changes.

@app.route('/user/<int:user_id>/edit', methods=['POST'])
def edit_user(req, res, user_id):
    user = db_session.query(User).get(user_id)
    if user:
        user.update(username=req.form['new_username'])
        return "User updated!"
    return "User not found", 404

instance.save()

Saves the current state of the instance to the database. This is useful if you modify an instance's attributes directly.

user = db_session.query(User).get(1)
user.username = "a_new_name"
user.save() # Commits the change

instance.delete()

Deletes the instance from the database.

@app.route('/user/<int:user_id>/delete')
def delete_user(req, res, user_id):
    user = db_session.query(User).get(user_id)
    if user:
        user.delete()
        return "User deleted."
    return "User not found", 404

Querying

Because ModelBase adds a query property, you can start your queries directly from your model class, which is a common and convenient pattern.

# Get a user by their primary key
user = User.query.get(1)

# Filter by username
user = User.query.filter_by(username='john_doe').first()

# Get all posts by a user
posts = Post.query.filter(Post.author == user).all()