DOCSEARCHbyAlgolia
Submit Docs

DBML Documentation

repository·master·Indexed 25 days ago

https://github.com/holistics/dbml

DBML is a domain-specific language for defining database structures in a human-readable and database-agnostic way, used for schema modeling, visualization, and documentation. The project includes @dbml/core for parsing, @dbml/cli for converting DBML to SQL via dbml2sql, and a Parser Playground for debugging the lexer, parser, analyzer, and interpreter pipeline stages.

Tokens
34.7K
Snippets
69
Records
238
Agent score
76%

What's inside DBML

  1. Overview of MSSQL Parser Support

    master

    The MSSQL (SQL Server) parser converts T-SQL DDL statements into DBML format using an ANTLR4 parser. It supports SQL Server-specific syntax such as IDENTITY columns, temporary tables (prefixed with #), and [identifier] square bracket syntax.

    Key Capabilities:

    • Data Definition: CREATE TABLE with parameterized types (e.g., VARCHAR(255)) and temporary tables.
    • Constraints: PRIMARY KEY, FOREIGN KEY (table-level), UNIQUE, CHECK, DEFAULT, and NOT NULL.
    • Auto-increment: IDENTITY with seed and increment.
    • Indexes: CREATE INDEX (basic, multi-column, unique).
    • Schema Modification: ALTER TABLE ADD CONSTRAINT for various constraint types.
    • Data Manipulation: Basic and multi-row INSERT statements.

  2. Introduction to DBML (Database Markup Language)

    master

    DBML is an open-source, declarative, and database-agnostic Domain Specific Language (DSL) designed to define and document database schemas. Unlike SQL DDL, which is imperative and database-specific, DBML is designed for high-level database architecting and is highly readable.

    Key Features:

    • Declarative: Focuses on defining the structure rather than the steps to create it.
    • Database-Agnostic: Works across different database engines (PostgreSQL, Oracle, etc.).
    • Visualizable: Can be used to generate ER diagrams and documentation.

    Best Practice: It is recommended to keep a database.dbml file in your project's root directory alongside your README.md or package.json to maintain a single source of truth for your schema.

    Table users {
  id integer
  username varchar
  role varchar
  created_at timestamp
}

Table posts {
  id integer [primary key]
  title varchar
  body text [note: 'Content of the post']
  user_id integer
  status post_status
  created_at timestamp
}

Enum post_status {
  draft
  published
  private [note: 'visible via URL only']
}

Ref: posts.user_id > users.id // many-to-one

  3. Overview of Oracle SQL Parser Support

    master
    The Oracle SQL parser module converts Oracle DDL statements into DBML format using an ANTLR4-based model. It is specifically designed to handle Oracle-specific syntax such as GENERATED AS IDENTITY columns, function-based indexes, and comprehensive ALTER TABLE ADD CONSTRAINT operations. It provides the best ALTER TABLE support among all supported databases in this project.

  4. Overview of DBML (Database Markup Language)

    master

    DBML (database markup language) is a simple, readable Domain Specific Language (DSL) designed to define database structures. It is database-agnostic, focusing on essential structure definitions rather than specific database syntaxes.

    Key ecosystem tools include:

    • dbdiagram.io: A free database visualizer.
    • dbdocs.io: A free database documentation application.
    • @dbml/core: The core library for DBML processing.
    • @dbml/cli: Command line interface for DBML tasks.

  6. Use the DBML Parser Playground features

    master

    The playground provides several tools for debugging the DBML parsing pipeline:

    Parser Pipeline Stages

    You can inspect the output of each stage:

    1. Lexer: Tokenization stage showing individual tokens.
    2. Parser: Syntax analysis stage showing the raw AST.
    3. Analyzer: Semantic analysis stage with symbol resolution.
    4. Interpreter: Final semantic tree for the database model.
    5. Errors: Parse errors with line and column information.

    Navigation and Editor

    • Bidirectional Navigation: Click tokens to highlight source code, or click AST nodes to jump to their source location. Use Cmd/Ctrl+Click for enhanced navigation.
    • Monaco Editor: Supports DBML syntax highlighting and a Vim mode toggle (located in the top-right).
    • Persistence: Work is automatically saved to localStorage. Use Cmd/Ctrl+S for manual saving.

  7. Snowflake SQL Parser Support Overview

    master

    The Snowflake model structure generator uses an ANTLR4 parser to convert Snowflake DDL statements into DBML format. It is designed to handle Snowflake-specific syntax such as IDENTITY columns with increment ranges.

    Key Limitations to Note:

    • FOREIGN KEY constraints: Currently causes an undefined error (known bug).
    • CHECK constraints: Causes a parse failure.
    • Column comments: Not supported.
    • Indexes: Not applicable as Snowflake does not support user-defined indexes.
    • GENERATED AS IDENTITY: Not supported; use the IDENTITY(start, increment) syntax instead.

  8. PostgreSQL SQL Parser Capabilities Overview

    master

    The PostgreSQL SQL parser module converts PostgreSQL DDL statements into DBML format. It supports a wide range of PostgreSQL-specific syntax, including enumerated types, SERIAL/BIGSERIAL columns, GENERATED AS IDENTITY, and various constraint definitions.

    Key Capabilities

    • Data Definition: CREATE TABLE with support for enumerated types, parameterized types (e.g., VARCHAR(255)), and array types (e.g., INTEGER[]).
    • Constraints: PRIMARY KEY (column, table, or multi-column), FOREIGN KEY (with ON UPDATE/ON DELETE actions), UNIQUE, CHECK, DEFAULT, and NOT NULL.
    • Auto-increment: SERIAL, BIGSERIAL, and GENERATED AS IDENTITY.
    • Indexes: CREATE INDEX supporting BTREE, HASH, GIST, BRIN, and GIN, including function-based indexes.
    • Comments: COMMENT ON TABLE and COMMENT ON COLUMN statements.
    • Data Manipulation: Basic INSERT and multi-row INSERT statements.

  9. Understand the DBML Playground Architecture

    master
    The DBML Playground is a multi-file editor featuring real-time parsing, syntax highlighting, diagnostics, and database schema visualization. It uses a three-pane layout (Files, Editor, Output) managed by Pinia stores for state management. The architecture relies on @dbml/parse for the core compiler logic.

  10. Supported Oracle SQL Features

    master

    The parser supports a wide range of Oracle SQL features for generating DBML, including:

    • Data Definition: CREATE TABLE with parameterized types (e.g., VARCHAR2(255), NUMBER(10,2)).
    • Constraints: PRIMARY KEY (column, table, or multi-column), FOREIGN KEY (with ON DELETE actions), UNIQUE, CHECK, DEFAULT, and NOT NULL.
    • Auto-increment: GENERATED AS IDENTITY, GENERATED ALWAYS, and GENERATED BY DEFAULT.
    • Indexes: Basic, multi-column, unique, and function-based indexes (e.g., UPPER(column)).
    • Comments: COMMENT ON TABLE and COMMENT ON COLUMN statements.
    • Schema Modification: Extensive ALTER TABLE ADD CONSTRAINT support for DEFAULT, NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.
    • Data Manipulation: Basic INSERT statements and Oracle-specific INSERT ALL for multi-row/multi-table inserts.

  11. Define Schemas and Tables

    master

    DBML allows defining tables within specific schemas using the schema_name.table_name syntax. If no schema is provided, the table defaults to the public schema.

    Table Syntax

    • Table Name: The identifier for the table.
    • Column Name: The identifier for the column.
    • Column Type: The data type. If the type contains spaces (e.g., double precision), wrap it in double quotes: "double precision". Types with parentheses like varchar(255) are supported as-is.
    • Table Alias: Use as to alias a table for easier referencing.
    // Table in default 'public' schema
Table table_name {
  column_name column_type [column_settings]
}

// Table in a specific schema
Table schema_name.table_name {
  column_name column_type [column_settings]
}

// Table with an alias
Table very_long_user_table as U {
  id integer
}

// Referencing an aliased table
Ref: U.id < posts.user_id
    Table schema_name.table_name {
  column_name column_type [column_settings]
}