Indexes

Multiple Fields

Indexes can be configured on one or more fields in order to improve speed of data retrieval, or defining uniqueness.

  1. package schema
  3. import (
  4.     "entgo.io/ent"
  5.     "entgo.io/ent/schema/index"
  6. )
  8. // User holds the schema definition for the User entity.
  9. type User struct {
  10.     ent.Schema
  11. }
  13. func (User) Indexes() []ent.Index {
  14.     return []ent.Index{
  15.         // non-unique index.
  16.         index.Fields("field1", "field2"),
  17.         // unique index.
  18.         index.Fields("first_name", "last_name").
  19.             Unique(),
  20.     }
  21. }

Note that for setting a single field as unique, use the Unique method on the field builder as follows:

  1. func (User) Fields() []ent.Field {
  2.     return []ent.Field{
  3.         field.String("phone").
  4.             Unique(),
  5.     }
  6. }

Index On Edges

Indexes can be configured on composition of fields and edges. The main use-case is setting uniqueness on fields under a specific relation. Let’s take an example:

er-city-streets

In the example above, we have a City with many Streets, and we want to set the street name to be unique under each city.

ent/schema/city.go

  1. // City holds the schema definition for the City entity.
  2. type City struct {
  3.     ent.Schema
  4. }
  6. // Fields of the City.
  7. func (City) Fields() []ent.Field {
  8.     return []ent.Field{
  9.         field.String("name"),
  10.     }
  11. }
  13. // Edges of the City.
  14. func (City) Edges() []ent.Edge {
  15.     return []ent.Edge{
  16.         edge.To("streets", Street.Type),
  17.     }
  18. }

ent/schema/street.go

  1. // Street holds the schema definition for the Street entity.
  2. type Street struct {
  3.     ent.Schema
  4. }
  6. // Fields of the Street.
  7. func (Street) Fields() []ent.Field {
  8.     return []ent.Field{
  9.         field.String("name"),
  10.     }
  11. }
  13. // Edges of the Street.
  14. func (Street) Edges() []ent.Edge {
  15.     return []ent.Edge{
  16.         edge.From("city", City.Type).
  17.             Ref("streets").
  18.             Unique(),
  19.     }
  20. }
  22. // Indexes of the Street.
  23. func (Street) Indexes() []ent.Index {
  24.     return []ent.Index{
  25.         index.Fields("name").
  26.             Edges("city").
  27.             Unique(),
  28.     }
  29. }

example.go

  1. func Do(ctx context.Context, client *ent.Client) error {
  2.     // Unlike `Save`, `SaveX` panics if an error occurs.
  3.     tlv := client.City.
  4.         Create().
  5.         SetName("TLV").
  6.         SaveX(ctx)
  7.     nyc := client.City.
  8.         Create().
  9.         SetName("NYC").
  10.         SaveX(ctx)
  11.     // Add a street "ST" to "TLV".
  12.     client.Street.
  13.         Create().
  14.         SetName("ST").
  15.         SetCity(tlv).
  16.         SaveX(ctx)
  17.     // This operation fails because "ST"
  18.     // was already created under "TLV".
  19.     if err := client.Street.
  20.         Create().
  21.         SetName("ST").
  22.         SetCity(tlv).
  23.         Exec(ctx); err == nil {
  24.         return fmt.Errorf("expecting creation to fail")
  25.     }
  26.     // Add a street "ST" to "NYC".
  27.     client.Street.
  28.         Create().
  29.         SetName("ST").
  30.         SetCity(nyc).
  31.         SaveX(ctx)
  32.     return nil
  33. }

The full example exists in GitHub.

Index On Edge Fields

Currently Edges columns are always added after Fields columns. However, some indexes require these columns to come first in order to achieve specific optimizations. You can work around this problem by making use of Edge Fields.

  1. // Card holds the schema definition for the Card entity.
  2. type Card struct {
  3.     ent.Schema
  4. }
  5. // Fields of the Card.
  6. func (Card) Fields() []ent.Field {
  7.     return []ent.Field{
  8.         field.String("number").
  9.             Optional(),
  10.         field.Int("owner_id").
  11.             Optional(),
  12.     }
  13. }
  14. // Edges of the Card.
  15. func (Card) Edges() []ent.Edge {
  16.     return []ent.Edge{
  17.         edge.From("owner", User.Type).
  18.             Ref("card").
  19.             Field("owner_id").
  20.             Unique(),
  21.     }
  22. }
  23. // Indexes of the Card.
  24. func (Card) Indexes() []ent.Index {
  25.     return []ent.Index{
  26.         index.Fields("owner_id", "number"),
  27.     }
  28. }

Dialect Support

Dialect specific features are allowed using annotations. For example, in order to use index prefixes in MySQL, use the following configuration:

  1. // Indexes of the User.
  2. func (User) Indexes() []ent.Index {
  3.     return []ent.Index{
  4.         index.Fields("description").
  5.             Annotations(entsql.Prefix(128)),
  6.         index.Fields("c1", "c2", "c3").
  7.             Annotations(
  8.                 entsql.PrefixColumn("c1", 100),
  9.                 entsql.PrefixColumn("c2", 200),
  10.             )
  11.     }
  12. }

The code above generates the following SQL statements:

  1. CREATE INDEX `users_description` ON `users`(`description`(128))
  3. CREATE INDEX `users_c1_c2_c3` ON `users`(`c1`(100), `c2`(200), `c3`)

Atlas Support

Starting with v0.10, Ent running migration with Atlas. This option provides more control on indexes such as, configuring their types or define indexes in a reverse order.

  1. func (User) Indexes() []ent.Index {
  2.     return []ent.Index{
  3.         index.Fields("c1").
  4.             Annotations(entsql.Desc()),
  5.         index.Fields("c1", "c2", "c3").
  6.             Annotations(entsql.DescColumns("c1", "c2")),
  7.         index.Fields("c4").
  8.             Annotations(entsql.IndexType("HASH")),
  9.         // Enable FULLTEXT search on MySQL,
  10.         // and GIN on PostgreSQL.
  11.         index.Fields("c5").
  12.             Annotations(
  13.                 entsql.IndexTypes(map[string]string{
  14.                     dialect.MySQL:    "FULLTEXT",
  15.                     dialect.Postgres: "GIN",
  16.                 }),
  17.             ),
  18.         // For PostgreSQL, we can include in the index
  19.         // non-key columns.
  20.         index.Fields("workplace").
  21.             Annotations(
  22.                 entsql.IncludeColumns("address"),
  23.             ),
  24.         // Define a partial index on SQLite and PostgreSQL.
  25.         index.Fields("nickname").
  26.             Annotations(
  27.                 entsql.IndexWhere("active"),
  28.             ),  
  29.         // Define a custom operator class.
  30.         index.Fields("phone").
  31.             Annotations(
  32.                 entsql.OpClass("bpchar_pattern_ops"),
  33.             ),
  34.     }
  35. }

The code above generates the following SQL statements:

  1. CREATE INDEX `users_c1` ON `users` (`c1` DESC)
  3. CREATE INDEX `users_c1_c2_c3` ON `users` (`c1` DESC, `c2` DESC, `c3`)
  5. CREATE INDEX `users_c4` ON `users` USING HASH (`c4`)
  7. -- MySQL only.
  8. CREATE FULLTEXT INDEX `users_c5` ON `users` (`c5`)
  10. -- PostgreSQL only.
  11. CREATE INDEX "users_c5" ON "users" USING GIN ("c5")
  13. -- Include index-only scan on PostgreSQL.
  14. CREATE INDEX "users_workplace" ON "users" ("workplace") INCLUDE ("address")
  16. -- Define partial index on SQLite and PostgreSQL.
  17. CREATE INDEX "users_nickname" ON "users" ("nickname") WHERE "active"
  19. -- PostgreSQL only.
  20. CREATE INDEX "users_phone" ON "users" ("phone" bpchar_pattern_ops)

Storage Key

Like Fields, custom index name can be configured using the StorageKey method. It’s mapped to a index name in SQL dialects.

  1. func (User) Indexes() []ent.Index {
  2.     return []ent.Index{
  3.         index.Fields("field1", "field2").
  4.             StorageKey("custom_index"),
  5.     }
  6. }