- ESAP Databases
- Multiple Databases in Django
- Database Changes
- Updating databases in place after new deploy
ESAP Databases
The ESAP API gateway has a (configuration) database per app (query, batch, ida), where at this moment the database that is associated with query app (esap_config.sqlite3) also contains tables for the whole project (like user information).
These databases are more like 'configuration files' than proper databases, because they mainly hold (static) configuration rather than dynamic (meta)data.
When this changes, for instance when dynamic data about 'jobs' or 'users' need to be stored, we may have to rethink the current mechanism. For instance, we may want to store dynamic data in a Postgres database in a docker container instead of in the current binary file in a shared directory.
There is currently no mechanism in place to migrate the database that is in place, so if changes have been made then the database should be manually copied from your development to production.
Multiple Databases in Django
Configure DATABASE_ROUTERS and DATABASES in settings.py
# Database
DATABASE_ROUTERS = ['query.database_router.QueryRouter',
'staging.database_router.StagingRouter',
'ida.database_router.IdaRouter']
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'esap_config.sqlite3'),
},
'staging': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'esap_staging_config.sqlite3'),
},
'ida': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'esap_ida_config.sqlite3'),
},database_router.py
Each app with a database must have a database_router.py to redirect the traffic to the correct database.
The order matters: the top router in the DATABASE_ROUTER dict is the router for the query app, whose database also still has some tables for the esap-gateway as a whole (this may change later).
So this first router, the QueryRouter, is configured to handle traffic to the 'default' esap_config.sqlite3 database for the following
apps : {'query', 'auth', 'contenttypes', 'sessions', 'admin'}
(Note that this router point to the default database).
Example of two database routers, the QueryRouter and the StagingRouter, both in the database_router.py file of their specific app.
class QueryRouter:
route_app_labels = {'query', 'auth', 'contenttypes', 'sessions', 'admin'}
def db_for_read(self, model, **hints):
if model._meta.app_label in self.route_app_labels:
return 'default'
def db_for_write(self, model, **hints):
return 'default'
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if a model in the query apps is
involved.
"""
if (
obj1._meta.app_label in self.route_app_labels or
obj2._meta.app_label in self.route_app_labels
):
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure the query apps only appear in the
'query' database.
"""
if app_label in self.route_app_labels:
return db == 'default'
return Noneclass StagingRouter:
route_app_labels = {'staging'}
def db_for_read(self, model, **hints):
"""
Attempts to read staging models go to staging database.
"""
if model._meta.app_label in self.route_app_labels:
return 'staging'
def db_for_write(self, model, **hints):
"""
Writes always go to staging.
"""
return 'staging'
def allow_relation(self, obj1, obj2, **hints):
"""
Allow relations if a model in the staging apps is
involved.
"""
if (
obj1._meta.app_label in self.route_app_labels or
obj2._meta.app_label in self.route_app_labels
):
return True
return None
def allow_migrate(self, db, app_label, model_name=None, **hints):
"""
Make sure the staging apps only appear in the
'staging' database.
"""
if app_label in self.route_app_labels:
return db == 'staging'
return NoneAdmin interface
When the use of multiple databases is configured then the admin.py must also be changed to support this.
This is done by adding MultiDBModelAdmin class to it (see the Django documentation).
This is how it looks in the admin.py in the query app.
from django.contrib import admin
from .models import EsapBaseObject, Archive, DataSet, Catalog, ParameterMapping
admin.site.register(EsapBaseObject)
admin.site.register(Archive)
admin.site.register(DataSet)
admin.site.register(Catalog)
admin.site.register(ParameterMapping)
class MultiDBModelAdmin(admin.ModelAdmin):
# A handy constant for the name of the alternate database.
using = 'query'
def save_model(self, request, obj, form, change):
# Tell Django to save objects to the 'other' database.
obj.save(using=self.using)
def delete_model(self, request, obj):
# Tell Django to delete objects from the 'other' database
obj.delete(using=self.using)
def get_queryset(self, request):
# Tell Django to look for objects on the 'other' database.
return super().get_queryset(request).using(self.using)
def formfield_for_foreignkey(self, db_field, request, **kwargs):
# Tell Django to populate ForeignKey widgets using a query
# on the 'other' database.
return super().formfield_for_foreignkey(db_field, request, using=self.using, **kwargs)
def formfield_for_manytomany(self, db_field, request, **kwargs):
# Tell Django to populate ManyToMany widgets using a query
# on the 'other' database.
return super().formfield_for_manytomany(db_field, request, using=self.using, **kwargs)Now the admin page can be used: http://sdc.astron.nl:15671/esap-api/admin/
Database Changes
Changing the database structure (migrations)
After changes to the models.py of an app, the database need to be migrated.
This can be done locally in development, but make sure that at that moment you have the latest version of the database available. You will probably have to copy it from the production server, because 'production holds the truth'. See running in Docker in production for information about where to find the latest databases.
Because we use multiple databases, the specific database to migrate must be specified in the migration step with the --database parameter.
This is an example of how that looks after modifying the ida data model.
export OIDC_RP_CLIENT_ID=foo
export OIDC_RP_CLIENT_SECRET=bar
python manage.py makemigrations ida --settings=esap.settings.dev
python manage.py migrate staging --database=ida --settings=esap.settings.devThe 'esap_config.sqlite3' is a special case because it is the original default database that also contains Django administrative information. This is how the migrate command for that database looks:
python manage.py migrate query --database=default --settings=esap.settings.devAfter the migration, the database needs to be copied back to where it came from just before the new version is deployed. (warning: Don't forget to commit the new migration file that appeared the 'migrations' directory)
Because of the static nature of these configuration databases, there may not be any changes in the original content. If you are not sure about that, or if the content has indeed changed, then it is better to re-apply the migration step on (a copy of) the database from production just before or after deployment.
See the Django documentation for more detailed documentation: https://docs.djangoproject.com/en/3.1/topics/migrations/
(the datamodel of the query app).
Changing the contents
The database Because the databases are binary files, there is not mechanism merge databases. So when changes to the content of the databases are made (with the admin app or with an external SQLite3 editor) the databases have to be kept in sync with the databases that are deployed in production.
YAML fixtures
Since August 2021, the (binary) SQLite3 database are no longer stored in the GitLab repo. Instead, Django fixtures are used to store configuration in YAML files. ESAP still uses the binary files, but they are created from the YAML fixtures during the initial installation.
When changing database structure or content, the proper YAML fixtures should also be updated in the repo.
Example: Dump SQLite3 database as YAML fixture
python -Xutf8 manage.py dumpdata query --format=yaml --settings=esap.settings.dev > esap/esap_config.yamlExample: load YAML fixture into SQLite3 database (development environment)
python manage.py migrate --settings=esap.settings.dev
python manage.py loaddata --database=default --settings=esap.settings.dev esap/esap_config.yamlexample: load 'interactive analyses' YAML fixture into SQLite3 database (Docker production environment)
First remove the esap_ida_config.sqlite3 file:
docker exec -it esap_api python manage.py migrate ida --database=ida --settings=esap.settings.docker--no-input
docker exec -it esap_api python manage.py loaddata --database=ida --settings=esap.settings.docker esap/esap_ida_config.yamlUpdating databases in place after new deploy
There are two types of databases:
-
containing static data: only containing static configuration information:
ida,query - containing dynamic data: also containing dynamic user data: accounts (shopping_basket)
There also two operations possible:
- changes in database structure: in migration files
- changes in database content: in YAML fixtures (for static databases only)
Content changes always require the database to be re-created from scratch (after removing it first) with commands like this:
docker exec -it esap_api python manage.py migrate ida --database=ida --settings=esap.settings.docker--no-input
docker exec -it esap_api python manage.py loaddata --database=ida --settings=esap.settings.docker esap/esap_ida_config.yamlThis is an example for a deployed ESAP in a production environment. For a development environment, or a deployment outside Docker, the commands need to be changed accordingly.
Structural changes require a database migration (based on the committed migration files).
If this is done for a static database (ida or query) then a new YAML fixture file should be created with the dumpdata command and then committed.
Example of a dumpdata command to create a YAML fixture for the config database:
python -Xutf8 manage.py dumpdata query --format=yaml --settings=esap.settings.dev > esap/esap_config.yamlTroubleshooting
Q: django.db.utils.IntegrityError: The row in table 'django_admin_log' with primary key '1' has an invalid foreign key
A: the contents of the esap_config.users table and esap_config.django_admin_log table went out of sync, probably because users were manually removed before.
Clear all records from the esap_config.django_admin_log table (for example with the DB Browser for Sqlite tool)