Freshmaker database
Freshmaker as an SQL toolkit uses SQLAlchemy extension for Flask. So whenever you want to add some columns or tables you should at least check models.py
file. Every class that has a FreshmakerBase as a parent is actually a table with its own columns, relationships etc.
As for database management system, we are using PostgreSQL in deployments and SQLite in local environment.
And besides that for testing we are using in-memory database, so whenever you are running tests from tests/
folder, new database is created and live while tests are running.
Setting up Database
When you for the first time want to create freshmaker database, you are supposed to run:
./manager createdb
This command will create new freshmaker.db
file for local use for you.
Database update
When some changes are applied to database scheme, like changing models.py
, you should generate migration file for a new database scheme by running:
./manager db migrate
After that you can apply that migration by running:
./manager db upgrade
After that all changes should be already applied to your local database.
Querying database
You can query your locally created database by running something like this in the directory with database file:
sqlite3 freshmaker.db 'SELECT id FROM artifact_builds;'
Database Migrations
Migration is similar to Cachito. Follow the steps below for database data and/or schema migrations:
Checkout the main branch and ensure no schema changes are present in
freshmaker/models.py
Set
SQLALCHEMY_DATABASE_URI
tosqlite:///*path-to-your-freshmaker.db*
in used configuration file.Run
./manager db upgrade
which will create an empty database in the root of your Git repositorycalled
freshmaker.db
with the current schema appliedCheckout a new branch where the changes are to be made
In case of schema changes,
Apply any schema changes to
freshmaker/models.py
Run
./manager db migrate
which will autogenerate a migration script infreshmaker/migrations/versions
In case of no schema changes,
Run
./manager db revision
to create an empty migration script file
Rename the migration script so that the suffix has a description of the change
Modify the docstring of the migration script
For data migrations, define the schema of any tables you will be modifying. This is so that it
captures the schema of the time of the migration and not necessarily what is in models.py since
that reflects the latest schema.
Modify the
upgrade
function to make the adjustments as necessaryModify the
downgrade
function to reverse the changes that were made in theupgrade
functionMake any adjustments to the migration script as necessary
To test the migration script,
Populate the database with some dummy data as per the requirement
Run
./manager db upgrade
Also test the downgrade by running
./manager db downgrade <previous revision>
(where previous revision is the revision ID of the previous migration script)
Remove the configuration of
SQLALCHEMY_DATABASE_URI
that you set earlierRemove
freshmaker.db
Commit your changes