Integrity Failures in Express with Cockroachdb

Integrity failures occur when an application fails to enforce data correctness, consistency, or referential integrity across operations. In an Express application using CockroachDB, these failures typically arise from a mismatch between optimistic application-level logic and the guarantees provided by the database, especially under concurrency, partial failures, or insufficient transaction usage.

CockroachDB is a distributed SQL database that provides strong consistency and serializable isolation by default. However, if an Express route does not explicitly use transactions or does not properly handle retryable errors, interleaved operations can lead to anomalies such as lost updates, dirty reads, or constraint violations that should have been prevented. For example, consider a financial transfer endpoint that reads a balance, computes a new value, and writes it back without a transaction:

app.put('/transfer', async (req, res) => {
  const { fromId, toId, amount } = req.body;
  const client = await pool.connect();
  const fromAcc = await client.query('SELECT balance FROM accounts WHERE id = $1', [fromId]);
  if (fromAcc.rows[0].balance < amount) {
    return res.status(400).send('Insufficient funds');
  }
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $3', [amount, toId]);
  client.release();
  res.send('OK');
});

This pattern is vulnerable to race conditions: two concurrent transfers can both read the same balance, each see sufficient funds, and then both deduct their amounts, resulting in a negative balance or lost updates. Although CockroachDB prevents dirty reads and ensures serializable execution, the application logic here does not serialize writes to the same rows, so interleaved statements can produce inconsistent state. This is an integrity failure rooted in missing transaction boundaries.

Another common integrity risk in Express with CockroachDB involves improper handling of unique constraints and foreign keys during batched or conditional inserts. For instance, an endpoint that creates a parent record and then, based on a computed value, creates child records might fail to maintain referential integrity if the parent ID is not correctly propagated within a transaction:

app.post('/order', async (req, res) => {
  const { customerId, items } = req.body;
  const client = await pool.connect();
  const insertCustomer = await client.query('INSERT INTO customers (id, name) VALUES ($1, $2) RETURNING id', [customerId, 'Example']);
  const parentId = insertCustomer.rows[0].id;
  for (const item of items) {
    await client.query('INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)', [parentId, item.productId, item.qty]);
  }
  client.release();
  res.status(201).json({ orderId: parentId });
});

If one of the item inserts fails (e.g., due to a missing product or a constraint), the customer record is already committed, creating an orphaned entity and breaking referential integrity. CockroachDB’s constraints will reject the bad item insert, but without a transaction that includes the customer insert, there is no automatic rollback of the customer record. This exposes an integrity failure where partial writes corrupt the dataset.

Finally, integrity failures can be exacerbated by incorrect isolation expectations. Developers might assume read-committed is the effective isolation level and rely on application-side checks instead of database constraints. CockroachDB defaults to serializable isolation, which prevents write skew, but if application code uses explicit locking or relies on non-repeatable reads within a transaction, subtle bugs can emerge. For example, checking existence and then inserting without holding a consistent lock can lead to duplicate entries under concurrency:

app.post('/ensure-tag', async (req, res) => {
  const { name } = req.body;
  const client = await pool.connect();
  const exists = await client.query('SELECT 1 FROM tags WHERE name = $1', [name]);
  if (exists.rows.length === 0) {
    await client.query('INSERT INTO tags (name) VALUES ($1)', [name]);
  }
  client.release();
  res.send('done');
});

Two concurrent requests can both see the tag as missing and both insert it, violating uniqueness if there is no database-side constraint. The correct approach is to rely on CockroachDB constraints and handle serialization errors, not to implement integrity checks at the application layer alone.

In summary, integrity failures in Express with CockroachDB typically stem from missing or misused transactions, inadequate constraint usage, and incorrect assumptions about isolation. The database can provide strong guarantees, but the application must structure its operations to leverage those guarantees consistently.

To remediate integrity failures, Express applications using CockroachDB should embrace explicit transactions, proper constraint design, and error handling aligned with CockroachDB’s serializable semantics. Below are concrete, production-grade patterns.

1. Use explicit transactions for multi-statement operations

Wrap all related reads and writes in a client transaction to ensure atomicity and consistency:

app.put('/transfer-safe', async (req, res) => {
  const { fromId, toId, amount } = req.body;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const fromAcc = await client.query('SELECT balance FROM accounts WHERE id = $1 FOR UPDATE', [fromId]);
    if (fromAcc.rows[0].balance < amount) {
      await client.query('ROLLBACK');
      return res.status(400).send('Insufficient funds');
    }
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $3', [amount, toId]);
    await client.query('COMMIT');
    res.send('OK');
  } catch (err) {
    await client.query('ROLLBACK');
    if (err.code === '40001') {
      // Serializable retry error
      return res.status(409).send('Conflict, please retry');
    }
    res.status(500).send('Internal error');
  } finally {
    client.release();
  }
});

The FOR UPDATE clause locks the rows for the duration of the transaction, preventing concurrent modifications and ensuring serializable execution. Handling CockroachDB’s serialization retry error (SQLSTATE 40001) is essential for robust operation.

2. Enforce referential integrity with transactions and constraints

Always perform inserts that depend on other entities within the same transaction, and rely on foreign key constraints:

app.post('/order-safe', async (req, res) => {
  const { customerId, items } = req.body;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO customers (id, name) VALUES ($1, $2) ON CONFLICT (id) DO NOTHING', [customerId, 'Example']);
    for (const item of items) {
      await client.query('INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)', [customerId, item.productId, item.qty]);
    }
    await client.query('COMMIT');
    res.status(201).json({ orderId: customerId });
  } catch (err) {
    await client.query('ROLLBACK');
    if (err.code === '23503') {
      // Foreign key violation
      return res.status(400).send('Referential integrity violation');
    }
    res.status(500).send('Internal error');
  } finally {
    client.release();
  }
});

Using ON CONFLICT DO NOTHING avoids duplicate customer creation, and foreign key constraints ensure that invalid order_id values are rejected by the database.

3. Avoid application-side checks for uniqueness; use constraints and handle retries

Replace existence checks with constraints and handle serialization errors gracefully:

app.post('/tag-safe', async (req, res) => {
  const { name } = req.body;
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO tags (name) VALUES ($1)', [name]);
    await client.query('COMMIT');
    res.status(201).send('Created');
  } catch (err) {
    await client.query('ROLLBACK');
    if (err.code === '23505') {
      // Unique violation, tag already exists — safe to ignore
      return res.status(200).send('Already exists');
    }
    if (err.code === '40001') {
      return res.status(409).send('Conflict, please retry');
    }
    res.status(500).send('Internal error');
  } finally {
    client.release();
  }
});

This pattern leverages CockroachDB’s unique constraint to enforce integrity, avoiding race conditions. The 23505 error code indicates a unique violation; returning a 200 status is acceptable if the business logic allows idempotent creation.

By combining explicit transactions, robust constraint usage, and proper error handling, Express applications can fully leverage CockroachDB’s consistency guarantees and avoid integrity failures.