[BUG]: Can't create index concurrently due to transaction block

[MatanYadaev]

What version of drizzle-orm are you using?

0.27.0

What version of drizzle-kit are you using?

0.19.3

Describe the Bug

Creating an index with the CONCURRENTLY option throws this error: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Expected behavior

There's should be an option to run a specific migration without a transaction.

Environment & setup

Postgres 15.3

[AndriiSherman]

Agree, will add this option

[clovis1122]

Was this implemented?

[thedewpoint]

@AndriiSherman I'm also dealing with this bug now, any update on it? Any chance we can prioritize?

[nklisch]

Could the orm not be smart enough to move this step outside a transaction? That may be the better choice? Ran into this issue and had to manually apply

[dakdevs]

The migration kit should probably just include the transaction statements in the migration files. So it can enter and exit it as-needed?

[dakdevs]

Curious if anyone has a workaround for this? We have a table with over 2m rows and we keep getting deadlocks when dealing with index creations.

[AntonioAngelino]

@Vinlock did you find a workaround?

[thedewpoint]

@AndriiSherman I know that this work is being prioritized, but can you recommend any workaround? I'm dealing with a table with millions of records and the only option I can think of is to run this sql manually and then run the migration (which shouldn't run since it checks if the index exists first)

I'm trying to avoid this though

[thedewpoint]

cc @L-Mario564 since i saw you were taking a look at this as well

[hsab]

@AndriiSherman I use a technique, where I use drizzle index generation to have a colocated, single-source-of-truth schema definition. I then have a script that extracts all CREATE INDEX ... lines from the drizzle-generated migrations to a separate custom sql file. To migrate, I use a custom migrate.ts script instead of relying on drizzle-kit. The migrate script will handle the drizzle-generated migration files (which at this point have no index definitions anymore), and then will execute the custom sql file with the indexes, ensuring that each CREATE INDEX... statement is executed as a separate transaction. Hope this helps.

schema.ts

export const profiles = pgTable(
  'profiles',
  {
    id: uuid(),
  },
  (table) => [index().on(table.id).concurrently()],
);

package.json

{
  ...
  "scripts": {
    "generate": "npx drizzle-kit generate && tsx scripts/extractIndexes.ts",
    "migrate": "cross-env DB_MIGRATING=true tsx ./migrate.ts"
  },
  ...
}

extractIndexes.ts

import path from 'path';
import fs from 'fs';

const migrationsPath = path.join(process.cwd(), 'migrations');
const customMigrationsPath = path.join(process.cwd(), 'customMigrations');
const indexesFile = path.join(customMigrationsPath, '0000_CUSTOM_indexes.sql');

const sqlFiles = fs
  .readdirSync(migrationsPath)
  .filter((file: string) => file.endsWith('.sql'))
  .map((file: string) => path.join(migrationsPath, file));

for (const sqlFile of sqlFiles) {
  const sqlData = fs.readFileSync(sqlFile, 'utf8');

  // Find all CREATE INDEX statements (case insensitive)
  const indexLines = sqlData
    .split('\n')
    .filter((line) => /^\s*create\s+index/i.test(line.trim()))
    .map((line) => line.trim());

  if (indexLines.length > 0) {
    // Append the found CREATE INDEX statements to the custom indexes file
    fs.appendFileSync(
      indexesFile,
      `\n\n--> statement-breakpoint\n${indexLines
        .map((line) => line.replace('--> statement-breakpoint', ''))
        .join('\n\n--> statement-breakpoint\n')}`,
      'utf8',
    );

    // Remove the CREATE INDEX lines from the original file
    const updatedSqlData = sqlData
      .split('\n')
      .filter((line) => !indexLines.includes(line.trim()))
      .join('\n');

    fs.writeFileSync(sqlFile, updatedSqlData, 'utf8');

    console.log(
      `Extracted ${indexLines.length} index(es) from ${path.basename(sqlFile)}`,
    );
  }
}

migrate.ts

/* eslint-disable no-await-in-loop */
import path from 'path';
import fs from 'fs';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { sql } from 'drizzle-orm';
import config from './drizzle.config';
import { db } from '.';

if (!process.env.DB_MIGRATING) {
  throw new Error(
    'You must set DB_MIGRATING to "true" when running migrations',
  );
}

if (
  process.env.DATABASE_URL !==
  'postgresql://postgres:[email protected]:54322/postgres'
) {
  throw new Error('You are running migrations in production');
}

// First run regular drizzle migrations
await migrate(db, {
  migrationsFolder: './migrations',
  migrationsSchema: config.migrations?.schema,
  migrationsTable: config.migrations?.table,
});

// Then handle custom migrations that need to run outside transactions
const customMigrationsPath = path.join(process.cwd(), 'customMigrations');
const sqlFiles = fs
  .readdirSync(customMigrationsPath)
  .filter((file: string) => file.endsWith('.sql'))
  .map((file: string) => path.join(customMigrationsPath, file));

// Use the raw connection for executing CONCURRENT index creation
for (const sqlFile of sqlFiles) {
  const sqlData = fs.readFileSync(sqlFile, 'utf8');
  const statements = sqlData.split('--> statement-breakpoint');

  // Execute each statement separately without transaction
  for (const statement of statements) {
    const trimmedStatement = statement.trim();
    if (trimmedStatement) {
      await db.execute(sql.raw(trimmedStatement));
    }
  }
}

await db.$client.end();

[gaspar09]

Another hack, to deal with drizzle auto adding BEGIN statements.

You can add COMMIT;--> statement-breakpoint before the CREATE INDEX CONCURRENTLY command

e.g.

COMMIT;--> statement-breakpoint
CREATE INDEX CONCURRENTLY "indexName" ON "tableName" ("columnName");

[thedewpoint]

Another hack, to deal with drizzle auto adding BEGIN statements.

You can add COMMIT;--> statement-breakpoint before the CREATE INDEX CONCURRENTLY command

e.g.

COMMIT;--> statement-breakpoint
CREATE INDEX CONCURRENTLY "indexName" ON "tableName" ("columnName");

I'll give this a shot thank you