the_jozika•18h ago

Migrations suck

I might be off here, but migrations in Convex feel kind of rough. My workflow: I’m on a dev branch tweaking the schema, I write migrations, and I migrate my local data to test. Then in a later update I need to drop some old fields and make more code changes. In “classic” SQL, I can bundle schema changes and data migrations together and let the pipeline roll forward automatically. In Convex, it seems like I have to roll the code back to an intermediate state, run the migrations on prod, deploy that intermediate state, and only then make the rest of the code changes again. It works, but it feels pretty clunky. Am I missing a better pattern for handling schema + data changes without the intermediate deploy step? How are people doing this smoothly in Convex?

5 Replies

Convex Bot•18h ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

smaccoun•16h ago

We had a discussion on this in the general channel. The short answer is you have to roll your own system if you want to handle running automatically in multiple envs. I've been working on my own system for a while that I think is actually getting close to what I want. I am testing it out over a few more migrations and once i feel good about it i'll make a github repo with a writeup on it. In our discussion with the convex team we came to the conclusion you just have to always do backward-compat migrations (favoring things like dual writes) which is fine, but i think there's just tons to the conversation being left out. For example, if you are doing specifically data migrations and want to run in multiple environments how do you do it? This is what you are getting to its clunky and i still think the ocnvex team should come up with an official solution

smaccoun•16h ago

For my solution i basically modelled dbmate ( https://github.com/amacneil/dbmate ) where I have a migration tracking table and a release table and I create typical time incremeneted migration file (for example a recent one i had was 20251008_153000_backfill_chat_room_user_events.ts ) and i wrote my own custom script to run them in CI in the different environments i release to. My migration talbe is

  migrationStatus: defineTable({
    migrationId: v.string(),
    status: v.union(
      v.literal("pending"),
      v.literal("running"),
      v.literal("completed"),
      v.literal("failed"),
      v.literal("rolled_back") // Add rollback status
    ),
    startedAt: v.optional(v.number()),
    completedAt: v.optional(v.number()),
    error: v.optional(v.string()),
    // New optional fields for better tracking
    name: v.optional(v.string()), // Migration name
    description: v.optional(v.string()), // What it does
    recordsProcessed: v.optional(v.number()),
    recordsUpdated: v.optional(v.number()),
    recordsErrored: v.optional(v.number()),
    executionTimeMs: v.optional(v.number()),
    environment: v.optional(v.string()), // Track which env ran it
  }).index("migrationId", ["migrationId"]),

  migrationStatus: defineTable({
    migrationId: v.string(),
    status: v.union(
      v.literal("pending"),
      v.literal("running"),
      v.literal("completed"),
      v.literal("failed"),
      v.literal("rolled_back") // Add rollback status
    ),
    startedAt: v.optional(v.number()),
    completedAt: v.optional(v.number()),
    error: v.optional(v.string()),
    // New optional fields for better tracking
    name: v.optional(v.string()), // Migration name
    description: v.optional(v.string()), // What it does
    recordsProcessed: v.optional(v.number()),
    recordsUpdated: v.optional(v.number()),
    recordsErrored: v.optional(v.number()),
    executionTimeMs: v.optional(v.number()),
    environment: v.optional(v.string()), // Track which env ran it
  }).index("migrationId", ["migrationId"]),

GitHub

GitHub - amacneil/dbmate: 🚀 A lightweight, framework-agnostic da...

🚀 A lightweight, framework-agnostic database migration tool. - amacneil/dbmate

smaccoun•16h ago

And my release tables are

export const releaseEnvironment = v.union(
  v.literal("development"),
  v.literal("staging"),
  v.literal("production"),
  v.literal("preview")
);
export type ReleaseEnvironment = Infer<typeof releaseEnvironment>;

export const releaseStatus = v.union(
  v.literal("running"),
  v.literal("succeeded"),
  v.literal("failed"),
  v.literal("canceled")
);
export type ReleaseStatus = Infer<typeof releaseStatus>;

export const releaseMigrationStatus = v.union(
  v.literal("pending"),
  v.literal("running"),
  v.literal("completed"),
  v.literal("failed"),
  v.literal("rolled_back")
);
export type ReleaseMigrationStatus = Infer<typeof releaseMigrationStatus>;

const releaseMigrationEntry = v.object({
  migrationId: v.string(),
  status: releaseMigrationStatus,
  startedAt: v.optional(v.number()),
  completedAt: v.optional(v.number()),
  error: v.optional(v.string()),
  recordsProcessed: v.optional(v.number()),
  recordsUpdated: v.optional(v.number()),
  recordsErrored: v.optional(v.number()),
});

const releaseHistory = defineTable({
  releaseId: v.string(),
  environment: releaseEnvironment,
  commitSha: v.string(),
  tag: v.optional(v.string()),
  initiatedBy: v.string(),
  workflowUrl: v.optional(v.string()),
  status: releaseStatus,
  startedAt: v.number(),
  completedAt: v.optional(v.number()),
  migrations: v.optional(v.array(releaseMigrationEntry)),
  notes: v.optional(v.string()),
})
  .index("releaseId", ["releaseId"])
  .index("environment_startedAt", ["environment", "startedAt"]);

export const releaseTables = {
  releaseHistory,
};

export const releaseEnvironment = v.union(
  v.literal("development"),
  v.literal("staging"),
  v.literal("production"),
  v.literal("preview")
);
export type ReleaseEnvironment = Infer<typeof releaseEnvironment>;

export const releaseStatus = v.union(
  v.literal("running"),
  v.literal("succeeded"),
  v.literal("failed"),
  v.literal("canceled")
);
export type ReleaseStatus = Infer<typeof releaseStatus>;

export const releaseMigrationStatus = v.union(
  v.literal("pending"),
  v.literal("running"),
  v.literal("completed"),
  v.literal("failed"),
  v.literal("rolled_back")
);
export type ReleaseMigrationStatus = Infer<typeof releaseMigrationStatus>;

const releaseMigrationEntry = v.object({
  migrationId: v.string(),
  status: releaseMigrationStatus,
  startedAt: v.optional(v.number()),
  completedAt: v.optional(v.number()),
  error: v.optional(v.string()),
  recordsProcessed: v.optional(v.number()),
  recordsUpdated: v.optional(v.number()),
  recordsErrored: v.optional(v.number()),
});

const releaseHistory = defineTable({
  releaseId: v.string(),
  environment: releaseEnvironment,
  commitSha: v.string(),
  tag: v.optional(v.string()),
  initiatedBy: v.string(),
  workflowUrl: v.optional(v.string()),
  status: releaseStatus,
  startedAt: v.number(),
  completedAt: v.optional(v.number()),
  migrations: v.optional(v.array(releaseMigrationEntry)),
  notes: v.optional(v.string()),
})
  .index("releaseId", ["releaseId"])
  .index("environment_startedAt", ["environment", "startedAt"]);

export const releaseTables = {
  releaseHistory,
};

BluePenguin•15h ago

In another thread we were just discussing a potential definition of a migratingSchema that would keep the online-migration mindset with full typescript support and removes complex infra. Essentially only 1 change is needed to define a strict migration-end-schema and your inserts & patches would work as if the new end schema is already fully enforced. After the migration is ran you only need to clean up the 'migrating' schema and everything will be fully typesafe. Discussion happening here if anyone interested: https://discord.com/channels/1019350475847499849/1421114757213327480/1421492336784965703

Migrations suck

Did you find this page helpful?