Skip to main content

​
Overview

MySQL dialect that uses the mysql2 library. This dialect works with both MySQL and MariaDB databases. Source: src/dialect/mysql/mysql-dialect.ts:43

​
Installation

Install the required peer dependency: 
npm install mysql2

​
Basic Usage

import { Kysely, MysqlDialect } from 'kysely'
import { createPool } from 'mysql2'

const db = new Kysely<Database>({
  dialect: new MysqlDialect({
    pool: createPool({
      host: 'localhost',
      database: 'mydatabase',
      user: 'myuser',
      password: 'mypassword',
      port: 3306,
      connectionLimit: 10,
    })
  })
})

​
Configuration

​
MysqlDialectConfig

Source: src/dialect/mysql/mysql-dialect-config.ts:8
​
pool
MysqlPool | (() => Promise<MysqlPool>)
required
A mysql2 Pool instance or a function that returns one.If a function is provided, it’s called once when the first query is executed. This allows for lazy initialization of the connection pool.See mysql2 pool documentation for available pool options.
​
onCreateConnection
(connection: DatabaseConnection) => Promise<void>
Called once for each created connection. Useful for setting up connection-level configuration.
new MysqlDialect({
  pool: createPool({ /* ... */ }),
  onCreateConnection: async (connection) => {
    await connection.executeQuery(
      CompiledQuery.raw('SET time_zone = "+00:00"')
    )
  }
})
​
onReserveConnection
(connection: DatabaseConnection) => Promise<void>
Called every time a connection is acquired from the connection pool. Use this for per-query setup.
new MysqlDialect({
  pool: createPool({ /* ... */ }),
  onReserveConnection: async (connection) => {
    // Run on every connection acquisition
    await connection.executeQuery(
      CompiledQuery.raw('SET SESSION sql_mode = "TRADITIONAL"')
    )
  }
})

​
Connection Pooling

The MySQL dialect uses the mysql2 library’s built-in connection pooling. Here are recommended pool settings: 
import { createPool } from 'mysql2'

const pool = createPool({
  // Connection settings
  host: 'localhost',
  port: 3306,
  database: 'mydb',
  user: 'user',
  password: 'password',

  // Pool settings
  connectionLimit: 10, // Maximum number of connections
  waitForConnections: true, // Queue requests when no connections available
  queueLimit: 0, // Unlimited queue size
  enableKeepAlive: true, // Keep connections alive
  keepAliveInitialDelay: 0, // Initial keep-alive delay
})

​
SSL/TLS Configuration

To connect using SSL: 
import { createPool } from 'mysql2'
import fs from 'fs'

const pool = createPool({
  host: 'localhost',
  database: 'mydb',
  ssl: {
    ca: fs.readFileSync('/path/to/ca-cert.pem'),
    key: fs.readFileSync('/path/to/client-key.pem'),
    cert: fs.readFileSync('/path/to/client-cert.pem'),
    rejectUnauthorized: true,
  }
})

​
Streaming Results

The MySQL dialect supports streaming large result sets: 
const stream = db
  .selectFrom('large_table')
  .selectAll()
  .stream()

for await (const row of stream) {
  console.log(row)
  // Process row by row without loading entire result set into memory
}
You can also configure stream options: 
const stream = db
  .selectFrom('large_table')
  .selectAll()
  .stream(1000) // High water mark: buffer up to 1000 rows

for await (const row of stream) {
  console.log(row)
}

​
Character Set and Collation

Configure character set and collation for your connection: 
import { createPool } from 'mysql2'

const pool = createPool({
  host: 'localhost',
  database: 'mydb',
  charset: 'utf8mb4',
  // Collation is set at database/table level
})

​
Timezone Handling

MySQL connections default to the server’s timezone. To use UTC: 
import { createPool } from 'mysql2'

const pool = createPool({
  host: 'localhost',
  database: 'mydb',
  timezone: '+00:00', // UTC
})
Or set it per connection: 
new MysqlDialect({
  pool: createPool({ /* ... */ }),
  onCreateConnection: async (connection) => {
    await connection.executeQuery(
      CompiledQuery.raw('SET time_zone = "+00:00"')
    )
  }
})

​
Multiple Statements

To execute multiple statements in a single query: 
import { createPool } from 'mysql2'

const pool = createPool({
  host: 'localhost',
  database: 'mydb',
  multipleStatements: true, // Enable multiple statements
})
Enabling multiple statements can expose you to SQL injection vulnerabilities if you’re not careful with user input. Only enable this if you need it and understand the security implications.

​
MariaDB Compatibility

The MysqlDialect works with MariaDB databases. Simply point it to your MariaDB server: 
import { createPool } from 'mysql2'

const pool = createPool({
  host: 'localhost',
  port: 3306, // Default MariaDB port
  database: 'mydb',
  user: 'user',
  password: 'password',
})

​
MysqlPool

Source: src/dialect/mysql/mysql-dialect-config.ts:37 The subset of the mysql2 Pool interface that Kysely requires: 
interface MysqlPool {
  getConnection(callback: (error: unknown, connection: MysqlPoolConnection) => void): void
  end(callback: (error: unknown) => void): void
}

​
MysqlPoolConnection

Source: src/dialect/mysql/mysql-dialect-config.ts:44 
interface MysqlPoolConnection {
  query(sql: string, parameters: ReadonlyArray<unknown>): {
    stream: <T>(options: MysqlStreamOptions) => MysqlStream<T>
  }
  query(
    sql: string,
    parameters: ReadonlyArray<unknown>,
    callback: (error: unknown, result: MysqlQueryResult) => void
  ): void
  release(): void
}

​
MysqlOkPacket

Source: src/dialect/mysql/mysql-dialect-config.ts:66 Result type for INSERT, UPDATE, and DELETE queries: 
interface MysqlOkPacket {
  affectedRows: number
  changedRows: number
  insertId: number
}

Build docs developers (and LLMs) love

Get started for freeTalk to us