SQL Export

The SQL Generator maps each entity and relationship to a CREATE TABLE statement, including primary- and foreign-key references. The generic SQL generator supports specification of any sort of datatype. When choosing the PostgreSQL, Oracle SQL, MySQL, MS SQL, or Db2 generator, the datatypes are mapped to the corresponding SQL dialect (see Datatypes below). If no datatype is specified for an attribute, the default datatype VARCHAR is used.

Datatypes

Known datatypes are mapped to valid datatypes in the corresponding SQL dialect (see Example below)

Type	PostgreSQL	Oracle SQL	MySQL	MS SQL	Db2
Integer	BIGINT, INT, INT2, INT4, INT8, INTEGER, SMALLINT	INTEGER	BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT	BIGINT, INT, SMALLINT, TINYINT	BIGINT, INTEGER, SMALLINT
Float Types	FLOAT, REAL	BINARY_DOUBLE, BINARY_FLOAT	DOUBLE, FLOAT	FLOAT, REAL	DOUBLE, FLOAT, REAL
Decimal	DECIMAL, NUMERIC	NUMBER, DECIMAL, NUMERIC	NUMBER, DECIMAL	DECIMAL, NUMERIC	DECIMAL, NUMERIC
Varchar	VARCHAR	VARCHAR2, VARCHAR	VARCHAR	VARCHAR, NVARCHAR	VARCHAR
Char	CHAR, CHARACTER	CHAR	CHAR	CHAR, NCHAR	CHAR
Date	DATE	DATE	DATE	DATE	DATE
Date Time	TIMESTAMP, TIME	TIMESTAMP	TIMESTAMP, TIME	DATETIME2, DATETIME, SMALLDATETIME, DATETIMEOFFSET, TIME	TIMESTAMP, TIME
Blob	BYTEA, IO	BLOB	BLOB	VARBINARY, IMAGE	BLOB
Clob	TEXT	CLOB	CLOB	TEXT, NTEXT	CLOB
Boolean	BOOLEAN		BOOLEAN	BIT

Generate DROP Table Statements

The VS Code extension provides a setting to generate DROP TABLE statements when generating SQL code. The setting is set to false by default and can be changed in the user or the workspace scope (note that workspace takes priority over user).

Examples

ER Model

erdiagram Model

entity E1 {
    id: INTEGER key
    name: CHARACTER
}

PostgreSQL

CREATE TABLE E1 (
	id INTEGER,
	name CHARACTER,
	PRIMARY KEY (id)
);

Oracle SQL

CREATE TABLE E1 (
	id INTEGER,
	name CHAR,	-- type mapped from: CHARACTER
	PRIMARY KEY (id)
);

MySQL

CREATE TABLE E1 (
	id BIGINT,	-- type mapped from: INTEGER
	name CHAR,	-- type mapped from: CHARACTER
	PRIMARY KEY (id)
);

MS SQL

CREATE TABLE E1 (
	id BIGINT,	-- type mapped from: INTEGER
	name CHAR,	-- type mapped from: CHARACTER
	PRIMARY KEY (id)
);

Db2

CREATE TABLE E1 (
	id INTEGER NOT NULL,	-- added NULL constraint
	name CHAR,	-- type mapped from: CHARACTER
	PRIMARY KEY (id)
);

Last update: v0.3.0