Kddl is a Swiss army knife for database models. Define your schema once, generate everything else (documentation, SQL scripts, code).

Kddl can be used as a CLI tool, a library, a Gradle plugin, or a Maven plugin. It is multiplatform (jvm, js, linux, macos, windows).

                                    ┌─────────────┐
                                ┌──>│ PostgreSQL  │
                                │   └─────────────┘
                                │   ┌─────────────┐
 ┌─────────────┐                ├──>│  HyperSQL   │
 │  .kddl file │───┐            │   └─────────────┘
 └─────────────┘   │   ┌──────┐ │   ┌─────────────┐
                   ├──>│ kddl │─┼──>│  PlantUML   │
 ┌─────────────┐   │   └──────┘ │   └─────────────┘
 │ JDBC source │───┘            │   ┌─────────────┐
 └─────────────┘                ├──>│    KDDL     │
    (jvm only)                  │   └─────────────┘
                                │   ┌─────────────┐
                                └──>│   Kotlin    │ (with skorm plugin)
                                    └─────────────┘

This tool is fully functional but still maturing. If you try it, be sure to give some feedback!

kddl [OPTIONS] > [output_file]

Options:

  -i, --input        -> mandatory; input file or JDBC URL (with credentials)
  -f, --format       -> mandatory; output format (value should be one of [kddl, plantuml, postgresql])
  -d, --driver       -> jdbc driver, needed when input is a JDBC URL (classname, must be present in the classpath)
  -q, --quoted       -> quoted identifiers
  -u, --uppercase    -> uppercase identifiers
  -n, --no-idempotent -> disable IF NOT EXISTS clauses in SQL output
      --debug        -> print the stack trace on error (default: the error message only)
  -h, --help         -> Usage info

Here's the example.kddl file, which should be enough to understand the syntax by example. It is the model the test suite generates from, and this page quotes it verbatim — a test fails if the two drift.

// Include other KDDL files (paths relative to current file)
// include 'shared/types.kddl'
// include 'common.kddl'
// Definition for database geo
// Supported data types:
//   boolean, tinyint (byte), smallint (short), integer, bigint, serial, bigserial, long,
//   float, double, numeric(n,p), money,
//   time, timetz, date, timestamp, timestamptz, interval, char, char(n), varchar(n), text,
//   enum(value1, value2, ...) or enum('value1', 'value2', ...), uuid, json, blob, clob, varbit
// a database contains options and schemas
database geo {
// a schema contains tables and links
schema infra {
// standalone enum declaration (values can be quoted or unquoted)
enum zone_type(urban, rural, industrial)

// a table contains fields, either given a type or a destination table
table zone {
  type zone_type              // reference to declared enum
  *code varchar(10)      // '*' stands for 'part of pk', otherwise pk is generated as needed
  !name varchar(50)       // '!' stands for unique
  description text?      // '?' stands for nullable field
  active boolean = false // default value
}

table department : zone // inherit a table from another (for engines which support table inheritance like PostgresQL)

table city : zone { hasTrain boolean? }  // declarations can be inlined

table link {
  distance integer
  *src_id -- zone    // mandatory foreign key field, navigable both ways
  *dst_id -- zone
  hub_id --> zone? (down)   // nullable, and only navigable link -> zone
}

city *-- department (up) // plantuml arrow direction can be specified

// UML one-liner chains: declares multiple relations in one statement
// company --* department --* team --* employee
// equivalent to: company --* department ; department --* team ; team --* employee

}
schema client {
table contact {
  // no primary key definition; see below
  // gender, lastname, firstname // field types are optional for plantuml (use a coma to disambiguate)
  gender char?                   // field types are mandatory for postgresql
  firstname varchar(200)
  +lastname varchar(200)         // field is indexed
  !(firstname, lastname)         // composite unique constraint; '+(a, b)' declares a composite index
  // constraint groups accept a partial-index condition (PostgreSQL only):
  //   !(a, b) where c is null    — also 'where c is not null', 'where flag', 'where not flag'
}

table location {
  name varchar(50) = 'untitled'    // string literals use single quotes
  nature enum('depart', 'arrival') as LocationNature // enum with custom type name
  address text?
}

location *-- contact    // will generate the implicit "contact_id serial" primary key in contact
location *-- infra.zone // foreign key referencing a table in another schema

// Chain syntax with nullable markers:
// category *--* product --* review?
// A '?' closes a reference ('--* review') and makes that relation's foreign key nullable.
// It cannot appear on the leading table, which no reference points at.

}
}

To generate the plantuml graph definition script for this model, do:

kddl -i example.kddl -f plantuml > example.pu
plantuml -Tpng example.pu

And here's the result:

You may need to install plantuml, with something like sudo apt install plantuml on linux platforms.

To generate the PostgreSQL creation script for this model, do:

kddl -i example.kddl -f postgresql > example.sql

To do the reverse, aka generate the kddl model file from a running JDBC database, you can do:

kddl -i jdbc://...<jdbc URL with credentials> -f kddl > output.kddl

The model is written as the engine describes it, with a few vendor readings: a PostgreSQL enum or nextval() default becomes an enum(...) or a serial; a MySQL/MariaDB enum gets its labels back, a '0000-00-00' default becomes a nullable column without one, and the 0 or '' an old dump puts on key columns is dropped.

A link says two things: where the foreign key lands, and which traversals the model means to expose.

* marks the many side. A chevron points at the 1 side: it names the only traversal exposed and, when no * says otherwise, it puts the many side at its tail. No chevron means both ways.

A chevron can only ever suppress the collection side. The single reference is the field you declared and costs one key lookup; the collection is the one you may not want generated. So a <*-- b and a --*> b do not exist, and *--* takes no chevron.

The same rules hold for a field link, where the field itself holds the key: author_id -- author exposes both, author_id -> author only book.author, and author_id <- author is rejected.

Traversal intent is read by code generators (skorm). SQL and PlantUML output ignore it.

table city : zone makes city a zone. The root of a hierarchy gets a kind column, an enum of every table name in the hierarchy defaulting to the root's own name:

kind enum_zone_kind NOT NULL DEFAULT 'zone'   -- enum_zone_kind: 'zone', 'department', 'city'

The column is synthesized at parse time like an implicit primary key, so code generators see an ordinary inherited column. It is not written back as kddl, and kind may not be declared on a table of the hierarchy. PostgreSQL stores each child in a base_<child> table and exposes it as an updatable view over the join, whose insert rule sets kind to the child's name.

plugins {
    id("com.republicate.kddl") version "0.30"
}

kddl {
    model.set(file("src/main/kddl/schema.kddl"))
    sql.set(file("$buildDir/generated-resources/main/init.sql"))
}

Then run:

./gradlew generateSQL

<plugin>
    <groupId>com.republicate.kddl</groupId>
    <artifactId>kddl-maven-plugin</artifactId>
    <version>0.30</version>
    <executions>
        <execution>
            <goals>
                <goal>generate-sql</goal>
            </goals>
            <configuration>
                <model>${project.basedir}/src/main/kddl/schema.kddl</model>
                <sql>${project.build.directory}/generated-resources/kddl/init.sql</sql>
                <format>POSTGRESQL</format>
            </configuration>
        </execution>
    </executions>
</plugin>

Available formats: POSTGRESQL, HYPERSQL, PLANTUML, KDDL

Options:

• model (required) — source .kddl file
• sql — output file (default: target/generated-resources/kddl/init.sql)
• format — output format (default: POSTGRESQL)
• quoted — use quoted identifiers (default: false)
• uppercase — use uppercase identifiers (default: false)

You'll need to have gradle installed.

Clone and build:

git clone https://github.com/arkanovicz/kddl.git
cd kddl
./install.sh

To install the kddl command everywhere, assuming that ~/bin is in your path:

ln -s ~/<path_to_kddl_repository>/kddl.sh ~/bin/kddl

Please adapt the installation and run scripts.

./gradlew build

• document library usage
• SQL→KDDL reverse engineering (work parked on feature/parse-ddl branch)
• db versioning handling (generation of update scripts from previous version, aka sql patches from two model versions)
• custom types
• more tests
• multi-level inheritance: a child of a child fails on its missing key, though its foreign key towards the base table is one (a subset of its parent's key)
• align fields (add a space if no field prefix)
• handle enum names collisions (=> error if values are not the same, factorized otherwise)
• option to reset target schema or not
• allow alternate prefix characters to be able to define several orthogonal keys
• backstitched identifiers support

• a field can be implied in one foreign key at most (but it may be a good practice...)
• multivalued foreign keys fields must be named after target primary key fields (quite the same...)
• inline anonymous enum(...) types are named after the field; two inline enums with the same field name across different tables will clash. Use a named enum (enum X(...) then field X) to share a type explicitly
• no table forward reference in the kddl file - it disallows circular references in a model foreign keys chain (there are very specific cases where this is necessary, but if it can be avoided it is most of the time a bad practice)