CLI tool to help with postgresql database schema updates, migrations and versioning.
The goal of wo is to provide project structure declarative as much as possible: - all database objects have their corresponding create script. - migration scripts should only be needed to update data-full objects - tables.
bash
cargo install wo
bash
git clone https://github.com/yuulive/wo
cargo install --path ./wo
env-local-db-0.sh (as an example) file like this:```bash
export WOCONNECTIONSTRING="..."
export WOADMINCONNECTION_STRING="..."
export WO_DIR="./wo"
export WOROLEPREFIX="prod_"
export WOROOTCERT="" ```
Environment variables need to be activated when using wo. You can do this by running:
bash
source env-local-db-0.sh
To initialize wo project run:
bash
wo init
This will create directory for storing all wo project data:
./wo/
├── create
│ ├── 00-create-role.sql
│ └── 01-create-database.sql
├── drop
│ ├── 00-drop-database.sql
│ └── 01-drop-role.sql
├── functions
├── migrations
├── roles
├── tables
├── schemas
│ └── public.sql
├── constraints
├── triggers
├── policies
├── extensions
├── types
└── views
Modify newly created ./wo/create/*.sql and ./wo/drop/*.sql scripts if needed.
./wo/create/* scripts if needed.bash
wo migrate
Two extra tables will be created:
wo_objects: contains a list of managed wo objects and their hashes.
sql
create table if not exists wo_objects (
po_id text primary key,
po_md5 text,
po_script text,
po_path text,
po_depends_on text[],
po_required_by text[]
);
wo_migrations: contains a list of executed migrations. Selecting the max value should reveal the current state of database. The first migration will be inserted as empty string.
sql
create table if not exists wo_migrations (
pm_id text primary key
);
./wo/**/*.sql../wo/migrations/* scripts.Run
bash
wo migrate
Test your fresh db maybe.
The table summarizes the way you should deliver changes (auto means it is enough to modify corresponding object script):
Object type | Create | Drop | Alter ----------- | ------ | --------- | ---- Table | auto | migration | migration View | auto | auto | auto Function | auto | auto | auto Constraint | auto | auto | auto Trigger | auto | auto | auto Policy | auto | auto | auto Schema | auto | auto | migration Role | auto | auto | auto Extension | auto | auto | migration Type | auto | auto | migration Function | auto | auto | auto
During the update there is short time period when the policies are dropped (if needed). This might be a security issue. This should be fixed once updates are applied in single transaction (needs more investigation).
Table changes can not be applied just by dropping the table and creating a new one without loosing the data. Therefore these changes must be delivered using migration scripts. Table scripts in the wo project must represent the latest version of the object after applying all migration scripts.
Scripts are located at ./wo/migrations/. These scripts are executed in alphabetical order before
updating all the other database objects.
If your migration depends on other database objects (a new table column associated with a function maybe) it is recommended to create those objects (if not exists) in the migration sctipt. This is to avoid problems with old versions of databases were mentioned objects don't yet exist. In the future schema verification process will be developed to show which migration scripts are broken.
bash
wo migrate
Database objects are: - tables - views - triggers - constraints - policies - functions - roles - schemas - extensions - types
Filenames for database objects must be of specific format :
- tables: ./wo/tables/<schema>.<name>.sql
- views: ./wo/views/<schema>.<name>.sql
- functions: : ./wo/functions/<schema>.<name>.sql
- triggers: ./wo/triggers/<schema>.<table>.<name>.sql
- constraints: ./wo/constraints/<schema>.<table>.<name>.sql
- policies: ./wo/policies/<schema>.<table>.<name>.sql
- roles: ./wo/roles/<name>.sql
- schemas: ./wo/schemas/<name>.sql
- extensions: ./wo/extensions/<name>.sql
- types: ./wo/types/<schema>.<name>.sql
Each file contains script to create that object.
Updates are done by dropping the object and creating a new one.
Drop scripts are generated by object type and object name. Tables will never be dropped automatically - they have to be dropper/updated using migration scripts or manually.
Example ./wo/tables/public.table0.sql:
```sql
create table table0 (
id bigserial primary key
);
-- create indexes -- create constraints -- create rules -- create triggers
```
Table constraints and indeces can be stored along with tables. But to modify them you will have to write migration scripts.
If you have circular foreign key dependencies you should define those constraints in a separate ./wo/constraints/ files to break the cycle.
Example ./wo/views/public.view0.sql:
```sql
-- it is recommended to include "or replace", otherwise it will be dropped and created again each time changes are made.
create or replace view view0 as
select t0.id
from table0 t0
join table1 t1 on t1.id = t0.id
-- create indexes maybe ```
During update all overidden functions will be dropped (if modified) and created again.
Some default permissions might be applied on a function when dropping and creating it.
In the function script you might want to add extra statement to alter default privileges:
sql
revoke execute on function some_function from public;
Example ./wo/constraints/public.table1.t0_id_fk.sql:
sql
alter table table1
add constraint t0_id_fk foreign key (t0_id) references table1 (id);
Example ./wo/policies/public.table1.policy1.sql:
sql
create policy policy1
on public.table1;
Policy script should not target specific role. Role assignments should be done in role scripts by altering given policy.
Example ./wo/roles/role0.sql:
sql
create role {wo_role_prefix}role0;
grant usage on schema schema0 to {wo_role_prefix}role0;
All permissions assignments should be done in role scripts.
Role objects will always be dropped and newly created when executing wo migrate.
This is to avoid default permissions assignment when other objects are recreated.
wo initWO_DIR.wo migrate./wo/create/ scripts to create role and database (using admin connection).wo_objects table and project contents.wo drop --no-joke./wo/drop/ scripts to drop role and database (using admin connection).public schema../wo/migrations/ is assumed to be increasing in alphabetical order.{wo_role_prefix} text should not be used for other porpuses as for database-role prefix in your scripts.At the current stage wo is not the best thing in the world. You might also want to check these alternatives: - refinery - flyway - diesel.rs - dbmigrate - and more...
Migration steps
- make your database up to date. (by running wo migrate)
- update wo
- drop tables wo_objects and wo_migrations
- run wo migrate
WO_ALLOW_DROP variable to protect production envs./example/./wo/initial/ execute after the database is created ./wo/final/ execute after the database objects are created