MariaDB API example

This project demonstrates a minimal API server that retrieves data from a MariaDB database.

Control the project by either pressing the Inject buttons in the Node-RED flow or calling its HTTP endpoints; both options run the same flows and give full system control.

Services Used

MariaDB – MYSQL Database

Built using the Sandbox Node-RED flow structure. (TODO: Add reference for more details).

List of installed packages

node-red-node-mysql - Executes passed MYSQL queries

List of used nodes

Inject/Timestamp – Triggers flow execution
Function – Adds custom logic using JavaScript
HTTP Request – Sends HTTP requests to external services
Debug – Displays messages in the debug console
Switch – Routes messages based on conditions
HTTP response – Returns response to sender of HTTP request
mysql - Imported node from installed package, used for executing passed queries

For details, see Node-RED Documentation

GROUP: Test basic queries

This group checks that the MariaDB node (from node-red-node-mysql) works as expected. The three flows run a basic CREATE → INSERT → SELECT cycle.

FLOW: create table

Runs a CREATE TABLE statement.

Generic pattern:

CREATE TABLE IF NOT EXISTS demo (
    id   INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64),
    qty  INT
);

FLOW: insert data

Adds one row to the table.

Generic pattern:

INSERT INTO demo (name, qty)
VALUES ('example', 42);

FLOW: view table

Reads data back with a SELECT.

Generic pattern:

SELECT * FROM demo
WHERE qty >= 0;   -- adjust filters as needed

GROUP: HTTP Restful API handler

This group turns the flows into a tiny REST service with three endpoints. Each flow maps one HTTP route to a MariaDB query. The MariaDB node returns a result object that includes msg.payload.affectedRows and, on error, msg.error. The flows use this information to set the HTTP reply:

200 query ran and affected the expected rows
4xx / 5xx validation failure or database error

Flows in the group are using HTTP-request node. Read more about the use of node here: … (TODO: add documentation)

FLOW: handle create db API request

POST /api/create-db

Body JSON:

{ "name": "my_database" }

Function Node is used to then create query:

CREATE DATABASE IF NOT EXISTS my_database;

Returns 201 when the database is created or already exists.

FLOW: handle create table API request

POST /api/create-table

Body (JSON): key name is the table name; every other key–value pair is a column definition:

{
  "name": "demo",
  "id":   "INT AUTO_INCREMENT PRIMARY KEY",
  "item": "VARCHAR(64)",
  "qty":  "INT"
}

The function node builds and executes

CREATE TABLE IF NOT EXISTS demo (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  item VARCHAR(64),
  qty  INT
);

Responds 201 on success.

FLOW: handle insert into table API request

POST /api/insert

Body (JSON): key name is the target table; other keys are the column names with their values:

{
  "name": "demo",
  "item": "widget",
  "qty":  42
}

Strings are automatically wrapped in quotes; numbers are left as-is. The resulting query for the example above is

INSERT INTO demo (item, qty) VALUES ('widget', 42);

Returns 200 and the number of affected rows, or 400 if required fields are missing.

GROUP: test API server

This group contains three client flows that exercise the REST endpoints defined earlier. Click the Inject button in each flow to send a request; a Debug node then shows the HTTP response and status code in the sidebar.

Flows in the group are using HTTP-request node. Read more about the use of node here: … (TODO: add documentation)

FLOW: create db

The Function node builds the request for `POST /api/create-db. msg.payload is a JSON body with a single field:

{ "name": "my_database" }

FLOW: create table

The Function node prepares the body for POST /api/create-table. name gives the table name; every other key–value pair defines a column:

{
  "name": "demo",
  "id":   "INT AUTO_INCREMENT PRIMARY KEY",
  "item": "VARCHAR(64)",
  "qty":  "INT"
}

FLOW: insert data

This flow hits POST /api/insert. name selects the target table, and the remaining properties become column–value pairs:

{
  "name": "demo",
  "item": "widget",
  "qty":  42
}

Run each flow in order—create db → create table → insert data—to confirm the API works end-to-end.