ashutosh_dev Posts - Answer Overflow

ashutosh_dev

•Created by ashutosh_dev on 10/9/2024 in #help

Setting Procedures and Triggers and custom SQLs

Hi. I am using drizzle with mysql in ts, I want to add functionality of procedures and trigger them after specific db calls. How to do it? and if i change something in procedure like edit/delete/add how to handle it's migration. In on case i have table and it has primary key which is autoincreement but i need to start the count from 1000, how to handle this and If I add something like this to other table after deploying my database how to handle it's migration

1 replies

DTDrizzle Team

•Created by ashutosh_dev on 7/15/2024 in #help

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?

2 replies

Gaming

Programming