mysql-js

• MySQL client for Node.js
• ES6 tagged template strings at the core
• Raw TCP implementation with prepared statements, pooling, transactions and TLS
• TypeScript-first API with typed query results

Getting started

Installation

npm install mysql-js

Usage

Create a single sql instance and reuse it across your application.

import mysql from 'mysql-js'

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

const rows = await sql`
  select
    id,
    username
  from account
  where id = ${1}
`

console.log(rows)

await sql.end()

Table of contents

• Connection
• Queries
• Building queries
• Results
• Transactions
• Reserved and direct connections
• TLS / SSL
• Dynamic passwords
• Custom MySQL types
• Observability
• Teardown
• TypeScript
• Compatibility

Connection

mysql([url], [options])

You can use either a MySQL connection string, an options object, or both. When both are provided, the explicit options override the values from the URL.

import mysql from 'mysql-js'

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

Supported connection options

const sql = mysql({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: 'secret',
  database: 'auth',
  connectTimeout: 10_000,
  max: 10,
  idleTimeout: 30_000,
  acquireTimeout: 30_000,
  prepare: true,
  maxStatements: 100,
  ssl: false,
  types: {
    decimal: (raw) => raw,
    newdecimal: (raw) => raw
  }
})

Queries

await sql\...``

Queries are written as tagged template literals. Interpolated values are sent as prepared statement parameters when possible, which keeps query building safe and ergonomic.

const name = 'murray'
const rows = await sql`
  select
    id,
    username
  from account
  where username = ${name}
`

Query parameters

Do not quote interpolated values yourself.

const id = 42

await sql`select * from account where id = ${id}`

Building queries

The sql(...) helper is available on the root client, bound connections and transactions.

Inserts from objects

await sql`
  insert into account ${sql({ username: 'murray', verifier: '123' })}
`

This becomes:

insert into account (`username`, `verifier`) values (?, ?)

Updates from objects

await sql`
  update account
  set ${sql({ username: 'walter' }, 'update')}
  where id = ${1}
`

IN (...) lists

await sql`
  select *
  from account
  where id in ${sql([1, 2, 3])}
`

Identifiers

Use sql.identifier() for trusted dynamic table or column names.

const column = sql.identifier('account.username')

await sql`
  select ${column}
  from account
`

Fragments

Use sql.fragment for reusable pieces of SQL built with the same interpolation rules.

const whereActive = sql.fragment`where active = ${true}`

await sql`
  select *
  from account
  ${whereActive}
`

Unsafe SQL

sql.unsafe() exists for advanced cases where you intentionally want to inject trusted SQL text.

await sql`
  select ${sql.unsafe('now()')}
`

Use this carefully.

Results

SELECT queries return arrays with extra metadata.

const rows = await sql`
  select id, username
  from account
`

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

Mutating queries return command metadata.

const result = await sql`
  insert into account (username)
  values (${'murray'})
`

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

MySQL numeric behavior

• FLOAT and DOUBLE are returned as number
• DECIMAL and NEWDECIMAL are returned as string by default

const rows = await sql`
  select cast(123.45 as decimal(10,2)) as price
`

console.log(rows[0]?.price)
// '123.45'

This avoids silent precision loss in JavaScript.

Transactions

Automatic transaction

const result = await sql.begin(async (tx) => {
  const insert = await tx`
    insert into account (username)
    values (${'murray'})
  `

  const rows = await tx`
    select *
    from account
    where id = ${insert.lastInsertId}
  `

  return rows
})

If anything throws, the transaction is rolled back automatically.

Manual transaction

const tx = await sql.begin()

try {
  await tx`insert into account (username) values (${'murray'})`
  await tx.commit()
} catch (error) {
  await tx.rollback()
  throw error
}

Savepoints

await sql.begin(async (tx) => {
  await tx.savepoint('create_profile', async () => {
    await tx`insert into profile (account_id) values (${1})`
  })
})

Reserved and direct connections

await sql.reserve()

Reserve a single pooled connection.

const reserved = await sql.reserve()

await reserved`select * from account`

reserved.release()

await sql.connect()

Create a direct dedicated connection outside the pool.

const connection = await sql.connect()

await connection`select * from account`

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

This is useful when you want a dedicated connection lifecycle.

TLS / SSL

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

Basic TLS

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

Custom TLS options

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

Client certificates

import { readFileSync } from 'node:fs'

const sql = mysql({
  host: 'db.example.com',
  user: 'app',
  password: 'secret',
  database: 'main',
  ssl: {
    ca: readFileSync('./ca.pem', 'utf8'),
    cert: readFileSync('./client-cert.pem', 'utf8'),
    key: readFileSync('./client-key.pem', 'utf8')
  }
})

Dynamic passwords

password can be a string, a synchronous function, or an async function. The value is resolved once per connection during the handshake.

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

Useful for:

• cloud auth tokens
• rotating credentials
• secret managers

Custom MySQL types

The types option lets you override selected MySQL parsers.

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

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

Currently supported custom parser hooks:

• types.decimal
• types.newdecimal

Prepared statements

Prepared statements are used automatically for parameterized queries and cached per connection.

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

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

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

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

Always shut down the client when your process exits.

await sql.end()

If you need to destroy connections immediately:

sql.destroy()

TypeScript

You can pass the expected result type to the tagged template.

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

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

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

const insert = await sql<InsertResult>`
  insert into account (username)
  values (${'murray'})
`

console.log(insert.lastInsertId)

Exported result types:

• QueryResult
• QueryRows
• SelectResult<T>
• CommandResult
• InsertResult
• UpdateResult
• DeleteResult

Compatibility

mysql-js is designed for MySQL and aims to stay compatible with MariaDB where the wire protocol overlaps.

Notes:

• lastInsertId is the primary insert-return mechanism
• RETURNING is not treated as a portable MySQL feature
• some SQL syntax differs between MySQL and MariaDB, so feature behavior should be documented explicitly when server-specific

Example setup

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

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

export default sql

// account.ts
import sql from './db'

export async function getAccount(id: number) {
  const rows = await sql`
    select *
    from account
    where id = ${id}
  `

  return rows[0] ?? null
}

Status

The project is currently 0.0.0-alpha.1.

Core features already available:

• tagged-template queries
• connection pool
• dedicated connections
• transactions and savepoints
• prepared statements with per-connection cache
• TLS / SSL
• dynamic passwords
• typed query results
• custom decimal parsers

License

See package.json for the current project license metadata.