I posted here: https://community.dbdiagram.io/t/postgres-array-export-fails/416

I'm not sure if the export of dbdiagram.io is considered part of dmbl or not but I thought I'd create an issue for it.

Postgres expects the following syntax for array types:

CREATE TABLE contacts (
    variants TEXT []
);

When I use:

variants array
It exports as:

"variants" array,
which fails.

User error on my part?

I mean your sistem can render a complete E-R, but I want to draw something that is situated before that, called 'skeleton' that wraps: entities, relations (their cardinality) and generalization of entities.
That 'skeleton' helps to visualize the situation better, that's why are not defined attributes yet.

So i've just wasted a couple of hours on building a diagram, first time using the tool...
Wrote some lines of code, gave it a name and saved.

Finished writing down tables, wanted to see some examples so i clicked "Load Sample" and thought it will open a new window...
But guess what? It overwrites your code! (But who knows?)

And than i could find mine, started opening new tabs, panicked a bit and not sure wtf did i do..
As a result, my code is gone and no way to get it back...
Bye bye 2 hours!
This is really frustrating...

A Sublime plug-in for creating and validating DBML would be awesome!

Hi,

The tool is great, and I am looking forward for the TableGroup feature.

I would like to know if it's possible to show the direction of the relationships with arrows. IMO it would show more clearly those relationship without the need to toggle the Highlight feature.

Here is an example to explain what I mean.

=>

The standard around UML could be used, as explained here : https://www.talend.com/resources/data-model-design-best-practices-part-2/

Thanks in advance.

All one-to-one relations from schema output as one-to-many in the DBML Ref.
Looking into the code I suspect the code in line 112 in DbmlExporter.js would have to be something like this: if (foreignEndpoint.relation === '1') line += '- ';else line += '< ';
instead of if (foreignEndpoint === '1') line += '- ';else line += '< ';

Hi,

I was thinking that it would be good to have a VS Code extension to visualise dbml diagrams within VS Code.

Would there be interest for this?

Thanks!

Postgres export comes close, but some of the features like composite PK) definitions aren't equivalent syntactically. Happy to provide syntax mapping from postgres to snowflake, and would have created a pull request, however will struggle with actual implementation as js isn't my forte.

It would be great if you could add SQLite export. Thanks!

DBML Tag

Concept

• Support metadata tag/label for DBML element (table, schema,...)
• Each element can have multiple tags associated.
• Each tag can be associated with multiple elements.

Syntax

Define the tag block separately.

Tag v1.0 {
  note: 'version 1.0'
  tables: [users, cities, countries]
}

Tag transaction {
  note: 'transaction tables'
  tables: [users, orders]
}

Table orders {
  id int [pk]
  user_id int [ref: > users.id]
  status order_status
  created_at datetime
}
...

• All the tag's attributes (note, associated tables) can be declared in the long-form format.

Define tag inside element

Table orders [tags: [transaction, v1.0]] {
  id int [pk]
  user_id int [ref: > users.id]
  status order_status
  created_at datetime
}

// or

Table orders {
  id int [pk]
  user_id [ref: > users.id]
  status order_status
  created_at datetime

  Tags {
    v1.0
    transaction
  }
}

Note

Objective

• Support define element's note inside element body
• Make writing long note easier with the new syntax.

Example

Table ecommerce.users {
  id int [pk]
  name varchar
  status user_status

  Note: 'contains all user info'
}

Schema ecommerce {
  Note: 'contains all tables belong to the ecommerce schema'
}

Enum user_status {
  active
  deactivated

  Note: 'user status enum'
}

unsigned should be added to the column settings, it's a very commonly used property of numeric columns and especially primary keys.

Here's a simplified version of the default schema for the users table for a fresh Laravel installation:

Table users {
    id bigint [pk, increment, unsigned]
    email varchar [not null, unique]
}

Adding typescript types to dbml-core would allow the project to provide an interface for people who want to write a backend for another database.

Working with large schemas can result in a single large DBML file. It would help reasoning about and maintaining a large schema if a single schema definition can span multiple files. That way, logically-grouped definitions can live in one file, and another file can reference those definitions by “including” them with a reference.

Something like this:

include ‘enums.dbml’

Hello, I'm really enjoying the tool, and in my modeling I'm doing, I ended up noting that when using the decimal data type, if I need to put a decimal range (10,2) that I usually use for money values does not work, is giving error and leaves the modeling with bugs ... It would be interesting that it worked so much for decimal, float etc ...

Error:

No error:

Trying to parse the below throws an error on time with zone:

CREATE TABLE public.hours_of_operation (
  id integer NOT NULL,
  created_at timestamp with time zone DEFAULT now() NOT NULL,
  updated_at timestamp with time zone DEFAULT now() NOT NULL,
  location_id integer NOT NULL,
  day text NOT NULL,
  open time with zone NOT NULL,
  close time with zone NOT NULL
);

Hi,
I wanted to check out dbml tool, but on my sample schema it throwns an error

I have dumped my database schema only like this:
mysqldump --no-data mydb.sql > mydb.sql

sql2dbml --mysql mydb.sql -o mydb.dbml
Expected ")" or fields but "`" found.

How do I fix this?

I have to define a field of Struct datatype that further has 2 more fields
e.g
email_settings Struct
email-settings further has 2 more fields fname String , Lname String

How to define email_settings?
Thank you

Many to many relationships handled with composite keys aren't that uncommon, is it a feature planned to be implemented?

Non empty comments are also resticted:

The Index example from the docs fails:

Table bookings {
  id integer
  country varchar
  booking_date date
  created_at timestamp

  indexes {
      (id, country) [pk] // composite primary key
      created_at [note: 'Date']
      booking_date
      (country, booking_date) [unique]
      booking_date [type: hash]
      (`id*2`)
      (`id*3`,`getdate()`)
      (`id*3`,id)
  }
}

Error message:

  ERROR: You have a syntax error at "table.dbml" line 9 column 19. Expected " ", "name:", "type:", "unique", PK, comment, or whitespace but "n" found.

Here we have a note in created_at index, which is not allowed by the syntax.
According to the IndexSetting syntax, only "unique", name or type allowed in index settings.

Should either add note to possible index settings, or fix the example in docs

Is there any forecast for implementation of auto increment and indexes?

when converting from postgres --> dbml, timestamptz is not supported. Can switch in sql documentation to timestamp, just complicates it further than needed to be. Output of timestamp in dbml is fine as well.

It would be nice to have support for ON DELETE CASCADE in dbdiagram.io

Is there any way to use the dbml cli tool to generate sql migration scripts?

Allow users to export SVG diagram from cli. I assume it is already implemented in dbdiagram. Can we open-source relevant piece and add as an exporter.

Is there a way to set a primary key for multiple columns?

For example, there is a one-to-many table with additional columns and all columns in total are the primary key?

table model_has_permissions {
  permission_id int(10) [unsigned]
  model_type varchar(255)
  model_id bigint(20) [unsigned]

  primary_keys {
    permission_id,
    model_id,
    model_type
  }
}

Annotation

Current problems

• Every time new concepts are requested for DBML, we have to look into how those concepts work for other databases such as MySQL and PostgreSQL to define the syntaxes that can fit all.
• Some concepts cannot be supported in DBML because the concept mismatch across databases.

Proposed solution: These problems can be solved by annotation.

Annotation

Annotations helps to associate metadata (information) to the DBML elements.

@<annotation-name> {
  // syntax of each annotation is independent. For example, the syntax of 
  // dbdiagram annotation can be different from the syntax of dbdocs 
  // annotation
}

Example of dbdiagram annotation:

Table users {
  id int [pk]
  name varchar
}

@dbdiagram {
  darkmode: on

  Table users {
    headercolor: #f3f2f9
    position {
      x: 181
      y: 212
    }
  } 
}

The example below fully demonstrate the power of annotation of supporting different database concepts without mixing concepts into general DBML code.

Table users {
  id int [pk]
  name varchar
  status user_status

  @postgresql {  // annotation for this table users; use for exporting this table to PostgreSQL
    id: [serial]
    Indexes {
      name: [type: gist]
    }
  }

  @mysql {  // annotation for this table users; use for exporting this table to MySQL
    id: [auto_increment]
    storage_type: memory
    Indexes {
      name: [type: hash]
    }
  }
}

Annotation can be placed anywhere in our DBML code. If it is placed inside an element, it will provide metadata for that element.

Avantages of annotation

• Annotation is a way to separate between high level data model (table, schema) and SQL low level implementation (indexes, storage type...)
• One DBML file can contains many kinds of annotation without any problem. For example, one DBML table can be associated with two database annotations (mysql, postgres ... ) for exporting purpose without polluting the original DBML code
• Annotation syntax is independent. Anyone, any team can define their own annotation syntax and write the annotation parser plugin to parse them.
• From the view of DBML, annotation can be treated like comment. It does not affect the structure or the meaning of the database
• From the technical view, DBML dialect with syntax mixed into original DBML code is hard to implement and maintain. For annotation, we just need to write another specific annotation parser plugin.
• The render system in third-party applications such as dbdiagram and dbdocs can easily render diagrams/documents base on annotations.
• The possibilities is endless. Our DBML will be easier to extend without worry how the mix specific database syntax into the general code.

The ability to register a 0 to Many or 0 to 1 relationship seems to be lacking currently.

This is the only thing preventing me easily being able to use the tool at work and its much better than draw.io so I’d preferably like to be able to!

Is there any plans to support geospatial datatypes in DBML?

We are using it with Postgres and it currently doesn't support any of the PostGIS data types like geom.

Just like you have unique setting for a column, an index setting would be convenient for single-column indexes. Here's an example for a table generated with a fresh Laravel installation:

Table password_resets {
    email varchar [index]
    token varchar [not null]
    created_at timestamp
}

Hi,

Is it possible to use Guids as ID's - I can't seem to find anything related to this in the documentation?

Thanks for putting this tool together, it's been very helpful so far!

I'm modeling a database that's going to include the PostGIS extension with geometry/geography data types. It looks like those data types are not currently supported. Is there any mechanism for defining custom data types within the schema that could then be used as data types in the column definitions?

How can I express constraints? For instance, I need to express that the value of column rating is between >0 and <=5? I see no documentation. Is this possible?

First of all thanks for the amazing work!

Looks like sql2dbml needs support for ALTER TABLE ... ON DELETE/UPDATE

It chokes if you have something like:

ALTER TABLE "foo" ADD CONSTRAINT "FK_blah" FOREIGN KEY ("userId") REFERENCES "user"("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

SyntaxError: Expected ",", ";", "DEFERRABLE", "INITIALLY", or "NOT" but "O" found.

Is there any way to define autoincrement fields? I have used [auto increment] in dbdiagram.io but it doesn't work

There are some way to export the comments together my diagram?

Hi all,

In the near future, we will introduce 4 major concepts for DBML: Schema, Multi-line String, Note and Annotation. Here is the draft proposal of those concepts. Suggestions and ideas are welcome !

Overall Structure of DBML Element

https://dbdiagram.io/d/5de91a62edf08a25543ec0d6

Schema

Schema ecommerce {
  Table users {
    id int [pk]
    name varchar
  }
}

// or

Table ecommerce.users {
  id int [pk]
  name varchar
}

Two syntaxes to define schema:

• Define schema explicitly Schema <schema-name> { ... }
• Implicitly define schema through table name Table <schema-name>.<table-name>

From the time the schema concept is introduced, tables that don't have prefix schema name will belong to the default public schema. Exporting DBML to SQL will not export the default public schema syntax if in the DBML code the public schema is not explicitly defined.

Table users {    // public schema syntax will not export to SQL
  id int [pk]
  name varchar
}

// is the same as

Table public.users {  // public schema syntax will export to SQL.
  id int [pk]
  name varchar
}

Multi-line String

2 types of format for multi-line string:

Block Style

Similar to YAML Block Scalar. https://yaml-multiline.info/.

In DBML, the block string will be indented with respect to the closing (right) bracket }. Syntax could be like this:

Note ecommerce: >+{
  This is schema note.
  This note can span over 
  multiple lines.
}

• Block Style Indicator: indicates how newlines inside the block should behave.

  • literal | style: newlines are kept
  • folded > style: newlines are replaces by spaces

• Block Chomping Indicator: controls what should happen with newlines at the end of the string.

  • clip: put a single newline at the end of the string
  • strip -: remove all newlines at the end of the string
  • keep +: keep all newlines at the end of the string

• Indentation Indicator: number of spaces to indent a block with respect to the closing (right) bracket. This will be guessed automatically from its first line. Need to explicitly specify this indicator if first line starts with extra space. Minimum number of spaces for indentation: 1

Flow Style: single-quoted string

In DBML:

'Several lines of text,\n
containing ''single quotes''. Escapes (like \n) don''t do anything.\n
\n
Newlines can be added by leaving a blank line.\n
··Leading whitespace on lines is ignored.'\n

Result:

Several lines of text, containing 'single quotes'. Escapes (like \n) don't do anything.
Newlines can be added by leaving a blank line. Leading whitespace on lines is ignored.

Note

Two types of note: inline note (already supported for fields) and short-form note

Short-form note

Note <element-name>: <content>

Note ecommerce: 'Contains all tables belong to ecommerce schema'

Note ecommerce.users: 'Contains all user information'

Note ecommerce.users.id: 'Id of user'

// multiple-line string
Note ecommerce: >{
  Contains all tables
  belong to ecommerce schema
}

Inline note

Schema ecommerce [note: 'Contains all tables belong to ecommerce schema'] {
  Table users [note: 'Contains all user information'] {
    id int [pk, note: 'Id of user']
    name varchar
  }
}

Annotation

Annotations helps to associate metadata (information) to the DBML elements.

@<annotation-name> {
  // syntax of each annotation is independent. For example, the syntax of 
  // dbdiagram annotation can be different from the syntax of dbdocs 
  // annotation
}

Example of dbdiagram annotation:

Table users {
  id int [pk]
  name varchar
}

@dbdiagram {
  darkmode: on

  Table users {
    headercolor: #f3f2f9
      position {
        x: 181
        y: 212
      }
  } 
}

Annotation can be placed anywhere in our DBML code. If it is placed inside an element, it will provide metadata for that element.

Table users {
  id int [pk]
  name varchar
  status user_status

  @postgresql {  // annotation for this table users; use for exporting this table to PostgreSQL
    id: [serial]
    Indexes {
      name: [type: gist]
    }
  }

  @mysql {  // annotation for this table users; use for exporting this table to MySQL
    id: [auto_increment]
    storage_type: memory
    Indexes {
      name: [type: hash]
    }
  }
}

Avantages of annotation

• Annotation syntax is independent. Anyone, any team can define their own annotation syntax and write the annotation parser plugin to parse them.
• From the view of DBML, annotation can be treated like comment. It does not affect the structure or the meaning of the database
• Annotation is a way to separate between high level data model (table, schema) and SQL low level implementation (indexes, storage type...)
• One DBML file can contains many kinds of annotation without any problem. For example, one DBML table can be associated with two database annotations (mysql, postgres ... ) for exporting purpose without polluting the original DBML code
• From the technical view, DBML dialect with syntax mixed into original DBML code is hard to implement and maintain. For annotation, we just need to write another specific annotation parser plugin.
• The render system in dbdiagram and dbdocs can render diagrams/documents base on annotations as well.
• The possibilities is endless. Our DBML will be easier to extend without worry how the mix specific database syntax into the general code.

We ran into an odd issue. The following code will fail to export to MSSQL only:

Table true_or_false {
tof boolean [default: 0]
}

It will NOT fail if:

• Exported to MySQL o Postgres.
• tof is declared as "bool" instead of "boolean".
• default value is "false" instead of 0.

So we basically managed to hit the only case it fails!

Thanks for the nice tool!~

There's a small issue when importing the timestamp type into the database when using

 created_at timestamp [default: `now()`]
 updated_at timestamp [default: 'now()']

This will be exported as

created_at timestamp DEFAULT (now()),
updated_at` timestamp DEFAULT 'now()'

Both of the outputs cause error during the import.

Supported syntax are

`created_at` timestamp DEFAULT now(),
`updated_at` timestamp DEFAULT CURRENT_TIMESTAMP

Columns settings describe PK for a single column but refer the reader to Indexes for composite PKs; the Indexes section does not mention composite PKs.

Hi there! I'm interested in using DBML as an metadata container for our Data Warehouse, and it would be interesting if we could add notes and settings for Tables, and custom settings for the Fields.

Something like that:

Table table_name [table_settings] {
field1 field_type [column_settings]
field1 field_type [column_settings]
}

where table_settings and column_settings could be an list of an arbitrary key/values and/or flags, not only the ['note', 'default', 'pk', 'null', 'unique', 'increment'] ones.

Those arbitrary and non-specified keys would have an implementation-dependent interpretation then.

In many databases, especially complex ones, views provide a way to select and/or collate data from multiple tables.

It would be interesting to allow DBML to represent virtual tables, that could be possibly drawn in a different way from regular tables in UI renderers.

Objective

• Support writing long string that can span over multiple lines
• Support writing markdown for DBML Note

Syntax

Multiline string will be defined between triple single quote '''

Note: '''
  # DBML - Database Markup Language

  [![Build Status](https://travis-ci.org/holistics/dbml.svg?branch=master)](https://travis-ci.org/holistics/dbml)
	
	
  DBML (database markup language) is a simple, readable DSL language designed to define database structures.
  For more information, please check out [DBML homepage](https://dbml.org)
'''

• Line breaks: <enter>
• Line continuation \:

Note: '''
  This is a very long long \
note.
'''

=> Result: 

This is a very long long note.

• Escape triple single quote in multi-line string \'''

Note: '''
  Sample note in DBML
	
  Note: \'''
    Note in DBML
  \'''
'''

=> Result:

Sample note in DBML

Note: '''
  Note in DBML
'''

• The number of spaces you use to indent a block string will be the minimum number of leading spaces among all lines. The parser will automatically remove the number of indentation spaces in the final output.

Example:

Note: '
  if (a === b) {   // 2 spaces indentation
    c = a + b;     // 4 spaces
  }                // 2 spaces
'

=> result: automatically remove 2 leading whitespaces at the beginning of each line

if (a ===b) {
  c = a + b;
}

I really like your platform. It is very convenient to map our relational database from code rather than to draw it in Visio(without data types).

I really hope there's a portable version of dbdiagrams.io as electron desktop application. If contributions are welcome, I would like to participate in doing open source for desktop application.

Schema

Overall structure of DBML

Schema syntax

Schema ecommerce {
  Table users {
    id int [pk]
    name varchar
  }

  Note: 'ecommerce schema'
}

// or

Table ecommerce.users {
  id int [pk]
  name varchar
}

Two syntaxes to define schema:

• Define schema explicitly Schema <schema-name> { ... }
• Implicitly define schema through table name Table <schema-name>.<table-name>

From the time the schema concept is introduced, tables, enums, table groups and refs that don't have prefix schema name will belong to the default public schema. Exporting DBML to SQL will not export the default public schema syntax if in the DBML code the public schema is not explicitly defined.

Table users {    // public schema syntax will not export to SQL
  id int [pk]
  name varchar
}

// is the same as

Table public.users {  // public schema syntax will export to SQL.
  id int [pk]
  name varchar
}

Schema contains:

• tables
• enums
• table groups
• refs

References between tables in different schemas are possible:

Table ecommerce.users {
  id int [pk]
  tracking_id int [ref: > snowplow.users.id]  // reference to id of user in schema snowplow
  name varchar
  email varchar
}

Table snowplow.users {
  id int [pk]
  ip varchar
  location varchar
}

Either needs a documentation update or a republishing/register of the cli package.

Docs describing npm install: https://dbdocs.io/?utm_source=dbdiagram&utm_medium=top_menu&utm_campaign=launch_dbdocs

npm install dbdocs

npm ERR! code E404

When I quote identifiers with backticks (a fairly typical MySQL convention), I'm unable to use the CLI to import a schema:

CREATE TABLE `users` (
  `user_id` int(11),
  PRIMARY KEY (`user_id`)
);

$ sql2dbml ~/Documents/db/users.sql
   ERROR: You have a syntax error at "users.sql" line 1 column 14. Expected "IF" or valid table name but "`" found.

It would be great if the cardinality of relationships could include whether a relationship is mandatory/optional.

Some reference here

This means that at either ends of a relationship we can express the minimum cardinality and the maximum cardinality to show something like

[min_card]...[max_card]

For example, let's take the relationship between the entities STUDENT and TEACHER.
Each teacher can have 0 to many students so the notation would show : 0...*
each student can have 1 to many teachers (let's suppose) so the notation would show: 1...*