为什么不用Diagrams,后面笔者发现DBML的局限性有点大，如果时个人数据库设计不如直接画图，如果是团队使用学习成本又不小，很纠结

DBML转可视化网站：

https://dbdocs.io/?utm_source=dbdiagram&utm_medium=promo_button

DBML语法说明文档：

https://www.dbml.org/home/#dbml-history
https://www.dbml.org/docs/#table-definition

备注：

1.字段后可以加的括号内内容：

  • note: 'string to add notes': add a metadata note to this column
  • primary key or pk: mark a column as primary key. For composite primary key, refer to the ‘Indexes’ section
  • null or not null: mark a column null or not null
  • unique: mark the column unique
  • default: some_value: set a default value of the column, please refer to the ‘Default Value’ section below

  • increment: mark the column as auto-increment

    2.可以设置的默认值

  • number value starts blank: default: 123 or default: 123.456

  • string value starts with single quotes: default: 'some string value'
  • expression value is wrapped with parenthesis: default:now() - interval ‘5 days’``

  • boolean (true/false/null): default: false or default: null

    3.索引的使用

  • Index with single field (with index name): CREATE INDEX on users (created_at)

  • Index with multiple fields (composite index): CREATE INDEX on users (created_at, country)
  • Index with an expression: CREATE INDEX ON films ( first_name + last_name )
  • (bonus) Composite index with expression: CREATE INDEX ON users ( country, (lower(name)) )
  1. Table bookings {
  2.   id integer
  3.   country varchar
  4.   booking_date date
  5.   created_at timestamp
  7.   indexes {
  8.       (id, country) [pk] // composite primary key
  9.       created_at [note: 'Date']
  10.       booking_date
  11.       (country, booking_date) [unique]
  12.       booking_date [type: hash]
  13.       (`id*2`)
  14.       (`id*3`,`getdate()`)
  15.       (`id*3`,id)
  16.   }
  17. }

4.外键的使用

三种类型：

  • <: one-to-many. E.g: users.id < posts.user_id
  • >: many-to-one. E.g: posts.user_id > users.id
  • -: one-to-one. E.g: users.id - user_infos.user_id

三种语法：

  1. //Long form
  2. Ref name_optional {
  3.   table1.column1 < table2.column2
  4. }
  6. //Short form:
  7. Ref name_optional: table1.column1 < table2.column2
  9. // Inline form
  10. Table posts {
  11.     id integer
  12.     user_id integer [ref: > users.id]
  13. }
  15. //设置级联关系
  16. Ref: products.merchant_id > merchants.id [delete: cascade, update: no action]

级联关系：

  • delete / update: cascade | restrict | set null | set default | no action
    Define referential actions. Similar to ON DELETE/UPDATE CASCADE/... in SQL.

多对多关系：
在DBML中视为两个多对一关系

5.备注

三种备注：

项目备注：
  2. Project DBML {
  3.   Note: '''
  4.     # DBML - Database Markup Language
  5.     DBML (database markup language) is a simple, readable DSL language designed to define database structures.
  7.     ## Benefits
  9.     * It is simple, flexible and highly human-readable
  10.     * It is database agnostic, focusing on the essential database structure definition without worrying about the detailed syntaxes of each database
  11.     * Comes with a free, simple database visualiser at [dbdiagram.io](http://dbdiagram.io)
  12.   '''
  13. }

表格备注：
  1. Table users {
  2.   id int [pk]
  3.   name varchar
  5.   Note: 'Stores user data'
  6.     // or
  7.   Note {
  8.     'This is a note of this table'
  9.   }
  10. }

数据列备注
  1. column_name column_type [note: 'replace text here']

6.枚举

用于定义表明状态，种类等

  1. enum job_status {
  2.     created [note: 'Waiting to be processed']
  3.     running
  4.     done
  5.     failure
  6. }
  8. Table jobs {
  9.     id integer
  10.     status job_status
  11. }

7.别名

  1. Table very_long_user_table as U {
  2.     ...
  3. }
  5. Ref: U.id < posts.user_id

DBML个人使用Vscode快捷语法：

在文件 > 首选项 > 用户代码片段(File->Preferences->User Snippets)选择你要修改哪种文件的模板，然后修改JSON文件即可