Skip to main content
← Back to index page

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/!