Changes

Page history
Migrate Databases authored by John Swinbank's avatar John Swinbank
Show whitespace changes
Inline Side-by-side
Configuration/Databases.md 0 → 100644
View page @ c9e524c3
[[_TOC_]]
## 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.
![copy_databases](uploads/00dd3bd1df12ff66df91451898a5a146/copy_databases.jpg)
## Multiple Databases in Django
* https://docs.djangoproject.com/en/3.0/topics/db/multi-db/#multiple-databases
Configure `DATABASE_ROUTERS` and `DATABASES` in `settings.py`
```python
# 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.
```python
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 None
```
```python
class 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 None
```
### Admin interface
* https://docs.djangoproject.com/en/3.0/topics/db/multi-db/#exposing-multiple-databases-in-django-s-admin-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.
```python
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/
![esap_gateway_admin](uploads/0e8a9cc70c97dbe672ba62271c0f3fb4/esap_gateway_admin.jpg)
## 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](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.
```bash
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.dev
```
The '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.dev
```
After 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/
![ESAP-gateway_DataModel.drawio__1_.svg](uploads/9d3100923045f1aa62f1ba384180a6c7/ESAP-gateway_DataModel.drawio__1_.svg)
*(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.yaml
```
#### Example: 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.yaml
```
#### example: 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.yaml
```
## Updating 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.yaml
```
This 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.yaml
```
### Troubleshooting
##### 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](https://sqlitebrowser.org/) tool)