• Fast full-featured MySQL client for Node.js
• 🏷️ ES6 Tagged Template
• 🎯 Simple surface API
• 🧩 Dynamic query support
• 🔌 TCP, pooling, TLS
• 🧠 Typed results

Installation

npm install mysql-next

Quick Start

// db.js
import mysql from 'mysql-next'

const sql = mysql('mysql://root:password@localhost:3306/app', {
  max: 10,
  idleTimeout: 30_000,
  acquireTimeout: 30_000,
  prepare: true,
  maxStatements: 100
})

export default sql

// users.js
import sql from './db.js'

export async function getUsersOver(age) {
  const users = await sql`
    select
      id,
      name,
      age
    from users
    where age > ${age}
  `

  return users
}

export async function insertUser({ name, age }) {
  const result = await sql`
    insert into users ${sql({ name, age })}
  `

  return result.lastInsertId
}

Connection

mysql([url], [options])

You can use either a mysql:// url connection string or an options object to define your connection properties. When both are provided, explicit options override values from the url.

const sql = mysql('mysql://username:password@host:3306/database', {
  host: '127.0.0.1',
  port: 3306,
  socketPath: undefined,
  user: 'root',
  password: 'secret',
  database: 'app',
  ssl: false,
  connectTimeout: 10_000,
  max: 10,
  idleTimeout: 30_000,
  acquireTimeout: 30_000,
  prepare: true,
  maxStatements: 100
})

Queries

await sql\...``

mysql-next uses tagged templates and sends interpolated values as bound parameters.

const users = await sql`
  select
    id,
    name
  from users
  where age > ${60}
`

const name = 'Mur'
const age = 60

const users = await sql`
  select
    name,
    age
  from users
  where name like ${name + '%'}
    and age > ${age}
`

Do not wrap interpolated values in quotes like '${name}'. The driver already turns them into bound parameters.

Building queries

Insert and update helpers

const user = {
  name: 'Murray',
  age: 68
}

await sql`insert into users ${sql(user)}`

// insert into users (`name`, `age`) values (?, ?)

await sql`
  update users
  set ${sql({ name: user.name, age: user.age }, 'update')}
  where id = ${user.id}
`

// update users set `name` = ?, `age` = ? where id = ?

Lists and partial fragments

const ids = [1, 2, 3]

const users = await sql`
  select
    *
  from users
  where id in ${sql(ids)}
`

const olderThan = (x) => sql.fragment`and age > ${x}`
const filterAge = true

await sql`
  select
    *
  from users
  where name is not null
  ${filterAge ? olderThan(50) : sql.fragment``}
`

SQL functions and raw fragments

const updatedAt = null

await sql`
  update users
  set updated_at = ${updatedAt || sql.unsafe('now()')}
`

Use sql.unsafe() only when the SQL text is fully trusted.

Prefer bound parameters whenever possible. sql.unsafe() and low-level helpers such as escapeValue() are not appropriate for untrusted user input.

Table and column names

Dynamic identifiers are supported with sql.identifier().

const table = sql.identifier('users')
const column = sql.identifier('users.created_at')

await sql`
  select ${column}
  from ${table}
`

Transactions

await sql.begin(fn)

Use sql.begin to start a transaction. A single connection is reserved for the callback and automatically committed or rolled back.

const user = await sql.begin(async (sql) => {
  const result = await sql`
    insert into users ${sql({ name: 'Murray', age: 68 })}
  `

  const users = await sql`
    select *
    from users
    where id = ${result.lastInsertId}
  `

  return users[0]
})

If anything throws, ROLLBACK is issued automatically.

Manual transactions

const tx = await sql.begin()

try {
  await tx`insert into users ${tx({ name: 'Murray', age: 68 })}`
  await tx.commit()
} catch (error) {
  await tx.rollback()
  throw error
}

Savepoints

await sql.begin(async (sql) => {
  await sql.savepoint('create_profile', async () => {
    await sql`insert into profiles ${sql({ user_id: 1 })}`
  })
})

Result Array

SELECT queries return arrays with metadata attached.

const rows = await sql`
  select id, name
  from users
`

rows.length
rows.count
rows.columns
rows[0]?.id

Mutating queries return command metadata.

const result = await sql`
  insert into users ${sql({ name: 'Murray' })}
`

result.command
result.count
result.affectedRows
result.lastInsertId

Connection details

Options

const sql = mysql('mysql://username:password@host:3306/database', {
  host: '127.0.0.1',
  port: 3306,
  socketPath: '/var/run/mysqld/mysqld.sock',
  database: 'app',
  user: 'root',
  password: 'secret',
  ssl: false,
  connectTimeout: 10_000,
  max: 10,
  idleTimeout: 30_000,
  acquireTimeout: 30_000,
  prepare: true,
  maxStatements: 100,
  types: {
    decimal: (raw) => raw,
    newdecimal: (raw) => raw
  }
})

Dynamic passwords

If your password needs to be resolved at connection time, pass a function.

const sql = mysql('mysql://root@localhost:3306/app', {
  password: async () => process.env.DB_TOKEN ?? ''
})

SSL / TLS

mysql-next can upgrade the connection to TLS during the MySQL handshake.

const sql = mysql({
  host: 'db.example.com',
  user: 'app',
  password: 'secret',
  database: 'main',
  ssl: true
})

Custom TLS options are also supported.

const sql = mysql('mysql://app:[email protected]:3306/main', {
  ssl: {
    rejectUnauthorized: false
  }
})

Avoid rejectUnauthorized: false outside local development. It disables server certificate verification and weakens TLS protection.

Authentication plugins

mysql-next currently supports these MySQL authentication plugins:

• mysql_native_password
• caching_sha2_password

Other authentication plugins are rejected explicitly.

The connection pool

Connections are created lazily. Instantiating mysql() does not open any connection by itself.

const sql = mysql() // no connection yet

await sql`select 1` // first connection is opened
await sql`select 1` // pooled connection reused

await Promise.all([sql`select 1`, sql`select 1`])

max controls the upper bound of concurrently active pooled connections.

Connections returned to the pool are reset before reuse so session state and cached prepared statements do not leak between consumers.

Prepared statements

Prepared statements are used automatically for parameterized queries when prepare is enabled.

const sql = mysql('mysql://root:password@localhost:3306/app', {
  prepare: true,
  maxStatements: 100
})

• prepare: false disables prepared statement caching
• maxStatements controls the per-connection statement cache size

Reserved and direct connections

await sql.reserve()

Reserve a pooled connection for isolated work.

const reserved = await sql.reserve()
await reserved`select * from users`
reserved.release()

await sql.connect()

Create a dedicated connection outside the pool.

const connection = await sql.connect()

await connection`select * from users`

await connection.end()
await sql.end()

Custom types

You can override MySQL type parsers with the types option.

import Decimal from 'decimal.js'
import mysql from 'mysql-next'

const sql = mysql('mysql://root:password@localhost:3306/app', {
  types: {
    decimal: (raw) => new Decimal(raw),
    newdecimal: (raw) => new Decimal(raw)
  }
})

Supported parser hooks: types.decimal and types.newdecimal.

Observability

The root client emits query events for pooled and dedicated connections.

sql.on('query', (event) => {
  console.log(event.sql)
  console.log(event.protocol)
  console.log(event.duration)
  console.log(event.rowCount)
  console.log(event.connectionId)
  console.log(event.serverConnectionId)
  console.log(event.errorCode)
  console.log(event.sqlState)
})

Pool lifecycle events are also available.

sql.on('connect', console.log)
sql.on('acquire', console.log)
sql.on('release', console.log)
sql.on('drain', console.log)
sql.on('error', console.error)

Teardown / Cleanup

To ensure proper teardown on shutdown, call await sql.end().

await sql.end()

This rejects new queries, waits for active work to finish, and closes underlying connections.

If you need to close everything immediately, use:

sql.destroy()

Error handling

Errors are thrown to the related query and never emitted as a global catch-all.

Server errors are exposed as MySQLError with MySQL-specific metadata.

import { MySQLError } from 'mysql-next'

try {
  await sql`select * from missing_table`
} catch (error) {
  if (error instanceof MySQLError) {
    console.log(error.code)
    console.log(error.sqlState)
  }
}

Connection and runtime errors from Node.js are forwarded as regular Error instances.

TypeScript support

You can pass the expected row list type directly to a query.

interface User {
  id: number
  name: string
}

const users = await sql<User[]>`select * from users`
users[0]?.id
users[0]?.name

You can also use exported result types.

import mysql, { type InsertResult, type SelectResult } from 'mysql-next'

const sql = mysql('mysql://root:password@localhost:3306/app')

const users = await sql<SelectResult<{ id: number; name: string }>>`
  select id, name
  from users
`

const insert = await sql<InsertResult>`
  insert into users ${sql({ name: 'Murray' })}
`

console.log(insert.lastInsertId)

Exported result types include QueryResult, QueryRows, SelectResult<T>, CommandResult, InsertResult, UpdateResult, and DeleteResult.

Migration tools

mysql-next does not ship with a migration system. Use your existing migration tool or execute SQL files during setup.

Support

Enjoying this tool? Consider supporting the project.

License

MIT