Intermediate18 min read

Application Development

Connect to MySQL from applications using drivers, ORMs, and robust error handling.

Database Connections

Use official or mature drivers (mysql2 for Node.js, psycopg-style mysql connectors for Python, JDBC for Java). Configure connection pools sized to your concurrency and server max_connections.

Set connect timeouts and test connections on checkout from pool in long-running processes to detect stale connections.

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: "shop",
  waitForConnections: true,
  connectionLimit: 10,
});

Query Execution

Always use parameterized queries. Prepared statements cache execution plans and prevent SQL injection. Batch inserts in transactions for throughput.

Return connections to the pool in finally blocks. Never hold connections open across await calls to external services within a transaction.

const [rows] = await pool.execute(
  "SELECT id, email FROM users WHERE status = ?",
  ["active"]
);

ORMs

Sequelize, TypeORM, Prisma, and Drizzle map objects to tables and manage migrations. ORMs accelerate development but can generate inefficient SQL—profile hot paths.

Use raw SQL or query builder escape hatches for complex reports. Keep migrations as the source of truth for schema.

N+1 query problems are common with ORM relations—use eager loading
Align ORM transaction boundaries with business operations
Generate types from schema for compile-time safety

// Prisma example
const users = await prisma.user.findMany({
  where: { status: "active" },
  select: { id: true, email: true },
});

Error Handling

Map MySQL error codes to application errors—1062 duplicate entry, 1213 deadlock, 1205 lock wait timeout. Retry deadlocks with jitter.

Log query context without logging secrets or full PII. Use circuit breakers when database is unavailable to fail fast.

catch (error) {
  if (error.code === "ER_LOCK_DEADLOCK") {
    return retry(transactionFn);
  }
  throw error;
}

Testing with MySQL

Use Docker or Testcontainers to spin up MySQL for integration tests. Reset schema or use transactions rolled back after each test for isolation.

Seed minimal fixtures representing edge cases—empty strings, max length values, concurrent update scenarios.