Want all records to be deleted given a specified datetime
Basically, I want to be able to delete a record, which contains a datetime value called lets say
deleteAt, at the time deleteAt specifies. For example, if a user creates a post, and assigns 4/15/23 12:00 AM as the deleteAt value, how can I delete it at that time, given that I'm using the t3 stack?9 Replies
Easiest way IMO would be to setup your database queries such that any posts with a
deleteAt value after the current time are not queried from your table, so any user-facing behavior will make them appear as deleted.
This could also be useful if a user wants to restore something they marked to delete after a while, or view a history of all posts (deleted & non-deleted).
If you really want these posts to be deleted from your table, I would still do the above, but also setup a cron job to just delete any posts that have a deleteAt value after the current time. If you're using vercel, I believe they added support for crons like that: https://vercel.com/blog/cron-jobs.
This would not delete the post exactly at the time specified by the user, but with the query restriction AND the cron job, it would effectively accomplish what you're looking for.ah i see, thanks!
If you use upstash as theo recommend, you can use their qstash feature (for scheduled task) https://docs.upstash.com/qstash
Getting Started | Upstash: Documentation
Introduction
What you’re after is called TTL. Ideally you’d have the database to automatically delete based on a TTL column (other databases like dynamo automatically support this)
But the suggested workarounds are just fine too
If you are using Prisma, I don't think using TTL is possible for now. Correct me if I'm wrong.
https://github.com/prisma/prisma/issues/5430
GitHub
Create TTL indexes for models · Issue #5430 · prisma/prisma
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 ...
If you restrict yourself to using Prisma only then yeah I guess you’re in a rut. Native support is possible in all these dbs. It’s use the Prisma schema which doesn’t have a way to define it
We can write the event in raw query and then use prisma
$queryRaw to execute it.
https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access
Here is the example to create event in SQL:
https://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/Prisma
Raw database access (Reference)
Learn how you can send raw SQL and MongoDB queries to your database using the raw() methods from the Prisma Client API.
Founder
MySQL Tutorial
Working with MySQL Scheduled Event
In this tutorial, you will learn about MySQL event scheduler and how to create MySQL events to automate database tasks.
Like that @jingleberry? This new to me. Thanks by the way.
Yeah I never tried that before but it looks like it’d do the job!