Self HostedToolsWebDatabases

how to store auto increment ID in appwrite PK

Dec 27, 2023, 17:05

View on Discord

Votes

Replies

Participants

Unknown

Messages

SuiiiOriginal post

Rank 3: Container

Nov 3, 2023, 13:46

how to store auto increment ID in appwrite PK

Summary

The user is asking how to store an auto-increment ID in Appwrite's primary key (PK). They mention that they have seen implementations of numeric incrementing and decrementing in the underlying database library but not in the Appwrite API. The user suggests using a locking mechanism to prevent concurrent calls from giving the same number to multiple requests. They propose creating a `counter` collection with a document having an attribute `value` set to `1` and a `counterLock` collection with a document representing the lock. The user provides sample code for implementing this logic. They also mention a concurrency issue and suggest using a query to get the

ideclon

Rank 5: Hypervisor

Nov 3, 2023, 16:02

Yeah, that’s not supported at the moment. You’ll have to check yourself what the last entry is an increment yourself

Suiii

Rank 3: Container

Nov 3, 2023, 16:02

any recommendation

ideclon

Rank 5: Hypervisor

Nov 3, 2023, 16:04

When you’re creating a new Document, first listDocuments, limit 1, ordered newest first, to get the last record created, and then create your new document with that+1

ideclon

Rank 5: Hypervisor

Nov 3, 2023, 16:05

Although if it’s possible that multiple records will be created at the same time, you’ll have to have a way to handle race conditions

Suiii

Rank 3: Container

Nov 3, 2023, 16:06

please also suggest the concurrency creation. if you don mind

ideclon

Rank 5: Hypervisor

Nov 3, 2023, 16:09

https://discord.com/channels/564160730845151244/1144319218528026697

Meldiron

Nov 3, 2023, 16:22

Hey there 👋 You could create Appwrite Function to implement this logic. You could make getId function, which would return to you a next available number, starting at 1.

This function could use locking mechanism to ensure 2 concurrent calls dont do DB queries, which prevents giving same number to 2 requests.

For example, you could have collection counter and in there 1 document with attribute value set to 1.

So in theory you could simply get this document, do +1, save document, and return incremented value.

To introduce locking, make collection couterLock, which could have just 1 boolean attribue locked. If there is document, couter is locked. If not, not locked. You also need to create document here with custom ID. No matter what ID but you need to make sure it's same always. That way unique index wont let you create 2 locks.

With that in mind, you could do something along lines:

locked = true

# Wait to be unlocked, and lock
while locked
  try
    get couterLock document
    sleep 1 second
  catch
    # Document not found - not locked

    try
      create couterLock document
      locked = false
    catch
      # Cannot create lock doc, someone else already made it
      sleep 1 second

# Code protected against race condition with concurrency
get counter document
increment value by 1
update counter document

# Unlock
delete couterLock document

Hope this helps 🙌

Suiii

Rank 3: Container

Nov 3, 2023, 16:26

very handy approach. I am just courious, is the https://github.com/appwrite/appwrite/issues/3905 coming out soon

Suiii

Rank 3: Container

Nov 3, 2023, 16:28

any sample function of this

Meldiron

Nov 3, 2023, 16:29

I have seem implementation of numeric incremental and decremental for both integers and floats in our underlying database library. I ahve not yet seen any PRs in Appwrite API itself. Most likely it wont be coming in next version release, unless it's last-minute implementation.

Meldiron

Nov 3, 2023, 16:30

I havent written any yet, and didnt see anyome implement it. But I did very similar approach in Appwrite when I needed to ensure locking for OAuth endpoints to get GitHub user information.
That makes me confident about the approach.

Suiii

Rank 3: Container

Nov 3, 2023, 16:31

okay, I will try to check this out.

Join the discussion

Reply to this thread by joining our Discord.

Reply on Discord

Need support?_

Join Discord Get premium support