CODEWITHSUNDEEP
javascriptsqlnode.jsasynchronous
Tushi Devi
Tushi Devi

Reputation: 11

How to run a SQL query on time intervals using node js and javascript

I am developing a parking system web application, where users can book parking slots. I have two tables in my database:

accounts (userID, username, Registration_plate_num,email,phone_num)

tickets (ticketID*,startingDate_time,endingDate_time,userID)

I would like to update my tickets table in away that every few minutes I check in the table to see if I find a ticket whose endingDate_time is actually smaller than "current" date_time, which implies that this ticket has been expired and is not longer needed in the database and should be removed.

Now, I would like to run this SQL query function in the **background ** as soon as I start running my web server. I have no problem in writing such as function, however, I am confused on where I meant to write the function So it runs in parallel with my web application.

I would really appreciate if i could get some guidance on how I could run this query in parallel, so once the server has started, the SQL query function gets called every few minutes and deletes the tickets that have been expired.

Thank you.

Upvotes: 1

Views: 647

Answers (1)

Matthew
Matthew

Reputation: 25763

It would probably be best to NOT tie this to your web application.

Some reasonings are:

  • What if you run more than 1 instance of your web application?
  • What if your web application runs on demand (such serverless cloud functions) and is not a long-running process?
  • What if your web application restarts, what is keeping track of how often the task gets invoked?

Instead, a few options may be a better choice:

  • Implement a CRON job on a server that invokes a (private) URL on your web application.
    1. Create a URL that accepts an authorization header / POST body
    2. Configure a CRON on a SINGLE application server (or leverage a 3rd party service) to run at a regular interval
  • Leverage your database, many databases can run scheduled tasks
    1. MySQL, Postgres, and SqlServer all support running crons
  • Create a dedicated worker process outside of your web application
    1. This can be something triggered via CRON, cloud function on a timer, etc.

If your scheduled task is going to be 100% database changes, and nothing else (such as sending emails, invoking APIs, etc.) then the database CRON job may be the most robust and simplest option.

Upvotes: 1

Related Questions