Create TTL indexes for models

[martineboh]

Problem

Create Time to Live (TTL) indexes on the model and let database do it automatically.

We are currently working on an app using Prisma with Postgres, and we wanted to set expiration time for entries in a row, something already available in Redis, Mongo and also Postgres (using TRIGGERS) or storing a timestamp and then manually code some sort of cron job to check what entries have expired. How to do this is currently not possible with Prisma (unless editing the generated SQL).

In Mongo:

👉 TTL indexes are special single-field indexes that MongoDB can use to automatically remove documents from a collection after a certain amount of time or at a specific clock time.

👉 A background thread in mongod reads the values in the index and removes expired documents from the collection.

Use Cases:

We want data entries/rows to be deleted automatically when the database table starts overflowing and our database memory reaches the max limit after a set time without manually writing cron jobs.

Few types of data that need to be cleaned periodically are:

• logs,
• user sessions,
• notifications,
• advertisements for limited time offers and discounts etc...

Consider a coupon generator app with Prisma, with the following schema:

model Coupon {
  id       String       @id @default(cuid()) @expire(after: “60s”, when: “useCount>100”)
  code     String       @unique
  region   RegionEnum?  @default(NONE)
  useCount Int
  users    CouponUser[]
  // ...
}

In this case, we want Postgres to set EXPIRE time for each coupon based on a directive @Expire(after: “60s”, when: “useCount>100”) or just delete the affected row after a minute of insertion @Expire(after: “1m”)

Also consider a Chat app using Prisma and Postgres, that allows messages to be deleted 60 seconds after the message is marked as ‘read’ with the following schema:

model Messages {
  id          String  @id @default(cuid()) @expire(after: “60s”, when: “read=true”)
  text        String
  sender_id   String
  receiver_id String
  read        Boolean @default(false)
  // ...
  expireAfter Int     @default(60)
  // ...
}

Suggested solution

Create Time to Live (TTL) indexes on the model and let database do it automatically after a certain amount of time or at a specific clock time. Prisma can then generate Postgres/SQL TRIGGERS based on the @expire directive for every model.

Alternatives

Our current alternative is using a cron to check for an expiring timestamp - to delete old records, which is heavy on the app. Postgres should be able to handle this seamlessly.

I really think Prisma should provide this sort of functionality, and should be in the roadmap as a feature for future releases.

[chronark]

This would be great.
I am currently using prisma with postgres for authentication among other things and I have a model for timed-one-time-passwords that would benefit from this

Right now I have an expiry DateTime field and when validating the token I check if it's in the past and delete if necessary. but that's not failproof because the database would grow if tokens are not redeemed but simply time out.

[dialnco]

TTL functionality would be a really great addition!
As mentioned, is already available on some DBs and it is a well-known functionality.
I think the straightforward approach would be setting a simple timestamp for the TTL field.

Providing this with logic as @expire(after: “60s”, when: “read=true”) could make this feature implementation more difficult to integrate on Prisma than a simpler version using timestamps as:
expireTTL DateTime @expire
or
expireTTL DateTime @ttl.

[matthewmueller]

Is this something that's supported natively by the MongoDB driver?

If so, you could probably use the raw query support we recently released. Not ideal, but might be a decent enough workaround until we support this feature.

Let us know!

[KrishGarg]

Update 2: those who just want to get it done... here

Is this something that's supported natively by the MongoDB driver?

If so, you could probably use the raw query support we recently released. Not ideal, but might be a decent enough workaround until we support this feature.

Let us know!

Yes. It is supported by the official driver and mongoose.
Can you please give an example of how can we do the same with the raw query in Prisma as there isn't much information in the docs about running raw commands?
Also, will there be any issues in running the command multiple times? Like it may run every time we restart the server and create multiple indexes or something?

Update:
I googled around and found some resources which might help others.
Raw Commands: https://www.mongodb.com/docs/manual/reference/command/
On recreation of an index on app startup: https://www.mongodb.com/community/forums/t/behavior-of-createindex-for-an-existing-index/2248

[mjn81]

hi! is there still no TTL support for prisma?

[janpio]

No, otherwise this issue would be closed and we would have documentation for how to use and create them.

[n10000k]

Any update / eta on this feature?

[arthurfiorette]

@janpio, this feature will be implemented in a close future?

[janpio]

We do not make statements about when features will be tackled until we tackle them. Right now this is not on our definitive roadmap, so we are not making any statements.

The original issue asks for support for other databases via constructs like triggers - that is definitely far off, as we are focussing on database features first (which might include the ability to define a trigger like that).

For MongoDB where this is a feature of the database itself, I see much higher chances: https://www.mongodb.com/docs/upcoming/core/index-ttl/

I also notice that the suggestion adds a new fiel level property. Would this not be more something to add to @@index as a additional attribute and maybe a new type? @@index([foo], expireAfterSeconds: 3600) or maybe @@index([foo], type: ttl, expireAfterSeconds: 3600)? Or is there a reason I am missing?

[janpio]

Related functionality for CockroachDB (which implements this on a table level (which makes a lot more sense than an index to me...)): #13982

[alanxp]

still wating for this implementation for mongodb TTL

[KhalilMeziane]

i come from future(08/23) and they are no updates

[sergio-toro]

This would be really helpful... right now we rely on a cron to ensure the index is being created in the service that needs it because every new deployment removes the index from the collection... 🤦🏽

[tiarebalbi]

Any specific reason we are not moving ahead with this ticket? Anyone working on it? Happy to contribute here

[alanxp]

i had to give up on Prisma, currently only using mongodb driver with JSON schemas untill they add this feature, it's very important.

P.S. I'm like many of you that keeps constantly ending up in this post checking for news and yet theres nothing .. lol

[ahsanzizan]

Is there any update on this issue?

[RashJrEdmund]

I just started out with Prisma, and I need this feature for a task.
Is there still no news on when these updates will be made??

[AbdurrahmanTalha]

any updates?

[sergio-toro]

Yeah, we've been following this since a long time ago, currently, we have to run a specific script after model changes to restore the TTL indexes that we are creating manually due to the lack of this crucial feature on the Prisma library

[broisnischal]

are we going to get it anytime soon?

[rojanmagar2001]

when we are getting this feature?

[alanxp]

when we are getting this feature?

Just use MongoDB driver and set TTL on the desired collection and time.

Unfortunately, Prisma wont do that update anytime soon. They prioritize SQL, not NoSQL. I already left prisma and only use the mongodb driver with JSON schemas made from zod.

[MichaelG824]

This would be incredibly useful to have.

[Lucasmm016]

Is there anything new? I'm leaving prism because of this very important role.

[Exp1ry]

Hey everyone

I've created my own function that deletes a record using setTimeout.

This is the best approach i've found using code, as it is purely async.

  // After email is sent, wait 1 minute. If user hasnt verified, we delete the OTP.
      setTimeout(async () => {
        const userAfterEmail = await prisma.user.findFirst({
          where: { email: user.email },
        });

        if (userAfterEmail && !userAfterEmail?.email_verified) {
          await prisma.oTP.delete({ where: { user_id: userAfterEmail.id } });
        }
      }, 60000);

[Wiper-R]

Hey everyone

I've created my own function that deletes a record using setTimeout.

This is the best approach i've found using code, as it is purely async.

  // After email is sent, wait 1 minute. If user hasnt verified, we delete the OTP.
      setTimeout(async () => {
        const userAfterEmail = await prisma.user.findFirst({
          where: { email: user.email },
        });

        if (userAfterEmail && !userAfterEmail?.email_verified) {
          await prisma.oTP.delete({ where: { user_id: userAfterEmail.id } });
        }
      }, 60000);

This solution is real bad, if your service goes down, then the record will stay there forever

[AbdurrahmanTalha]

A simple solution might be to just create a cron job that checks if the current time is greater than the deletion time and if it is greater then automatically delete :)