ddsl
Data-Definition-Specific Language (DDSL, pronounced "diesel") provides a scripting language for DDL and migrations.
Why a new language
A relational database system (RDS) is not a source code repository. DDL needs to be stored and versioned separately
in order to manage it like any other code artifact. There are few tools to manage and apply DDL thus stored
during release and upgrade activities.
DDSL helps you store your database DDL and migrations in one revision control repository. It is opinionated
about the structure of the repository. That allows a set of simple commands to apply DDL objects and migrations
agnostic of the database structure or RDS in use.
Install
go get github.com/neighborly/ddsl
Usage
ddsl -s <source_repo> -d <database_rds_url> -c COMMAND
ddsl -s <source_repo> -d <database_rds_url> -f /pat/to/file.ddsl
ddsl --version
The usage can be shortend by setting environment variables.
DDSL_SOURCE
- Source code repo URL for the database DDL and migrations
DDSL_DATABASE
- Database URL in format expected by RDS, properly URL encoded
Command Syntax
All commands exception MIGRATE
and SQL
accept a final token of @git_tag
which will run the command against that version of the DDL reposititory.
Commands may be separated by a semicolon.
Databases
Databases cannot be created within a transaction on certain RDSs such as Postgres. When creating a database from scratch,
the recommended order of operations is:
CREATE ROLES
CREATE DATABASE
CREATE EXTENSIONS; CREATE SCHEMAS
CREATE
CREATE DATABASE
CREATE ROLES
CREATE EXTENSIONS
CREATE SCHEMAS
CREATE FOREIGN KEYS
CREATE SCHEMA foo
CREATE TABLES IN foo
CREATE VIEWS IN foo
CREATE TABLE foo.bar
CREATE VIEW foo.cat
CREATE INDEXES ON foo.bar @v1.1
CREATE CONSTRAINTS ON foo.cat @v1.2
DROP
DROP DATABASE
DROP ROLES
DROP EXTENSIONS
DROP SCHEMAS
DROP FOREIGN KEYS
DROP SCHEMA foo
DROP TABLES IN foo
DROP VIEWS IN foo
DROP TABLE foo.bar
DROP VIEW foo.cat
DROP INDEXES ON foo.bar @v1.1
DROP CONSTRAINTS ON foo.cat @v1.2
SQL
SQL `
UPDATE foo.bar SET field1 = 4 WHERE field2 = 0;
DELETE FROM foo.bar WHERE field1 <> 4;
`
MIGRATE
MIGRATE TOP
MIGRATE BOTTOM
MIGRATE UP 2
MIGRATE DOWN 2
Database Repo Structure
DDSL is opinionated about the structure of the database source repository.
The following structure is required.
π <any_parent_path>
π <database_name>
π database.create.<ext>
π database.drop.<ext>
π extensions.create.<ext>
π extensions.drop.<ext>
π foreign_keys.create.<ext>
π foreign_keys.drop.<ext>
π roles.create.<ext>
π roles.drop.<ext>
π schemas
π <schema_name>
π schema.create.<ext>
π schema.drop.<ext>
π constraints
π <table_or_view_name>.create.<ext>
π <table_or_view_name>.drop.<ext>
π indexes
π <table_or_view_name>.create.<ext>
π <table_or_view_name>.drop.<ext>
π tables
π <table_name>.create.<ext>
π <table_name>.drop.<ext>
π views
π <view_name>.create.<ext>
π <view_name>.drop.<ext>
π migrations
π <version>_<title>.up.ddsl
π <version>_<title>.down.ddsl
Migrations are written in DDSL because often migrations simply need to create a specific table
or index, or drop something. The DDL for that already exists in some version of the database
code repository, so it is DRY to be able to access that code from within the migrations. You
can also run SQL commands in the migrations using the DDSL SQL
command.