DT
Drizzle Team•8mo ago
CS

What am I doing wrong with migrations?

Hey there drizzle members! So I've been trying to set up a project with NestJS using Drizzle - and while Drizzle ORM itself has been working splendildy so far, we've been having trouble with the migrations part. We're using MySQL (mysql2 driver), trying to get the suggested await migrate(); workflow running. My database script looks like this: 
import * as mysql from "mysql2/promise";
import { MySql2Database, drizzle } from "drizzle-orm/mysql2";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { getEnv } from "./env";

export let db: MySql2Database<Record<string, never>>;

export async function initializeDatabase() {
    const env = getEnv();
    const connection = await mysql.createConnection({
        host: env.database.host,
        user: env.database.user,
        password: env.database.pass,
        database: env.database.db,
    });

    const db = drizzle(connection);

    // Currently crashing the app startup for me, check out manual migration in npm scripts (npm run drizzle:push:dev)
    await migrate(db, { migrationsFolder: "./db/migrations" });
}
import * as mysql from "mysql2/promise";
import { MySql2Database, drizzle } from "drizzle-orm/mysql2";
import { migrate } from "drizzle-orm/mysql2/migrator";
import { getEnv } from "./env";

export let db: MySql2Database<Record<string, never>>;

export async function initializeDatabase() {
    const env = getEnv();
    const connection = await mysql.createConnection({
        host: env.database.host,
        user: env.database.user,
        password: env.database.pass,
        database: env.database.db,
    });

    const db = drizzle(connection);

    // Currently crashing the app startup for me, check out manual migration in npm scripts (npm run drizzle:push:dev)
    await migrate(db, { migrationsFolder: "./db/migrations" });
}
My drizzle.config.ts is as follows: 
import { defineConfig } from "drizzle-kit";
import { getEnv } from "@/env";
const env = getEnv();

export default defineConfig({
    schema: "./db/schema.ts",
    driver: "mysql2",
    out: "./db/migrations",
    dbCredentials: {
        user: env.database.user,
        password: env.database.pass,
        host: env.database.host,
        database: env.database.db,
    },
    verbose: true,
    strict: true,
});
import { defineConfig } from "drizzle-kit";
import { getEnv } from "@/env";
const env = getEnv();

export default defineConfig({
    schema: "./db/schema.ts",
    driver: "mysql2",
    out: "./db/migrations",
    dbCredentials: {
        user: env.database.user,
        password: env.database.pass,
        host: env.database.host,
        database: env.database.db,
    },
    verbose: true,
    strict: true,
});
No description
12 Replies
CS
CSOP•8mo ago
When it reaches the migrate line though, it seems to fail: 
C:\Users\CS\Documents\Code\API\node_modules\mysql2\promise.js:94
    const localErr = new Error();
                     ^
Error: Table 'card_type' already exists
    at PromiseConnection.query (C:\Users\CS\Documents\Code\API\node_modules\mysql2\promise.js:94:22)        
    at MySql2PreparedQuery.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\session.ts:85:18)
    at MySql2Session.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\session.ts:81:5)   
    at MySql2Transaction.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\db.ts:335:23)  
    at C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:58:16
    at MySql2Session.transaction (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\session.ts:242:25) 
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at MySqlDialect.migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:51:3)    
    at migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\migrator.ts:10:2)
    at initializeDatabase (C:\Users\CS\Documents\Code\API\src\db.ts:20:5)
C:\Users\CS\Documents\Code\API\node_modules\mysql2\promise.js:94
    const localErr = new Error();
                     ^
Error: Table 'card_type' already exists
    at PromiseConnection.query (C:\Users\CS\Documents\Code\API\node_modules\mysql2\promise.js:94:22)        
    at MySql2PreparedQuery.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\session.ts:85:18)
    at MySql2Session.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\session.ts:81:5)   
    at MySql2Transaction.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\db.ts:335:23)  
    at C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:58:16
    at MySql2Session.transaction (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\session.ts:242:25) 
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at MySqlDialect.migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:51:3)    
    at migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql2\migrator.ts:10:2)
    at initializeDatabase (C:\Users\CS\Documents\Code\API\src\db.ts:20:5)
CS
CSOP•8mo ago
Incidentally, that's the first line in the generated SQL files
No description
CS
CSOP•8mo ago
So far, it has only helped to reset the DB, run drizzle-kit generate, and then it worked again. I think our understanding of the migration part might be wrong. I'd be glad to receive any tips on how to properly handle this! Thanks in advance 👋
Startup Spells 🪄 Newsletter Guy
Startup Spells 🪄 Newsletter Guy•8mo ago
i run migrations manually like you said in npm scripts. example -> https://github.com/deadcoder0904/easypanel-nextjs-sqlite or any projects in my profile i did face this error today randomly. idk why. i deleted my sqlite file & re-ran again & it worked. i dont think that migrations re-run again if the table already exists so thats definitely a weird error as all migrations cli handle that part automatically. maybe a schema change?
Ricardo Romero
Ricardo Romero•8mo ago
I also just run migrations manually. I have a migrate.ts script and run it with bun.
Startup Spells 🪄 Newsletter Guy
Startup Spells 🪄 Newsletter Guy•8mo ago
i just use node --env-file .env.production drizzle-migrate.mjs script for now as bun windows hasn't come yet. just 7 days away so i can replace those scripts with bun too lol. or maybe wait 6 months for it to get stable.
CS
CSOP•8mo ago
I just noticed something even more weird, when trying to bugfix one of my queries
CS
CSOP•8mo ago
In that error message, and in a new one I am getting (for which the code itself should be absolutely fine...), in the stacktrace, I can see it trying to access files in 
at MySqlDialect.migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts)
at MySqlDialect.migrate (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts)
But there is no such file on my filesystem. I would assume it's actually part of the Drizzle-ORM package, but I have no clue why it's logging that path. 
[Nest] 25660  - 26/03/2024, 23:02:57   ERROR [ExceptionsHandler] Cannot read properties of undefined (reading 'name')
TypeError: Cannot read properties of undefined (reading 'name')
    at C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:100:43
    at Array.flatMap (<anonymous>)
    at MySqlDialect.buildUpdateSet (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:98:6)
    at MySqlDialect.buildUpdateQuery (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:110:23)
    at QueryPromise.getSQL (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:174:23)
    at QueryPromise.prepare (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:184:33)
    at QueryPromise.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:190:15)
    at QueryPromise.then (C:\Users\CS\Documents\Code\API\node_modules\src\query-promise.ts:31:15)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
[Nest] 25660  - 26/03/2024, 23:02:57   ERROR [ExceptionsHandler] Cannot read properties of undefined (reading 'name')
TypeError: Cannot read properties of undefined (reading 'name')
    at C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:100:43
    at Array.flatMap (<anonymous>)
    at MySqlDialect.buildUpdateSet (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:98:6)
    at MySqlDialect.buildUpdateQuery (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\dialect.ts:110:23)
    at QueryPromise.getSQL (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:174:23)
    at QueryPromise.prepare (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:184:33)
    at QueryPromise.execute (C:\Users\CS\Documents\Code\API\node_modules\src\mysql-core\query-builders\update.ts:190:15)
    at QueryPromise.then (C:\Users\CS\Documents\Code\API\node_modules\src\query-promise.ts:31:15)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
Is there any clue on what might be causing this... ?
No description
Ricardo Romero
Ricardo Romero•8mo ago
What package manager are you using? Maybe it's a case of implicit dependency?
CS
CSOP•8mo ago
Using npm I think the console logs with file locations aren't that important, since it's obviously finding those files The error seems to stem from this part though: 
    buildUpdateSet(table: MySqlTable, set: UpdateSet): SQL {
        const tableColumns = table[Table.Symbol.Columns];

        const columnNames = Object.keys(tableColumns).filter((colName) =>
            set[colName] !== undefined || tableColumns[colName]?.onUpdateFn !== undefined
        );

        const setSize = columnNames.length;
        return sql.join(columnNames.flatMap((colName, i) => {
            const col = tableColumns[colName]!;

            const value = set[colName] ?? sql.param(col.onUpdateFn!(), col);
            const res = sql`${sql.identifier(col.name)} = ${value}`;

            if (i < setSize - 1) {
                return [res, sql.raw(', ')];
            }
            return [res];
        }));
    }
    buildUpdateSet(table: MySqlTable, set: UpdateSet): SQL {
        const tableColumns = table[Table.Symbol.Columns];

        const columnNames = Object.keys(tableColumns).filter((colName) =>
            set[colName] !== undefined || tableColumns[colName]?.onUpdateFn !== undefined
        );

        const setSize = columnNames.length;
        return sql.join(columnNames.flatMap((colName, i) => {
            const col = tableColumns[colName]!;

            const value = set[colName] ?? sql.param(col.onUpdateFn!(), col);
            const res = sql`${sql.identifier(col.name)} = ${value}`;

            if (i < setSize - 1) {
                return [res, sql.raw(', ')];
            }
            return [res];
        }));
    }
At the part where it's trying to access col.name apparently But I have no clue why this is happening in my update query Ok I think I fixed my problem, it was something stupid from my side... (a small typo basically)
CS
CSOP•8mo ago
Which relates to another issue, is there any workaround for tables getting defined as any ? I think I read somewhere that it's a limitation from typescript (when it comes to relations between tables), but is there some other way to "re-define" the appropriate fields of a table?
No description
CS
CSOP•8mo ago
Ah found it in another answer here from last year: define the callback in a references function as AnyMySqlColumn
Want results from more Discord servers?
Add your server