glucinater•14mo ago

Is there a way to rename columns in a table?

I'm in the process of switching a schema in my project from v.any() to a more properly defined setup. However some of the columns in my table are in the format "A-", "B+", etc so I am not able to deploy anymore. Is there a way to rename the columns in my table without a migration(on the free tier and its a large table)?

24 Replies

ian•14mo ago

You might be surprised how easy a "migration" can be. For instance, you could do:

export const changeColumn = internalMutation(
  async (ctx) => {
    for await (const row of ctx.db.query('mytable')) {
      await ctx.db.patch(row._id, {
        Aplus: row["A+"],
        "A+": undefined,
      });
    }
});

export const changeColumn = internalMutation(
  async (ctx) => {
    for await (const row of ctx.db.query('mytable')) {
      await ctx.db.patch(row._id, {
        Aplus: row["A+"],
        "A+": undefined,
      });
    }
});

And use the dashboard or npx convex run to run it if you can load all of the rows in one transaction, this is all it takes. I have some migration helpers linked elsewhere for when you need to do it in batches (for a table with thousands of rows) You can also try out the "Bulk Edit" functionality on the dashboard - use the button to select all rows, and apply a patch. It doesn't do much for column renames where you want to preserve the existing column's data though

ian•14mo ago

I'm guessing "large table" means thousands of rows, so this post is more relevant: https://stack.convex.dev/migrating-data-with-mutations Soon I hope that you can just import this helper from an npm package - sorry you have to copy-paste for now!

Migrating Data With Mutations

Using mutations to migrate data in Convex.

glucinaterOP•14mo ago

I’m more worried it will use up my bandwidth for the month, not on the difficulty of doing it

ian•14mo ago

If it's batches of 100, it shouldn't be many function calls. is it database bandwidth you're worried about? To answer your question, we don't have a way to rename columns in a way that doesn't involve re-writing all of your documents in the table. Give it a shot and if you run into limit issues holler - if this is a serious project I wouldn't want you to feel constrained by limits. I'd love to hear what you're working on

glucinaterOP•14mo ago

Ok, just wanted to check. Yeah database bandwidth is my biggest concern, it tends to fill up pretty fast for me

glucinaterOP•14mo ago

its a semi-serious project, essentially I found a way to link together professor and class data for my uni and create a basic site to see what classes/profs to avoid. A ~messy~ version is here (https://gradeapp-five.vercel.app) but I'm currently recreating the UI with shadcn and v0.dev. There are about ~70k rows in the table

CourseGrade

Generated by create next app

ian•14mo ago

pretty cool - if it takes off would you get a Pro account or are you constrained to free hosting?

glucinaterOP•14mo ago

Im open to getting a pro account if needed, for now its really only used by me and my friends so don't really hit the limits unless I have to mess with a certain table in my db.

glucinaterOP•14mo ago

I just ran the mutation following this guide (https://stack.convex.dev/migrating-data-with-mutations) and got some weird behavior: When using the runMigration action I got this output

Migrating Data With Mutations

Using mutations to migrate data in Convex.

glucinaterOP•14mo ago

I used the same guide for something similar in the summer and I believe it ran without issue, was the migration helper updated since then? I also checked the table via the dashbaord and did not see any new columns

ian•14mo ago

It looks like it ran once correctly, doing 100 items. To run it for all, call the runMigration action with that parameter the name is likely "someFile:updateGradeColumns"

glucinaterOP•14mo ago

Ah I see, the migration worked but I'm not able to filter on the data tab by the new schema I made (at least not autofill). Also the optional validator has started giving my weird behavior for one of my columns (switched to v.any as a temp measure)

glucinaterOP•14mo ago

I set first_name as v.optional(v.string()) but the validator is showing as v.string() is the main issue

ian•14mo ago

v.union(v.null(), v.string()) seems to be what you want, you have one with a value of "null" by the error you can check that document by its ID to see what the value is

glucinaterOP•14mo ago

It originally was NaN and i changed it to null as a test, I thought the above definition was what v.optional didi?

ian•14mo ago

Optional is undefined or "Unset" as it shows in the dashboard. You can do this once to save some typing:

const nullableString = v.union(v.null(), v.string());

const nullableString = v.union(v.null(), v.string());

we've thought about having v.nullable in the past

glucinaterOP•14mo ago

Ah I see, convex did not like your nullableString unfortuinatley

ian•14mo ago

NaN is a v.number() I believe? so v.union(v.number(), v.null(), v.string()) if it really could be any of those 3? I would look at that name and try to figure out why the firstname was being parsed as a number in whatever code inserted it

glucinaterOP•14mo ago

Its a name so it shouldnt be either, the null still errors so I think someting else may be at play

ian•14mo ago

my guess is you want v.optional(v.string()) for FirstName and just fix the data to match The null error is earlier in the terminal, the new error is only the last one and now it's complaining about a different document, so the "null" fixed it for that document I'm guessing It only shows one error at a time, it doesn't list every document at once that doesn't match

glucinaterOP•14mo ago

Ah didnt catch that all good now I think my migration is officially over, thanks for all your help!

yanis•9mo ago

@ian, how do you do this for a table called "blocks" to rename column "player" to "byPlayer" while preserving contents of the rows that exist (less than 100)?

Michal Srb•9mo ago

@yanis you cannot do this on the dashboard (right now), as the dashboard only allows queries. Also the migration helpers have been improved. Follow this article for instructions, and let us know if you run into any problems: https://stack.convex.dev/migrating-data-with-mutations

Stateful Migrations using Mutations

Online migrations in Convex using mutations. Including a helper to track migration state!

ian•9mo ago

If you use the helpers in that doc:

const toByPlayer = migration({
  table: "blocks",
  migrateOne: (ctx, block) => ({
    byPlayer: block.player,
    player: undefined,
  },
});

const toByPlayer = migration({
  table: "blocks",
  migrateOne: (ctx, block) => ({
    byPlayer: block.player,
    player: undefined,
  },
});

Is there a way to rename columns in a table?

Did you find this page helpful?