Database Markup Language (DBML) is a powerful, intuitive language for defining database schemas. nself uses DBML as the foundation for schema-first development, allowing you to design your database structure with code that's both human-readable and machine-processable.
DBML (Database Markup Language) is an open-source domain-specific language designed to define and document database schemas. Created by the team at Holistics, DBML provides:
Define tables with columns, data types, and constraints:
Table users {
id integer [pk, increment] // Primary key with auto-increment
email varchar(255) [unique, not null]
password_hash varchar(255) [not null]
first_name varchar(100)
last_name varchar(100)
is_active boolean [default: true]
created_at timestamp [default: `now()`]
updated_at timestamp [default: `now()`]
Note: 'Users table for authentication and profiles'
}DBML supports all PostgreSQL data types:
Table data_types_example {
// Numeric types
id bigserial [pk]
price decimal(10,2)
quantity integer
rating float
percentage double
// Text types
title varchar(255)
content text
status char(1)
// Date/Time types
created_at timestamp
birth_date date
work_hours time
event_time timestamptz
// Boolean
is_active boolean
// JSON
metadata json
settings jsonb
// Arrays
tags text[]
scores integer[]
// UUID
user_id uuid
// Custom types (enums)
role user_role
}Define column properties with attributes:
Table products {
id uuid [pk, default: `gen_random_uuid()`]
name varchar(255) [not null]
slug varchar(255) [unique, not null]
price decimal(10,2) [not null, note: 'Price in USD cents']
description text [null]
is_featured boolean [default: false]
category_id uuid [ref: > categories.id]
created_at timestamp [default: `now()`]
}Available Attributes:
pk - Primary keyunique - Unique constraintnot null - Not null constraintnull - Explicitly nullable (default)increment - Auto increment (for integers)default: value - Default valueref: > table.column - Foreign key referencenote: 'description' - Column documentationTable authors {
id uuid [pk]
name varchar(255)
}
Table books {
id uuid [pk]
title varchar(255)
author_id uuid [ref: > authors.id] // Many books to one author
}
// Alternative syntax
Ref: books.author_id > authors.idTable books {
id uuid [pk]
title varchar(255)
}
Table genres {
id uuid [pk]
name varchar(100)
}
// Junction table
Table book_genres {
book_id uuid [ref: > books.id]
genre_id uuid [ref: > genres.id]
Indexes {
(book_id, genre_id) [pk]
}
}
// Alternative: Define relationships separately
Ref: book_genres.book_id > books.id
Ref: book_genres.genre_id > genres.idTable users {
id uuid [pk]
email varchar(255)
}
Table user_profiles {
id uuid [pk]
user_id uuid [unique, ref: - users.id] // One-to-one with users
bio text
avatar_url varchar(500)
}Relationship Types:
> - One-to-many (one authors to many books)< - Many-to-one (many books to one author)- - One-to-one<> - Many-to-many (requires junction table)Define custom enumerated types:
Enum user_role {
admin
editor
author
subscriber
}
Enum order_status {
pending [note: 'Order is being processed']
confirmed
shipped
delivered
cancelled [note: 'Order was cancelled']
}
Table users {
id uuid [pk]
email varchar(255)
role user_role [default: 'subscriber']
}
Table orders {
id uuid [pk]
status order_status [default: 'pending']
}Define database indexes for performance:
Table posts {
id uuid [pk]
title varchar(255)
author_id uuid
category_id uuid
is_published boolean
published_at timestamp
created_at timestamp
Indexes {
// Simple indexes
(author_id)
(category_id)
(is_published)
// Composite indexes
(is_published, published_at) [name: 'idx_posts_published']
(author_id, created_at)
// Unique indexes
(title, author_id) [unique]
// Partial indexes with conditions
(published_at) [where: 'is_published = true']
// Index types
(title) [type: hash]
(content) [type: gin] // For full-text search
}
}Organize related tables:
TableGroup user_management {
users
user_profiles
user_sessions
}
TableGroup content_management {
posts
categories
tags
post_tags
}
TableGroup ecommerce {
products
orders
order_items
payments
}Add comprehensive documentation:
Table users {
id uuid [pk, note: 'Primary key using UUID for security']
email varchar(255) [unique, not null, note: 'User email address for login']
password_hash varchar(255) [not null, note: 'Bcrypt hashed password']
Note: '''
Users table stores all user authentication and basic profile information.
Password is hashed using bcrypt with cost factor 12.
Email verification is required before activation.
'''
}
// Project-level documentation
Project ecommerce_platform {
database_type: 'PostgreSQL'
Note: '''
E-commerce platform database schema
This schema supports:
- Multi-tenant product catalog
- Order management and fulfillment
- Customer accounts and profiles
- Inventory tracking
- Payment processing integration
Version: 2.1.0
Last updated: 2025-08-06
'''
}Follow consistent naming patterns:
// Table names: singular, lowercase with underscores
Table user_profile {
// Column names: lowercase with underscores
id uuid [pk]
user_id uuid
first_name varchar(100)
last_name varchar(100)
created_at timestamp
updated_at timestamp
}
// Foreign keys: table_name + _id
Table posts {
id uuid [pk]
author_id uuid [ref: > users.id] // References users table
category_id uuid [ref: > categories.id]
}
// Junction tables: table1_table2
Table post_tags {
post_id uuid [ref: > posts.id]
tag_id uuid [ref: > tags.id]
}
// Enum values: lowercase
Enum post_status {
draft
published
archived
}nself automatically validates your DBML schema for:
# Validate schema
nself db validate
# Validate with performance suggestions
nself db validate --performance
# Check for breaking changes
nself db validate --breaking-changes// User management
Enum user_role {
subscriber
contributor
author
editor
administrator
}
Table users {
id uuid [pk, default: `gen_random_uuid()`]
email varchar(255) [unique, not null]
username varchar(50) [unique, not null]
password_hash varchar(255) [not null]
display_name varchar(100)
role user_role [default: 'subscriber']
is_active boolean [default: true]
email_verified_at timestamp
created_at timestamp [default: `now()`]
updated_at timestamp [default: `now()`]
}
// Content structure
Table categories {
id uuid [pk, default: `gen_random_uuid()`]
name varchar(100) [unique, not null]
slug varchar(100) [unique, not null]
description text
parent_id uuid [ref: > categories.id]
sort_order integer [default: 0]
created_at timestamp [default: `now()`]
}
Enum post_status {
draft
published
scheduled
archived
}
Table posts {
id uuid [pk, default: `gen_random_uuid()`]
title varchar(255) [not null]
slug varchar(255) [unique, not null]
excerpt text
content text
featured_image_url varchar(500)
author_id uuid [ref: > users.id, not null]
category_id uuid [ref: > categories.id]
status post_status [default: 'draft']
published_at timestamp
created_at timestamp [default: `now()`]
updated_at timestamp [default: `now()`]
Indexes {
(slug) [unique]
(author_id, status, published_at)
(category_id)
(status, published_at) [name: 'idx_posts_published']
}
}Generate DBML from existing PostgreSQL database:
# Generate DBML from existing database
nself db import --from postgresql://user:pass@host:port/db
# Import specific tables only
nself db import --tables users,posts,comments
# Import with relationships
nself db import --include-relationshipsConvert SQL DDL to DBML:
# Convert SQL file to DBML
nself db import --from-sql schema.sql
# Multiple files
nself db import --from-sql "migrations/*.sql"Now that you understand DBML schema design:
DBML provides the foundation for professional database development with nself, enabling you to design, document, and evolve your database schema with confidence.