Send commands from a database to a PLC and move pneumatic actuators

To send commands from a database to a PLC, you store each command as a row with an id and a status, you move new rows to the PLC through a small service (MQTT, Modbus TCP or a web API), and your PLC writes the result back to the same row. Your database decides what should happen. Your PLC executes it, confirms it with a real signal, and keeps a known state when the network is gone.

Our examples use pneumatic cylinders and an ESP32 PLC. You can apply the same logic to electric actuators and to any PLC with a network port.

How can a database control pneumatic actuators through a PLC?

A database can't switch a solenoid valve. A PLC can. So your job is to build a reliable path from a row in a table to a PLC output, and back with a confirmation. The chain has five links:

1. A row is written. Your ERP, a web app or an operator inserts a command: which actuator, which action, and status PENDING.
2. The command reaches the PLC. A service sends new rows over MQTT or Modbus TCP, or the PLC asks a web API for them.
3. The PLC switches an output. The output energises a valve coil, the valve sends air to the cylinder, and the cylinder moves the gate, door or damper.
4. The PLC checks the result. A limit switch tells it that the cylinder reached the end of its stroke.
5. The status goes back to the row. SENT when your PLC accepts the command, DONE when the position is confirmed, FAULT or SKIPPED with a reason when it isn't.

Each link can fail on its own. With a good design you'll see every failure in your table.

Design the command table before the PLC program

Your command table is the contract between IT and OT. You need a unique increasing id, the target, the action, a status and timestamps written by the database server.

Example A minimal MySQL table:

CREATE TABLE gate_command (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  gate_id     SMALLINT UNSIGNED NOT NULL,
  command     ENUM('OPEN','CLOSE') NOT NULL,
  status      ENUM('PENDING','SENT','DONE','FAULT','SKIPPED')
              NOT NULL DEFAULT 'PENDING',
  reason      VARCHAR(64) NULL,
  created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at  DATETIME(3) NULL,
  INDEX idx_pending (status, id)
);

INSERT INTO gate_command (gate_id, command) VALUES (3, 'OPEN');

Four rules we'd apply from day one:

• Order by id, not by client time. Two applications can have different clocks. The auto increment id gives everybody one order.
• Only the PLC side writes the final status. Your application writes PENDING. DONE means the PLC saw the limit switch, nothing less.
• Keep a reason for every row that didn't run. "Default state active" or "position timeout" saves you hours of diagnosis.
• Never delete rows to cancel them. Mark them SKIPPED, because your table is also your command log.

Three ways to move a command from MySQL to the PLC

In all three options we put a small program between your database and your PLCs. It keeps database credentials and SQL traffic off the control network. You can run it on an industrial PC or a Linux based controller near the PLCs.

Command service with MQTT

Your service reads PENDING rows and publishes each one to a topic per PLC, for example plc-a/cmd. Your PLC subscribes, executes and publishes the acknowledgement to plc-a/ack, and the service writes it back to the row. You'll need a broker. It fits well with many PLCs or an unstable link.

Command service with Modbus TCP

Here your PLC is the Modbus TCP server and your service is the client. The service writes the command id, gate number and action into holding registers. Your PLC puts the last executed id and its status in other registers, and the service reads them back. Your SCADA or HMI can read the same registers.

Web API polled by the PLC

A small web API in front of the database answers GET /next-command?plc=a with the next row for that PLC and takes the acknowledgement with a POST. Your PLC starts every exchange, which helps behind a firewall.

Polling or push: the interval sets the worst delay

Unless you use triggers or change data capture on the MySQL binlog, something has to poll the database. If your service polls every 1 s, a new row waits up to 1 s, plus transport and cylinder stroke. At 5 s it waits up to 5 s, which is often fine for a gate and too slow for a jog button. One service reading an indexed status column every second is a light load. Twenty PLCs with their own SQL connections is not.

Push (MQTT) removes the wait between service and PLC, not between database and service. For less delay there, let your application notify the service, and keep polling as a backup.

Command acknowledgement and idempotency on the PLC

A command is idempotent when receiving it twice has the same effect as once. A message can arrive twice: MQTT QoS 1 can deliver again, and a service can restart and resend.

Our simplest rule: your PLC remembers the highest command id it's executed and ignores any id that's equal or lower. Since ids only grow, late old commands are rejected too. Store that value in non volatile memory, or let your table protect you after a restart.

Confirm the position, not the output. An output that's ON only proves the PLC sent current to a coil. It doesn't prove there was air or that the cylinder moved. Use a limit switch at the end of the stroke and a timeout: if the switch doesn't change in time, the row goes to FAULT.

You also decide what happens with two commands for the same gate:

• Newest wins. Your service marks older PENDING rows for that gate as SKIPPED, reason "newer command". Good when only the final position matters.
• Run in order. Each command waits until the previous one for that gate is DONE. Good when every movement counts.

Network loss: keep a known state on the PLC side

A PLC doesn't know by itself that the database is gone. Its outputs stay as they are until your program changes them. So you need a watchdog: the service sends a heartbeat (an MQTT message, or a Modbus register that increases every second) only while it can read the database. If your PLC receives nothing for a set time, for example 5 s, it moves every actuator to its default state, switches on an alarm lamp and stops accepting commands.

You define the default state per actuator. For one gate it's "close". For another, "hold the current position" is better, because closing could block a passage. Write it down, with the valve that makes it possible.

Reconnection needs a rule too. Your service reads the real positions from the PLC first, updates the table, and only then sends new commands. Remember that network loss and power loss are different: on network loss your PLC runs the default state, on power loss nothing runs and only the valve and the mechanics decide. Check both cases for every actuator.

A default state is not a safety function

People often call this a "safe state", but it's a control function. It keeps your process predictable, and it's useful. It doesn't protect people. A standard PLC program, a watchdog and a database aren't validated as safety related parts of a control system.

If a moving gate or cylinder can injure someone, your machine needs a risk assessment (ISO 12100) and a separate safety circuit designed to the required performance level (ISO 13849-1). In pneumatics, that circuit usually removes air pressure with a safety rated dump valve, controlled by a safety relay or safety controller, independent of the PLC and the network. Your PLC can read the state of that circuit, but it mustn't be part of it.

5/2 single solenoid, 5/2 double solenoid or 5/3 closed centre valve

The valve decides what your cylinder does when the output turns off or the power fails. Choose it with the default state, not after.

One practical note: your PLC must never energise both coils of a double solenoid valve at the same time. Check the valve symbol on your datasheet, not only the name.

Wiring solenoid valve coils to PLC outputs

You've got three common ways to connect the coils, shown here as examples. The right one depends on coil voltage, number of valves and distance to the cabinet.

• Direct outputs. One transistor output per 24 Vdc coil. Check the coil current against the output rating, and add a suppression diode if the coil doesn't have one.
• Interface relays. An output drives a relay, and the relay switches the coil. Use it when coil voltage or current doesn't match the output, or when you want a clear isolation point.
• Valve island (valve terminal) with a bus interface. Many valves on one manifold, and your PLC writes bits over a network or fieldbus. Check which protocol the island supports, for example Modbus TCP, before you choose the PLC.

Example I/O sizing with an ESP32 PLC 42. According to its product page, it has 16 outputs (10 digital optoisolated and 6 analog/digital/PWM, 5 to 24 Vdc, no relay outputs) and 26 inputs (14 digital optoisolated and 12 analog/digital). Say you've got 6 gates with single solenoid valves and two limit switches each: you need 6 outputs and 12 inputs, and 10 outputs stay free for pilot lamps. With double solenoid valves, the same gates use 12 outputs. Check output ratings in the ESP32 PLC family datasheet. For more I/O you can add expansion modules over I2C (up to 127) or a second PLC.

Example code: MQTT command handler for an ESP32 PLC

Example This code illustrates the pattern. It isn't code from any customer installation, and it isn't ready for production. It shows duplicate rejection by id, acknowledgement states, a position timeout and a watchdog.

First, your command service. It reads PENDING rows, publishes them, writes acknowledgements back, and sends a heartbeat only after a successful query. It uses the PyMySQL and paho-mqtt 2.x Python libraries.

# EXAMPLE ONLY: command service between MySQL and the PLCs
import json, time
import pymysql
import paho.mqtt.client as mqtt

db = pymysql.connect(host="localhost", user="plc_service", password="change-me",
                     database="plant", autocommit=True)
GATE_TO_PLC = {1: ("plc-a", 1), 2: ("plc-a", 2), 3: ("plc-a", 3)}  # example map

def on_ack(client, userdata, msg):
    ack = json.loads(msg.payload)
    if ack["status"] == "DUPLICATE":
        return                                   # already handled once
    with db.cursor() as cur:
        cur.execute("UPDATE gate_command SET status=%s, reason=%s, "
                    "updated_at=NOW(3) WHERE id=%s",
                    (ack["status"], ack.get("reason") or None, ack["id"]))

client = mqtt.Client(mqtt.CallbackAPIVersion.VERSION2, client_id="db-service")
client.on_message = on_ack
client.connect("192.168.1.10", 1883)
client.subscribe("+/ack", qos=1)

while True:
    with db.cursor() as cur:
        cur.execute("SELECT id, gate_id, command FROM gate_command "
                    "WHERE status='PENDING' ORDER BY id LIMIT 20")
        rows = cur.fetchall()
    for cid, gate, cmd in rows:
        plc, local_gate = GATE_TO_PLC[gate]
        payload = json.dumps({"id": cid, "gate": local_gate, "cmd": cmd},
                             separators=(",", ":"))
        client.publish(plc + "/cmd", payload, qos=1)
    client.publish("plc-a/heartbeat", "1")     # only reached if the query worked
    client.loop(timeout=1.0)                    # handles acks in this same thread

A row stays PENDING until your PLC answers SENT, so the service may publish it more than once. That's on purpose: the PLC rejects repeats by id. A real service also needs reconnection and error handling.

Then your PLC side, for the Arduino IDE with the Industrial Shields ESP32 boards package and the PubSubClient library. Pin names depend on your model, so check them in the boards package.

// EXAMPLE ONLY: minimal pattern, not production code.
// Three gates, 5/2 single solenoid valves with spring return (output ON = open),
// one "open" and one "closed" limit switch per gate.
#include <Ethernet.h>
#include <PubSubClient.h>

const int N = 3;
const int VALVE[N]   = {Q0_0, Q0_1, Q0_2};  // valve coil outputs
const int IS_OPEN[N] = {I0_0, I0_1, I0_2};  // limit switch: gate open
const int IS_SHUT[N] = {I0_3, I0_4, I0_5};  // limit switch: gate closed
const int ALARM_LAMP = Q0_7;

const unsigned long LINK_TIMEOUT_MS = 5000;  // no message for 5 s: default state
const unsigned long MOVE_TIMEOUT_MS = 8000;  // no limit switch after 8 s: FAULT

byte mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0x00, 0x42};
EthernetClient eth;
PubSubClient mqtt(eth);

long lastId = 0;                 // highest command id already executed
unsigned long lastContact = 0;   // last message from the service
bool defaultState = false;

struct Move { long id; bool open; unsigned long start; bool busy; };
Move moves[N];

void ack(long id, const char* status, const char* reason = "") {
  char msg[96];
  snprintf(msg, sizeof(msg), "{\"id\":%ld,\"status\":\"%s\",\"reason\":\"%s\"}",
           id, status, reason);
  mqtt.publish("plc-a/ack", msg);
}

void enterDefaultState() {
  for (int g = 0; g < N; g++) {
    digitalWrite(VALVE[g], LOW);  // spring returns every gate to closed
    moves[g].busy = false;
  }
  digitalWrite(ALARM_LAMP, HIGH);
  defaultState = true;
}

// Payload example: {"id":1042,"gate":2,"cmd":"OPEN"}  (gate 1..N)
void onMessage(char* topic, byte* payload, unsigned int len) {
  lastContact = millis();
  if (strcmp(topic, "plc-a/heartbeat") == 0) return;

  char buf[64];
  len = len < sizeof(buf) - 1 ? len : sizeof(buf) - 1;
  memcpy(buf, payload, len);
  buf[len] = 0;

  long id = 0; int gate = 0; char cmd[8] = "";
  if (sscanf(buf, "{\"id\":%ld,\"gate\":%d,\"cmd\":\"%7[A-Z]\"}", &id, &gate, cmd) != 3) return;

  if (id <= lastId) { ack(id, "DUPLICATE"); return; }   // never run a row twice
  if (gate < 1 || gate > N) { ack(id, "SKIPPED", "unknown gate"); return; }
  if (defaultState) { ack(id, "SKIPPED", "default state active"); return; }

  lastId = id;
  int g = gate - 1;
  bool open = strcmp(cmd, "OPEN") == 0;
  digitalWrite(VALVE[g], open ? HIGH : LOW);
  moves[g] = {id, open, millis(), true};
  ack(id, "SENT");
}

void checkMoves() {
  for (int g = 0; g < N; g++) {
    if (!moves[g].busy) continue;
    bool arrived = digitalRead(moves[g].open ? IS_OPEN[g] : IS_SHUT[g]) == HIGH;
    if (arrived) {
      ack(moves[g].id, "DONE");
      moves[g].busy = false;
    } else if (millis() - moves[g].start > MOVE_TIMEOUT_MS) {
      ack(moves[g].id, "FAULT", "position timeout");
      moves[g].busy = false;
    }
  }
}

void setup() {
  for (int g = 0; g < N; g++) pinMode(VALVE[g], OUTPUT);
  pinMode(ALARM_LAMP, OUTPUT);
  Ethernet.begin(mac);
  mqtt.setServer(IPAddress(192, 168, 1, 10), 1883);
  mqtt.setCallback(onMessage);
  enterDefaultState();   // start in a known state and wait for the service
}

void loop() {
  if (!mqtt.connected()) {
    if (mqtt.connect("plc-a")) {
      mqtt.subscribe("plc-a/cmd", 1);
      mqtt.subscribe("plc-a/heartbeat");
      mqtt.publish("plc-a/status", "ONLINE");
    }
  }
  mqtt.loop();

  if (millis() - lastContact > LINK_TIMEOUT_MS) {
    if (!defaultState) enterDefaultState();
  } else if (defaultState) {
    defaultState = false;               // link is back: accept new commands
    digitalWrite(ALARM_LAMP, LOW);
  }
  checkMoves();
}

Here the default state is "close", because a single solenoid valve with spring return closes the gate when the output turns off. If one of your gates must hold its position, it needs a different valve (for example 5/3 closed centre) and a different line in enterDefaultState(). With Modbus TCP, you replace the MQTT callback with a register check.

The pattern in a real installation

This pattern already runs in a real installation. An Industrial Shields customer controls 18 pneumatic gates and 9 signal pilots with 2 ESP32 PLC 42 units, and the open and close commands come over the network from a MySQL database. The customer's own team programmed and commissioned the system with the Industrial Shields documentation and the MQTT and Modbus courses on this website, and it works without problems. The options above are general engineering choices for your own design. Read the pneumatic gate control case study for the full story.

MQTT and Modbus tutorials for ESP32 PLC

You'll find a step by step guide for each part of this architecture:

• MQTT remote commands with a response topic (PLC Solution Library)
• Building an MQTT over TLS connection: a guide for ESP32 PLC
• Modbus TCP with Arduino and ESP32 based PLCs
• Modbus RTU with Arduino and ESP32 based PLCs
• RS-485 with Arduino and ESP32 based PLCs
• First steps with Modbus (course)
• How to use SQLite with ESP32 PLC, to keep a local command log on the PLC
• MSSQL library for Arduino and ESP32 industrial PLC
• ESP32 PLC 42 product page and the ESP32 PLC family

FAQ about database commands to a PLC

Should the PLC poll the database, or should the commands be pushed to it?

We'd suggest one service that polls the table and pushes each command to your PLC over MQTT, or writes it over Modbus TCP. Your PLC never opens a database connection, and that service's interval sets your worst delay.

How do I stop the same command from moving a gate twice?

Give each command a unique id that only grows, such as a MySQL auto increment column. Your PLC stores the highest id it's executed and ignores any id that's equal or lower.

Is a default state on network loss a safety function?

No. It's a control function in your PLC program and it doesn't protect people. That needs a risk assessment (ISO 12100) and a separate safety circuit designed to ISO 13849-1, independent of the PLC and the network.

Which valve keeps a pneumatic cylinder in place when the signal is lost?

A 5/2 double solenoid valve keeps its last switched position, so your cylinder completes that stroke. To stop it in mid stroke, use a 5/3 closed centre valve. A 5/2 single solenoid valve returns to rest.

Can an ESP32 PLC read commands from MySQL?

Yes, through a small service in the middle. The service reads the table and sends each command over MQTT or Modbus TCP, or your PLC asks a web API for the next row. Your database credentials stay off the control network.

Can I use Modbus TCP instead of MQTT for database commands?

Yes. Your PLC is the Modbus TCP server, your service writes each command into holding registers and reads the result back from other registers. You don't need a broker.