slim acorn
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)?
twin spoke
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,
});
}
});
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
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!
slim acorn
I’m more worried it will use up my bandwidth for the month, not on the difficulty of doing it
twin spoke
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
slim acorn
Ok, just wanted to check. Yeah database bandwidth is my biggest concern, it tends to fill up pretty fast for me
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
twin spoke
pretty cool - if it takes off would you get a Pro account or are you constrained to free hosting?
slim acorn
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.
slim acorn
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
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
twin spoke
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"
slim acorn
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)
I set first_name as v.optional(v.string()) but the validator is showing as v.string() is the main issue
twin spoke
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
slim acorn
It originally was NaN and i changed it to null as a test, I thought the above definition was what v.optional didi?
twin spoke
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());
we've thought about having v.nullable in the past
slim acorn
Ah I see, convex did not like your nullableString unfortuinatley
twin spoke
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
slim acorn
Its a name so it shouldnt be either, the null still errors so I think someting else may be at play
twin spoke
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
slim acorn
Ah didnt catch that all good now
I think my migration is officially over, thanks for all your help!
glass maple
You might be surprised how easy a "migration" can be. For instance, you could do...
@twin spoke, 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)?
ripe plank
@glass maple 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:
