[BUG]: MySQL / Planetscale wants to redo migration steps

[jkrumm]

After executing migrations drizzle wants to do parts again. Please help me this is probably a bug. It wants to drop primary keys and add them again and then again the timestamp defaults. The first migration works fine though and tables are persisted perfectly. Just that it doesn't get a proper diff when running again.

DB: MySQL / Planetscale

"drizzle-orm": "^0.29.1", "drizzle-kit": "^0.20.6",

my migration script:

"db:push": "drizzle-kit push:mysql --config=drizzle.config.ts",

drizzle.config.ts

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/server/db/schema.ts",
  driver: "mysql2",
  dbCredentials: {
    uri: process.env.DATABASE_URL!,
  },
  strict: true,
  verbose: true,
  breakpoints: true,
});

schema.ts

import {
  boolean,
  decimal,
  int,
  mediumint,
  mysqlEnum,
  mysqlTableCreator,
  smallint,
  timestamp,
  varchar,
} from "drizzle-orm/mysql-core";
import {
  type InferInsertModel,
  type InferSelectModel,
  relations,
} from "drizzle-orm";
import { nanoid } from "nanoid";

/**
 * Multi-project schema feature of Drizzle ORM. Use the same database instance for multiple projects.
 * @see https://orm.drizzle.team/docs/goodies#multi-project-schema
 */
export const mysqlTable = mysqlTableCreator((name) => `fpp_${name}`);

/**
 * ROOMS
 */

export const rooms = mysqlTable("rooms", {
  id: int("id").autoincrement().primaryKey().notNull(),
  number: mediumint("number").unique("rooms_number_unique_idx").notNull(),
  name: varchar("name", { length: 15 }).unique("rooms_name_unique_idx"),
  firstUsedAt: timestamp("first_used_at").defaultNow().notNull(),
  lastUsedAt: timestamp("last_used_at").defaultNow().onUpdateNow().notNull(),
});

export type IRoom = InferSelectModel<typeof rooms>;
export type ICreateRoom = InferInsertModel<typeof rooms>;

/**
 * VOTES
 */

export const votes = mysqlTable("votes", {
  id: int("id").autoincrement().primaryKey().notNull(),
  roomId: int("room_id").notNull(),
  avgEstimation: decimal("avg_estimation", {
    precision: 4,
    scale: 2,
  }).notNull(),
  maxEstimation: decimal("max_estimation", {
    precision: 4,
    scale: 2,
  }).notNull(),
  minEstimation: decimal("min_estimation", {
    precision: 4,
    scale: 2,
  }).notNull(),
  amountOfEstimations: decimal("amount_of_estimations", {
    precision: 4,
    scale: 2,
  }).notNull(),
  amountOfSpectators: smallint("amount_of_spectators").notNull(),
  duration: smallint("duration").notNull(),
  votedAt: timestamp("voted_at").defaultNow().notNull(),
});

export type IVote = InferSelectModel<typeof votes>;
export type ICreateVote = InferInsertModel<typeof votes>;

export const votesRelations = relations(votes, ({ one }) => ({
  room: one(rooms, {
    fields: [votes.roomId],
    references: [rooms.id],
  }),
}));

/**
 * USERS
 */

export const users = mysqlTable("users", {
  id: varchar("id", { length: 21 }).primaryKey().$defaultFn(nanoid).notNull(),
  device: varchar("device", { length: 50 }),
  os: varchar("os", { length: 50 }),
  browser: varchar("browser", { length: 50 }),
  country: varchar("country", { length: 5 }),
  region: varchar("region", { length: 100 }),
  city: varchar("city", { length: 100 }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export type IUser = InferSelectModel<typeof users>;
export type ICreateUser = InferInsertModel<typeof users>;

/**
 * ESTIMATIONS
 */

export const estimations = mysqlTable("estimations", {
  id: int("id").primaryKey().autoincrement().notNull(),
  userId: varchar("user_id", { length: 21 }).notNull(),
  roomId: int("room_id").notNull(),
  estimation: smallint("estimation"),
  spectator: boolean("spectator").default(false).notNull(),
  estimatedAt: timestamp("estimated_at").defaultNow().notNull(),
});

export type IEstimation = InferSelectModel<typeof estimations>;
export type ICreateEstimation = InferInsertModel<typeof estimations>;

export const estimationsRelations = relations(estimations, ({ one }) => ({
  users: one(users, {
    fields: [estimations.userId],
    references: [users.id],
  }),
  rooms: one(rooms, {
    fields: [estimations.roomId],
    references: [rooms.id],
  }),
}));

/**
 * PAGE_VIEWS
 */

export const RouteType = {
  HOME: "HOME",
  CONTACT: "CONTACT",
  IMPRINT: "IMPRINT",
  ROOM: "ROOM",
  ANALYTICS: "ANALYTICS",
  ROADMAP: "ROADMAP",
} as const;

export const pageViews = mysqlTable("page_views", {
  id: int("id").primaryKey().autoincrement().notNull(),
  userId: varchar("user_id", { length: 21 }).notNull(),
  route: mysqlEnum("route", Object.values(RouteType) as [string]).notNull(),
  roomId: int("room_id"),
  viewedAt: timestamp("viewed_at").defaultNow().notNull(),
});

export type IPageView = InferSelectModel<typeof pageViews>;
export type ICreatePageView = InferInsertModel<typeof pageViews>;

export const pageViewsRelations = relations(pageViews, ({ one }) => ({
  users: one(users, {
    fields: [pageViews.roomId],
    references: [users.id],
  }),
}));

/**
 * EVENTS
 */

export const EventType = {
  CONTACT_FORM_SUBMISSION: "CONTACT_FORM_SUBMISSION",
} as const;

export const events = mysqlTable("events", {
  id: int("id").primaryKey().autoincrement().notNull(),
  userId: varchar("user_id", { length: 21 }).notNull(),
  event: mysqlEnum("event", Object.keys(EventType) as [string]).notNull(),
  eventAt: timestamp("event_at").defaultNow().notNull(),
});

export type IEvent = InferSelectModel<typeof events>;
export type ICreateEvent = InferInsertModel<typeof events>;

export const eventsRelations = relations(events, ({ one }) => ({
  users: one(users, {
    fields: [events.userId],
    references: [users.id],
  }),
}));

/**
 * USERS_RELATIONS
 */

export const usersRelations = relations(users, ({ many }) => ({
  pageViews: many(pageViews),
  estimations: many(estimations),
  events: many(events),
}));

/**
 * ROOMS_RELATIONS
 */

export const roomsRelations = relations(rooms, ({ many }) => ({
  votes: many(votes),
  pageViews: many(pageViews),
  estimations: many(estimations),
}));

/** ------------------------------------------------------------------ */

/**
 * FEATURE_FLAGS
 */

export const FeatureFlagType = {
  CONTACT_FORM: "CONTACT_FORM",
} as const;

export const featureFlags = mysqlTable("feature_flags", {
  name: mysqlEnum("name", Object.keys(FeatureFlagType) as [string])
    .unique("feature_flags_name_unique_idx")
    .notNull(),
  enabled: boolean("enabled").default(false).notNull(),
});

The first migration:

CREATE TABLE `fpp_estimations` (
    `id` int AUTO_INCREMENT NOT NULL,
    `user_id` varchar(21) NOT NULL,
    `room_id` int NOT NULL,
    `estimation` smallint,
    `spectator` boolean NOT NULL DEFAULT false,
    `estimated_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `fpp_estimations_id` PRIMARY KEY(`id`)
);

CREATE TABLE `fpp_events` (
    `id` int AUTO_INCREMENT NOT NULL,
    `user_id` varchar(21) NOT NULL,
    `event` enum('CONTACT_FORM_SUBMISSION') NOT NULL,
    `event_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `fpp_events_id` PRIMARY KEY(`id`)
);

CREATE TABLE `fpp_feature_flags` (
    `name` enum('CONTACT_FORM') NOT NULL,
    `enabled` boolean NOT NULL DEFAULT false,
    CONSTRAINT `feature_flags_name_unique_idx` UNIQUE(`name`)
);

CREATE TABLE `fpp_page_views` (
    `id` int AUTO_INCREMENT NOT NULL,
    `user_id` varchar(21) NOT NULL,
    `route` enum('HOME','CONTACT','IMPRINT','ROOM','ANALYTICS','ROADMAP') NOT NULL,
    `room_id` int,
    `viewed_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `fpp_page_views_id` PRIMARY KEY(`id`)
);

CREATE TABLE `fpp_rooms` (
    `id` int AUTO_INCREMENT NOT NULL,
    `number` mediumint NOT NULL,
    `name` varchar(15),
    `first_used_at` timestamp NOT NULL DEFAULT (now()),
    `last_used_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fpp_rooms_id` PRIMARY KEY(`id`),
    CONSTRAINT `rooms_number_unique_idx` UNIQUE(`number`),
    CONSTRAINT `rooms_name_unique_idx` UNIQUE(`name`)
);

CREATE TABLE `fpp_users` (
    `id` varchar(21) NOT NULL,
    `device` varchar(50),
    `os` varchar(50),
    `browser` varchar(50),
    `country` varchar(5),
    `region` varchar(100),
    `city` varchar(100),
    `created_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `fpp_users_id` PRIMARY KEY(`id`)
);

CREATE TABLE `fpp_votes` (
    `id` int AUTO_INCREMENT NOT NULL,
    `room_id` int NOT NULL,
    `avg_estimation` decimal(4,2) NOT NULL,
    `max_estimation` decimal(4,2) NOT NULL,
    `min_estimation` decimal(4,2) NOT NULL,
    `amount_of_estimations` decimal(4,2) NOT NULL,
    `amount_of_spectators` smallint NOT NULL,
    `duration` smallint NOT NULL,
    `voted_at` timestamp NOT NULL DEFAULT (now()),
    CONSTRAINT `fpp_votes_id` PRIMARY KEY(`id`)
);

Then without changing anything and running my migration script it wants to do:

"db:push": "drizzle-kit push:mysql --config=drizzle.config.ts",

ALTER TABLE `fpp_estimations` DROP PRIMARY KEY;
ALTER TABLE `fpp_events` DROP PRIMARY KEY;
ALTER TABLE `fpp_page_views` DROP PRIMARY KEY;
ALTER TABLE `fpp_rooms` DROP PRIMARY KEY;
ALTER TABLE `fpp_users` DROP PRIMARY KEY;
ALTER TABLE `fpp_votes` DROP PRIMARY KEY;
ALTER TABLE `fpp_estimations` MODIFY COLUMN `estimated_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_events` MODIFY COLUMN `event_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_page_views` MODIFY COLUMN `viewed_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_rooms` MODIFY COLUMN `first_used_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_rooms` MODIFY COLUMN `last_used_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `fpp_users` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_votes` MODIFY COLUMN `voted_at` timestamp NOT NULL DEFAULT (now());
ALTER TABLE `fpp_estimations` ADD PRIMARY KEY(`id`);
ALTER TABLE `fpp_events` ADD PRIMARY KEY(`id`);
ALTER TABLE `fpp_page_views` ADD PRIMARY KEY(`id`);
ALTER TABLE `fpp_rooms` ADD PRIMARY KEY(`id`);
ALTER TABLE `fpp_users` ADD PRIMARY KEY(`id`);
ALTER TABLE `fpp_votes` ADD PRIMARY KEY(`id`);

[VaniaPopovic]

Encountering the same issue with Planetscale db:push

[CodeWithShreyans]

Same, have to manually do the necessary migrations every time.

[JonasDoesThings]

Same.

Schema:

export const organization_memberships = mysqlTable('organization_memberships', {
  userId: varchar('userId', {length: 36}).notNull(),
  organizationId: varchar('organizationId', {length: 36}).notNull(),
  role: tinyint('role', {unsigned: true}).default(ORG_ROLES_ID_MEMBER).notNull(),
}, (table) => ({
  pk: primaryKey({columns: [table.organizationId, table.userId]}),
}));

Always results in

 Warning  Found data-loss statements:
· You're about to change role column type from tinyint to tinyint unsigned with 2 items

drizzle-kit: v0.20.6 drizzle-orm: v0.29.1

😿

[JonasDoesThings]

FYI: Still present in drizzle-kit v0.20.7 even though other similar-ish issues were fixed

[jkrumm]

FYI: Still present in drizzle-kit v0.20.7 even though other similar-ish issues were fixed

From my understanding the drizzle-kit package is doing the migrations but I can be mistaken in thinking that the version of drizzle-orm doesn't play a part

[JonasDoesThings]

FYI: Still present in latest release

drizzle-kit@0.20.8 drizzle-orm@0.29.2

[jkrumm]

@AndriiSherman @AlexBlokh @filipsobol

Could you guys please look into? It really seems like a bug unfortunately

[JonasDoesThings]

Issue still persists, drizzle-kit want's to ALTER lots of stuff without need.

[fabiendeborde]

Hello, I am having the same problem with composite primary keys and also timestamp columns. I didn't change my models, and this is the output after drizzle-kit push:

ALTER TABLE `user_statuses` DROP PRIMARY KEY;
ALTER TABLE `messages` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `messages` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `messages` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `user_statuses` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `user_statuses` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `users` MODIFY COLUMN `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `users` MODIFY COLUMN `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `user_statuses` ADD PRIMARY KEY(`user_id`,`talk_id`);

[fabiendeborde]

@AndriiSherman @AlexBlokh @filipsobol

Hi again, is this issue investigated ? As the project is getting more and more tables, I get more of these SQL statements and it is now impossible to use drizzle-kit push. I can't really drop timestamp columns or composite primary keys, so the fix got to come from drizzle-kit... :(