0
# MySQL2
1

2
MySQL2 is a fast MySQL client for Node.js that provides comprehensive database connectivity with focus on performance. It supports prepared statements, non-UTF8 encodings, binary log protocol, compression, SSL, and offers both callback-based and Promise-based APIs for maximum flexibility in modern JavaScript applications.
3

4
## Package Information
5

6
- **Package Name**: mysql2
7
- **Package Type**: npm
8
- **Language**: JavaScript/TypeScript
9
- **Installation**: `npm install mysql2`
10

11
## Core Imports
12

13
### Callback-based API (main module)
14

15
```javascript
16
const mysql = require('mysql2');
17
```
18

19
ES modules:
20

21
```javascript
22
import * as mysql from 'mysql2';
23
import { createConnection, createPool, createPoolCluster } from 'mysql2';
24
```
25

26
### Promise-based API
27

28
```javascript
29
const mysql = require('mysql2/promise');
30
```
31

32
ES modules:
33

34
```javascript
35
import * as mysql from 'mysql2/promise';
36
import { createConnection, createPool, createPoolCluster } from 'mysql2/promise';
37
```
38

39
## Basic Usage
40

41
### Simple Connection
42

43
```javascript
44
const mysql = require('mysql2');
45

46
// Create a connection
47
const connection = mysql.createConnection({
48
  host: 'localhost',
49
  user: 'your_username',
50
  password: 'your_password',
51
  database: 'your_database'
52
});
53

54
// Execute a query
55
connection.query('SELECT * FROM users WHERE id = ?', [1], (error, results, fields) => {
56
  if (error) throw error;
57
  console.log(results);
58
});
59

60
connection.end();
61
```
62

63
### Promise-based Usage
64

65
```javascript
66
const mysql = require('mysql2/promise');
67

68
async function main() {
69
  const connection = await mysql.createConnection({
70
    host: 'localhost',
71
    user: 'your_username',
72
    password: 'your_password',
73
    database: 'your_database'
74
  });
75

76
  const [rows, fields] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);
77
  console.log(rows);
78
  
79
  await connection.end();
80
}
81
```
82

83
### Connection Pool
84

85
```javascript
86
const mysql = require('mysql2');
87

88
const pool = mysql.createPool({
89
  host: 'localhost',
90
  user: 'your_username',
91
  password: 'your_password',
92
  database: 'your_database',
93
  connectionLimit: 10
94
});
95

96
pool.query('SELECT * FROM users', (error, results) => {
97
  if (error) throw error;
98
  console.log(results);
99
});
100
```
101

102
## Architecture
103

104
MySQL2 is built around several key components:
105

106
- **Connection Management**: Single connections, connection pools, and pool clusters for scalability
107
- **Query Execution**: Support for both text queries and prepared statements with parameter binding
108
- **Protocol Implementation**: Native JavaScript implementation of MySQL client/server protocol
109
- **Type System**: Comprehensive TypeScript definitions with full type safety
110
- **Authentication**: Multiple authentication methods including caching_sha2_password and mysql_native_password
111
- **Compression & SSL**: Built-in support for connection compression and SSL/TLS encryption
112
- **Binary Log**: Support for MySQL binary log protocol for replication scenarios
113

114
## Capabilities
115

116
### Database Connections
117

118
Core connection functionality for establishing and managing database connections with flexible configuration options.
119

120
```javascript { .api }
121
function createConnection(config: ConnectionOptions | string): Connection;
122
const connect = createConnection; // Alias for createConnection
123
function createQuery(sql: string, values?: any[]): Query;
124
```
125

126
```typescript { .api }
127
interface ConnectionOptions {
128
  host?: string;
129
  port?: number;
130
  user?: string;
131
  password?: string;
132
  database?: string;
133
  charset?: string;
134
  timezone?: string;
135
  ssl?: SslOptions | string;
136
  acquireTimeout?: number;
137
  timeout?: number;
138
  // ... additional options
139
}
140

141
interface ConnectionConfig extends ConnectionOptions {
142
  mergeFlags(defaultFlags: string[], userFlags: string[] | string): number;
143
  getDefaultFlags(options?: ConnectionOptions): string[];
144
  getCharsetNumber(charset: string): number;
145
  getSSLProfile(name: string): { ca: string[] };
146
  parseUrl(url: string): {
147
    host: string;
148
    port: number;
149
    database: string;
150
    user: string;
151
    password: string;
152
    [key: string]: any;
153
  };
154
}
155
```
156

157
[Database Connections](./connections.md)
158

159
### Connection Pools
160

161
Connection pooling for scalable database access with automatic connection management and load balancing.
162

163
```javascript { .api }
164
function createPool(config: PoolOptions | string): Pool;
165
```
166

167
```typescript { .api }
168
interface PoolOptions extends ConnectionOptions {
169
  connectionLimit?: number;
170
  waitForConnections?: boolean;
171
  queueLimit?: number;
172
  maxIdle?: number;
173
  idleTimeout?: number;
174
}
175
```
176

177
[Connection Pools](./pools.md)
178

179
### Pool Clusters
180

181
Pool cluster management for multiple connection pools with pattern-based routing and load balancing across multiple databases.
182

183
```javascript { .api }
184
function createPoolCluster(config?: PoolClusterOptions): PoolCluster;
185
```
186

187
```typescript { .api }
188
interface PoolClusterOptions {
189
  canRetry?: boolean;
190
  removeNodeErrorCount?: number;
191
  restoreNodeTimeout?: number;
192
  defaultSelector?: string;
193
}
194
```
195

196
[Pool Clusters](./pool-clusters.md)
197

198
### Query Execution
199

200
Comprehensive query execution with support for text queries, prepared statements, and parameter binding.
201

202
```javascript { .api }
203
// Connection methods
204
query(sql: string, values?: any[], callback?: function): Query;
205
execute(sql: string, values?: any[], callback?: function): void;
206
```
207

208
```typescript { .api }
209
interface QueryOptions {
210
  sql: string;
211
  values?: any[];
212
  timeout?: number;
213
  typeCast?: boolean | function;
214
}
215
```
216

217
[Query Execution](./queries.md)
218

219
### SQL Utilities
220

221
SQL string manipulation and escaping utilities for safe query construction and SQL injection prevention.
222

223
```javascript { .api }
224
function escape(value: any, stringifyObjects?: boolean, timeZone?: string): string;
225
function escapeId(value: any, forbidQualified?: boolean): string;
226
function format(sql: string, values?: any[], stringifyObjects?: boolean, timeZone?: string): string;
227
function raw(sql: string): { toSqlString: () => string };
228
```
229

230
[SQL Utilities](./sql-utilities.md)
231

232
### Promise Integration
233

234
Promise-based wrappers providing modern async/await support for all database operations.
235

236
```javascript { .api }
237
// Promise module functions
238
function createConnection(config: ConnectionOptions | string): Promise<PromiseConnection>;
239
function createPool(config: PoolOptions | string): PromisePool;
240
function createPoolCluster(config?: PoolClusterOptions): PromisePoolCluster;
241
```
242

243
[Promise Integration](./promises.md)
244

245
### Server Creation
246

247
MySQL server implementation for creating custom MySQL protocol servers and handling client connections.
248

249
```javascript { .api }
250
function createServer(handler?: (connection: Connection) => void): Server;
251
```
252

253
[Server Creation](./server.md)
254

255
## Constants and Types
256

257
```typescript { .api }
258
// MySQL data types
259
const Types: {
260
  DECIMAL: number;
261
  TINY: number;
262
  SHORT: number;
263
  LONG: number;
264
  FLOAT: number;
265
  DOUBLE: number;
266
  NULL: number;
267
  TIMESTAMP: number;
268
  LONGLONG: number;
269
  INT24: number;
270
  DATE: number;
271
  TIME: number;
272
  DATETIME: number;
273
  YEAR: number;
274
  NEWDATE: number;
275
  VARCHAR: number;
276
  BIT: number;
277
  VECTOR: number;
278
  JSON: number;
279
  NEWDECIMAL: number;
280
  ENUM: number;
281
  SET: number;
282
  TINY_BLOB: number;
283
  MEDIUM_BLOB: number;
284
  LONG_BLOB: number;
285
  BLOB: number;
286
  VAR_STRING: number;
287
  STRING: number;
288
  GEOMETRY: number;
289
  // ... additional types
290
};
291

292
// MySQL charsets
293
const Charsets: {
294
  [key: string]: number;
295
  BIG5_CHINESE_CI: number;
296
  LATIN1_SWEDISH_CI: number;
297
  UTF8_GENERAL_CI: number;
298
  UTF8MB4_GENERAL_CI: number;
299
  // ... hundreds of charset constants
300
};
301

302
// Charset to encoding mappings
303
const CharsetToEncoding: {
304
  [key: number]: string;
305
};
306
```
307

308
## Authentication Plugins
309

310
```javascript { .api }
311
interface AuthPlugin {
312
  (pluginMetadata: {
313
    connection: Connection;
314
    command: string;
315
  }): Buffer | Promise<Buffer>;
316
}
317

318
const authPlugins: {
319
  caching_sha2_password: AuthPlugin;
320
  mysql_clear_password: AuthPlugin;
321
  mysql_native_password: AuthPlugin;
322
  sha256_password: AuthPlugin;
323
};
324
```
325

326
## Parser Cache Management
327

328
```javascript { .api }
329
function setMaxParserCache(max: number): void;
330
function clearParserCache(): void;
331
```