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

pdinaOP•2y 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•2y ago

Maybe it's something related with this limitation?

cHef.41000•17mo ago

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

Berzerker•14mo ago

anyone else figured it out?

Berzerker•14mo 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•14mo ago

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

energistic•12mo 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