OutputsGenerating SQL

Generating SQL

resin writes a schema as SQL DDL for MySQL, PostgreSQL, SQLite, SQL Server or Oracle. It is Importing SQL run backwards: every fact goes where the import reads it from, so importing what resin writes gives the same document back, apart from the few things SQL cannot hold.

npx resin shop.erd --to-sql postgres > schema.sql

shop.erdOpen in playground

service accounts "Accounts service" {
  table users "People who sign in" {
    id     bigint        pk
    email  varchar(255)  uk
  }
}

service ordering "Order service" {
  table orders {
    id       bigint   pk
    user_id  bigint   ~> users  index
    status   varchar  enum(PENDING, PAID)
  }

  table order_items {
    id        bigint  pk
    order_id  bigint  -> orders  index as ix_order_items_order
    quantity  int
  }
}

Two services: the accounts service owns users, the order service owns orders and their items

--to-sql postgres prints:

-- Generated by resin for PostgreSQL

CREATE SCHEMA accounts;
CREATE SCHEMA ordering;

CREATE TABLE accounts.users (
  id     bigint        NOT NULL,
  email  varchar(255)  NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (email)
);
COMMENT ON TABLE accounts.users IS 'People who sign in';

CREATE TABLE ordering.orders (
  id       bigint   NOT NULL,
  user_id  bigint   NOT NULL,  -- ~> accounts.users.id
  status   varchar  NOT NULL CHECK (status IN ('PENDING', 'PAID')),
  PRIMARY KEY (id)
);
CREATE INDEX ON ordering.orders (user_id);

CREATE TABLE ordering.order_items (
  id        bigint  NOT NULL,
  order_id  bigint  NOT NULL,
  quantity  int     NOT NULL,
  PRIMARY KEY (id)
);
CREATE INDEX ix_order_items_order ON ordering.order_items (order_id);

ALTER TABLE ordering.order_items ADD FOREIGN KEY (order_id) REFERENCES ordering.orders (id);

What becomes what

resin	SQL
A table	`CREATE TABLE`, in document order. External tables are not created: they live elsewhere
`audit envers`	`revinfo` and `<table>_aud`, after the other tables
A column	Its name and type as written, and `NOT NULL` unless the type has `?`
`pk`	`PRIMARY KEY (...)`
`uk`, `unique(...)`	`UNIQUE (...)`, or `CONSTRAINT name UNIQUE (...)`
`index`, `index(...)`	`CREATE INDEX` after the table; on MySQL an `INDEX` inside it
`enum(...)`	`CHECK (column IN (...))`
`->`, `foreign(...) ->`	`FOREIGN KEY ... REFERENCES`, added with `ALTER TABLE` after every table, so tables can refer to one another in any order. SQLite cannot add one later, so there it sits inside `CREATE TABLE`
A description	`COMMENT` on MySQL, `COMMENT ON` on PostgreSQL and Oracle, `MS_Description` on SQL Server, an SQL comment on SQLite
`~>`, `enc`	An SQL comment after the column: `-- ~> users.id`, `-- encrypted`

Types are written as they are: resin does not translate types between databases, so write the types of the database you generate for. Two cases keep enum types working: MySQL's type enum becomes enum('A', 'B'), and on PostgreSQL a column with values whose type is not a built-in one gets CREATE TYPE ... AS ENUM. Names are quoted only when they need it: a name that is not a plain identifier, a reserved word such as order, or, on PostgreSQL, a name with capitals.

Services

Without --service, the whole document goes into one database, with a schema for each service (CREATE SCHEMA, ordering.orders), so foreign keys between services can be created. This is also how the import reads services back. On SQL Server, tables outside every service go in dbo.

With --service <name>, only that service's tables are written, without a schema: the DDL of the service's own database. A foreign key into another service cannot exist there, so it becomes a comment and a note:

npx resin shop.erd --to-sql mysql --service ordering

-- Generated by resin for MySQL, service ordering

CREATE TABLE orders (
  id       bigint   NOT NULL,
  user_id  bigint   NOT NULL,  -- ~> accounts.users.id
  status   varchar  NOT NULL CHECK (status IN ('PENDING', 'PAID')),
  PRIMARY KEY (id),
  INDEX (user_id)
);

CREATE TABLE order_items (
  id        bigint  NOT NULL,
  order_id  bigint  NOT NULL,
  quantity  int     NOT NULL,
  PRIMARY KEY (id),
  INDEX ix_order_items_order (order_id)
);

ALTER TABLE order_items ADD FOREIGN KEY (order_id) REFERENCES orders (id);

Notes

The SQL goes to standard output and the notes to standard error: everything resin wrote in a way you might not expect. For the document above, --to-sql oracle notes:

shop.erd: note: services are written as schemas, which Oracle makes as users: CREATE SCHEMA is left out, so create the users accounts and ordering first
shop.erd: note: the index on ordering.orders (user_id) has no name, which Oracle needs: named ix_orders_user_id

What does not come back

Importing the SQL gives the same document, except for what SQL has no place for:

Logical references (~>) and enc are SQL comments, which the import does not read. Importing with --infer-refs finds most logical references again, and an external table that only logical references point at does not come back.
On SQLite, descriptions are SQL comments, and services do not come back, as SQLite has no schemas.
An index whose name resin made up comes back with that name.
A document with one service and tables outside it comes back without the service.

In the playground

Export, then SQL DDL, shows the SQL in the side panel, with the database, the service, Copy, Download and the notes. See Playground.

In code

toSql(model, { dialect, service }) returns { sql, notes }; SQL_DIALECTS lists the dialects. See Library.

Edit this page on GitHubGrammar v0.2

Generating SQL

What becomes what#

Services#

Notes#

What does not come back#