Create GIN index in Postgres

I need to create this index in postgres:

CREATE INDEX users_name_gin_trgm_idx ON users USING gin (name gin_trgm_ops);

CREATE INDEX users_name_gin_trgm_idx ON users USING gin (name gin_trgm_ops);

and this is the code I have:

export const UserModel = pgTable(
    'users',
    {
      id: serial('id').primaryKey(),
      name: varchar('name'),
      email: varchar('email', { length: 255 })
    },
    (table) => {
      return {
        name: index('name').on(table.name)
        // SPACE FOR INDEX DECLARATION!
      };
    }
);

export const UserModel = pgTable(
    'users',
    {
      id: serial('id').primaryKey(),
      name: varchar('name'),
      email: varchar('email', { length: 255 })
    },
    (table) => {
      return {
        name: index('name').on(table.name)
        // SPACE FOR INDEX DECLARATION!
      };
    }
);

Reading the docs on GitHub I was able to create "normal" index, but it's not clear how to specify index configuration options (like the type of index etc).

7 Replies

pdina•14mo ago

what I tried (not working):

...
(table) => {
    return {
      name: index('name')
        .on(table.name)
        .using(sql`gin (${table.name} gin_trgm_ops)`)
      };
    }
}
...

...
(table) => {
    return {
      name: index('name')
        .on(table.name)
        .using(sql`gin (${table.name} gin_trgm_ops)`)
      };
    }
}
...

ookamiiixd•14mo ago

Maybe it's something related with this limitation?

cHef.41000•12mo ago

Did you figure anything out for this? Did you just manually add the index line to the migration .sql file? @pdina

Berzerker•10mo ago

anyone else figured it out?

Berzerker•10mo ago

https://github.com/drizzle-team/drizzle-orm/issues/247

GitHub

Add Full-Text Search capability · Issue #247 · drizzle-team/drizzle...

This is a feature request for Full-Text Search functionality to perform a fulltext search on specific fields. Ideally with support for using GIN/GIST indexes to speed up full text search.

Berzerker•10mo ago

does anyone have idea how to escape those " ... " around the column definiton for ts_vector so it's a valid SQL?

energistic•7mo ago

I wrote a little script to remove the quotes and add a GIN index

import fs from 'node:fs/promises'
import path from 'node:path'

const migrationsFolder = path.join('.', 'drizzle', 'migrations')
const files = await fs.readdir(migrationsFolder)
const sqlFiles = files
    .filter(file => file.endsWith('.sql'))
    .map(file => path.join(migrationsFolder, file))

for (const file of sqlFiles) {
    const fileContents = await fs.readFile(file, 'utf8')

    const tablesWithRawGins = findTableNameWithVectorText(fileContents)

    const gins = tablesWithRawGins
        .map(
            tableName =>
                `CREATE INDEX content_search_idx ON ${tableName} USING GIN (vector_text);`,
        )
        .join('\n')

    const regex =
        /"vector_text" "tsvector GENERATED ALWAYS AS \(to_tsvector\('english', name\)\) STORED"/g
    const newFileContents = fileContents.replace(
        regex,
        `"vector_text" tsvector GENERATED ALWAYS AS (to_tsvector('english', name)) STORED`,
    )
    await fs.writeFile(file, newFileContents + '\n' + gins)
}

function findTableNameWithVectorText(input: string): string[] {
    const lines = input.split('\n')
    let tableName: string | null = null
    let foundTableNames = []

    for (let i = 0; i < lines.length; i++) {
        const line = lines[i].trim()

        // Check for CREATE TABLE line and capture table name
        if (line.startsWith('CREATE TABLE IF NOT EXISTS') && line.endsWith('(')) {
            const match = line.match(/"([^"]+)"/)
            if (match) {
                tableName = match[1]
            }
        }

        // Check for vector_text line
        if (
            line ===
            '"vector_text" "tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', name)) STORED"'
        ) {
            foundTableNames.push(tableName as string)
        }
    }

    return foundTableNames
}

import fs from 'node:fs/promises'
import path from 'node:path'

const migrationsFolder = path.join('.', 'drizzle', 'migrations')
const files = await fs.readdir(migrationsFolder)
const sqlFiles = files
    .filter(file => file.endsWith('.sql'))
    .map(file => path.join(migrationsFolder, file))

for (const file of sqlFiles) {
    const fileContents = await fs.readFile(file, 'utf8')

    const tablesWithRawGins = findTableNameWithVectorText(fileContents)

    const gins = tablesWithRawGins
        .map(
            tableName =>
                `CREATE INDEX content_search_idx ON ${tableName} USING GIN (vector_text);`,
        )
        .join('\n')

    const regex =
        /"vector_text" "tsvector GENERATED ALWAYS AS \(to_tsvector\('english', name\)\) STORED"/g
    const newFileContents = fileContents.replace(
        regex,
        `"vector_text" tsvector GENERATED ALWAYS AS (to_tsvector('english', name)) STORED`,
    )
    await fs.writeFile(file, newFileContents + '\n' + gins)
}

function findTableNameWithVectorText(input: string): string[] {
    const lines = input.split('\n')
    let tableName: string | null = null
    let foundTableNames = []

    for (let i = 0; i < lines.length; i++) {
        const line = lines[i].trim()

        // Check for CREATE TABLE line and capture table name
        if (line.startsWith('CREATE TABLE IF NOT EXISTS') && line.endsWith('(')) {
            const match = line.match(/"([^"]+)"/)
            if (match) {
                tableName = match[1]
            }
        }

        // Check for vector_text line
        if (
            line ===
            '"vector_text" "tsvector GENERATED ALWAYS AS (to_tsvector(\'english\', name)) STORED"'
        ) {
            foundTableNames.push(tableName as string)
        }
    }

    return foundTableNames
}

Gaming

Programming

Create GIN index in Postgres