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 |