Setting up a database

Websauna uses a SQL database for its primary persistent data storage. This tutorial and recommendation suggests you to use the PostgreSQL database.

To get Websauna application started, you need to

  • Create a database
  • Have a user which can connect database
  • Create Websauna initial database table creation scripts
  • Run these scripts against your local database

Creating the database

The default development database name is application_dev where application originates from your application name.

Creating database on OSX

To create a database on Homewbrew / OSX use createdb PostgreSQL command:

createdb myapp_dev

Creating database on Ubuntu

You need to sudo to postgres user to run any PostgreSQL commands:

sudo -u postgres createdb myapp_dev

Changing database name or authentication details

If you need to change the default database name or its connection details you can edit sqlalchemy.url settings in development.ini.

Creating migration scripts

Note

This will be covered later in detail.

Websauna stores data in SQL database’s tables. When these tables are changed, the database must be instructed to perform the changes. The change is recorded as a migration script which can repeatably run across several computers (coworkers, different servers). Initially you will need the migration scripts to create database tables for user and groups of your website.

We use the ws-alembic command for this task. You run ws-alembic in your package root folder:

cd myapp
ws-alembic -c company/application/conf/development.ini -x packages=all revision --auto -m "Initial migration"

This creates the migration script for the default user and groups SQL tables.

Now you should be able to locate migration scripts:

ls alembic/versions
...
-rw-r--r-- 1 moo staff 3610 Jan  6 20:52 8513e50cda41_initial_migration.py

Running the migration script

The migration scripts can be run repeatedly against multiple databases. First you need to run it against the database on your local development computer:

ws-alembic -c company/application/conf/development.ini -x packages=all upgrade head

Checking what’s in your database

Install utils to including the Python package dependency for pgcli, using pip:

pip install -e "company.application[utils]"

Then you can use ws-db-shell to open a pgcli prompt to explore your PostgreSQL database:

ws-db-shell company/application/conf/development.ini

wattcoin_dev> \dt
+----------+--------------------------+--------+---------+
| Schema   | Name                     | Type   | Owner   |
|----------+--------------------------+--------+---------|
| public   | activation               | table  | moo     |
| public   | alembic_history_myapp    | table  | moo     |
| public   | group                    | table  | moo     |
| public   | usergroup                | table  | moo     |
| public   | users                    | table  | moo     |