Proposal: Reusable Table Definitions

[NQPhuc]

Hi everyone,

To make defining common database patterns easier and more efficient in DBML, we're proposing a new feature: TablePartial.

The core idea is to allow you to define reusable blocks of columns, indexes, and settings that can be easily included in multiple table definitions. Think of standard audit trails (created_at, updated_at), soft delete fields, or common indexing patterns.

This syntax is inspired by and aims to address many of the ideas and suggestions raised in the following discussions and issues:

• Decorator to auto inject columns #675
• Inheritance syntax for common columns #117
• https://community.dbdiagram.io/t/add-ability-for-tables-to-be-extended/1731
• https://community.dbdiagram.io/t/table-inheritance/3118

Proposed Syntax:

Use TablePartial to define reusable blocks and ~<partial_name> to include them in tables.

TablePartial <partial_name> [<attribute_list>] {
  <column_definitions>
  <index_definitions>
}

Table <table_name> [<attribute_list>] {
  ~<partial_name>
  <column_definitions>
  ~<another_partial_name>
  <index_definitions>
}

Example:

TablePartial base_template [headerColor: #ff0000] {
  id int [pk, not null]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
}

TablePartial soft_delete_template {
  delete_status boolean [not null]
  deleted_at timestamp [default: `now()`]
}

TablePartial email_index {
  email varchar [unique]

  indexes {
    email [unique]
  }
}

Table users {
  ~base_template
  ~email_index
  name varchar
  ~soft_delete_template
}

// Result
Table users [headerColor: #ff0000] {
  id int [pk, not null]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
  email varchar [unique]
  name varchar
  delete_status boolean [not null]
  deleted_at timestamp [default: `now()`]

  indexes {
    email [unique]
  }
}

Design Considerations:

We explored various ways to achieve reusability. The chosen approach, using TablePartial with the ~ include syntax, functions like composition or mixins - the contents of the partial are effectively copied directly into the target table definition ("auto-inject").

We opted for this composition model over implementing OOP-style table inheritance for several reasons:

1. Simplicity & Clarity: Composition leads to a very clear final table structure. All columns and indexes are explicitly present in the resulting definition, making it easy to understand the final schema without tracing an inheritance hierarchy.
2. Alignment with DBML's Purpose: DBML is primarily a database-agnostic, declarative language for defining database schemas. True inheritance introduces complexities that don't directly map well to standard relational database structures and could complicate the language unnecessarily.
3. Database Reality: Relational databases don't usually support native table inheritance. The composition approach more closely reflects how reusable patterns (like standard audit columns) are implemented in practice—by adding the necessary columns directly to each relevant table.
4. Flexibility: It allows mixing and matching multiple partials easily without the potential conflicts or complexities associated with multiple inheritance paradigms.

We'd love your feedback on this proposal! Please share your thoughts below.

Thanks!

[TeaNguyen]

Implemented in #709