Add single-column, composite, and unique indexes to your tables for better query performance.

Indexes

Indexes improve query performance by allowing the database to find rows faster. The Schema Builder provides methods for creating single-column, composite, and unique indexes.

Single-Column Index

Create an index on a single column using bp.Index():

bp.Index("email")

Pass the name of the column to index. The index name is generated automatically. To override it, chain .Named("...") (see Custom Index Names).

Composite Index

Index multiple columns together by passing additional column names:

bp.Index("user_id", "status")

Composite indexes are useful for queries that filter or sort on multiple columns simultaneously.

Unique Index

Create a unique index that enforces uniqueness across one or more columns:

bp.UniqueIndex("email")

For composite unique indexes:

bp.UniqueIndex("user_id", "plan_id")

This ensures no two rows can have the same combination of user_id and plan_id.

The .Unique() column modifier adds a unique constraint on a single column. Use bp.UniqueIndex() when you need a composite unique constraint across multiple columns.

Index Naming

By default, index names are generated automatically from the index type, the table name, and the column names. The name uses a type prefix followed by the table and each column:

Method	Prefix	Example (table `users`)
`Index`	`idx`	`idx_users_email`
`UniqueIndex`	`uniq`	`uniq_users_email`
`FulltextIndex`	`ft`	`ft_articles_title_body`
`SpatialIndex`	`sp`	`sp_places_location`

These generated names are what you pass to bp.DropIndex() when removing an index.

When the auto-generated name is too long, collides, or you simply prefer your own convention, chain .Named("...") on any index method to override it. An empty string is ignored, so the default name is preserved.

bp.Index("email").Named("idx_users_email_lower")
bp.UniqueIndex("user_id", "plan_id").Named("uq_subscription")
bp.FulltextIndex("title", "body").Named("ft_articles_search")

The custom name is the one you pass to bp.DropIndex() later:

s.Alter("users", func(bp *schema.Blueprint) {
    bp.DropIndex("idx_users_email_lower")
})

.Named() only changes the index name — the indexed columns and index type stay exactly as you declared them.

Complete Example

migrations/20240401_create_articles_table.go

package migrations

import (
    "github.com/gopackx/go-migration/pkg/schema"
)

type CreateArticlesTable struct{}

func (m *CreateArticlesTable) Up(s *schema.Builder) error {
    return s.Create("articles", func(bp *schema.Blueprint) {
        bp.ID()
        bp.BigInteger("author_id").Unsigned()
        bp.BigInteger("category_id").Unsigned()
        bp.String("title", 255)
        bp.String("slug", 255)
        bp.Text("content")
        bp.String("status", 20).Default("draft")
        bp.Timestamp("published_at").Nullable()
        bp.Timestamps()

        // Single-column index
        bp.Index("author_id")

        // Composite index for filtering by author and status
        bp.Index("author_id", "status")

        // Unique index on slug
        bp.UniqueIndex("slug")

        // Composite unique index
        bp.UniqueIndex("author_id", "title")
    })
}

func (m *CreateArticlesTable) Down(s *schema.Builder) error {
    return s.DropIfExists("articles")
}

Adding Indexes to Existing Tables

You can add indexes in an Alter call:

s.Alter("users", func(bp *schema.Blueprint) {
    bp.Index("created_at")
})

Fulltext Index

Create a fulltext index for text search on one or more columns:

bp.FulltextIndex("title", "body")

Pass the column names you want to include in the fulltext index. The generated SQL depends on the database grammar:

Database	Generated SQL
PostgreSQL	`CREATE INDEX ... ON "table" USING GIN (to_tsvector('english', "col"))`
MySQL	`FULLTEXT KEY ("title", "body")` (inline) or `CREATE FULLTEXT INDEX ...` (standalone)
SQLite	Returns an error — not supported

migrations/20240501_create_articles_table.go

s.Create("articles", func(bp *schema.Blueprint) {
    bp.ID()
    bp.String("title", 255)
    bp.Text("body")
    bp.Timestamp("created_at").Nullable()

    // Fulltext index for search
    bp.FulltextIndex("title", "body")
})

Spatial Index

Create a spatial index for geometry or geography columns:

bp.SpatialIndex("location")

The generated SQL depends on the database grammar:

Database	Generated SQL
PostgreSQL	`CREATE INDEX ... ON "table" USING GIST ("location")`
MySQL	`SPATIAL KEY ("location")` (inline) or `CREATE SPATIAL INDEX ...` (standalone)
SQLite	Returns an error — not supported

migrations/20240601_create_places_table.go

s.Create("places", func(bp *schema.Blueprint) {
    bp.ID()
    bp.String("name", 255)
    bp.String("location", 255)
    bp.Timestamp("created_at").Nullable()

    // Spatial index for geographic queries
    bp.SpatialIndex("location")
})

SQLite does not support fulltext or spatial indexes. Calling bp.FulltextIndex() or bp.SpatialIndex() with the SQLite grammar will return an error. If you need to support SQLite alongside other databases, consider using conditional logic or separate migration files.

What's Next?

Foreign Keys — define relationships between tables
Utility Operations — drop, rename, and inspect tables

Indexes

On this page