[FEATURE]: add new generateBaseSql config which allows user to generate a schema only SQL file useful for provisioning an empty DB for testing purposes

[capaj]

Feature hasn't been suggested before.

• I have verified this feature I'm about to request hasn't been suggested before.

Describe the enhancement you want to request

ATM I use this script to generate a base SQL for testing purposes:

#!/usr/bin/env bun
/**
 * Generate base.sql from current schema using drizzle-kit
 * This creates a clean SQL file with CREATE TABLE statements for faster test database initialization
 *
 * The approach:
 * 1. Create a temporary directory for drizzle to generate a fresh migration
 * 2. Run drizzle-kit generate with a temporary config
 * 3. Copy the generated SQL to base.sql
 * 4. Clean up temporary files
 */

import { $ } from 'bun'
import { existsSync, readdirSync } from 'node:fs'
import { join } from 'path'
import { fileURLToPath } from 'url'

const __dirname = fileURLToPath(new URL('.', import.meta.url))
const migrationsFolder = join(__dirname, '../../drizzle')
const baseFilePath = join(migrationsFolder, 'base.sql')
const tempDir = join(__dirname, '../../.temp-drizzle')
const projectRoot = join(__dirname, '../..')
const tempConfigPath = join(__dirname, '../../drizzle.temp.config.ts')
const metaDir = join(migrationsFolder, 'meta')
const tempMetaBackupDir = join(__dirname, '../../.temp-drizzle-meta')

async function generateBaseSql() {
  console.log('Generating base.sql from schema...')

  const metaExisted = existsSync(metaDir)
  let metaBackedUp = false

  try {
    // Create temporary directory for fresh migration
    await $`rm -rf ${tempDir}`.quiet()
    await $`mkdir -p ${tempDir}`.quiet()

    await $`rm -rf ${tempMetaBackupDir}`.quiet()
    if (metaExisted) {
      await $`mkdir -p ${tempMetaBackupDir}`.quiet()
      await $`cp -R ${metaDir}/. ${tempMetaBackupDir}`.quiet()
      metaBackedUp = true
    }

    // Create a temporary drizzle config that points to temp directory
    const tempConfig = `
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema/*',
  out: './.temp-drizzle',
  dbCredentials: {
    url: 'postgresql://dummy:dummy@localhost:5432/dummy'
  }
})
`
    await Bun.write(tempConfigPath, tempConfig)

    // Run drizzle-kit generate with temp config
    console.log('Running drizzle-kit generate...')
    await $`cd ${projectRoot} && bunx drizzle-kit generate --config=drizzle.temp.config.ts`.quiet()

    // Read the generated migration file
    const files = readdirSync(tempDir)
    const sqlFiles = files.filter((f: string) => f.endsWith('.sql'))

    if (sqlFiles.length === 0) {
      throw new Error('No SQL file generated by drizzle-kit')
    }

    // Read the first (and should be only) SQL file
    const generatedSql = await Bun.file(join(tempDir, sqlFiles[0]!)).text()

    // Filter out PL/pgSQL functions that PGlite doesn't support
    const statements = generatedSql
      .split('--> statement-breakpoint')
      .map((s: string) => s.trim())
      .filter((s: string) => s.length > 0)
      .filter((statement: string) => {
        if (
          statement.includes('LANGUAGE plpgsql') ||
          statement.includes('$$ LANGUAGE plpgsql')
        ) {
          console.log('  Skipping PL/pgSQL function')
          return false
        }
        return true
      })

    // Extract CREATE TYPE statements from existing migrations that drizzle-kit didn't generate
    // This handles cases where enums aren't exported in schema files
    console.log(
      'Scanning existing migrations for missing CREATE TYPE statements...'
    )
    const existingFiles = readdirSync(migrationsFolder)
    const existingMigrations = existingFiles
      .filter((f: string) => f.endsWith('.sql') && f !== 'base.sql')
      .sort()

    const generatedTypes = new Set(
      statements
        .filter((s: string) => s.startsWith('CREATE TYPE'))
        .map((s: string) => {
          // Extract type name from CREATE TYPE "public"."type_name"
          const match = s.match(/CREATE TYPE "public"\."([^"]+)"/)
          return match ? match[1] : null
        })
        .filter(Boolean)
    )

    const missingTypes: string[] = []
    for (const file of existingMigrations) {
      const filePath = join(migrationsFolder, file)
      const sql = await Bun.file(filePath).text()
      const migrationStatements = sql
        .split('--> statement-breakpoint')
        .map((s: string) => s.trim())
        .filter((s: string) => s.startsWith('CREATE TYPE'))

      for (const stmt of migrationStatements) {
        const match = stmt.match(/CREATE TYPE "public"\."([^"]+)"/)
        const typeName = match ? match[1] : null
        if (typeName && !generatedTypes.has(typeName)) {
          missingTypes.push(stmt)
          generatedTypes.add(typeName)
          console.log(`  Found missing type: ${typeName}`)
        }
      }
    }

    // Merge missing types with generated statements
    statements.unshift(...missingTypes)

    // Sort statements: CREATE TYPE must come before CREATE TABLE, CREATE INDEX, etc.
    const sortedStatements = statements.sort((a: string, b: string) => {
      const getOrder = (statement: string) => {
        if (statement.startsWith('CREATE TYPE')) return 1
        if (statement.startsWith('CREATE TABLE')) return 2
        if (statement.startsWith('ALTER TABLE')) return 3
        if (statement.startsWith('CREATE UNIQUE INDEX')) return 4
        if (statement.startsWith('CREATE INDEX')) return 5
        return 10 // Other statements
      }
      return getOrder(a) - getOrder(b)
    })

    const baseSql = sortedStatements.join('\n--> statement-breakpoint\n')

    // Write to base.sql
    await Bun.write(baseFilePath, baseSql)

    console.log(`✓ Generated base.sql with ${statements.length} statements`)
    console.log(`  Location: ${baseFilePath}`)
  } catch (error) {
    console.error('Failed to generate base.sql:', error)
    throw error
  } finally {
    await $`rm -rf ${tempDir}`.quiet().catch(() => {})
    await $`rm -f ${tempConfigPath}`.quiet().catch(() => {})

    if (metaBackedUp) {
      await $`rm -rf ${metaDir}`.quiet().catch(() => {})
      await $`mkdir -p ${metaDir}`.quiet().catch(() => {})
      await $`cp -R ${tempMetaBackupDir}/. ${metaDir}`.quiet().catch(() => {})
    } else if (!metaExisted) {
      await $`rm -rf ${metaDir}`.quiet().catch(() => {})
    }

    await $`rm -rf ${tempMetaBackupDir}`.quiet().catch(() => {})
  }
}

generateBaseSql().catch((error) => {
  console.error('Failed to generate base.sql:', error)
  process.exit(1)
})

but I am tired of copying this from project to project.
Please include this into drizzle

Ideally there could be a new config user can enable:

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema/*',
  out: './drizzle',
  generateBaseSql: true,  // the new config
  dbCredentials: {
    url: dbUrl
  }
})

optionally

  generateBaseSql: './drizzle/base.sql',  // the new config specifying the path

would generate the SQL file into the specified path

The SQL file contains schema only SQL script which is useful for provisioning DBs for testing. Be it real DB like postgres or pglite.
Sure you can run your existing SQL files, but those can contain thousands of unnecessary data backfilling SQL commands you don't want to run for each test you run.