How to design a system that deals with incrementing registration number in MongoDB-CodePudding

In MongoDB how to create Registration number ?

i need auto incrementing value but mongodb doesn't have auto increment by default (risk in case concurrency failure), so how to do it?

Eg. current registration no : 1 now when i insert a new record this must be 1 1 = 2

CodePudding user response：

There is no built in way to achieve this, there are a few solutions for certain situations.

For example iff you're using Mongo Realm you can define a DB trigger, I recommend following this guide

If you're using mongoose in your app there are certain plugins like mongoose-auto-increment that do it for you.

The way they work is by creating an additional collection that contains a counter that be used for every insert, however this is not perfect as it your db is still vulnerable to updates and manual human errors. This is still the only viable solution, I recommend to also create a unique index on that field to at least guarantee that quality.

CodePudding user response：

I do it like that

FindOneAndUpdate is atomic

static public async Task<long> NextInt64Async(IMongoDatabase db, string seqCollection, long q = 1, CancellationToken cancel = default)
{
    long result = 1;
    BsonDocument r = await db.GetCollection<BsonDocument>("seq").FindOneAndUpdateAsync<BsonDocument>(
        filter: Builders<BsonDocument>.Filter.Eq("_id", seqCollection),
        update: Builders<BsonDocument>.Update.Inc("seq", q),
        options: new FindOneAndUpdateOptions<BsonDocument, BsonDocument>() { ReturnDocument = ReturnDocument.After, IsUpsert = true },
        cancellationToken: cancel
    );
    if (r != null)
        result = r["seq"].AsInt64;
    return result;
}
....
await collection.InsertOneAsync(new Person() { Id = await NextInt64Async(db, "person"), Name = "Person"   i });

Find full example here https://github.com/iso8859/learn-mongodb-by-example/blob/main/dotnet/02 - Intermediate/InsertLongId.cs

CodePudding user response：

If you need to avoid gaps, you can use the following approach that involves only updates to a single document that are atomic:

First, you pre-fill the invoice collection with a reasonable amount of documents (if you expect 1000 invoices per day, you could create the documents for a year in advance) that has a unique, increasing and gap-less number, e.g.

[
  { _id: "abcde1", InvoiceId: 1, HasInvoice: false, Invoice: null },
  { _id: "abcde2", InvoiceId: 2, HasInvoice: false, Invoice: null },
  { _id: "abcde3", InvoiceId: 3, HasInvoice: false, Invoice: null },
  ...
]

There should be a unique index on InvoiceId and for efficient querying/sorting during the updates another one on HasInvoice and InvoiceId. You'd need to insert new documents if you are about to run out of prepared documents.

When creating an invoice, you perform a FindOneAndModify operation that gets the document with the lowest InvoiceId that does not have an Invoice yet. Using the updates, you assign the Invoice, e.g.:

var filter = Builders<Invoice>.Filter.Eq(x => x.HasInvoice, false);
var update = Builders<Invoice>.Update
  .Set(x => x.HasInvoice, true)
  .Set(x => x.Invoice, invoiceDetails);
var options = new FindOneAndUpdateOptions<Invoice>()
  {
    Sort = Builders<Invoice>.Sort.Ascending(x => x.InvoiceId),
    ReturnDocument = ReturnDocument.After,
  };
var updatedInvoice = await invoices.FindOneAndUpdateAsync(filter, update, options);

FindOneAndModify returns the updated document so that you can access the assigned invoice id afterwards.

Due to the atomic execution of FindAndModify there is no need for transactions; gaps are not possible as always the lowest InvoiceId is found.