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
pdina14mo 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
ookamiiixd13mo ago
Maybe it's something related with this limitation?
cHef.41000
cHef.4100012mo ago
Did you figure anything out for this? Did you just manually add the index line to the migration .sql file? @pdina
Berzerker
Berzerker9mo ago
anyone else figured it out?
Berzerker
Berzerker9mo ago
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
Berzerker9mo ago
does anyone have idea how to escape those " ... " around the column definiton for ts_vector so it's a valid SQL?
energistic
energistic6mo 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
}