Setting _creationTime during data migration
Hi! I understand that _creationTime is a reserved field that Convex generates automatically when inserting a record. We’re migrating from a MySQL database where we already have a creation_time field. Is it possible to set a custom value for _creationTime during migration, or is there another recommended approach?
9 Replies
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!
If you import data from a csv/json file as described here https://docs.convex.dev/database/import-export/, I believe that existing
_creationTime and _id (i.e. system fields) will be respected during the import.
This is the behavior, at least, when you import a snapshot from a Convex deployment - the data there contains _creationTime and _id, and they are imported as-is rather than generated at import time.The thing with importing from a CSV is that our migration will be gradual. That means a user of the current software will see a notice asking if they want to migrate to the new version of the system, and they’ll give their consent before their data is moved.
We’re not doing a full database migration all at once, because many users are still accustomed to the old system. That’s why the migration will be progressive. In this scenario, handling imports through CSV would be a bit more complex.
I personally keep my own
createdAt field for these scenarios where I'm migrating from a previously existing system.
Transform your pre-existing created timestamp to a number, and you can then use <record>.createdAt ?? <record>._creationTime moving forwardYeah what erquhart suggests is probably the way to go then. To prevent confusion, you could name your custom field
migratedAt and make that nullable. New documents, where the original is created in Convex, will then not have migratedAt set at all@erquhart
The problem with this scenario is that it can't sort by creation date when sorting queries. My biggest concern with Convex is not being able to manually provide the _id and _creationTime. This is the biggest challenge I have when migrating from another database. Why can't we provide these two when creating a document? (Especially the id.)
Generating ids and creation time for insertions is just a core part of how Convex works currently, but on the matter of how to do what you're trying to do, you can sort by the createdAt field if you add it to an index. Are you seeing this not working for you? And yeah, the id thing can be a pain in migrations as you'll need to update relations to use the new ids. To ease migration for that you can have relations be based first on the former id, which you can keep in an optional field, and secondarily on the generated id for new records.
Migrations can be tough, but it's definitely doable, happy to help figure things out any way I can.
I've been through it myself (postgres/neon/hasura/graphql -> convex), took some work, but super worth it.
Exactly, that’s the issue. While we could follow the recommendation mentioned above and use another field like legacyCreationTime, the only way to apply sorting on it in a query would be to add it to an index — which we’d rather avoid, as it would be an unnecessary index in this case.
That’s why the idea would be for _creationTime to be set based on a legacy value during a migration from one database to another.
Yeah this will require a workaround no matter what currently. Instead of
legacyCreationTime, I'd make a createdAt field, can be named whatever but not considered "legacy". You'd set this field to the value of the existing created timestamp from the previous system during migration, and set it to the value of _creationTime when creating new records in Convex, and do your sorting on that index. I understand the desire to avoid this index, but it's a requirement to do what you're trying to do currently.