Examples
Examples
Real input, real output. Every diagram on this page was drawn by SchemaDrawer from the files shown next to it.
Example 1
PetClinic in three Flyway migrations
Seven tables: owners have pets of a type, vets have specialties through a join table, pets have visits. Drawn on PostgreSQL 18.
CREATE TABLE owners
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
CONSTRAINT pk_owners PRIMARY KEY (id)
);
CREATE TABLE types
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_types PRIMARY KEY (id)
);
CREATE TABLE pets
(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
birth_date DATE,
type_id INT NOT NULL,
owner_id INT NOT NULL,
CONSTRAINT pk_pets PRIMARY KEY (id),
CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);
CREATE TABLE vets
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_vets PRIMARY KEY (id)
);
CREATE TABLE specialties
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_specialties PRIMARY KEY (id)
);
CREATE TABLE vet_specialties
(
vet_id INT NOT NULL,
specialty_id INT NOT NULL,
CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);
CREATE TABLE visits
(
id INT NOT NULL,
pet_id INT NOT NULL,
visit_date DATE,
description VARCHAR(255),
CONSTRAINT pk_visits PRIMARY KEY (id),
CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
);
Primary keys in bold with PK; foreign keys marked FK, each drawn as an arrow from the referenced table to the referencing one and labelled with the column. vet_specialties has a composite primary key made of two foreign keys. Open the SVG.
Example 2
The same schema in ten other tools
SchemaDrawer's test suite keeps the PetClinic schema written for every supported tool. Whatever the input language, the diagram is the one above.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet id="001-owners" author="schemadrawer">
<createTable tableName="owners">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_owners" nullable="false"/>
</column>
<column name="first_name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="last_name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="address" type="VARCHAR(255)"/>
<column name="city" type="VARCHAR(80)"/>
<column name="telephone" type="VARCHAR(20)"/>
</createTable>
</changeSet>
<changeSet id="001-types" author="schemadrawer">
<createTable tableName="types">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_types" nullable="false"/>
</column>
<column name="name" type="VARCHAR(80)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="001-pets" author="schemadrawer">
<createTable tableName="pets">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_pets" nullable="false"/>
</column>
<column name="name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="birth_date" type="DATE"/>
<column name="type_id" type="INT">
<constraints nullable="false"
foreignKeyName="fk_pets_types"
references="types(id)"/>
</column>
<column name="owner_id" type="INT">
<constraints nullable="false"
foreignKeyName="fk_pets_owners"
references="owners(id)"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet id="002-vets" author="schemadrawer">
<createTable tableName="vets">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_vets" nullable="false"/>
</column>
<column name="first_name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
<column name="last_name" type="VARCHAR(50)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="002-specialties" author="schemadrawer">
<createTable tableName="specialties">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_specialties" nullable="false"/>
</column>
<column name="name" type="VARCHAR(80)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet id="002-vet-specialties" author="schemadrawer">
<createTable tableName="vet_specialties">
<!--
The composite key is declared here rather than added afterwards: SQLite cannot add a
primary key to a table that already exists, and Liquibase refuses addPrimaryKey
there outright. Naming both columns inside createTable makes one composite key, and
every engine takes it.
-->
<column name="vet_id" type="INT">
<constraints nullable="false"
primaryKey="true"
primaryKeyName="pk_vet_specialties"
foreignKeyName="fk_vet_specialties_vets"
references="vets(id)"/>
</column>
<column name="specialty_id" type="INT">
<constraints nullable="false"
primaryKey="true"
primaryKeyName="pk_vet_specialties"
foreignKeyName="fk_vet_specialties_specialties"
references="specialties(id)"/>
</column>
</createTable>
</changeSet>
</databaseChangeLog>
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet id="003-visits" author="schemadrawer">
<createTable tableName="visits">
<column name="id" type="INT">
<constraints primaryKey="true" primaryKeyName="pk_visits" nullable="false"/>
</column>
<column name="pet_id" type="INT">
<constraints nullable="false"
foreignKeyName="fk_visits_pets"
references="pets(id)"/>
</column>
<column name="visit_date" type="DATE"/>
<column name="description" type="VARCHAR(255)"/>
</createTable>
</changeSet>
</databaseChangeLog>
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<include file="changes/001-owners-and-pets.xml" relativeToChangelogFile="true"/>
<include file="changes/002-vets.xml" relativeToChangelogFile="true"/>
<include file="changes/003-visits.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
CREATE TABLE owners
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
CONSTRAINT pk_owners PRIMARY KEY (id)
);
CREATE TABLE types
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_types PRIMARY KEY (id)
);
CREATE TABLE pets
(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
birth_date DATE,
type_id INT NOT NULL,
owner_id INT NOT NULL,
CONSTRAINT pk_pets PRIMARY KEY (id),
CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);
CREATE TABLE vets
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_vets PRIMARY KEY (id)
);
CREATE TABLE specialties
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_specialties PRIMARY KEY (id)
);
CREATE TABLE vet_specialties
(
vet_id INT NOT NULL,
specialty_id INT NOT NULL,
CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);
CREATE TABLE visits
(
id INT NOT NULL,
pet_id INT NOT NULL,
visit_date DATE,
description VARCHAR(255),
CONSTRAINT pk_visits PRIMARY KEY (id),
CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
);
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = []
operations = [
migrations.CreateModel(
name="Owner",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
("first_name", models.CharField(max_length=50)),
("last_name", models.CharField(max_length=50)),
("address", models.CharField(max_length=255, null=True)),
("city", models.CharField(max_length=80, null=True)),
("telephone", models.CharField(max_length=20, null=True)),
],
options={
"db_table": "owners",
},
),
migrations.CreateModel(
name="Type",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
("name", models.CharField(max_length=80)),
],
options={
"db_table": "types",
},
),
migrations.CreateModel(
name="Pet",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
("name", models.CharField(max_length=50)),
("birth_date", models.DateField(null=True)),
(
"type",
models.ForeignKey(
db_column="type_id",
on_delete=django.db.models.deletion.DO_NOTHING,
to="petclinic.type",
),
),
(
"owner",
models.ForeignKey(
db_column="owner_id",
on_delete=django.db.models.deletion.DO_NOTHING,
to="petclinic.owner",
),
),
],
options={
"db_table": "pets",
},
),
]
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
dependencies = [
("petclinic", "0001_owners_types_pets"),
]
operations = [
migrations.CreateModel(
name="Vet",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
("first_name", models.CharField(max_length=50)),
("last_name", models.CharField(max_length=50)),
],
options={
"db_table": "vets",
},
),
migrations.CreateModel(
name="Specialty",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
("name", models.CharField(max_length=80)),
],
options={
"db_table": "specialties",
},
),
migrations.CreateModel(
name="VetSpecialty",
fields=[
("pk", models.CompositePrimaryKey("vet", "specialty")),
(
"vet",
models.ForeignKey(
db_column="vet_id",
on_delete=django.db.models.deletion.DO_NOTHING,
to="petclinic.vet",
),
),
(
"specialty",
models.ForeignKey(
db_column="specialty_id",
on_delete=django.db.models.deletion.DO_NOTHING,
to="petclinic.specialty",
),
),
],
options={
"db_table": "vet_specialties",
},
),
]
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
dependencies = [
("petclinic", "0002_vets_specialties"),
]
operations = [
migrations.CreateModel(
name="Visit",
fields=[
("id", models.IntegerField(primary_key=True, serialize=False)),
(
"pet",
models.ForeignKey(
db_column="pet_id",
on_delete=django.db.models.deletion.DO_NOTHING,
to="petclinic.pet",
),
),
("visit_date", models.DateField(null=True)),
("description", models.CharField(max_length=255, null=True)),
],
options={
"db_table": "visits",
},
),
]
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace Clinic.Migrations;
/// <summary>
/// Hand-written in EF Core's own migration shape. The id is given by the [Migration] attribute
/// and repeated in the file name, which is the convention `dotnet ef migrations add` follows.
/// No column is given an explicit `type:` string, so each provider maps the CLR type itself and
/// the one file produces the same schema on PostgreSQL and on MySQL.
/// </summary>
[Migration("20240101120000_CreateOwnersAndPets")]
public partial class CreateOwnersAndPets : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "owners",
columns: table => new
{
id = table.Column<int>(nullable: false),
first_name = table.Column<string>(maxLength: 50, nullable: false),
last_name = table.Column<string>(maxLength: 50, nullable: false),
address = table.Column<string>(maxLength: 255, nullable: true),
city = table.Column<string>(maxLength: 80, nullable: true),
telephone = table.Column<string>(maxLength: 20, nullable: true)
},
constraints: table =>
{
table.PrimaryKey("pk_owners", x => x.id);
});
migrationBuilder.CreateTable(
name: "types",
columns: table => new
{
id = table.Column<int>(nullable: false),
name = table.Column<string>(maxLength: 80, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("pk_types", x => x.id);
});
migrationBuilder.CreateTable(
name: "pets",
columns: table => new
{
id = table.Column<int>(nullable: false),
name = table.Column<string>(maxLength: 50, nullable: false),
birth_date = table.Column<DateOnly>(nullable: true),
type_id = table.Column<int>(nullable: false),
owner_id = table.Column<int>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("pk_pets", x => x.id);
table.ForeignKey(
name: "fk_pets_types",
column: x => x.type_id,
principalTable: "types",
principalColumn: "id");
table.ForeignKey(
name: "fk_pets_owners",
column: x => x.owner_id,
principalTable: "owners",
principalColumn: "id");
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "pets");
migrationBuilder.DropTable(name: "types");
migrationBuilder.DropTable(name: "owners");
}
}
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace Clinic.Migrations;
[Migration("20240202120000_CreateVets")]
public partial class CreateVets : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "vets",
columns: table => new
{
id = table.Column<int>(nullable: false),
first_name = table.Column<string>(maxLength: 50, nullable: false),
last_name = table.Column<string>(maxLength: 50, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("pk_vets", x => x.id);
});
migrationBuilder.CreateTable(
name: "specialties",
columns: table => new
{
id = table.Column<int>(nullable: false),
name = table.Column<string>(maxLength: 80, nullable: false)
},
constraints: table =>
{
table.PrimaryKey("pk_specialties", x => x.id);
});
// The composite primary key.
migrationBuilder.CreateTable(
name: "vet_specialties",
columns: table => new
{
vet_id = table.Column<int>(nullable: false),
specialty_id = table.Column<int>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("pk_vet_specialties", x => new { x.vet_id, x.specialty_id });
table.ForeignKey(
name: "fk_vet_specialties_vets",
column: x => x.vet_id,
principalTable: "vets",
principalColumn: "id");
table.ForeignKey(
name: "fk_vet_specialties_specialties",
column: x => x.specialty_id,
principalTable: "specialties",
principalColumn: "id");
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "vet_specialties");
migrationBuilder.DropTable(name: "specialties");
migrationBuilder.DropTable(name: "vets");
}
}
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace Clinic.Migrations;
/// <summary>
/// Deliberately named so that its class name sorts *before* the other two: `AddVisits` <
/// `CreateOwnersAndPets` < `CreateVets`. It has to run last all the same, because `visits`
/// has a foreign key to `pets`. If anything but the migration id decided the order, this
/// migration would run first and the run would fail on the missing `pets` table.
/// </summary>
[Migration("20240303120000_AddVisits")]
public partial class AddVisits : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "visits",
columns: table => new
{
id = table.Column<int>(nullable: false),
pet_id = table.Column<int>(nullable: false),
visit_date = table.Column<DateOnly>(nullable: true),
description = table.Column<string>(maxLength: 255, nullable: true)
},
constraints: table =>
{
table.PrimaryKey("pk_visits", x => x.id);
table.ForeignKey(
name: "fk_visits_pets",
column: x => x.pet_id,
principalTable: "pets",
principalColumn: "id");
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "visits");
}
}
class CreateOwnersAndPets < ActiveRecord::Migration[8.1]
def change
# `id: :integer, default: nil` is the only spelling that yields a plain INT
# primary key on both engines. Plain `id: :integer` yields `serial` on
# PostgreSQL and `int AUTO_INCREMENT` on MySQL; mentioning :default at all
# is what switches the auto-increment behaviour off.
create_table :owners, id: :integer, default: nil do |t|
t.string :first_name, limit: 50, null: false
t.string :last_name, limit: 50, null: false
t.string :address, limit: 255
t.string :city, limit: 80
t.string :telephone, limit: 20
end
create_table :types, id: :integer, default: nil do |t|
t.string :name, limit: 80, null: false
end
create_table :pets, id: :integer, default: nil do |t|
t.string :name, limit: 50, null: false
t.date :birth_date
t.integer :type_id, null: false
t.integer :owner_id, null: false
end
add_foreign_key :pets, :types, column: :type_id, name: "fk_pets_types"
add_foreign_key :pets, :owners, column: :owner_id, name: "fk_pets_owners"
end
end
class CreateVets < ActiveRecord::Migration[8.1]
def change
create_table :vets, id: :integer, default: nil do |t|
t.string :first_name, limit: 50, null: false
t.string :last_name, limit: 50, null: false
end
create_table :specialties, id: :integer, default: nil do |t|
t.string :name, limit: 80, null: false
end
# A real composite primary key (Rails 7.1+): no surrogate `id` column is
# added, and the columns come out in the order of the block.
create_table :vet_specialties, primary_key: [:vet_id, :specialty_id] do |t|
t.integer :vet_id, null: false
t.integer :specialty_id, null: false
end
add_foreign_key :vet_specialties, :vets, column: :vet_id, name: "fk_vet_specialties_vets"
add_foreign_key :vet_specialties, :specialties, column: :specialty_id, name: "fk_vet_specialties_specialties"
end
end
class CreateVisits < ActiveRecord::Migration[8.1]
def change
create_table :visits, id: :integer, default: nil do |t|
t.integer :pet_id, null: false
t.date :visit_date
t.string :description, limit: 255
end
add_foreign_key :visits, :pets, column: :pet_id, name: "fk_visits_pets"
end
end
/**
* Owners, pet types and pets.
*
* Written with Knex's own schema builder, which is what a Knex migration
* normally is: the same file emits PostgreSQL and MySQL DDL, so one upload
* draws on both engines.
*
* Named constraints are passed as a plain string - table.primary(cols, name).
* The documented object form, table.primary(cols, { constraintName: name }),
* is rendered wrongly by MySQL's table compiler inside createTable (knex
* 3.3.0): it wraps the whole object and emits
* `constraint \`pk_owners\` as \`constraintName\` primary key (...)`.
*/
exports.up = async function up(knex) {
await knex.schema.createTable('owners', (table) => {
table.integer('id').notNullable();
table.string('first_name', 50).notNullable();
table.string('last_name', 50).notNullable();
table.string('address', 255);
table.string('city', 80);
table.string('telephone', 20);
table.primary(['id'], 'pk_owners');
});
await knex.schema.createTable('types', (table) => {
table.integer('id').notNullable();
table.string('name', 80).notNullable();
table.primary(['id'], 'pk_types');
});
await knex.schema.createTable('pets', (table) => {
table.integer('id').notNullable();
table.string('name', 50).notNullable();
table.date('birth_date');
table.integer('type_id').notNullable();
table.integer('owner_id').notNullable();
table.primary(['id'], 'pk_pets');
table.foreign('type_id', 'fk_pets_types').references('id').inTable('types');
table.foreign('owner_id', 'fk_pets_owners').references('id').inTable('owners');
});
};
exports.down = async function down(knex) {
await knex.schema.dropTableIfExists('pets');
await knex.schema.dropTableIfExists('types');
await knex.schema.dropTableIfExists('owners');
};
/** Vets, specialties and the join table between them. */
exports.up = async function up(knex) {
await knex.schema.createTable('vets', (table) => {
table.integer('id').notNullable();
table.string('first_name', 50).notNullable();
table.string('last_name', 50).notNullable();
table.primary(['id'], 'pk_vets');
});
await knex.schema.createTable('specialties', (table) => {
table.integer('id').notNullable();
table.string('name', 80).notNullable();
table.primary(['id'], 'pk_specialties');
});
await knex.schema.createTable('vet_specialties', (table) => {
table.integer('vet_id').notNullable();
table.integer('specialty_id').notNullable();
table.primary(['vet_id', 'specialty_id'], 'pk_vet_specialties');
table.foreign('vet_id', 'fk_vet_specialties_vets').references('id').inTable('vets');
table.foreign('specialty_id', 'fk_vet_specialties_specialties').references('id').inTable('specialties');
});
};
exports.down = async function down(knex) {
await knex.schema.dropTableIfExists('vet_specialties');
await knex.schema.dropTableIfExists('specialties');
await knex.schema.dropTableIfExists('vets');
};
/** Visits a pet paid to the clinic. */
exports.up = async function up(knex) {
await knex.schema.createTable('visits', (table) => {
table.integer('id').notNullable();
table.integer('pet_id').notNullable();
table.date('visit_date');
table.string('description', 255);
table.primary(['id'], 'pk_visits');
table.foreign('pet_id', 'fk_visits_pets').references('id').inTable('pets');
});
};
exports.down = async function down(knex) {
await knex.schema.dropTableIfExists('visits');
};
import { MigrationInterface, QueryRunner } from 'typeorm';
export class CreateOwnersAndPets1704067201000 implements MigrationInterface {
name = 'CreateOwnersAndPets1704067201000';
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE owners (
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
CONSTRAINT pk_owners PRIMARY KEY (id)
)`);
await queryRunner.query(`CREATE TABLE types (
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_types PRIMARY KEY (id)
)`);
await queryRunner.query(`CREATE TABLE pets (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
birth_date DATE,
type_id INT NOT NULL,
owner_id INT NOT NULL,
CONSTRAINT pk_pets PRIMARY KEY (id),
CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
)`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TABLE pets`);
await queryRunner.query(`DROP TABLE types`);
await queryRunner.query(`DROP TABLE owners`);
}
}
import { MigrationInterface, QueryRunner } from 'typeorm';
export class CreateVets1704067202000 implements MigrationInterface {
name = 'CreateVets1704067202000';
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE vets (
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_vets PRIMARY KEY (id)
)`);
await queryRunner.query(`CREATE TABLE specialties (
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_specialties PRIMARY KEY (id)
)`);
await queryRunner.query(`CREATE TABLE vet_specialties (
vet_id INT NOT NULL,
specialty_id INT NOT NULL,
CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
)`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TABLE vet_specialties`);
await queryRunner.query(`DROP TABLE specialties`);
await queryRunner.query(`DROP TABLE vets`);
}
}
import { MigrationInterface, QueryRunner } from 'typeorm';
export class CreateVisits1704067203000 implements MigrationInterface {
name = 'CreateVisits1704067203000';
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`CREATE TABLE visits (
id INT NOT NULL,
pet_id INT NOT NULL,
visit_date DATE,
description VARCHAR(255),
CONSTRAINT pk_visits PRIMARY KEY (id),
CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
)`);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TABLE visits`);
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('owners', function (Blueprint $table) {
$table->integer('id');
$table->string('first_name', 50);
$table->string('last_name', 50);
$table->string('address', 255)->nullable();
$table->string('city', 80)->nullable();
$table->string('telephone', 20)->nullable();
$table->primary('id');
});
Schema::create('types', function (Blueprint $table) {
$table->integer('id');
$table->string('name', 80);
$table->primary('id');
});
Schema::create('pets', function (Blueprint $table) {
$table->integer('id');
$table->string('name', 50);
$table->date('birth_date')->nullable();
$table->integer('type_id');
$table->integer('owner_id');
$table->primary('id');
$table->foreign('type_id')->references('id')->on('types');
$table->foreign('owner_id')->references('id')->on('owners');
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('pets');
Schema::dropIfExists('types');
Schema::dropIfExists('owners');
}
};
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('vets', function (Blueprint $table) {
$table->integer('id');
$table->string('first_name', 50);
$table->string('last_name', 50);
$table->primary('id');
});
Schema::create('specialties', function (Blueprint $table) {
$table->integer('id');
$table->string('name', 80);
$table->primary('id');
});
Schema::create('vet_specialties', function (Blueprint $table) {
$table->integer('vet_id');
$table->integer('specialty_id');
$table->primary(['vet_id', 'specialty_id']);
$table->foreign('vet_id')->references('id')->on('vets');
$table->foreign('specialty_id')->references('id')->on('specialties');
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('vet_specialties');
Schema::dropIfExists('specialties');
Schema::dropIfExists('vets');
}
};
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*/
public function up(): void
{
Schema::create('visits', function (Blueprint $table) {
$table->integer('id');
$table->integer('pet_id');
$table->date('visit_date')->nullable();
$table->string('description', 255)->nullable();
$table->primary('id');
$table->foreign('pet_id')->references('id')->on('pets');
});
}
/**
* Reverse the migrations.
*/
public function down(): void
{
Schema::dropIfExists('visits');
}
};
"""create owners and pets"""
from alembic import op
import sqlalchemy as sa
revision = '0001'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
op.create_table(
'owners',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('first_name', sa.String(50), nullable=False),
sa.Column('last_name', sa.String(50), nullable=False),
sa.Column('address', sa.String(255), nullable=True),
sa.Column('city', sa.String(80), nullable=True),
sa.Column('telephone', sa.String(20), nullable=True),
)
op.create_table(
'types',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('name', sa.String(80), nullable=False),
)
op.create_table(
'pets',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('name', sa.String(50), nullable=False),
sa.Column('birth_date', sa.Date, nullable=True),
sa.Column('type_id', sa.Integer, sa.ForeignKey('types.id'), nullable=False),
sa.Column('owner_id', sa.Integer, sa.ForeignKey('owners.id'), nullable=False),
)
def downgrade():
op.drop_table('pets')
op.drop_table('types')
op.drop_table('owners')
"""create vets"""
from alembic import op
import sqlalchemy as sa
revision = '0002'
down_revision = '0001'
branch_labels = None
depends_on = None
def upgrade():
op.create_table(
'vets',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('first_name', sa.String(50), nullable=False),
sa.Column('last_name', sa.String(50), nullable=False),
)
op.create_table(
'specialties',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('name', sa.String(80), nullable=False),
)
op.create_table(
'vet_specialties',
sa.Column('vet_id', sa.Integer, sa.ForeignKey('vets.id'), primary_key=True),
sa.Column('specialty_id', sa.Integer, sa.ForeignKey('specialties.id'), primary_key=True),
)
def downgrade():
op.drop_table('vet_specialties')
op.drop_table('specialties')
op.drop_table('vets')
"""create visits"""
from alembic import op
import sqlalchemy as sa
revision = '0003'
down_revision = '0002'
branch_labels = None
depends_on = None
def upgrade():
op.create_table(
'visits',
sa.Column('id', sa.Integer, primary_key=True, autoincrement=False),
sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id'), nullable=False),
sa.Column('visit_date', sa.Date, nullable=True),
sa.Column('description', sa.String(255), nullable=True),
)
def downgrade():
op.drop_table('visits')
-- +goose Up
CREATE TABLE owners
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
address VARCHAR(255),
city VARCHAR(80),
telephone VARCHAR(20),
CONSTRAINT pk_owners PRIMARY KEY (id)
);
CREATE TABLE types
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_types PRIMARY KEY (id)
);
CREATE TABLE pets
(
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
birth_date DATE,
type_id INT NOT NULL,
owner_id INT NOT NULL,
CONSTRAINT pk_pets PRIMARY KEY (id),
CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);
-- +goose Down
DROP TABLE pets;
DROP TABLE types;
DROP TABLE owners;
-- +goose Up
CREATE TABLE vets
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_vets PRIMARY KEY (id)
);
CREATE TABLE specialties
(
id INT NOT NULL,
name VARCHAR(80) NOT NULL,
CONSTRAINT pk_specialties PRIMARY KEY (id)
);
CREATE TABLE vet_specialties
(
vet_id INT NOT NULL,
specialty_id INT NOT NULL,
CONSTRAINT pk_vet_specialties PRIMARY KEY (vet_id, specialty_id),
CONSTRAINT fk_vet_specialties_vets FOREIGN KEY (vet_id) REFERENCES vets (id),
CONSTRAINT fk_vet_specialties_specialties FOREIGN KEY (specialty_id) REFERENCES specialties (id)
);
-- +goose Down
DROP TABLE vet_specialties;
DROP TABLE specialties;
DROP TABLE vets;
-- +goose Up
CREATE TABLE visits
(
id INT NOT NULL,
pet_id INT NOT NULL,
visit_date DATE,
description VARCHAR(255),
CONSTRAINT pk_visits PRIMARY KEY (id),
CONSTRAINT fk_visits_pets FOREIGN KEY (pet_id) REFERENCES pets (id)
);
-- +goose Down
DROP TABLE visits;
All 22 variants, with the layout each tool expects, are on the migration tools pages.
Example 3
The same migrations on six engines
Identical Flyway files, six databases. The tables are the same; the type names are what each engine reports, and Oracle folds unquoted names to upper case.
Example 4
An e-commerce schema, thirteen tables
Customers with addresses, a product catalog with a category tree, orders with items, payments, shipments and coupons, and product reviews. Four Flyway migrations on PostgreSQL 18.
CREATE TABLE customers
(
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
phone VARCHAR(30),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE addresses
(
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers (id),
line1 VARCHAR(255) NOT NULL,
line2 VARCHAR(255),
city VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
country_code CHAR(2) NOT NULL,
is_default BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE categories
(
id BIGSERIAL PRIMARY KEY,
parent_id BIGINT REFERENCES categories (id),
name VARCHAR(120) NOT NULL,
slug VARCHAR(120) NOT NULL UNIQUE
);
CREATE TABLE products
(
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(64) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
description TEXT,
price_cents INTEGER NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'EUR',
weight_kg NUMERIC(8, 3),
is_active BOOLEAN NOT NULL DEFAULT true,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE product_categories
(
product_id BIGINT NOT NULL REFERENCES products (id),
category_id BIGINT NOT NULL REFERENCES categories (id),
PRIMARY KEY (product_id, category_id)
);
CREATE TABLE inventory
(
product_id BIGINT PRIMARY KEY REFERENCES products (id),
quantity INTEGER NOT NULL DEFAULT 0,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE orders
(
id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers (id),
shipping_address_id BIGINT NOT NULL REFERENCES addresses (id),
billing_address_id BIGINT NOT NULL REFERENCES addresses (id),
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
currency CHAR(3) NOT NULL,
total_cents INTEGER NOT NULL,
placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE order_items
(
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders (id),
product_id BIGINT NOT NULL REFERENCES products (id),
quantity INTEGER NOT NULL,
unit_price_cents INTEGER NOT NULL
);
CREATE TABLE payments
(
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders (id),
provider VARCHAR(40) NOT NULL,
amount_cents INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
paid_at TIMESTAMPTZ
);
CREATE TABLE shipments
(
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders (id),
carrier VARCHAR(40) NOT NULL,
tracking_number VARCHAR(64),
shipped_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ
);
CREATE TABLE coupons
(
id BIGSERIAL PRIMARY KEY,
code VARCHAR(32) NOT NULL UNIQUE,
percent_off SMALLINT NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL
);
CREATE TABLE order_coupons
(
order_id BIGINT NOT NULL REFERENCES orders (id),
coupon_id BIGINT NOT NULL REFERENCES coupons (id),
PRIMARY KEY (order_id, coupon_id)
);
CREATE TABLE reviews
(
id BIGSERIAL PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products (id),
customer_id BIGINT NOT NULL REFERENCES customers (id),
rating SMALLINT NOT NULL,
body TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Scroll inside the frame, or open the SVG. Notice bigserial for identity columns, bpchar for CHAR(2), timestamptz, and the self-reference categories.parent_id.
Example 5
When a migration is broken
A missing comma, the kind of typo that reaches production on a Friday. SchemaDrawer answers with the migration tool's own words.
V1__create_owners.sql
CREATE TABLE owners
(
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL
last_name VARCHAR(50) NOT NULL,
CONSTRAINT pk_owners PRIMARY KEY (id)
);
{
"jobId": "7d2e9a10-4c6b-4f1e-9b3a-5e8d1c2f3a4b",
"status": "ERROR",
"createdAt": "2026-09-25T08:03:12Z",
"errorMessage": "Flyway migration failed (exit code 1).\nOutput:\nFlyway OSS Edition 13.7.0 by Redgate\n...\nMigrating schema \"public\" to version \"1 - create owners\"\nErrors:\nERROR: Migration of schema \"public\" to version \"1 - create owners\" failed! Changes successfully rolled back.\nERROR: Failed to execute script V1__create_owners.sql\n----------------------------------------------\nSQL State : 42601\nMessage : ERROR: syntax error at or near \"last_name\"\n Position: 100\nLocation : /flyway/sql/V1__create_owners.sql\nLine : 1",
"diagramUrl": null
}
The same message Flyway prints locally, with the PostgreSQL error and position. The GitHub Action fails the workflow with it, which makes a broken migration visible in the pull request that introduces it.
Draw yours
Your migrations, your engine version, one SVG. Free while we build it.