I need to rekey my tables after importing

[azure solstice]

I'm used to a database-first development approach (only an SQL background) meaning that any modifications to the structure of my data/tables/relations, I have always managed directly in the database via SQL statements. I'm fully on-bard with Convex's mission and position on SQL, but I'm still in the early stages of the Mt Everest of learning curves for me.

I've started small, imorting a primary table, some secondary tables (my terms) and a few many-to-many tables that link them. Since convex generates new unique row IDs, I would like to update/replace the values that make up all of the relationships in the many-to-many linking tables. What is the convex/JS way to do this, updating foreign keys based on new primary keys?

[lavish hornet]

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](https://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!

[jaunty lynx]

migrations 😄 https://www.convex.dev/components/migrations

[azure solstice]

I've read that and the Intro To Migrations post and I'm sorry but they are gibberish to me. I never even used migrations in ORMs, I did all of the DB editing myself. My brain is looking for something like "update that column in that table where value in that other column equals value in this table this column" (I know that isn't valid SQL, just an illustration). I'm not able to identify anything in the examples that looks like something like that. I don't see any kind of patch where this equals that.

I would love to see a write up of a real world example of maybe a students/classes many to many relationship being imported from an outside database, migrated into convex and showing how the relationships get rekeyed.

[jaunty lynx]

i'm not sure of the exact desired outcome, but maybe this example will help

export const updateStudentReferences = migrations.define({
  table: "enrollments",
  migrateOne: async (ctx, enrollment) => {
    // Skip if already has valid Convex ID
    if (enrollment.studentId) {
      return;
    }

    // Look up the student by external_id
    const student = await ctx.db
      .query("students")
      .withIndex("by_external_id", q => 
        q.eq("external_id", enrollment.external_student_id))
      .unique();
    
    if (!student) {
      console.error("Missing student for enrollment:", enrollment);
      return;
    }
    
    // Update the enrollment with new Convex ID
    await ctx.db.patch(enrollment._id, {
      studentId: student._id,
      external_student_id: undefined,  // Clean up the old ID field
    });
  },
});

[azure solstice]

I'll work with that. But imagine a Students table, a Classes table, and an Enrollments table linking them. The Students and Classes now have a new Convex ID column along with their original ID column from their source DB. The Enrollments table now looks like convex_row_id | source_row_id | source_student_id | source_class_id. The Enrollments table now needs to have the source_student_id and source_class_id updates/replaced with the new respective convex_id from their tables, either that or two new columns that map the convex_id to the source_id, or something.

[jaunty lynx]

yep makes sense. that's actually the example i had until i told claude to simplify it by only doing one mapping

[azure solstice]

Why is my migration stopping after 100 documents?

[jaunty lynx]

not sure. how are you running the migration?

[azure solstice]

Both from the dashboard and from the CLI. Passing "cursor": null because it requires something. Is it maybe a paid vs free thing?

[jaunty lynx]

definitely not a paid feature :D. what CLI command are you running?

[azure solstice]

npx convex run migrations:updateTypeReferences '{"dryRun": false, "cursor": null}'

I was using a students classes example before. The actual database is devices and noting a type of device.

[jaunty lynx]

from https://www.convex.dev/components/migrations

do

export const runIt = migrations.runner(internal.migrations.setDefaultValue);

and then run that one instead

with

npx convex run migrations:runIt

[azure solstice]

I'm getting a "Property 'migrations' does not exist on type..." error when saving. Here is the open bits of the file.

`import { Migrations } from "@convex-dev/migrations";
import { components, internal } from "./_generated/api.js";
import { DataModel } from "./_generated/dataModel.js";

export const migrations = new Migrations<DataModel>(components.migrations);

export const runIt = migrations.runner(internal.migrations.updateTypeReferences);

export const updateTypeReferences = migrations.define({
table: "devices",
migrateOne: async (ctx, theDevice) => {`

[jaunty lynx]

i assume that error is coming from the internal.migrations.updateTypeReferences line? that's unexpected and unfortunate.

can you try the other method

export const run = migrations.runner();

and then

npx convex run migrations:run '{"fn": "migrations:updateTypeReferences"}'

[azure solstice]

That's what I had setup and working before anforwhaever reason it stopped after 100.

I get this when it finishes.
{ continueCursor: '0718305258bd58a98d9116be84b8ddef5a9e1bd5e782df604f55a3732e8f45dc6125e72fbc566f6af869efe74a885ba5d2f446ac6b95c20dcddd2cef8f7949cd1a678a44330b940648abceda28334488d072937c9aa3db23164d1087e9d698c84f22aa180bb765509648b91b84e3cab4e9c190410a3b2d14f704e4a8cae44aa531603d85c1e65c4a4d8fa9116a7ca71ba3493c730e', isDone: false, processed: 100

[jaunty lynx]

are you sure you ran npx convex run migrations:run '{"fn": "migrations:updateTypeReferences"}' ?

from the source code of migrations:run it doesn't look like it returns a continueCursor/isDone/processed thing.

[azure solstice]

oops. sorry, no, I had the migration in the migrations.ts file. However, when I put export const run = migrations.runner(); in the standalone file I get a "couldnot find function" error. npx convex dev finishes without error, but something ain't happening.

[jaunty lynx]

what standalone file is this?

[azure solstice]

I'm sorry, I'm doing my best to follow the docs but they can be very vague. I have a file called runThis.ts and in it is the migration . It now has this at the top.
i`mport { Migrations } from "@convex-dev/migrations";
import { components } from "./_generated/api.js";
import { DataModel } from "./_generated/dataModel.js";

export const migrations = new Migrations<DataModel>(components.migrations);
export const run = migrations.runner();

export const updateTypeReferences = migrations.define({
table: "devices",
migrateOne: `

[jaunty lynx]

ok cool. if the file is named "runThis.ts" then you want

npx convex run runThis:run '{"fn": "runThis:updateTypeReferences"}'

[azure solstice]

Oh I see now. The docs do not connect the "runTHis" with the name of the file

[jaunty lynx]

the docs assume everything is in convex/migrations.ts

e.g. this command changes too, which explains why it didn't work

export const runIt = migrations.runner(internal.runThis.updateTypeReferences);

[azure solstice]

The docs talk about non-required migrations.ts file but then seem to reference it for the rest of the time, but the context of the word "migrations" gets lost

[jaunty lynx]

i agree, it's definitely not clear when "migrations" means the name of the component vs the name of the file

[azure solstice]

OK. That was one huge missing piece of the puzzle for me.

[jaunty lynx]

for posterity, this comment is incorrect which is why your initial attempt didn't work https://github.com/get-convex/migrations/blob/2db277e970890808b0a2a4a063340313bfec1544/src/client/index.ts#L286

(the function is not recursive, and if it were then your command npx convex run migrations:updateTypeReferences '{"dryRun": false, "cursor": null}' would have worked)

[azure solstice]

OK thanks. Gotta split for a while but I will come back to this. Thanks so much. Are you a convex dev?

[jaunty lynx]

yep 😄

[bold island]

How does this look: https://github.com/get-convex/migrations/pull/7

[azure solstice]

Yeah, something like that. Maybe a suggested best practice, and/or a "here are some strategies to manage migration scripts" one file to rule them all, or multiple files based on a context

[azure solstice]

That was quicker than I thought. OK. I went back to the migrations.ts file to simplify it. The npx convex run migrations:run '{"fn": "migrations:updateTypeReferences"}' commad loops through the entire table. Any other version I've run in the CLI or dashboard stops at 100. For what its worth.

And success! Thank you so much!

[bold island]

btw if you pass the fn argument to the function directly it'll also do them all. The hint about this wasn't getting printed correctly:

console.warn(
  "Running this from the dashboard? Here's some args to use:"
);
console.warn({
  "Dry run": '{ "dryRun": true, "cursor": null }',
  "For real": '{ "fn": "path/to/migrations:yourFnName" }',
});

You can see if it's more discoverable if you install @convex-dev/[email protected]

[azure solstice]

Thanks for that. One suggestion for clarifying this, as I indicated to Lee. For someone new to convex, the context of the use of the word "migrations" gets confusing. It wasn't clear that making a file called migrations.ts (which was described as optional) is relevant to other commands that use the filename in them. It wasn't clear that "migrations" was just a word used as part of a command because we are working with migrations, vs the name of a file and therefore a part of the command structure.

The example export const runIt = migrations.runner(internal.migrations.setDefaultValue); It wasn't obvious, at least to me, that where the word migrations appears is in reference to a file name and not a connection to the migrations module. This translates to { "fn": "path/to/migrations:yourFnName" } here its obvious that its a path to something, but it was confusing because if you don't get that migrations is a reference to a filename (in the whole context of the documentation) its like, how do I know the path to the command for migrations? I'm sure this seems silly and many devs are like "duh." But for someone totally knew to this kind of database it's far from obvious.

Speaking as a primarily SQL person, all of the docs would be more helpful if, instead of a "here what this might look like" and then showing a somewhat generic version of a command, the examples had more practical context that help the example make more sense. All of the right ingredients are there in the docs, but they expect you to know the recipe when the desired dish could be something I've never cooked before and I'm not certain how to propceed.

Anyway, I'm loving this community, convenx is making more sense by the day. Just tyring to make helpful suggestions so that other SQL only people can make the leap.