MySQL and MariaDB

Read tables in MySQL 5.7 or later or MariaDB 10.3 or later, on a managed service or your own server, and run read-only SQL against them.

Connect it in the console under Integrations > Connectors. See Connectors for how connectors work and who can use them.

Create a read-only credential

Create a user with SELECT only:

CREATE USER 'vaif_reader'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, SHOW VIEW ON shop.* TO 'vaif_reader'@'%';

The host must resolve to a public address: VAIF Studio refuses private, loopback, link-local and cloud metadata addresses, and connects to the address it checked, so a DNS change cannot redirect it.

Settings

Setting		Notes
Host	Required	For example mysql.example.com.
Port	Optional	Default 3306.
Database	Required	For example shop.
Username	Required	Use a user with SELECT only.
Password	Required	Stored encrypted and never shown again.
SSL	Optional	One of: Encrypt and verify the certificate (recommended), Encrypt, don't verify the certificate, No encryption. Default: Encrypt and verify the certificate (recommended).

Database is the database (schema) to read. Choose Encrypt, don’t verify for a server whose certificate is signed by a private authority.

Tables

Table	Filters	Returns
tables	None	Tables and views in the configured database.
columns	table (required)	Columns of one table.
sql	query (required)	Run one read-only SQL statement. Needs the developer role.

Query from the API

curl -s -X POST "https://api.vaif.studio/database/connector/$VAIF_PROJECT_ID/query" \
  -H "Authorization: Bearer $VAIF_TOKEN" -H "Content-Type: application/json" \
  -d '{"wrapperId":"mysql_fdw","table":"sql","filters":{"query":"SELECT status, COUNT(*) AS orders FROM orders GROUP BY status"},"limit":50}'

$VAIF_TOKEN is a console access token. See Connectors for the routes.

How it stays read-only

Queries run one statement at a time in a READ ONLY transaction with a server-side time limit (MAX_EXECUTION_TIME on MySQL, max_statement_time on MariaDB). MySQL commits schema changes outside a transaction, so CREATE, DROP, ALTER and INTO OUTFILE are refused before they are sent. When two columns in a result share a name, give them aliases with AS.