jinx Database Tools
jDbT is a small utility designed to quickly generate SQL schemas and associated information from a lightweight spec.
status: - Test - Prod team: name: text address: line1: text line2: text zip: text city: text country: text post: +?title: text ?content: text member: firstname: text lastname: text nickname: text ?team_id: address_id: status: status | 'Test' __unique: [ firstname, lastname ] tag: +name: text __check: name <> 'prolapse' post_tag: post_id: tag_id: __pk: [ tag_id, post_id ]
jdbt ./schema.yml > schema.sql jdbt ./schema.yml dot | dot -Tpng -o schema.png # or curl -F "schema=<schema.yml" http://jdbt.cleverapps.io/
create type status as enum('Test', 'Prod'); create table team ( team_id uuid primary key, name text not null ); create table address ( address_id uuid primary key, line1 text not null, line2 text not null, zip text not null, city text not null, country text not null ); create table post ( post_id uuid primary key, title text unique, content text ); create table member ( member_id uuid primary key, firstname text not null, lastname text not null, nickname text not null, team_id uuid references team(team_id), address_id uuid not null references address(address_id), status status default 'Test'::status not null, unique (firstname, lastname) ); create table tag ( tag_id uuid primary key, name text not null unique, constraint cst_0 check (name <> 'prolapse') ); create table post_tag ( post_id uuid not null references post(post_id), tag_id uuid not null references tag(tag_id), primary key (tag_id, post_id) );
stack setup stack build stack install
The input is a
yml file describing the tables.
The following conventions are assumed:
- every field has a
not nullconstraint by default, unless it has a
?at the beginning of its name
+at the beginning of a field name means it has a
- all tables have a primary key named
<table_name>_idof type uuid
- every column named
<something>_idis assumed to be a foreign key
ToDo: allow the overriding of these conventions:
- allow to define these properties explicitely
- don't add the primary key if there is already one defined
Right now only PostgreSQL is supported. Are there any other RDBMSs out there anyway?
jDbT can output a file in the
dot format, which you can compile with
Play Evolution scripts
Scala case classes
SQL dummy data
jDbT was originally a python script put together by @clementd, @ahoy_jon and @benjaminvialle for contract work. @clementd rewrote it from scratch in Haskell after a few years.