patochem•14mo ago

Can't import ID when using cli import feature

Hi! Congrats on Convex. It's an awesome tool! I'm trying to import data using the convex cli and json files but I need to also import the ID (I have a PK/FK situation) But doing so always throws an error :

400 Bad Request: InvalidIdError: Hit an error while importing:
Provided document ID "4q2v7830x6335s3y3r56mngm9k8bh50" doesn't match '_id' field "4m04b2em2dp8ynmntab6cz929k4m528"

400 Bad Request: InvalidIdError: Hit an error while importing:
Provided document ID "4q2v7830x6335s3y3r56mngm9k8bh50" doesn't match '_id' field "4m04b2em2dp8ynmntab6cz929k4m528"

Can someone help me regarding that?

11 Replies

oscklm•14mo ago

Can you show a snippet of the json ur trying to import?

patochemOP•14mo ago

sure!

    {
        "_id" : "4m04b2em2dp8ynmntab6cz929k4m528",
        "id" : 1198,
        "level" : 2,
        "name" : "SXXXXXXXXX",
        "type" : "xxxxxxx",
        "url" : "https://xxxxxxxxxxx",
        "resource_id" : null
    },

    {
        "_id" : "4m04b2em2dp8ynmntab6cz929k4m528",
        "id" : 1198,
        "level" : 2,
        "name" : "SXXXXXXXXX",
        "type" : "xxxxxxx",
        "url" : "https://xxxxxxxxxxx",
        "resource_id" : null
    },

oscklm•14mo ago

Is there a reason why u would not want Convex to generate the ID for you? What you could do is just leave out the _id field and it would import fine. Because right now it seems to be conflicting with the document ID convex is auto generating. In case you wanted to save your _id i would just call the field something else like customId or whatever suits your application. About overwriting the ID when importing with Convex CLI - I can't tell you if it's possible, kinda looks like it isn't. But I bet someone from the Convex team can elaborate on this.

patochemOP•14mo ago

This issue is the following: I'm importing the data from an existing mySQL database where there are several tables "linked" together by PK/FK relationships I wanted to take advantage of the v.id('tableName') feature of convex to maintain those relationship. Let's imagine the following:

const ObjectSchema = {
  name: v.string(),
  ....
}

const PieceSchema = {
  name: v.string(),
  ....
}

const ObjectPiecesSchema = {
  text: v.string(),
  objectId: v.id('objects'),
  pieceId: v.id('pieces')
}

export default defineScema( {
  pieces: defineTable(PieceSchema),
  objects: defineTable(ObjectSchema),
  objectsPieces: defineTable(ObjectPiecesSchema)
})

const ObjectSchema = {
  name: v.string(),
  ....
}

const PieceSchema = {
  name: v.string(),
  ....
}

const ObjectPiecesSchema = {
  text: v.string(),
  objectId: v.id('objects'),
  pieceId: v.id('pieces')
}

export default defineScema( {
  pieces: defineTable(PieceSchema),
  objects: defineTable(ObjectSchema),
  objectsPieces: defineTable(ObjectPiecesSchema)
})

So in order to 'migrate' my db, I have to: - import the 'pieces' to convex (without Id) - import the 'objects' to convex (without Id) - export the 'pieces' and 'objects' tables with autogenerated id - update the pieceId field of the "objectsPieces" table in my database, with the autogenerated Id, to maintain the relationship - Do the same with the ObjectId - export the 'objectsPieces' table to json - import this json to convex. So far so good. However, now I need to change some fields and values in the convex 'pieces' table. So I - export the table to json - import this json to my MySQL database - work the data in MySQL - export the data back to a json file to import again in convex. I can't let convex re-generate the _id field because then it would break the relationship in the "objectsPieces" convex table. Can I solve this using the CLI?

lee•14mo ago

unfortunately this is not possible today. we're working on it -- see Snapshot Import here https://discord.com/channels/1019350475847499849/1019372556693815418/1169349065872519258 i'm a little confused about the flow where the convex table is being exported into mysql and then back into convex. can you elaborate on that? one workaround, until we support referential integrity in imports, is to make a custom id field separate from the built-in _id

patochemOP•14mo ago

sure. I'm still prototyping the app so the whole data model is not completely defined yet. That's the reason why I had to export a table from convex, add some fields and fill the values from other tables/data I have somewhere else. I could try to do all this programmatically but, working with sql is so much easier nad faster I already have that. Those id fields are the actual id of the MySql database. But what I wanted is to use the convex native Reference field to simulate a PK/FK relationship

Michal Srb•14mo ago

One nice property of Convex is that any field can be "just as good" as the built-in _id field. As long as you set up an index on that field, you get the same lookup performance.

lee•11mo ago

Follow up here: npx convex import supports importing _id fields now

Riki•10mo ago

@lee many thanks for your help. I would like to migrate to convex and use the ids I currently have in my backend. When I try to insert this entity with convex 1.10.0:

[
  {
    "_id": "02AyrRyRckYOmNgE3mel8mrjV8L2",
    "maritalStatus": "SINGLE"
  }
]

[
  {
    "_id": "02AyrRyRckYOmNgE3mel8mrjV8L2",
    "maritalStatus": "SINGLE"
  }
]

I have the following error attached. Am I doing something wrong? Thanks in advance

lee•10mo ago

@Riki where did that _id come from? convex ids fit a particular format (https://docs.convex.dev/database/import-export/import#features says "The _id field must match Convex's ID format.") so if it came from somewhere other than another convex deployment, that's not going to work

Importing Data Snapshots | Convex Developer Hub

Import data into Convex

Riki•10mo ago

Oh alright, sorry I thought Convex ID format meant somehow just a string. This id comes from another backend/db on an existing app that I would like to migrate to convex Nevermind then, I'll just create a dedicated field to store my previous ids Thanks Lee!

Can't import ID when using cli import feature

Did you find this page helpful?