There is a way to enforce referential integrity in MongoDB [duplicate]

Question

When you search for referential integrity in relation to Mongo-DB the standard response is "MongoDB does not support this". The standard explanation is that MongoDB supports refs and populate, however there is nothing that prevents you changing the ref to an invalid value. This is a major deterrent for many developers coming from a SQL background.

Consalve

Answer 1

In relation to referential integrity on deletes, provided all delete requests are served by your application then it can be handled by checking ID does not exists in related collections prior to deleting records. I do this as follows

CRUD Operations (We are only concerned with Delete here - note how I am passing an array of objects being the collection and field that needs to be matched against ID of document (record) we are deleting

const express = require('express')
const router = express.Router()
const iflexCRUD = require('../../lib/iflexCRUD')

const { UnitType } = require('../../models/Unittype')
const { Unit } = require('../../models/Unit')

iflexCRUD.create(router, '/', UnitType)
iflexCRUD.read(router, '/', UnitType, { sort: 'name' })
iflexCRUD.update(router, '/:id', UnitType)
iflexCRUD.deleteByID(router, '/:id', UnitType, [
  {
    model: Unit,
    field: 'unittype'
  }
])
iflexCRUD.delete(router, '/unittype/:unittype', UnitType)

module.exports = router

CRUD Delete Handler This is a generic delete request handler that I use for CRUD operations I passes an array of Collection / Field values and checks to see if there is a single record that matches the ID of the document being deleted.

// CRUD-DELETE
iflexCRUD.deleteByID = (router, route, Collection, refs = []) => {
  router.delete(route, async (req, res) => {
    try {
      let exception = false
      //Enforce Referential Integrity for deletes - Deny when ID is used in any of refs collections
      //Loop through any referenced files (first record) to ensure there are no other collections using this document
      for (let i = 0; i < refs.length; i++) {
        if (!exception) {
          let refObj = {}
          refObj[refs[0].field] = req.params.id
          const result = await refs[i].model.findOne(refObj, (err, rec) => {})
          exception = result !== null
        }
      }
      // Process deletion of there are no exceptions
      if (!exception) {
        const doc = await Collection.deleteOne({ _id: req.params.id })
        res.send(doc)
      } else {
        return res
          .status(401)
          .json(
            'Document is already use in related collection  - it cannot Delete!'
          )
      }
    } catch (e) {
      return res.status(401).json(e.message)
    }
  })
}

Consalve

In other words, "no, you can't enforce". Only cross your fingers and pray. Even if the DB is only accessed through your API server, this code/approach is still prone to race conditions. – Brandenbrandenburg

I'm not sure I get you - I have tried to make this clear. If your application controls all reads and writes to your database then YES you can enforce it. If you allow access to the database from other sources, then NO – Consalve

This is being developed for node.js - This is not a multi-threaded solution – Consalve

"This is not a multi-threaded solution" - you're confusing "parallel" with "concurrent", I think. The issue I'm alluding to is perfectly possible in node.js environment. Even if it wasn't, data integrity relies on you having only one app server? That's a very... fragile approach. – Brandenbrandenburg

It's not perfect I know and it would be great if MongoDB had some concept of supporting this in order to bridge the gap between SQL and noSQL databases. My post is only to help others facing the same issue as me. Do you have a better solution for handling relational integrity issues using a no-SQL database? – Consalve

Don't get me wrong, I appreciate the effort (am not just an angry dude on the internet). The problem is, app-level data integrity is impossible. Sure, it may work 99.99% of time, but you never know when it'll fail. And it will. Proper data integrity must be done by the thing that stores it, the database. Anything else is false hopes. – Brandenbrandenburg

One of ways of avoiding this in mongo is doing embedding instead of referencing. – Brandenbrandenburg

Cheers - Lets hope that in time, this is something that will be addressed natively in the database – Consalve

Who knows, they just might add this. Mongodb has shard-local ACID transactions now. I say, it's 20 years away from catching up with today's postgresql :) – Brandenbrandenburg

Answer 2

THERE IS A SOLUTION!

Sure - there is no way to prevent someone changing a reference through Compass or another GUI but there is definitely a way to do it when you are developing your own API and in control of all read and write operations to the database.

I will illustrate with an example from some code I am currently working on. Firstly the UnitType.js defines a very simple collection with simply a name and an ID. The magic is in Unit.js - Have a look at the custom validator for unittype field. It issues an asynch request to the UnitType model to find the related UnitType by its _id field. If the ID provided on save or update is invalid, an error message will be returned "Invalid Object ID" otherwise record will be saved. In this way it is never possible to create or modify a record with an invalid UnitType record.

This process can be repeated if there are multiple references.

Provided you are using the same model throughout your application, there is no need to write additional code to support referential integrity

I hope this helps

// UnitType.js - MongoDB Schema
const mongoose = require('mongoose')
const UnitType = mongoose.model(
  'UnitType',
  new mongoose.Schema(
    {
      name: {
        type: String,
        required: true,
        minlength: 5,
        maxlength: 40
      }
    },
    { collection: 'unittype' }
  )
)

exports.UnitType = UnitType

// Unit.js - MongoDB Schema
const mongoose = require('mongoose')
const { UnitType } = require('./Unittype')
const Unit = mongoose.model(
  'Unit',
  new mongoose.Schema(
    {
      door: {
        type: String,
        required: true,
        minlength: 2,
        maxlength: 10,
        index: true,
        unique: true
      },
      name: {
        type: String,
        required: true,
        minlength: 5,
        maxlength: 40
      },
      location: {
        type: String
      },
      description: {
        type: String
      },
      unittype: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'UnitType',
        // Ensure that UnitType iD is valid (note isAsync is deprecated)
        validate: {
          validator: async function(v) {
            return await UnitType.findById(v, (err, rec) => rec !== null)
          },
          message: 'Invalid Object ID'
        }
      }
    },
    { collection: 'unit' }
  )
)

exports.Unit = Unit

Consalve

Hmm... I am not sure if I would count doing stuff in the application layer as "enforce referential integrity in MongoDB". Also, this does not cover the case of deleting the referenced object afterwards (which a database that enforces referential integrity would either reject or cascade). – Screeching

I tried to make it clear that this does not achieve referential integrity at the database level, but that as long as access to our database is only via our own API methods then we have achieved this. In relation to delete integrity, the delete request is controlled by our API and this can be checked in our delete methods. I'll post how I do it in the answer below – Consalve

But today MongoDB is an ACID base with Transactions. I think it's possible to check if the refs are valid before writing to the database in a transaction. – Apologete