Client Connectors, Connection Strings, TLS, Timeouts, Pooling, and Session Contracts

Learning outcomes

ServiceHub has a simple production symptom: after a network flap, some requests fail quickly, others wait for tens of seconds, and a few return dates in the wrong time zone after a pooled connection is reused. None of those bugs is “just a connector issue.” A connection contract is the agreement between the application and MariaDB about endpoint identity, authentication, encryption, time bounds, character set, SQL mode, time zone, transaction state, and what happens when a physical connection returns to a pool.

1. A connection is a stateful protocol session, not a URL

A connection string identifies where and how the client should connect, but the resulting MariaDB session has server-side state. It can own a transaction, session variables, temporary tables, user variables, SQL mode, time zone and locks. A pool keeps physical connections open so requests can borrow them cheaply. That makes pool checkout fast, but it also means “new request” does not necessarily mean “new server session.”

The key operational rule is to test the contract on the exact connector version you deploy. Pool reset behavior, defaults and TLS option names are connector concerns; server SQL state is MariaDB behavior. Do not infer one from the other.

2. Create a least-privilege local application identity

DROP DATABASE IF EXISTS servicehub19_l1;CREATE DATABASE servicehub19_l1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE TABLE servicehub19_l1.connection_probe (  probe_id BIGINT PRIMARY KEY AUTO_INCREMENT,  note VARCHAR(120) NOT NULL,  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;DROP USER IF EXISTS 'svc19_app'@'127.0.0.1';CREATE USER 'svc19_app'@'127.0.0.1' IDENTIFIED BY 'replace-only-in-local-lab';GRANT SELECT, INSERT ON servicehub19_l1.* TO 'svc19_app'@'127.0.0.1';SELECT VERSION() AS server_version, @@hostname AS server_host,       @@port AS server_port, @@character_set_server AS server_charset,       @@collation_server AS server_collation;

Use a disposable local password only for this lab. Production credentials belong in a secret manager, protected environment injection, or another platform-appropriate secret channel—not committed source, connection URLs in logs, or screenshots.

3. Build the connector contract and record versions

mkdir servicehub19-clientcd servicehub19-clientnpm init -ynpm install mariadbnode --versionnpm list mariadb

const mariadb = require('mariadb');const fs = require('fs');const tls = process.env.DB_CA  ? { ca: fs.readFileSync(process.env.DB_CA), rejectUnauthorized: true }  : undefined;const pool = mariadb.createPool({  host: process.env.DB_HOST || '127.0.0.1',  port: Number(process.env.DB_PORT || 3306),  user: process.env.DB_USER || 'svc19_app',  password: process.env.DB_PASSWORD,  database: 'servicehub19_l1',  connectionLimit: 6,  connectTimeout: 3000,  socketTimeout: 5000,  charset: 'utf8mb4',  ssl: tls});async function withDb(work) {  let conn;  try {    conn = await pool.getConnection();    await conn.query("SET SESSION time_zone = '+00:00'");    await conn.query("SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");    return await work(conn);  } finally {    if (conn) conn.release();  }}module.exports = { pool, withDb };

connectTimeout bounds connection establishment; socketTimeout bounds socket inactivity according to the connector. They are not SQL statement budgets. A query may still need an application deadline/cancellation policy. TLS encryption alone is not enough: the client must trust the intended CA and reject an invalid peer identity. For production, use the exact Connector/Node.js TLS documentation for the deployed release and verify the certificate path and hostname behavior.

4. Make the session contract observable

const { pool, withDb } = require('./pool');(async () => {  try {    await withDb(async conn => {      const rows = await conn.query(`        SELECT CONNECTION_ID() AS cid,               CURRENT_USER() AS authenticated_account,               @@SESSION.time_zone AS tz,               @@SESSION.sql_mode AS sql_mode,               @@character_set_connection AS conn_charset,               @@hostname AS server_host      `);      console.table(rows);    });  } finally {    await pool.end();  }})();

Expected values include the application account, UTC session time zone, the explicit SQL mode, and utf8mb4. The connection ID proves which server session answered the query; it does not prove that future pool checkouts will reuse the same physical connection.

5. Wrong approach: assume release means “factory-new session”

const { pool } = require('./pool');(async () => {  let a = await pool.getConnection();  const first = await a.query('SELECT CONNECTION_ID() AS cid');  await a.query("SET @request_tag = 'request-A'");  a.release();  let b = await pool.getConnection();  const second = await b.query('SELECT CONNECTION_ID() AS cid, @request_tag AS request_tag');  console.log({ first, second });  b.release();  await pool.end();})();

Current Connector/Node.js exposes pool controls such as resetAfterUse (documented default true) and noControlAfterUse (documented default false). Therefore the default current pool is designed to roll back/reset state on release, but deployments can change those controls and older connector behavior may differ. Treat this as a probe, not a promise you leave unverified: record the exact connector version/configuration, verify what is reset, initialize required session state at checkout, and never make business correctness depend on residue from a previous borrower.

6. TLS, timeout, and health-check judgment

A readiness check should prove that the application can reach the intended MariaDB role with the contract it actually needs. SELECT 1 proves little beyond round-trip SQL. A stronger check can include server identity, database selection, read/write role expectations, and—when Galera is used—wsrep_ready and node state. Yet health checks must remain cheap: they should not run large table scans or create write load.

7. Reproducible lab and cleanup

SELECT USER() AS client_identity, CURRENT_USER() AS authenticated_account,       @@SESSION.time_zone, @@SESSION.sql_mode, @@character_set_connection;SHOW GRANTS FOR 'svc19_app'@'127.0.0.1';-- After the Node.js probes finish:DROP USER IF EXISTS 'svc19_app'@'127.0.0.1';DROP DATABASE IF EXISTS servicehub19_l1;

Production judgment and bridge to Lesson 2

Use pooling when connection setup cost matters and the workload has repeatable concurrency, but size the pool from measured active concurrency and database capacity—not web-worker count folklore. Record the endpoint, TLS requirements, timeout semantics, session initialization, connector version, and pool-reset assumptions as an application contract. The next lesson moves one layer inward: once a connection is trustworthy, values still need to cross the SQL boundary safely through prepared statements, type mapping, and Unicode.

Application contract deep dive: a connection is reusable state, not a disposable socket

A production application rarely opens a brand-new database process for every statement. A pool keeps physical MariaDB sessions alive while logical requests borrow and return them. That distinction explains why the connection contract must cover more than host, port, user, and password. A borrowed session can carry transaction state, SQL mode, time zone, character set, temporary objects, user variables, prepared statements, and server-side timeout history. A reliable checkout path therefore verifies the server identity and establishes the session assumptions that the request needs; the return path either proves the connector resets those assumptions or performs an explicit reset strategy.

Timeouts should be separated by failure phase. A connect timeout bounds DNS/TCP/TLS/authentication establishment. Pool acquisition timeout bounds how long the application waits for an available pooled connection. Query or socket read/write timeouts bound an already-connected operation. Treating them as one generic “database timeout” makes incident diagnosis harder because pool saturation, network loss, TLS failure, and a slow statement have different remedies. Record the connector error code, elapsed phase, server thread id when available, and pool occupancy before deciding that MariaDB itself is slow.

• At checkout: identify the target node, verify TLS policy, and establish session SQL mode, time zone, and character set.
• During use: keep transactions explicit, bind parameters, and avoid request-global state leaking into the session.
• At release: rollback unfinished work and reset or discard contaminated sessions according to verified connector behavior.
• During failover: expire or validate old pooled connections so traffic does not keep using a node whose role changed.

This lifecycle is also a security boundary. Credentials belong in a secret provider or protected environment, certificates need identity verification rather than encryption alone, and connection diagnostics must avoid logging passwords or sensitive query parameters. The contract should be testable: borrow the same physical session twice, deliberately change session state on the first checkout, and prove what the second checkout observes.

Authoritative references

Primary references are current MariaDB documentation or official connector source; verify the target server/connector version before relying on defaults or option behavior.

• MariaDB Connector/Node.js Promise API
• MariaDB Connector/Node.js connection options
• MariaDB Connector/Node.js official source repository
• MariaDB Server security documentation