The database is the core of the foodsharing system. It stores all [important information]database-tables-columns.md). The following chapters help you to understand the development tools and required steps to modify and publish changes on the database.
Phinx is a migration tool for databases and helps to update and to maintain the database schema. Each change in database schema is described by a migration file. On execution of this changeset can be rolled out to the database and in case of problems is a roll-back possible. A roll-back needs to be implemented and can not be done on each change, e. g. removing a column.
Steps to generate modification changeset
Create a new migration changeset
$> ./scripts/docker-compose exec app vendor/bin/phinx create <YourMigrationChangeset>
$> sudo chmod 777 migrations/<timestamp>_<YourMigrationChangeset>.php
<YourMigrationChangeset>is short summary in filename
<timestamp>automatic generated timestamp
The following example shows a possible structure of a migration file.
final class AddForumThreadStatus extends AbstractMigration
public function change(): void
->addColumn('status', 'integer', [
'null' => false,
'default' => '0',
'limit' => 10,
'signed' => false,
'comment' => '@Region:ThreadStatus status of the thread (open or closed)',
Test migration script by a dry-run
$> ./scripts/docker-compose exec app vendor/bin/phinx migrate --dry-run
execute the exchange on the local database
$> ./scripts/docker-compose exec app vendor/bin/phinx migrate
Merge request special cases
- Mark MR with label
for MR: sql migration | beta
for MR: sql migration | prod
- Mark MR with label
Date documentation script extracts out of the running database instance all information for developer documentation. This should make it easier to understand the existing information, and helps to develop new queries or to reuse already existing queries.
The script can be executed as developer by the following command:
ERROR: No container found for docsis shown then bring the dev environment up via
This will update the
The script supports different information extraction additional to the SQL
CREATE TABLE ... provided information.
PHP Type links
Column descriptions/comments which use following tags
@<PHPModule>:<Classfilename>are linked to the PHP DBConstants. This simplifies the description of PHP Enums/constants which are stored as integers.
Additional meta-information in
The script uses
docs/database_metadata.jsonto get information about the table like column links, todos, or descriptions. This is a helper describe old tables and fors todos are migrated to GitLab issues. The table comment and column comment need to be set on creation of the table. Take care that later changing will change may the column definition.
Find PHP Modules which use tables
The script generates out of the PHP code base a table usage report. This helps to see the usage of database tables in module provided gateway.
Guidelines and rules
Each database modification should be implemented as phinx migration to
This allows us to track changes and run migration step by step and simplifies database changes in GIT merge requests
Add a description for each new table, so that other developers understand the meaning of the stored information and the usage for the users.
Add a description for each new column, so that other developers understand the meaning of the stored information.
Use PHP Type links in column description
The PHP code use column types like int or strings, which have a restricted set of values like enums. This should be described in DBconstant, so that other developers understand the meaning of the values.