🔍 Support Index for Tables

1. Users can define index using DBML in the code editor
2. Users can export DBML to SQL with the indexes defined as SQL

There are 3 types of index definitions:

Example 1 with PostgreSQL

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

• Index with multiple fields (composite index):
  CREATE INDEX on users (created_at, country)

• Index with an expression:
  CREATE INDEX ON users (lower(name))

• (bonus) Composite index with expression:

  CREATE INDEX ON users ( country, (lower(name)) )

Indexes {
	created_at [name: "Date"]
	(created_at, country)
	`lower(name)`
    (country,`lower(name)`)
	(country) [unique]
	booking_date [type: btree]
}

Index Settings

• type: type of index (btree, gin, gist, hash depending on DB), we only accept type Btree and Hash for now.
• name: name of index
• unique: unique index

Example 2

Users can define single or multi-column indexes. Example 2 shows a multi-column index.

Table products {
  id int [pk]
  name varchar
  merchant_id int [not null]
  price int
  status varchar
  created_at datetime [default: `now()`]

  Indexes {
    (merchant_id, status) [name:"product_status"]
    id [unique]
  }
}

In the visual plane,

Share with us what you think of our Index syntax or any other releases at our new community forum on https://community.dbdiagram.io/!