README

Create a new migration

Creates a new migration file using the current timestamp to name the file.

> vendor/bin/dtql mark
ADD COL table.col type;

Migration written to '18284744.dtql`.

Add to an existing migration

> vendor/bin/dtql mark -f 18284744.dtql
ADD COL table.col type;
ADD COL table2.col2 type2;

Migration written to '18284744.dtql'

Update to the latest schema

> vendor/bin/dtql migrate --dsn "sqlite://my_sqlite.db"
Updated to version 18284744.

Migrate to a specific version

> vendor/bin/dtql migrate --dsn "sqlite://my_sqlite.db" 18374591.dtql
Reverted to version 18374591.

Dump the current schema

This is particularly important for creating a schema for unit tests.

> vendor/bin/dtql dump --schema --dsn="sqlite://dt.db" --sql="mysql"

Dump the current data

> vendor/bin/dtql dump --data --dsn="sqlite://dt.db" --sql="mysql"

DTQL Definition

Adding a comment

-- this is a comment

Creating a table

ADD TAB schema.my_table (
 id primary,
 name text,
 created_at timestamp,
 "count" int DEFAULT 1,
 price_usd double
);

Data Types

To remain compatible across all storage formats, we recommend the following data types:

type	description
primary	the primary key, integer type, auto-incremented
timestamp	date/time format (with timezone, where available)
int	integer value
double	decimal value
text	text type
*	literal type--can cause storage compatibility issues

Types can also be followed by a default value by specifying 'DEFAULT val'.

Moving a table

MV TAB schema.my_table > new_schema.new_table;

Drop a table

RM TAB schema.my_table;

Adding a column

You may specify a default value, or a foreign key constraint.

ADD COL table.col type;

Modifying a column

To make operations reversible, you must provide the original type.

MV COL table.col type > table.new-col new-type;

Removing a column

It is required to supply the type, to make the operation reversible.

RM COL table.col type;

Adding an index/foreign key

A strict foreign key (FK!) will cause cascade deletes where supported.

You can create data subclass relationships using 'ISA'. Data subclasses differ semantically from foreign keys, and maybe be used to create is_a manifests in DTModels.

ADD FK wheels.bus_id ~ buses.id;
ADD FK! bees.hive_id ~ hive.id;
ADD ISA employees.contact_id ~ contacts.id;
ADD IDX table.col;

Removing an index/foreign key

RM FK! bees.hive_id ~ hive.id;
RM ISA employees.contact.id ~ contacts.id;
RM IDX table.col;

Syntax Highlighting

You can find the latest syntax highlighting package for Atom at https://github.com/expressive-analytics/atom-language-dtql

expressive-analytics / deep-thought-migration

Maintainers

Details