Proposal: Annotation for DBML

[phuongduyphan]

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.

[TheKnarf]

Annotations might also help support database migrations #61.

I can imagine a few different annotations for migrations

• Renamed

Table users {
  @Renamed(from="fullname")
  name varchar
}

That way, once a migration command is ran to gennerate a migration script the tool knows not to drop the fullname column and create a new name, but instead to rename it (similarly for tables).

• DropOnMigration

@DropOnMigration
Table something_cache {
   id varchar
   last_updated date
}

Knows to always drop the table (or column) and recreate it on every migration.

• NotARenamed

Table users {
  @Renamed(from="nickname")
  name varchar
  @NotARename
  nickname varchar
}

Allows you when a migration is created to rename nickname into the name column, and then create a new nickname column.

Then once a migration file is created you can go in and strip those annotations.

This is just one idea for how one could better support migrations, but I do like the idea of annotations to be able to extend dbml.

[TheKnarf]

Maybe the dbml toolchain could have some sort of middleware layer where plugins could come in and announce which annotations they hook into and add additional abilities.

[johnlawlundqb]

Was there any progress on this concept?

[xingftw]

bump