The CDS API and CDN service should be authenticated on the database with a RW Role on all tables. A role with CREATE role is needed only to run migration scripts. Here is an example of roles creation script
CREATE ROLE "cds-adm";
ALTER ROLE "cds-adm" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;
CREATE ROLE "cds-rw";
ALTER ROLE "cds-rw" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;
GRANT "cds-rw" TO "cds-adm" GRANTED BY postgres;
GRANT CREATE ON DATABASE cds TO "cds-adm";
GRANT CREATE ON SCHEMA public TO "cds-adm";
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public REVOKE ALL ON SEQUENCES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public REVOKE ALL ON SEQUENCES FROM "cds-adm";
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public GRANT USAGE,UPDATE ON SEQUENCES TO "cds-rw";
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public REVOKE ALL ON TABLES FROM "cds-adm";
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public GRANT TRIGGER,TRUNCATE ON TABLES TO "cds-adm";
ALTER DEFAULT PRIVILEGES FOR ROLE "cds-adm" IN SCHEMA public GRANT SELECT,INSERT,REFERENCES,DELETE,UPDATE ON TABLES TO "cds-rw";This folder contains Migration scripts. Each migration scripts contains Upgrade and Downgrade statements.
It is possible to upgrade and downgrade your database schema. It can also show to the migration status.
Commands below ask you to run:
$ <PATH_TO_CDS>/engine database -hengine binary can be generated by running:
$ cd <PATH_TO_CDS>/engine
$ go buildSubCommand database:
$ <PATH_TO_CDS>/engine database -h
Manage CDS database
Usage:
api database [command]
Available Commands:
upgrade Upgrade schema
downgrade Downgrade schema
status Show current migration status
Global Flags:
--db-host string DB Host (default "localhost")
--db-maxconn int DB Max connection (default 20)
--db-name string DB Name (default "cds")
--db-password string DB Password
--db-port string DB Port (default "5432")
--db-sslmode string DB SSL Mode: require (default), verify-full, or disable (default "require")
--db-timeout int Statement timeout value (default 3000)
--db-user string DB User (default "cds")
Use "api database [command] --help" for more information about a command.
This will never-applied migration scripts (ie. run the Up parts) and mark them as applied. You can user dry-run option to see which scripts would be executed.
$ <PATH_TO_CDS>/engine database upgrade -h
Migrates the database to the most recent version available.
Usage:
api database upgrade [flags]
Flags:
--dry-run Dry run upgrade
--limit int Max number of migrations to apply (0 = unlimited)
--migrate-dir string CDS SQL Migration directory (default "./engine/sql/api")
Global Flags:
--db-host string DB Host (default "localhost")
--db-maxconn int DB Max connection (default 20)
--db-name string DB Name (default "cds")
--db-password string DB Password
--db-port string DB Port (default "5432")
--db-sslmode string DB SSL Mode: require (default), verify-full, or disable (default "require")
--db-timeout int Statement timeout value (default 3000)
--db-user string DB User (default "cds")This will undo migration scripts (ie. run the Down parts) and mark them never applied. You can user dry-run option to see which scripts would be executed.
$ <PATH_TO_CDS>/engine database downgrade -h
Migrates the database to the most recent version available.
Usage:
api database upgrade [flags]
Flags:
--dry-run Dry run upgrade
--limit int Max number of migrations to apply (0 = unlimited)
--migrate-dir string CDS SQL Migration directory (default "./engine/sql/api")
Global Flags:
--db-host string DB Host (default "localhost")
--db-maxconn int DB Max connection (default 20)
--db-name string DB Name (default "cds")
--db-password string DB Password
--db-port string DB Port (default "5432")
--db-sslmode string DB SSL Mode: require (default), verify-full, or disable (default "require")
--db-timeout int Statement timeout value (default 3000)
--db-user string DB User (default "cds")Show migration status.
$ <PATH_TO_CDS>/engine database status --db-host <host> --db-password <password> --db-name <database> --migrate-dir ./engine/sql/api
| MIGRATION | APPLIED |
|------------------------------|---------------------------------------|
| 000_create_all.sql | 2016-10-26 16:01:08.575758 +0200 CEST |
Rules:
- Never delete any scripts
- Always increment migration scripts prefix number
- Create scripts must be updated whenever Migration scripts are created or updated
- Never forget
Downparts in migration scripts
Migrations are defined in SQL files, which contain a set of SQL statements. Special comments are used to distinguish up and down migrations.
-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);
-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;You can put multiple statements in each block, as long as you end them with a semicolon (;).
If you have complex statements which contain semicolons, use StatementBegin and StatementEnd to indicate boundaries:
-- +migrate Up
CREATE TABLE people (id int);
-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
create_query text;
BEGIN
-- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd
-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;