MySQL and Twilio integrate through MySQL triggers that write event records to a dedicated notification queue table, combined with a polling listener process that reads pending rows and dispatches Twilio SMS, enabling SMS notifications from MySQL row-level events without requiring any changes to application code. This integration is used by teams running MySQL on-premises or on Amazon RDS, Google Cloud SQL, or Azure Database for MySQL who want SMS alerts for new records, status transitions, and threshold conditions directly from the database without adding a separate event bus. The MySQL trigger-plus-queue pattern is preferred over direct HTTP calls from triggers because MySQL does not natively support outbound HTTP, making the queue table the reliable bridge between the database and the Twilio API.
What You Need Before You Start
Confirm that the MySQL user your application uses has the TRIGGER privilege on the target database by running SHOW GRANTS FOR 'your_user'@'%'; in the MySQL CLI and verifying TRIGGER appears in the grant list, or granting it with GRANT TRIGGER ON your_database.* TO 'your_user'@'%';. Create the notification queue table by running: CREATE TABLE sms_queue (id INT AUTO_INCREMENT PRIMARY KEY, recipient_phone VARCHAR(20) NOT NULL, message_body TEXT NOT NULL, status ENUM('pending','sent','failed') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, processed_at TIMESTAMP NULL, error_message TEXT NULL); which serves as the relay between MySQL triggers and the Twilio listener. Install the mysql2 and twilio npm packages in your listener project by running npm install mysql2 twilio dotenv and store your Twilio Account SID, Auth Token, From Number, and MySQL connection string as environment variables. From Twilio, provision an SMS-capable phone number and complete A2P 10DLC registration in the Twilio Console before enabling US-bound outbound messaging.
Step-by-Step Integration Guide
Create a MySQL AFTER INSERT trigger on your orders table that writes a row to the sms_queue table: DELIMITER // CREATE TRIGGER after_order_insert AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO sms_queue (recipient_phone, message_body) VALUES (NEW.customer_phone, CONCAT('Hi ', NEW.customer_name, ', order #', NEW.id, ' has been confirmed. Total: $', NEW.total)); END // DELIMITER ;. Create an AFTER UPDATE trigger for status change notifications: DELIMITER // CREATE TRIGGER after_order_status_update AFTER UPDATE ON orders FOR EACH ROW BEGIN IF NEW.status <> OLD.status AND NEW.customer_phone IS NOT NULL AND NEW.customer_phone <> '' THEN INSERT INTO sms_queue (recipient_phone, message_body) VALUES (NEW.customer_phone, CONCAT('Order #', NEW.id, ' status updated to: ', NEW.status)); END IF; END // DELIMITER ;. Write the Node.js polling listener that reads pending rows from sms_queue and dispatches Twilio SMS: const mysql = require('mysql2/promise'); const twilio = require('twilio'); const client = twilio(process.env.TWILIO_ACCOUNT_SID, process.env.TWILIO_AUTH_TOKEN); async function processQueue() { const conn = await mysql.createConnection(process.env.DATABASE_URL); const [rows] = await conn.execute('SELECT * FROM sms_queue WHERE status = ? LIMIT 10 FOR UPDATE SKIP LOCKED', ['pending']); for (const row of rows) { try { const msg = await client.messages.create({ to: row.recipient_phone, from: process.env.TWILIO_FROM_NUMBER, body: row.message_body }); await conn.execute('UPDATE sms_queue SET status = ?, processed_at = NOW() WHERE id = ?', ['sent', row.id]); } catch (err) { await conn.execute('UPDATE sms_queue SET status = ?, error_message = ?, processed_at = NOW() WHERE id = ?', ['failed', err.message, row.id]); } } await conn.end(); } setInterval(processQueue, 5000);. Run the listener with PM2 using pm2 start listener.js --name mysql-twilio-listener to ensure it restarts on failure.
Common Issues and How to Fix Them
MySQL triggers do not fire for bulk INSERT or UPDATE operations executed with INSERT INTO ... SELECT or UPDATE ... WHERE using a non-row-based binary log format in replication environments, causing SMS to be missed for batch operations. Confirm your MySQL server uses row-based binary logging by checking the variable with SHOW VARIABLES LIKE 'binlog_format'; and if it shows STATEMENT, change it to ROW by setting binlog_format=ROW in your my.cnf, which ensures triggers fire for every affected row in bulk operations. The FOR UPDATE SKIP LOCKED clause in the listener query requires MySQL 8.0 or higher, and on older MySQL 5.7 instances the query fails with a syntax error, causing the listener to crash and stop processing the SMS queue. For MySQL 5.7 compatibility, replace SKIP LOCKED with a status-based lock using an additional column such as locked_at TIMESTAMP NULL in the sms_queue table, updating locked_at to NOW() in the SELECT step and only selecting rows where locked_at IS NULL AND status = 'pending'. The MySQL AFTER INSERT trigger writes to sms_queue even when the parent INSERT into orders is later rolled back within the same transaction, causing SMS to be sent for orders that never committed and do not actually exist. Convert the sms_queue insert to occur only after a confirmed commit by using an AFTER INSERT trigger that writes to sms_queue using a separate stored procedure invoked from application code after transaction commit, or by having the listener cross-check each sms_queue row's linked order ID against the orders table before dispatching.
How to Get More from This Integration
Build a MySQL Event Scheduler-based alert by creating a MySQL EVENT using CREATE EVENT inventory_alert ON SCHEDULE EVERY 15 MINUTE DO INSERT INTO sms_queue (recipient_phone, message_body) SELECT manager_phone, CONCAT('Low stock alert: ', product_name, ' has only ', quantity, ' units remaining') FROM products WHERE quantity < 10 AND quantity > 0; enabling a fully database-internal scheduled SMS alert that does not require any external cron job or application scheduler. Add retry logic to the listener by creating a separate retry process that queries sms_queue for rows where status equals failed and created_at is less than 1 hour ago, retrying each failed SMS dispatch with the same Twilio call and updating the status back to pending with a retry_count increment, providing automatic recovery from transient Twilio API errors without manual intervention. Create a multi-recipient SMS by modifying the trigger to look up all team members who should be notified from a notification_subscriptions table filtered by event type, and inserting one sms_queue row per subscriber phone using a cursor within the trigger body, enabling role-based SMS notification distribution driven entirely by MySQL data. Extend the integration to include an inbound SMS reply log by configuring your Twilio inbound SMS webhook to call a PHP or Node.js endpoint that inserts the inbound message into a separate inbound_sms table with the From phone, Body, and timestamp, allowing your MySQL-based application to JOIN the inbound replies against outbound records in sms_queue to build a conversation history view.
Conclusion
MySQL triggers with a queue table and Twilio together create a reliable, database-native SMS notification system that works with any MySQL-compatible hosting without requiring changes to application code. Contact Telphi Consulting to build and deploy a MySQL Twilio SMS integration for your database environment.
Ready to Transform Your Business Communications?
Get a free consultation with our VoIP experts and discover how we can help you save costs, improve efficiency, and scale your business.
Comments (0)
Join the discussion and share your thoughts (AI-moderated for quality)
Be the first to comment
No comments yet. Share your thoughts below.