0
# MySQL
1
2
MySQL is a comprehensive Node.js driver for MySQL databases providing connection management, query execution, connection pooling, and cluster support. It offers both simple connection interfaces for basic applications and advanced features like connection pooling, clustering, and streaming for enterprise applications.
3
4
## Package Information
5
6
- **Package Name**: mysql
7
- **Package Type**: npm
8
- **Language**: JavaScript
9
- **Installation**: `npm install mysql`
10
11
## Core Imports
12
13
```javascript
14
const mysql = require('mysql');
15
```
16
17
For ES6 modules:
18
19
```javascript
20
import * as mysql from 'mysql';
21
```
22
23
## Basic Usage
24
25
```javascript
26
const mysql = require('mysql');
27
28
// Create a connection
29
const connection = mysql.createConnection({
30
host: 'localhost',
31
user: 'root',
32
password: 'password',
33
database: 'mydb'
34
});
35
36
// Connect and query
37
connection.connect();
38
39
connection.query('SELECT * FROM users WHERE id = ?', [1], (error, results, fields) => {
40
if (error) throw error;
41
console.log(results);
42
});
43
44
connection.end();
45
```
46
47
## Architecture
48
49
MySQL is built around several key components:
50
51
- **Connection Management**: Individual database connections with lifecycle management and event-driven architecture
52
- **Connection Pooling**: Efficient connection pooling with configurable limits, queuing, and automatic connection recovery
53
- **Cluster Support**: Pool clustering with load balancing, failover capabilities, and namespace-based routing
54
- **Query Engine**: Support for parameterized queries, prepared statements, transactions, and streaming results
55
- **Security Layer**: Built-in SQL injection protection with escaping utilities and prepared statement support
56
- **Type System**: Complete MySQL type constants and field metadata support
57
58
## Capabilities
59
60
### Connection Management
61
62
Core database connection functionality providing direct MySQL server connectivity with full lifecycle management, transaction support, and event-driven architecture.
63
64
```javascript { .api }
65
function createConnection(config);
66
67
// Connection configuration
68
interface ConnectionConfig {
69
host?: string;
70
port?: number;
71
user?: string;
72
password?: string;
73
database?: string;
74
charset?: string;
75
timezone?: string;
76
connectTimeout?: number;
77
acquireTimeout?: number;
78
timeout?: number;
79
reconnect?: boolean;
80
ssl?: boolean | object;
81
}
82
```
83
84
[Connection Management](./connection.md)
85
86
### Connection Pooling
87
88
Advanced connection pooling system for managing multiple database connections efficiently. Ideal for applications with concurrent database access requirements and production environments.
89
90
```javascript { .api }
91
function createPool(config);
92
93
// Pool configuration extends ConnectionConfig
94
interface PoolConfig extends ConnectionConfig {
95
connectionLimit?: number;
96
queueLimit?: number;
97
acquireTimeout?: number;
98
reconnect?: boolean;
99
}
100
```
101
102
[Connection Pooling](./pooling.md)
103
104
### Cluster Management
105
106
Pool clustering functionality with load balancing and failover support. Perfect for distributed applications requiring high availability and horizontal scaling across multiple MySQL servers.
107
108
```javascript { .api }
109
function createPoolCluster(config);
110
111
// Cluster configuration
112
interface ClusterConfig {
113
canRetry?: boolean;
114
removeNodeErrorCount?: number;
115
restoreNodeTimeout?: number;
116
defaultSelector?: string;
117
}
118
```
119
120
[Cluster Management](./clustering.md)
121
122
### Query Execution
123
124
Comprehensive query execution system supporting parameterized queries, streaming results, and event-driven processing. Includes full transaction support and prepared statements.
125
126
```javascript { .api }
127
function createQuery(sql, values, callback);
128
129
// Query options
130
interface QueryOptions {
131
sql: string;
132
values?: any[];
133
timeout?: number;
134
nestTables?: boolean | string;
135
typeCast?: boolean | function;
136
}
137
```
138
139
[Query Execution](./queries.md)
140
141
### Security & Utilities
142
143
SQL injection protection and utility functions for safe database interactions. Includes escaping functions, raw SQL wrappers, and SQL formatting utilities.
144
145
```javascript { .api }
146
function escape(value, stringifyObjects, timeZone);
147
function escapeId(value, forbidQualified);
148
function format(sql, values, stringifyObjects, timeZone);
149
function raw(sql): object;
150
```
151
152
[Security & Utilities](./security.md)
153
154
## Types
155
156
### MySQL Field Types
157
158
Complete MySQL field type constants for handling database metadata and type-specific operations.
159
160
```javascript { .api }
161
const Types = {
162
DECIMAL: 0,
163
TINY: 1,
164
SHORT: 2,
165
LONG: 3,
166
FLOAT: 4,
167
DOUBLE: 5,
168
NULL: 6,
169
TIMESTAMP: 7,
170
LONGLONG: 8,
171
INT24: 9,
172
DATE: 10,
173
TIME: 11,
174
DATETIME: 12,
175
YEAR: 13,
176
NEWDATE: 14,
177
VARCHAR: 15,
178
BIT: 16,
179
TIMESTAMP2: 17,
180
DATETIME2: 18,
181
TIME2: 19,
182
JSON: 245,
183
NEWDECIMAL: 246,
184
ENUM: 247,
185
SET: 248,
186
TINY_BLOB: 249,
187
MEDIUM_BLOB: 250,
188
LONG_BLOB: 251,
189
BLOB: 252,
190
VAR_STRING: 253,
191
STRING: 254,
192
GEOMETRY: 255
193
};
194
195
// Client capability flags
196
const CLIENT_FLAGS = {
197
CLIENT_LONG_PASSWORD: 1, // Use the improved version of Old Password Authentication
198
CLIENT_FOUND_ROWS: 2, // Send found rows instead of affected rows
199
CLIENT_LONG_FLAG: 4, // Allow more column flags
200
CLIENT_CONNECT_WITH_DB: 8, // Database (schema) name can be specified on connect
201
CLIENT_NO_SCHEMA: 16, // Don't allow database.table.column syntax
202
CLIENT_COMPRESS: 32, // Use compression protocol
203
CLIENT_ODBC: 64, // ODBC client
204
CLIENT_LOCAL_FILES: 128, // Enable LOAD DATA LOCAL INFILE
205
CLIENT_IGNORE_SPACE: 256, // Parser can ignore spaces before '('
206
CLIENT_PROTOCOL_41: 512, // New 4.1 protocol
207
CLIENT_INTERACTIVE: 1024, // This is an interactive client
208
CLIENT_SSL: 2048, // Switch to SSL after handshake
209
CLIENT_IGNORE_SIGPIPE: 4096, // IGNORE sigpipes
210
CLIENT_TRANSACTIONS: 8192, // Client knows about transactions
211
CLIENT_RESERVED: 16384, // Old flag for 4.1 protocol
212
CLIENT_SECURE_CONNECTION: 32768, // New 4.1 authentication
213
CLIENT_MULTI_STATEMENTS: 65536, // Enable/disable multi-stmt support
214
CLIENT_MULTI_RESULTS: 131072, // Enable/disable multi-results
215
CLIENT_PS_MULTI_RESULTS: 262144, // Multi-results in PS-protocol
216
CLIENT_PLUGIN_AUTH: 524288, // Client supports plugin authentication
217
CLIENT_CONNECT_ATTRS: 1048576, // Client supports connection attributes
218
CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA: 2097152, // Enable authentication response packet
219
CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS: 4194304, // Can handle server requests for expired password
220
CLIENT_SESSION_TRACK: 8388608, // Client supports session tracking
221
CLIENT_DEPRECATE_EOF: 16777216 // Client no longer needs EOF packet
222
};
223
```
224
225
### Common Interfaces
226
227
```javascript { .api }
228
// Query result structure
229
interface QueryResult {
230
results: any[];
231
fields: FieldInfo[];
232
}
233
234
// Field metadata
235
interface FieldInfo {
236
catalog: string;
237
db: string;
238
table: string;
239
orgTable: string;
240
name: string;
241
orgName: string;
242
charsetNr: number;
243
length: number;
244
type: number;
245
flags: number;
246
decimals: number;
247
default?: any;
248
zeroFill: boolean;
249
protocol41: boolean;
250
}
251
252
// Error structure
253
interface MysqlError extends Error {
254
code: string; // Error code (e.g., 'ER_DUP_ENTRY', 'PROTOCOL_CONNECTION_LOST')
255
errno: number; // MySQL error number
256
sqlState?: string; // SQL state code (5-character string)
257
sqlMessage?: string; // MySQL server error message
258
sql?: string; // SQL query that caused the error
259
fatal: boolean; // Whether the error is fatal (connection lost)
260
fieldCount?: number; // Number of fields (for result errors)
261
index?: number; // Parameter index (for parameter errors)
262
}
263
```