# mm_sqlite

[中文](./README.md) | [English](./README_EN.md)

High-performance SQLite database operation module with API fully compatible with mm_mysql. A lightweight data storage solution for Node.js applications.

## Installation

```bash
npm install mm_sqlite --save
```

## Features

- 🚀 **High Performance**: Deeply optimized SQL builder and connection management
- 🔄 **Fully Compatible**: API fully compatible with mm_mysql module, seamless switching
- 📊 **Memory Optimized**: Optimized memory usage, reduced GC pressure
- 🔧 **Chainable API**: Fluent SQL builder with chainable calls
- 🛡️ **Error Handling**: Comprehensive error handling mechanism
- 📈 **Connection Pool Support**: Connection pool mode for better concurrency
- 🔒 **Transaction Support**: Complete transaction handling mechanism
- 🔍 **Type Safety**: Comprehensive JSDoc type annotations

## Requirements

- Node.js 14.0+
- SQLite3 3.0+

## Quick Start

### Basic Usage

#### Initialization and Connection

```javascript
const { Sqlite } = require('mm_sqlite');

// Create database instance
const sqlite = new Sqlite({
    dir: './db/',                  // Database file storage directory
    database: 'test',              // Database filename (without extension)
    charset: 'utf8mb4',            // Character set
    timezone: '+08:00',            // Timezone
    connect_timeout: 20000,        // Connection timeout (ms)
    acquire_timeout: 20000,        // Connection acquisition timeout (ms)
    query_timeout: 20000,          // Query timeout (ms)
    connection_limit: 1,           // Connection limit (>1 enables connection pool)
    enable_keep_alive: true,       // Enable keep-alive
    keep_alive_initial_delay: 10000, // Keep-alive initial delay
    enable_reconnect: true,        // Enable reconnection
    reconnect_interval: 1000,      // Reconnection interval
    max_reconnect_attempts: 5,     // Maximum reconnection attempts
    wait_for_connections: true,    // Wait for connections
    pool_min: 1,                   // Connection pool minimum connections
    pool_max: 5,                   // Connection pool maximum connections
    pool_acquire_timeout: 30000,   // Connection pool acquisition timeout
    pool_idle_timeout: 60000,      // Connection pool idle timeout
    pool_reap_interval: 1000       // Connection pool cleanup interval
});

// Open database connection
await sqlite.open();
```

#### Basic Operations

```javascript
// Get database manager
const db = sqlite.db();

// Set table name
const userDb = db.new('users', 'id');

// Insert data
const result = await userDb.add({
    username: 'John',
    email: 'john@example.com',
    age: 28,
    created_at: new Date()
});

// Query data
const users = await userDb.get();

// Update data
await userDb.set({ id: 1 }, { age: 29 });

// Delete data
await userDb.del({ id: 1 });
```

#### Advanced Queries

```javascript
// Conditional queries
const adultUsers = await userDb.get({
    age: { _gte: 18 }
});

// Complex queries with sorting and pagination
const users = await userDb.get({
    status: 'active',
    age: { _gte: 18, _lte: 65 }
}, {
    order: 'created_at desc',
    limit: 10,
    offset: 0
});

// Transaction operations
const transaction = await db.start();
try {
    await userDb.add({ username: 'Alice', email: 'alice@example.com' });
    await userDb.add({ username: 'Bob', email: 'bob@example.com' });
    await db.commit(transaction);
} catch (error) {
    await db.rollback(transaction);
    throw error;
}
```

## API Reference

### Sqlite Class

#### Constructor
```javascript
new Sqlite(config)
```

#### Configuration Parameters

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| dir | string | './db/' | Database file storage directory |
| database | string | 'test' | Database filename (without .sqlite extension) |
| charset | string | 'utf8mb4' | Character set |
| timezone | string | '+08:00' | Timezone offset |
| connect_timeout | number | 20000 | Connection timeout in milliseconds |
| acquire_timeout | number | 20000 | Connection acquisition timeout |
| query_timeout | number | 20000 | Query timeout |
| connection_limit | number | 1 | Connection limit (>1 enables connection pool) |
| enable_keep_alive | boolean | true | Enable keep-alive mechanism |
| keep_alive_initial_delay | number | 10000 | Keep-alive initial delay |
| enable_reconnect | boolean | true | Enable automatic reconnection |
| reconnect_interval | number | 1000 | Reconnection interval |
| max_reconnect_attempts | number | 5 | Maximum reconnection attempts |
| wait_for_connections | boolean | true | Wait for available connections |
| pool_min | number | 1 | Connection pool minimum connections |
| pool_max | number | 5 | Connection pool maximum connections |
| pool_acquire_timeout | number | 30000 | Connection pool acquisition timeout |
| pool_idle_timeout | number | 60000 | Connection pool idle timeout |
| pool_reap_interval | number | 1000 | Connection pool cleanup interval |

#### Main Methods

- `open()` - Open database connection
- `close()` - Close database connection
- `db()` - Get database manager instance
- `run(sql, params)` - Execute query SQL
- `exec(sql, params)` - Execute non-query SQL

### DB Class

#### Table Operations
- `addTable(table, field, type, auto, commit, timeout)` - Create table
- `dropTable(table, timeout)` - Drop table
- `renameTable(table, new_table, timeout)` - Rename table
- `emptyTable(table, timeout)` - Empty table
- `hasTable(table, timeout)` - Check if table exists

#### Field Operations
- `addField(table, field, type, len, def, not_null, auto, comment, timeout)` - Add field
- `dropField(table, field, timeout)` - Drop field
- `renameField(table, field, new_field, type, timeout)` - Rename field
- `fields(table, field_name, timeout)` - Get table fields
- `hasField(table, field, timeout)` - Check if field exists

#### Data Operations
- `add(data, timeout)` - Insert data
- `set(where, data, timeout)` - Update data
- `del(where, timeout)` - Delete data
- `get(where, options, timeout)` - Query data
- `count(where, timeout)` - Count records
- `sum(field, where, timeout)` - Sum field values
- `max(field, where, timeout)` - Get maximum value
- `min(field, where, timeout)` - Get minimum value

#### Transaction Operations
- `start()` - Start transaction
- `commit(transaction)` - Commit transaction
- `rollback(transaction)` - Rollback transaction

#### Utility Methods
- `new(table, key)` - Create new DB instance with table and key
- `table(table)` - Set table name
- `key(key)` - Set primary key
- `size(size)` - Set batch size
- `getTableData(table, batchSize, timeout)` - Get table data in batches

## Query Conditions

### Comparison Operators
- `_eq` - Equal to
- `_neq` - Not equal to
- `_gt` - Greater than
- `_gte` - Greater than or equal to
- `_lt` - Less than
- `_lte` - Less than or equal to

### Logical Operators
- `_and` - Logical AND
- `_or` - Logical OR
- `_not` - Logical NOT

### Array Operators
- `_in` - In array
- `_nin` - Not in array

### String Operators
- `_like` - Like pattern
- `_nlike` - Not like pattern

### Null Operators
- `_null` - Is null
- `_nnull` - Is not null

## Query Options

### Pagination
- `limit` - Number of records to return
- `offset` - Number of records to skip

### Sorting
- `order` - Sort order (e.g., 'name asc', 'created_at desc')

### Field Selection
- `field` - Specific fields to return
- `group` - Group by fields

## Error Handling

All methods include comprehensive error handling:

```javascript
try {
    const result = await db.get({ id: 1 });
} catch (error) {
    console.error('Query failed:', error.message);
}
```

## Development Specifications

### Code Style
- Use 2-space indentation
- Single quotes for strings
- One class per file or multiple functions

### Naming Conventions
- Class names: PascalCase
- Function/method names: camelCase
- Parameters/variables: snake_case
- Constants: UPPER_SNAKE_CASE
- Maximum name length: 20 characters
- Prefer single-word names

### Error Handling
- Parameter validation using `throw new TypeError()`
- Use try...catch for calling other class methods
- Chinese error messages (for Chinese version)

### Performance
- Method length ≤ 40 lines
- Single line ≤ 100 characters
- Each class focuses on single responsibility
- Each method does one thing only

## License

MIT License - see LICENSE file for details.

## Contributing

1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

## Support

For issues and questions, please open an issue on the GitHub repository.