Skip to main content

Multiple schemas

Several schemas: schema.table names, schema-qualified enums, table aliases, cross-schema refs in inline, short and long form, and the implicit public schema.

Covers: schema.table names; schema-qualified and aliased enums; table alias (as U) used in refs; cross-schema refs in inline, short and long form; explicit public. prefix; group of schema-qualified tables

11-multi-schema.dbml
11-multi-schema.dbmlcore_tablescore_tablescore.users.id < core.memberships.user_idcore.organizations.id < core.memberships.organization_idcore.users.id < blogging.posts.user_idblogging.comments.post_id > blogging.posts.id delete: cascadeblogging.comments.author_id > core.users.idbilling_owner: billing.invoices.organization_id > core.organizations.id delete: restrictbilling.invoice_lines.invoice_id > billing.invoices.id delete: cascadepublic.feature_flags.owner_id > core.users.id delete: set nullcore.userscore.usersid integer · primary key · incrementidintegeremail varchar(255) · unique · not nullemailUNNvarchar(255)status core.account_status · not null · default: activestatusNNcore.account_statuscore.organizationscore.organizationsid integer · primary key · incrementidintegername varchar(200) · not nullnameNNvarchar(200)core.membershipscore.membershipsuser_id integer · foreign keyuser_idintegerorganization_id integer · foreign keyorganization_idintegerrole varchar(20) · not nullroleNNvarchar(20)blogging.postsblogging.postsid integer · primary key · incrementidintegeruser_id integer · foreign keyuser_idintegertitle varchar(300)titlevarchar(300)published_at timestamppublished_attimestampblogging.commentsblogging.commentsid integer · primary key · incrementidintegerpost_id integer · foreign key · not nullpost_idNNintegerauthor_id integer · foreign key · not nullauthor_idNNintegerbody textbodytextbilling.invoicesbilling.invoicesid integer · primary key · incrementidintegerorganization_id integer · foreign key · not nullorganization_idNNintegerstate billing.invoice_state · not null · default: draftstateNNbilling.invoice_statetotal decimal(12,2)totaldecimal(12,2)billing.invoice_linesbilling.invoice_linesid integer · primary key · incrementidintegerinvoice_id integer · foreign key · not nullinvoice_idNNintegerdescription varchar(300)descriptionvarchar(300)amount decimal(12,2)amountdecimal(12,2)feature_flagsfeature_flagskey varchar(64) · primary keykeyvarchar(64)enabled boolean · not null · default: falseenabledNNbooleanowner_id integer · foreign keyowner_idinteger

Source​

examples/11-multi-schema.dbml
examples/11-multi-schema.dbml
// Several schemas: schema.table names, schema-qualified enums, table aliases,
// cross-schema refs in inline, short and long form, and the implicit public schema.

enum core.account_status {
active
suspended
closed
}

enum billing.invoice_state as InvoiceState {
draft
issued
paid
void
}

Table core.users as U {
id integer [pk, increment]
email varchar(255) [not null, unique]
status core.account_status [not null, default: 'active']
}

Table core.organizations {
id integer [pk, increment]
name varchar(200) [not null]
}

Table core.memberships {
user_id integer [ref: > U.id]
organization_id integer [ref: > core.organizations.id]
role varchar(20) [not null]

indexes {
(user_id, organization_id) [pk]
}
}

Table blogging.posts {
id integer [pk, increment]
user_id integer [ref: > core.users.id]
title varchar(300)
published_at timestamp
}

Table blogging.comments {
id integer [pk, increment]
post_id integer [not null]
author_id integer [not null]
body text
}

Table billing.invoices {
id integer [pk, increment]
organization_id integer [not null]
state InvoiceState [not null, default: 'draft']
total "decimal(12,2)"
}

Table billing.invoice_lines {
id integer [pk, increment]
invoice_id integer [not null]
description varchar(300)
amount "decimal(12,2)"
}

// No schema prefix: lives in public.
Table feature_flags {
key varchar(64) [pk]
enabled boolean [not null, default: false]
owner_id integer
}

// Short form, cross-schema
Ref: blogging.comments.post_id > blogging.posts.id [delete: cascade]
Ref: blogging.comments.author_id > U.id

// Long form, cross-schema
Ref billing_owner {
billing.invoices.organization_id > core.organizations.id [delete: restrict]
}

Ref {
billing.invoice_lines.invoice_id > billing.invoices.id [delete: cascade]
}

// public -> core
Ref: public.feature_flags.owner_id > core.users.id [delete: set null]

TableGroup core_tables [color: #34495e] {
core.users
core.organizations
core.memberships
}