Issue with migration file to MySQL database

[SOMONSOUM]

Anyone help to solve this issue?

This my migration file

import { Kysely, sql } from 'kysely';

const userTableName = 'user';

export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .createTable(userTableName)
    .addColumn('id', 'uuid', (col) => col.primaryKey())
    .addColumn('email', 'varchar', (col) => col.notNull().unique())
    .addColumn('password', 'varchar', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull(),
    )
    .addColumn('updated_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull(),
    )
    .addColumn('deleted_at', 'timestamp')
    .execute();
}

export async function down(database: Kysely<unknown>): Promise<void> {
  await database.schema.dropTable(userTableName).execute();
}

I got error

yarn migrate:latest
failed to execute migration "20240324124249_add_users_table"
Failed to migrate
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid primary key, `email` varchar not null unique, `password` varchar not null, ' at line 1
    at Packet.asError (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/commands/command.js:29:26)
    at PoolConnection.handlePacket (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/connection.js:481:34)
    at PacketParser.onPacket (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/connection.js:97:12)
    at PacketParser.executeStart (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/mysql2/lib/connection.js:104:25)
    at Socket.emit (node:events:514:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9)
    at MysqlConnection.executeQuery (/Users/japan/Workspace/nodejs/nestjs-kysely-mysql-graphql/node_modules/kysely/dist/cjs/dialect/mysql/mysql-driver.js:119:69)

[koskimas]

uuid is not a valid MySQL data type.

[SOMONSOUM]

uuid is not a valid MySQL data type.

thanks, but how can I use id as a primary key and auto increment ? and set created_at, updated_at to current date?

[igalklebanov]

Hey 👋

CREATE TABLE example_table (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

import { sql } from "kysely";

await db.schema
  .createTable("example_table")
  .addColumn("id", "integer", (cb) =>
    cb.unsigned().notNull().autoIncrement().primaryKey(),
  )
  .addColumn("created_at", "timestamp", (cb) =>
    cb.defaultTo(sql`CURRENT_TIMESTAMP`),
  )
  .addColumn("updated_at", "timestamp", (cb) =>
    cb
      .defaultTo(sql`CURRENT_TIMESTAMP`)
      .modifyEnd(sql`ON UPDATE CURRENT_TIMESTAMP`),
  )
  .execute();

https://kyse.link/Visis