Automate database tasks with Events

Did you know you don't need to rely on cron jobs or external tools to run automatic tasks in your system? In MariaDB and MySQL, for example, you can use Events, a feature that lets you schedule and run tasks directly in the database in a simple, efficient way.

What are Events?

Events are like cron jobs, but managed by the database itself. With them, you can automate recurring tasks, such as:

• Cleaning up old records.
• Periodically updating tables.
• Database maintenance, without needing external scripts.

Advantages of using Events

• No dependency on cron: everything happens inside the database, with no need for external scripts.
• Centralized management: all tasks live in the same place as your data.
• Flexible scheduling: run tasks periodically, at specific times, or just once.

Creation examples for MariaDB/MySQL

1. Imagine you want to automatically clean up old records from a table called logs every day:

CREATE EVENT clean_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

2. Run table optimization and analysis updates monthly:

CREATE EVENT clean_logs
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;

3. Imagine you have a table called invoices with invoice records. You can create an event to update the status of overdue invoices:

CREATE EVENT update_overdue_invoices
ON SCHEDULE EVERY 1 DAY
DO
  UPDATE invoices
  SET status = 'overdue'
  WHERE due_date < NOW() AND status != 'paid';

🎲 Databases that offer Events natively

✅ MariaDB: full support with CREATE EVENT, allowing task automation directly in the database.

✅ MySQL: similar to MariaDB.

❎ PostgreSQL: doesn't have native support for Events, but you can use the pg_cron extension for task scheduling.

❎ MongoDB: doesn't have native support for scheduled events, requiring external tools.

Documentation

MariaDB Events Overview

MySQL Event Scheduler Overview

pg_cron - Run periodic jobs in PostgreSQL

Thanks for reading this far, see you next time!