Skip to main content

Overview

TODO

General

Database table structures

New tables can be added with db-migrate tool e.g.

cd tools/db-migrate/
npm i
npx db-migrate create

This creates a new file in the migrations/ folder. Go ahead and replace that new file with an existing migration script and modify for your needs.

When your ready run npm run up to connect and execute against your local docker mysql instance.

Migrations

See migrations for more info on how to write migrations.

Naming conventions

areacaseplural
fieldssnake_caseno plurals
tablenamecamelCaseplurals permitted on pivot tables, but data tables should ideally not end in s

Basic table structure

When adding a new table please include

CREATE TABLE `myTable` (

-- ID is almost always advisable
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

-- Unless this is for all environments we almost always includes the domain_id
`domain_id` int(11) NOT NULL,

-- ... feature specific

-- include this unless immutable, e.g. messages
`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-- Always include created_time.
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),

-- constraint naming structure
-- (fk|unique|index)_[tablename]_field1[_field2..]

UNIQUE `unique_myTable_domain_id_user_id` ('domain_id', 'user_id')
KEY `fk_myTable_domain_id` (`domain_id`),
CONSTRAINT `fk_myTable_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;