[FEATURE]: Customize migration file name

[MatanYadaev]

Describe what you want

Please allow a way to customize the migration file name. 0000_create_users_table, 0001_add_status_to_users_table, etc

[t1mmen]

Choice of prefix would be great, too. It'd be nice if migration SQL filenames were compatible with Supabase's migration system (eg timestamp_description.sql)

[nicobao]

I need this too, to integrate with flyway: https://www.red-gate.com/blog/database-devops/flyway-naming-patterns-matter

Thanks for your work!

[nicobao]

Just in case you need a work-around, I created a custom bash script to do the necessary renaming for the flyway Versioned migration naming convention:

Example usage:

➜  scripts git:(main) ✗ ./rename_sql_migration_file.sh ../drizzle
Renamed ../drizzle/0000_volatile_triathlon.sql to V0000__volatile_triathlon.sql
Renamed 0000_volatile_triathlon to V0000__volatile_triathlon in ../drizzle/meta/_journal.json

Bash script:

#!/bin/bash

# Provide the directory path as an argument to the script
directory="$1"

# Check if the directory exists
if [ ! -d "$directory" ]; then
    echo "Directory does not exist."
    exit 1
fi

# Use find to search for files with ".sql" extension in the given directory
# Then, use a for loop to go through each filename and extract only the filenames without extensions
find "$directory" -type f -name "*.sql" -print0 | while IFS= read -r -d '' filename; do
    basename=$(basename "$filename" .sql)

    # Check if the basename matches the desired format (4 digits followed by an underscore)
    if [[ $basename =~ ^[0-9]{4}_ ]]; then
        # Extract the first 4 digits and the rest of the basename
        first_four_digits=${basename:0:4}
        rest_of_basename=${basename:5}

        # Rename the file using the desired format (V<4_digits>__<some_name>.sql)
        new_basename="V${first_four_digits}__${rest_of_basename}"
        new_filename="$directory/$new_basename.sql"
        mv "$filename" "$new_filename"

        echo "Renamed $filename to $new_basename.sql"

        # Update the JSON file if it exists
        json_file="$directory/meta/_journal.json"
        if [ -f "$json_file" ]; then
            jq --arg old_tag "$basename" --arg new_tag "$new_basename" \
               '(.entries[] | select(.tag == $old_tag).tag) |= $new_tag' "$json_file" > "$json_file.tmp"
            mv "$json_file.tmp" "$json_file"
            echo "Renamed $basename to $new_basename in $directory/meta/_journal.json"
        else
            echo "Warning: _journal.json file not found in $directory."
        fi
    else
        echo "Invalid format: $filename"
    fi
done

Renaming the drizzle/ directory this way makes it so that it you can keep using drizzle-kit generate and drizzle-kit drop without issues. I didn't try migrate, introspect, pull or the other commands.

I did not modify drizzle/meta/0000_snapshot.json and co on purpose, because otherwise the command drizzle-kit drop fails.

[Blankeos]

Definitely would be a great feature to add. Would like to also be able to use the current date + a descriptive name format:

drizzle-kit generate:pg --prefix date --tag support_for_authors

= 20231014_support_for_authors.sql

[golu360]

Is this issue still open or available for grabs?

[nicobao]

Is this issue still open or available for grabs?

I dunno but I'd love if it could be implemented 🙏

[StefanoDeVuono]

Is this issue still open or available for grabs?

I dunno but I'd love if it could be implemented 🙏

@nicobao It looks open, but it seems we have to wait for drizzle-kit to be open sourced.

[nicobao]

Just in case you need a work-around, I created a custom bash script to do the necessary renaming for the flyway Versioned migration naming convention:

Example usage:

➜  scripts git:(main) ✗ ./rename_sql_migration_file.sh ../drizzle
Renamed ../drizzle/0000_volatile_triathlon.sql to V0000__volatile_triathlon.sql
Renamed 0000_volatile_triathlon to V0000__volatile_triathlon in ../drizzle/meta/_journal.json

Bash script:

#!/bin/bash

# Provide the directory path as an argument to the script
directory="$1"

# Check if the directory exists
if [ ! -d "$directory" ]; then
    echo "Directory does not exist."
    exit 1
fi

# Use find to search for files with ".sql" extension in the given directory
# Then, use a for loop to go through each filename and extract only the filenames without extensions
find "$directory" -type f -name "*.sql" -print0 | while IFS= read -r -d '' filename; do
    basename=$(basename "$filename" .sql)

    # Check if the basename matches the desired format (4 digits followed by an underscore)
    if [[ $basename =~ ^[0-9]{4}_ ]]; then
        # Extract the first 4 digits and the rest of the basename
        first_four_digits=${basename:0:4}
        rest_of_basename=${basename:5}

        # Rename the file using the desired format (V<4_digits>__<some_name>.sql)
        new_basename="V${first_four_digits}__${rest_of_basename}"
        new_filename="$directory/$new_basename.sql"
        mv "$filename" "$new_filename"

        echo "Renamed $filename to $new_basename.sql"

        # Update the JSON file if it exists
        json_file="$directory/meta/_journal.json"
        if [ -f "$json_file" ]; then
            jq --arg old_tag "$basename" --arg new_tag "$new_basename" \
               '(.entries[] | select(.tag == $old_tag).tag) |= $new_tag' "$json_file" > "$json_file.tmp"
            mv "$json_file.tmp" "$json_file"
            echo "Renamed $basename to $new_basename in $directory/meta/_journal.json"
        else
            echo "Warning: _journal.json file not found in $directory."
        fi
    else
        echo "Invalid format: $filename"
    fi
done

Renaming the drizzle/ directory this way makes it so that it you can keep using drizzle-kit generate and drizzle-kit drop without issues. I didn't try migrate, introspect, pull or the other commands.

I did not modify drizzle/meta/0000_snapshot.json and co on purpose, because otherwise the command drizzle-kit drop fails.

Hi, This doesn't work anymore with the newer versions of drizzle. It says my snapshot.json files are malformed when I launch any drizzle command :/. Please, drizzle team, allow us to customize the output name. Thanks.

[devjume]

Here is my javascript/node.js based workaround.

This can be run from package.json script and it also works with Supabase. (See bottom) Thanks @nicobao for the idea.

Following code renames migrations files to [TIMESTAMP]_[ORIGINAL-NAME].sql format and updates _journal.json file

import fs from 'fs'

console.log("Renaming migrations...")

const migrationsPath = "./supabase/migrations"
const journalPath = `${migrationsPath}/meta/_journal.json`

if (!fs.existsSync(migrationsPath)) {
  console.log(`Migration directory '${migrationsPath}' does not exists!`);
}

if (!fs.existsSync(journalPath)) {
  console.log(`'${journalPath}' does not exists!`);
}

const jounalData = fs.readFileSync(journalPath, 'utf8')
const journalObj = JSON.parse(jounalData)

const files = fs.readdirSync(migrationsPath, {withFileTypes: true})
  .filter(item => !item.isDirectory())
  .map(item => item.name)

const filteredFiles = files.filter(file => file.match(/^[0-9]{4}_.+\.sql/))

for (let i = 0; i < filteredFiles.length; i++) {
  const filename = filteredFiles[i]
  const filenameWithoutExtension = filename.slice(0, -4)
  //const firstFourDigits = filename.slice(0, 4);
  //const restOfFilename = filename.slice(4);

  const journalEntry = journalObj.entries.find(entry => entry.tag === filenameWithoutExtension)

  // Grab timestamp from journal entry
  const timestamp = journalEntry.when

  // Create new filename
  const newFilename = `${timestamp}_${filenameWithoutExtension}`

  // Rename file
  fs.renameSync(`${migrationsPath}/${filename}`, `${migrationsPath}/${newFilename}.sql`)

  // Update journal entry
  journalEntry.tag = newFilename
}

// Rewrite journal file
fs.writeFile(journalPath, JSON.stringify(journalObj, null, 2), 'utf8', function(err) {
  if (err) {
    console.log(`Error rewriting journal file: ${err}`);
  } else {
    console.log(`Migration files renamed successfully.`);
  }
});

For Supabase users:

The scripts work with Supabase cli's migration command and in the UI.

Here is my package.json scripts for Supabase workflow. (I use pnpm & pnpx)

"db:migration:generate": "drizzle-kit generate:pg --config=drizzle.supabase.config.ts && node ./utils/rename-migrations.js",
"db:migration:push": "pnpx supabase db push",

1. Generate migration files to `./supabase/migrations2
2. Run renaming scripts
3. Push changes to remote Supabase instance

Renaming file stored at: ./utils/rename-migrations.js

[djedlajn]

Any plans on open sourcing drizzle-kit so this can be implemented natively?

[lukerep]

Looks like there will be some changes that will support this workflow in this drizzle-kit beta version: https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.21.0-beta

[AndriiSherman]

Looks like there will be some changes that will support this workflow in this drizzle-kit beta version: https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.21.0-beta

that's true. We already on 0.22.0 for drizzle-kit and custom names were added in 0.21.0 https://orm.drizzle.team/kit-docs/commands#generate-migrations

just use drizzle-kit generate --name init_db

[yudax42]

Looks like there will be some changes that will support this workflow in this drizzle-kit beta version: https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.21.0-beta

that's true. We already on 0.22.0 for drizzle-kit and custom names were added in 0.21.0 https://orm.drizzle.team/kit-docs/commands#generate-migrations

just use drizzle-kit generate --name init_db

This still prefixes the migration file name with the migration ID 0000_init_db. Is there a way to completely remove the prefix and replace it with a custom ID, like a timestamp? For example, I have a pre-existing Supabase database, and the migration system of Supabase uses the format timestamp_description.sql

[zda]

This still prefixes the migration file name with the migration ID 0000_init_db. Is there a way to completely remove the prefix and replace it with a custom ID, like a timestamp? For example, I have a pre-existing Supabase database, and the migration system of Supabase uses the format timestamp_description.sql

Agreed. Currently we have an awkward two-phase process for creating migrations – use another tool to generate an appropriately-named migration file (timestamp_description.sql), then generate the contents with drizzle-kit, then copy the contents into the first file. We thought this issue would simplify things, but it only fixed the "description" part.

[dector]

Is there a way to completely remove the prefix and replace it with a custom ID, like a timestamp?

I'm on 0.25.0 and can do this:

drizzle-kit generate --name init --prefix none