Error in mysql migration

using "drizzle-orm": "0.31.2", "drizzle-kit": "0.22.8", drizzle.config.ts file, it is in root folder of project

import {defineConfig} from 'drizzle-kit'

export default defineConfig({
  dialect: 'mysql',
  out: './drizzle',
  schema: './app/db/schema/*',
  dbCredentials: {
    url: process.env['DB_URL'] ?? '',
  },
  verbose: true,
  // migrations: {schema: './app/db/schema/*'},
})

import {defineConfig} from 'drizzle-kit'

export default defineConfig({
  dialect: 'mysql',
  out: './drizzle',
  schema: './app/db/schema/*',
  dbCredentials: {
    url: process.env['DB_URL'] ?? '',
  },
  verbose: true,
  // migrations: {schema: './app/db/schema/*'},
})

connection file

import {drizzle} from 'drizzle-orm/mysql2'
import mysql from 'mysql2/promise'
import dotenv from 'dotenv'

dotenv.config()

export const client = await mysql.createConnection({
  supportBigNumbers: true,
  database: 'chekmate',
  uri: process.env['DB_URL'],
})

export const dbClient = drizzle(client)

import {drizzle} from 'drizzle-orm/mysql2'
import mysql from 'mysql2/promise'
import dotenv from 'dotenv'

dotenv.config()

export const client = await mysql.createConnection({
  supportBigNumbers: true,
  database: 'chekmate',
  uri: process.env['DB_URL'],
})

export const dbClient = drizzle(client)

Everything working fine. Created the schemas file for db, drizzle-kit push worked fine, all table created successfully and drizzle-kit generate generated first random1.sql file in drizzle folder. Then a column value type has to be changed, changes done in schema and drizzle-kit generate also generated another random2.sql file with only alter command in it. db-migration.ts

import {migrate} from 'drizzle-orm/mysql2/migrator'
import {client, dbClient} from '~/db/client'

// This will run migrations on the database, skipping the ones already applied
await migrate(dbClient, {migrationsFolder: './drizzle'})

// Don't forget to close the client, otherwise the script will hang
await client.end()

import {migrate} from 'drizzle-orm/mysql2/migrator'
import {client, dbClient} from '~/db/client'

// This will run migrations on the database, skipping the ones already applied
await migrate(dbClient, {migrationsFolder: './drizzle'})

// Don't forget to close the client, otherwise the script will hang
await client.end()

now pnpm tsx db-migration.ts command is failing with table already exist error. It is try to run 1st genereated random1.sql file first and failing with error. Need to know what I am doing wrong here?

1 Reply

rphlmr ⚡•6mo ago

https://discord.com/channels/1043890932593987624/1262344647854915675

Gaming

Programming

Error in mysql migration