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
area | case | plural |
---|---|---|
fields | snake_case | no plurals |
tablename | camelCase | plurals 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;