Versatile tool for managing database models, converting them to formats like PostgreSQL, kddl, and PlantUML. Offers reverse engineering, supports various identifiers, and handles schemas and tables.
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,
h,
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.
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
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.29"
}
kddl {
model.set(file("src/main/kddl/schema.kddl"))
sql.set(file("$buildDir/generated-resources/main/init.sql"))
}
Then run:
./gradlew generateSQL
Available formats: POSTGRESQL, HYPERSQL, PLANTUML, KDDL
Options:
model (required) — source .kddl filesql — output file (default: )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
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// 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.
}
}
| link | foreign key | exposed traversals |
|---|
a *-- b | on a | a.b and b.as |
a --* b | on b | b.a and a.bs |
a *--> b | on a | a.b |
a <--* b | on b | b.a |
a --> b | on a | a.b |
a <-- b | on b | b.a |
a *--* b | join table | both, always |
a -- b | rejected | nothing says which side holds the key |
<plugin>
<groupId>com.republicate.kddl</groupId>
<artifactId>kddl-maven-plugin</artifactId>
<version>0.29</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>
target/generated-resources/kddl/init.sqlformat — output format (default: POSTGRESQL)quoted — use quoted identifiers (default: false)uppercase — use uppercase identifiers (default: false)feature/parse-ddlSurfaced from shared tags and platforms — no rankings paid for.