Database and SQL¶
- PostgreSQL vs. SQLite
- Accessing database session
- Debugging SQL queries
- Custom database sessions
- PostgreSQL specific
Websauna uses an SQL database. SQLAlchemy object relations mapping (ORM) library is used to create Python classes representing the model of data. From these models corresponding SQL database tables are created in the database.
By default, PostgreSQL database software is recommended, though Websauna is compatible, but not tested, with all databases supported by SQLAlchemy.
Websauna currently supports PostgreSQL and SQLite databases, but highly recommend usage of PostgreSQL as SQLite support will be removed in a future version.
- No software installation needed
- Good for local development when starting out
- Cannot handle multiple users
- No decimal support: cannot do local development in money or accounting oriented applications
- No datetime support
All database operations are done through a SQLAlchemy session (
Session is exposed as
def my_view(request): dbsession = request.dbsession user = dbsession.query(User).get(1)
request.dbsession transaction is bound to HTTP request lifecycle. If HTTP request succeeds, the transaction is committed. If HTTP request fails due to a raised exception, but not due to error value return from view, the transaction is rolled back and nothing is written into a database.
This is a common pattern when writing model APIs. You have an existing database object and you want to query related objects. In this case you can grab the session from the existing object using
from sqlalchemy.orm import Session class UserOwnedAccount(Base): # ... @classmethod def create_for_user(cls, user, asset): dbsession = Session.object_session(user) account = Account(asset=asset) dbsession.flush() uoa = UserOwnedAccount(user=user, account=account) return uoa
You need to manually manage transaction lifecycle as there is no real HTTP request lifecycle:
import transaction request = init_websauna("conf/development.ini") with transaction.manager: user = dbsession.query(User).get(1) user.full_name = "Foo Bar"
For asynchronous tasks session is available through
websauna.system.http.Request given as an compulsory argument for tasks. Transaction-aware tasks maintain their own transction lifecycle and there is no need to invoke transaction manager or commit manually:
@test_celery_app.task(base=TransactionalTask) def sample_task(request, user_id): dbsession = request.dbsession User = get_user_class(registry) u = dbsession.query(User).get(user_id) u.username = "set by celery"
[logger_sqlalchemy] level = WARN handlers = qualname = sqlalchemy.engine # "level = INFO" Show SQL queries in the console # "level = DEBUG" logs SQL queries and results. # "level = WARN" logs neither. (Recommended for production systems.)
You can override the default factory for
db_session = scoped_session(sessionmaker(extension=ZopeTransactionExtension())) # A function that will resolve dbsession for a request def create_test_dbsession(request: Request) -> Session: return db_session class Initializer(WattcoinInitializer): def configure_database(self): """Configure database without transaction manager (for test isolation). """ from websauna.system.model.meta import create_transaction_manager_aware_dbsession from websauna.system.model.interfaces import ISQLAlchemySessionFactory from pyramid.interfaces import IRequest self.config.include(".model.meta") self.config.registry.registerAdapter(factory=create_test_dbsession, required=(IRequest,), provided=ISQLAlchemySessionFactory)
websauna.system.user.usermixin uses the following column types might not be available on other database systems
sqlalchemy.dialects.postgresql.JSONB(can be downgraded to
sqlalchemy.dialects.postgresql.JSONfor older PostgreSQL version compatibility)
sqlalchemy.dialects.postgresql.INET- IPv4 and IPv6 addresses
sqlalchemy.dialects.postgresql.UUID- IPv4 and IPv6 addresses
At the moment
- Either Websauna must be patched with emulation layer for these columns for other database systems. It should be relative easy to emulate these with text columns and custom SQLAlchemy types
- Use your custom user model without these fields