Foreign key violations with SERIAL in Node.js

While following the cockroachdb example for building an app using the sequelize orm for node.js, I expanded upon it to add associations between my models. Primary keys are INT with auto-increment via unique_rowid(). Based on your documentation, this is your implementation of SERIAL.

After synching my models, I attempt to create records using the following code:

models.sequelize.sync({ force: true })
  .then(function () {
    return models.Customer.create({
      name: "Sample Customer"
    })
  })
  .then(function (result) {
    const id = parseInt(result.dataValues.id, 10)
    return models.Order.bulkCreate([
      { subtotal: 100.00, customer_id: id },
      { subtotal: 200.00, customer_id: id }
    ])
  })

When this is run, I get “error: foreign key violation: value [255737842446434300] not found in customers@primary [id]

I realize my parseInt doesn’t appear to have the precision required in order to take the string id returned from the customer record (id = “255737842446434302”) in order to generate a JavaScript integer, but I don’t know how to accomplish this. Can someone please assist? We’re evaluating whether to use CockroachDB for our product and it’s very promising tech!

hi darren -

its been my limited experience that sequelize wants to be in complete control of the primary id. can you remove the _, customer id: id code?

Hi @dcarter!

Sorry for the delay in answering your question—I know you also tried over on StackOverflow. I’ve cross-posted my answer there.

The problem, as you’ve discovered, is that IDs generated by unique_rowid(), like 255737842446434300, are too large to fit in a JavaScript Number, which can only exactly represent integers up to 253 - 1. The usual solution to this problem is to use strings instead. That is, you could simply elide the call to parseInt and pass { ..., customer_id: "255737842446434300"} directly to Order.bulkCreate.

Unfortunately, Sequelize will then generate a SQL statement that looks like this:

INSERT INTO orders (customer_id) VALUES ('255737842446434300');

PostgreSQL will happily convert that string literal into an int8, but CockroachDB will complain about using a string literal where an int8 is expected. I have open PRs to fix this on either the CockroachDB side or the Sequelize adapter side, but neither has landed yet. We’ll almost certainly have a solution for v1.1, however!


In the meantime—or if you want a solution compatible with CockroachDB v1.0—you can work around the issue by using UUID primary keys with the Sequelize.BLOB type.

Ok, we’ve updated the Sequelize adapter to solve this problem! I’ve updated the StackOverflow answer with this information as well.

The latest version of sequelize-cockroachdb, v1.0.2, teaches Sequelize to coerce strings used in numeric contexts into CockroachDB integers. Simply don’t call parseInt on result.dataValues.id and it should work as you expect.

models.sequelize.sync({ force: true })
  .then(function () {
    return models.Customer.create({
      name: "Sample Customer"
    })
  })
  .then(function (result) {
    return models.Order.bulkCreate([
      { subtotal: 100.00, customer_id: result.dataValues.id },
      { subtotal: 200.00, customer_id: result.dataValues.id }
    ])
  })

I’ve run into this myself with a raw javascript app, and I was perplexed for a few minutes. Marshaling id’s through javascript as numbers has to be very common, and we’re failing these users completely. The problem is not listed in the docs of either SERIAL or unique_rowid() (cc @jesse).
I didn’t find any issues discussing this on github. Does anyone here know if we have any plans to address this in any way, or is the current state of affairs as good as it gets?

IDs are opaque, not numbers, so my (apparently unpopular) advice is to use BYTES instead of INTEGER for your IDs. This is my advice for all languages, not just javascript. (is javascript’s byte-array support as lacking as its integer support? Then you may want to use STRING instead).

There’s not much else we can do to support people who insist on both treating IDs as integers and using a programming language that doesn’t properly support (64-bit) integers. The upcoming SEQUENCE support would help here by making the integers small enough to fit within javascript’s limitations, but at a heavy performance cost.

Not sure what the other implications would be, but representing the IDs as a string in a different base than 10 could avoid this issue. It’d probably be a pretty breaking change, but here’s an example mapping using Crockford’s base 32:

288465328529047553 => '806NP0EFV001'
288553920235765761 => '80968BX36001'
288554182571327491 => '8096G079W003'
288559807702532098 => '809BKQ191002'

Apart from avoiding the issue of unsafely treating the IDs as integers in languages without robust 64-bit integer support, this is also nice in that it makes the IDs more concise, preserves lexicographic order, and gives some more visibility into the inner workings of the SERIAL format (in that example, for instance, you can clearly see that the first two IDs were generated on node 1, the 3rd on node 3, and the final on node 2).

Again, I’m unsure of the compatibility issues of such a change, but it’s food for thought :slight_smile:

@bdarnell, what I believe some distributed Postgres implementation does is make SERIAL work by having each node preallocate batches of ints based on a central counter. When you’re running low on ids, you allocate the next batch in the background. That’s the kind of thing I had in mind. I think the cost of this should be acceptable.
I know that SEQUENCE support is coming and I was thinking that perhaps we can implement SERIAL in terms of it with the scheme above (but I don’t know what guarantees SEQUENCE is supposed to give exactly).

@twrobel, I think the compatibility here is the problem (as you’ve noticed). SERIAL is supposed to produce numbers.

A couple thoughts:

  • This is documented in the FAQs, though it sounds like it’s not very discoverable.

  • Even with sequences, we’ll need to support int4 as a column type. It’s the node-pg driver that’s (properly) marshaling int8s as strings; if you store a small-valued sequence into an int8 column, those values will get stringified on their way through the node-pg driver.

  • This issue bites people in Postgres, too. Any app that gets sufficiently large uses int8s.