# quicklite

**Repository Path**: jl15988/quicklite

## Basic Information

- **Project Name**: quicklite
- **Description**: 一个轻量级的 SQLite ORM 工具包，专为 Node.js 和 Electron 应用程序设计。
- **Primary Language**: Unknown
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 3
- **Forks**: 0
- **Created**: 2025-03-22
- **Last Updated**: 2025-10-30

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Sqlite, ORM, Nodejs, Database, SQL

## README

# QuickLite

A lightweight ORM toolkit for SQLite in Node.js and Electron applications.

## Features

- Simple and efficient database connection management
- Entity-based table schema definition
- Automatic table creation and migrations
- Type-safe query builder
- Generic repository pattern for CRUD operations
- Supports transactions, indexes, and foreign keys
- Zero dependencies apart from better-sqlite3
- Perfect for Electron and Node.js applications
- Database migration and automatic upgrade tools
- Data backup and recovery support
- Query performance analysis tools

## Documentation

- [English](README.en.md)
- [中文文档](README.md)

## Additional Documentation

- [Query Analyzer](docs/en/QueryAnalyzer.md)
- [查询分析器中文文档](docs/zh-CN/QueryAnalyzer.md)

## License

MIT

## Installation

```bash
npm install quicklite better-sqlite3
```

> Requires Node.js v14.21.1 or later. The toolkit uses better-sqlite3 v11.8.1 (with SQLite 3.48.0).

## Basic Usage

### Database Connection

```typescript
import { DatabaseManager } from 'quicklite';

// In Node.js
const dbManager = DatabaseManager.getInstance({
  dbPath: './myapp.db',
  enableWAL: true,
  enableForeignKeys: true
});

// In Electron, typically in main process
import path from 'path';
import { app } from 'electron';

const userDataPath = app.getPath('userData');
const dbPath = path.join(userDataPath, 'database/myapp.db');

const dbManager = DatabaseManager.getInstance({
  dbPath,
  enableWAL: true,
  enableForeignKeys: true,
  sqliteOptions: {
    // Optional better-sqlite3 configuration options
    readonly: false,
    fileMustExist: false,
    timeout: 5000
  },
  // Optional: provide entity classes during connection creation
  entities: [User, Post]
});

// Get the database instance
const db = dbManager.getDatabase();

// Close database connection when needed
// dbManager.closeDatabase();
```

### Define Entity Models

QuickLite supports two ways to define entity models: by overriding the getTableInfo() method or using decorators.

#### Method 1: Override getTableInfo() Method

```typescript
import { BaseEntity, TableInfo } from 'quicklite';

export class User extends BaseEntity {
  id?: number;
  username!: string;
  email?: string;
  createdAt?: number;
  
  // Define table schema
  static getTableInfo(): TableInfo {
    return {
      name: 'users', // Specify table name
      primaryKey: 'id',
      columns: [
        {
          name: 'id',
          type: 'INTEGER',
          primaryKey: true,
          autoIncrement: true
        },
        {
          name: 'username',
          type: 'TEXT',
          notNull: true,
          unique: true
        },
        {
          name: 'email',
          type: 'TEXT',
          unique: true
        },
        {
          name: 'createdAt',
          type: 'INTEGER',
          default: 'CURRENT_TIMESTAMP'
        }
      ],
      indices: [
        {
          name: 'idx_users_email',
          columns: ['email'],
          unique: true
        }
      ]
    };
  }
}
```

#### Method 2: Using Decorators

```typescript
import { BaseEntity, Table, Column, Index } from 'quicklite';

@Table({ name: 'users' })
@Index<User>({ name: 'idx_users_email', columns: ['email'], unique: true })
export class User extends BaseEntity {
  @Column({
    type: 'INTEGER',
    primaryKey: true,
    autoIncrement: true
  })
  id?: number;

  @Column({
    type: 'TEXT',
    notNull: true,
    unique: true
  })
  username!: string;

  @Column({
    type: 'TEXT',
    unique: true
  })
  email?: string;

  @Column({
    type: 'INTEGER',
    default: 'CURRENT_TIMESTAMP'
  })
  createdAt?: number;
}
```

> Note: The Index decorator supports generics to constrain column names to entity properties, providing type safety.

### Initialize Database Tables

QuickLite provides two ways to initialize database tables: through the simplified interface of `DatabaseManager` or by directly using `DbInitializer`.

#### Method 1: Using DatabaseManager Simplified Interface

```typescript
import { DatabaseManager } from 'quicklite';
import { User, Post, Comment, Category } from './models';

// Method 1: Provide entity classes during connection creation
const dbManager = DatabaseManager.getInstance({
  dbPath: './myapp.db',
  entities: [User, Post] // Provide entities in options
});

// Initialize all tables (create if they don't exist)
dbManager.initTables();

// Method 2: Use fluent API to register entities and initialize
dbManager
  .registerEntity(User)
  .registerEntity(Post)
  .registerEntities([Comment, Category])
  .initTables(false); // false means don't force rebuild (default)

// Access full API through initializer property
// Only create tables that don't exist yet (safe mode)
const tablesCreated = dbManager.initializer.checkAndInitMissingTables();
console.log(`Created ${tablesCreated} new tables`);

// Force rebuild all tables (will drop existing tables first)
dbManager.initTables(true);
```

#### Method 2: Directly Using DbInitializer

When you need advanced table management features, you can use `DbInitializer` directly:

```typescript
import { DbInitializer } from 'quicklite';
import { User } from './models/User';
import { Post } from './models/Post';

// Get database instance
const db = dbManager.getDatabase();

// Method 1: Pass entity classes array during initialization
const entities = [User, Post];
const dbInitializer = new DbInitializer(db, entities);
dbInitializer.initTables();

// Method 2: Register entities individually using registerEntity
const dbInitializer2 = new DbInitializer(db);
dbInitializer2.registerEntity(User);
dbInitializer2.registerEntity(Post);
dbInitializer2.initTables();

// Only create tables that don't exist yet (safe mode)
const tablesCreated = dbInitializer.checkAndInitMissingTables();
console.log(`Created ${tablesCreated} new tables`);

// Force rebuild all tables (will drop existing tables first)
dbInitializer.initTables(true);
```

#### Common Initialization Patterns

```typescript
// Convenient way to register entities and initialize tables
dbManager
  .registerEntities([User, Post, Comment])
  .initTables();

// Safe initialization: only create missing tables
dbManager
  .registerEntities([User, Post, Comment]);
const created = dbManager.initializer.checkAndInitMissingTables();

// Force rebuild: drop and recreate all tables
dbManager
  .registerEntities([User, Post, Comment])
  .initTables(true);
```

### Create Services for CRUD Operations

```typescript
import { BaseService } from 'quicklite';
import { User } from './models/User';

// Create service instance directly with database connection
const userService = new BaseService<User>(db, User);

// Insert a new user
const newUser = {
  username: 'johndoe',
  email: 'john@example.com',
  createdAt: Date.now()
};

// Insert and get the generated ID
const userId = userService.insert(newUser);

// Batch insert (within a single transaction)
userService.batchInsert([
  { username: 'user1', email: 'user1@example.com' },
  { username: 'user2', email: 'user2@example.com' }
]);

// Get user by ID
const user = userService.getById(userId);

// Find users with conditions
const users = userService.find({
  where: { email: 'john@example.com' },
  orderBy: 'createdAt DESC',
  limit: 10
});

// Find a single user
const firstUser = userService.findOne({
  where: { username: 'johndoe' }
});

// Count matching records
const count = userService.count({
  where: { age: { $gt: 25 } }
});

// Update with explicit condition
userService.update({
  email: 'newemail@example.com',
  status: 'active'
}, { id: userId }); // Specify WHERE condition

// Update using primary key from entity
userService.update({
  id: userId, // ID will be used as condition
  email: 'updated@example.com'
});

// Delete with condition
userService.delete({ username: 'johndoe' });

// Delete by ID
userService.deleteById(userId);

// Execute custom query
const results = userService.query(
  'SELECT * FROM users WHERE age > ?', 
  [25]
);

// Execute custom SQL statement
const result = userService.execute(
  'UPDATE users SET active = ? WHERE last_login < ?',
  [false, Date.now() - 30 * 24 * 60 * 60 * 1000]
);
console.log(`Updated ${result.changes} rows`);
```

#### Extending The Service Class

You can create custom service classes that extend `BaseService` to implement entity-specific business logic:

```typescript
export class UserService extends BaseService<User> {
  constructor(db: Database.Database) {
    super(db, User);
  }
  
  // Custom method to find a user by username
  findByUsername(username: string): User | null {
    return this.findOne({
      where: { username }
    });
  }
  
  // Get active users
  getActiveUsers(): User[] {
    return this.find({ where: { active: true } });
  }
  
  // Custom business logic
  deactivateInactiveUsers(days: number): number {
    const cutoffDate = Date.now() - days * 24 * 60 * 60 * 1000;
    const result = this.execute(
      'UPDATE users SET active = ? WHERE last_login < ?',
      [false, cutoffDate]
    );
    return result.changes;
  }
}

// Usage
const userService = new UserService(db);
const inactiveUsers = userService.deactivateInactiveUsers(30);
```

### Using Query Builder for Complex Queries

```typescript
import { QueryBuilder } from 'quicklite';

// Get database instance
const db = dbManager.getDatabase();

// Create query builder from service
const queryBuilder = userService.createQueryBuilder();

// Or create query builder directly
const query = new QueryBuilder<User>(db, 'users');

// Build a complex query
query.select('users.*', 'COUNT(posts.id) as postCount')
  .leftJoin('posts', 'posts.userId = users.id')
  .where('createdAt', '>', Date.now() - 30 * 24 * 60 * 60 * 1000)
  .andWhere(qb => {
    qb.where('username', 'LIKE', '%john%')
      .or(subQb => {
        subQb.where('email', 'LIKE', '%john%');
      });
  })
  .groupBy('users.id')
  .having('postCount', '>', 5)
  .orderBy('postCount', 'DESC')
  .limit(10);

// Execute the query and get all results
const activeUsers = query.all();

// Get the first result
const topUser = query.first();

// Count matching records
const userCount = query.count();

// Advanced query example: Find most active users with their latest posts
const advancedQuery = new QueryBuilder(db, 'users')
  .select('users.id', 'users.username', 'posts.title as latestPostTitle')
  .leftJoin('posts', 'posts.userId = users.id')
  .where('users.active', '=', true)
  .andWhere(qb => {
    qb.where('posts.createdAt', '>', Date.now() - 7 * 24 * 60 * 60 * 1000)
      .or(subQb => {
        subQb.where('users.premium', '=', true);
      });
  })
  .groupBy('users.id')
  .having('COUNT(posts.id)', '>=', 3)
  .orderBy('posts.createdAt', 'DESC')
  .limit(20);

const activeUsers = advancedQuery.all();
```

## Transaction Support

QuickLite provides two ways to work with transactions: using the utility class or using better-sqlite3's built-in transactions.

### Method 1: Using TransactionUtils

```typescript
import { TransactionUtils } from 'quicklite';

// Create and execute a transaction
TransactionUtils.executeTransaction(db, () => {
  userService.insert({ username: 'user1' });
  userService.insert({ username: 'user2' });
  // If any operation fails, all changes will be rolled back
});
```

### Method 2: Using better-sqlite3 Built-in Transactions

```typescript
// Create a transaction function
const transaction = db.transaction(() => {
  userService.insert({ username: 'user1' });
  userService.insert({ username: 'user2' });
  // If any operation fails, all changes will be rolled back
});

// Execute the transaction
transaction();
```

### Error Handling in Transactions

```typescript
try {
  TransactionUtils.executeTransaction(db, () => {
    userService.insert({ username: 'user1' });
    
    // If an error is thrown here, the entire transaction will roll back
    if (someCondition) {
      throw new Error('Operation aborted');
    }
    
    userService.insert({ username: 'user2' });
  });
  console.log('Transaction completed successfully');
} catch (error) {
  console.error('Transaction failed and rolled back:', error);
}
```

## Utility Classes

QuickLite provides a series of utility classes to assist with database operations, performance optimization, and data management.

### Backup Utility (BackupUtil)

Provides SQLite database backup and recovery functions:

```typescript
import { BackupUtil } from 'quicklite';

// Backup the database
const success = BackupUtil.backup(sourceDb, './backups/backup.db');
if (success) {
  console.log('Database backup successful');
}

// Backup with progress callback
BackupUtil.backup(sourceDb, './backups/backup.db', (progress) => {
  console.log(`Backup progress: ${progress.totalPages - progress.remainingPages}/${progress.totalPages}`);
});

// Restore from backup file
const restoreSuccess = BackupUtil.restore('./backups/backup.db', './restored.db');

// Restore with progress callback
BackupUtil.restore('./backups/backup.db', './restored.db', (progress) => {
  console.log(`Restore progress: ${progress.totalPages - progress.remainingPages}/${progress.totalPages}`);
});
```

#### Key Methods

| Method | Description |
|------|------|
| `backup(db: Database, backupPath: string, callback?: (progress) => void): boolean` | Create a database backup to the specified file path, with optional progress callback |
| `restore(backupPath: string, targetDbPath: string, callback?: (progress) => void): boolean` | Restore a database from backup file to the target path, with optional progress callback |

### Data Transfer Utility (DataTransferUtil)

Provides data import/export and data transfer functionality:

```typescript
import { DataTransferUtil } from 'quicklite';

// Export table data to JSON
const exportedCount = DataTransferUtil.exportToJson(userService, './users.json', {
  where: { active: true },
  orderBy: 'createdAt DESC',
  metadata: { source: 'production', version: '1.2.0' }
});
console.log(`Exported ${exportedCount} records`);

// Import data from JSON
const importedCount = DataTransferUtil.importFromJson(userService, './users.json', {
  clearTable: false,     // Whether to clear the table before import
  checkTableName: true,  // Check if the table name in JSON matches
  transform: (record) => {
    // Optional data transformation function
    record.importedAt = Date.now();
    return record;
  }
});
console.log(`Imported ${importedCount} records`);

// Export query results to CSV
const csvRows = DataTransferUtil.exportQueryToCsv(
  db, 
  'SELECT id, username, email FROM users WHERE age > ?', 
  './filtered_users.csv',
  [30]
);
console.log(`Exported ${csvRows} rows to CSV`);

// Copy table data between databases
const copiedRows = DataTransferUtil.copyTableData(
  sourceDb, 
  targetDb, 
  'users',
  {
    where: 'active = 1',
    batchSize: 1000
  }
);
console.log(`Copied ${copiedRows} rows`);
```

#### Key Methods

| Method | Description |
|------|------|
| `exportToJson<T>(service: BaseService<T>, filePath: string, options?): number` | Export service table data to JSON file, returns count of exported records |
| `importFromJson<T>(service: BaseService<T>, filePath: string, options?): number` | Import data from JSON file to service table, returns count of imported records |
| `exportQueryToCsv(db: Database, query: string, filePath: string, params?: any[]): number` | Execute SQL query and export results to CSV file, returns exported rows |
| `copyTableData(sourceDb: Database, targetDb: Database, tableName: string, options?): number` | Copy table data between different database instances, returns copied rows |

#### Data Transfer Format

The exported JSON files conform to the following structure:

```typescript
interface DataTransferFormat {
  tableName: string;         // Table name
  records: Record<string, any>[];  // Data records
  metadata?: {               // Metadata
    exportTime: number;      // Export timestamp
    version?: string;        // Version information
    schema?: any;            // Table structure information
    [key: string]: any;      // Custom metadata
  };
}
```

### Query Analyzer (QueryAnalyzer)

Provides SQL query performance analysis and optimization suggestions:

```typescript
import { QueryAnalyzer } from 'quicklite';

// Analyze SQL query
const analysis = QueryAnalyzer.analyze(
  db,
  'SELECT * FROM users WHERE age > ?',
  [30]
);

console.log('Query plan:', analysis.queryPlan);
console.log('Execution time:', analysis.executionTime, 'ms');
console.log('Performance suggestions:', analysis.suggestions);

// Get index suggestions
const indexSuggestions = QueryAnalyzer.suggestIndices(
  db,
  `SELECT u.name, o.product, SUM(o.amount) as total
   FROM users u 
   JOIN orders o ON u.id = o.user_id 
   WHERE u.age > 30 
   GROUP BY u.id
   ORDER BY total DESC`
);

console.log('Index suggestions:', indexSuggestions);

// Extract table names from SQL statement
const tableNames = QueryAnalyzer.extractTableNames(
  'SELECT * FROM users JOIN orders ON users.id = orders.user_id'
);
console.log('Tables involved:', tableNames); // ['users', 'orders']
```

#### Key Methods

| Method | Description |
|------|------|
| `analyze(db: Database, sql: string, params?: any[]): QueryAnalysisResult` | Analyze SQL query and return detailed analysis results |
| `suggestIndices(db: Database, sql: string): string[]` | Suggest indexes to create based on SQL query analysis |
| `extractTableNames(sql: string): string[]` | Extract all table names from an SQL statement |

#### Analysis Result Structure

```typescript
interface QueryAnalysisResult {
  sql: string;              // Analyzed SQL statement
  queryPlan: QueryPlanNode[]; // SQLite query plan nodes
  executionTime: number;    // Execution time (milliseconds)
  suggestions: string[];    // Performance optimization suggestions
}

interface QueryPlanNode {
  id: number;               // Plan node ID
  parentId: number | null;  // Parent node ID
  detail: string;           // Detailed information
}
```

### Database Migration Utility (MigrationUtil)

Provides database schema migration and automatic upgrade functionality:

```typescript
import { MigrationUtil } from 'quicklite';

// Check table structure differences
const diff = MigrationUtil.checkTableDiff(db, User);
console.log('Added columns:', diff.addedColumns);
console.log('Altered columns:', diff.alteredColumns);
console.log('Removed columns:', diff.removedColumns);
console.log('Needs rebuild:', diff.needsRebuild);

// Migrate table structure to match latest entity definition (preserve data)
const success = MigrationUtil.migrateTable(db, User, true);
if (success) {
  console.log('Table structure migration successful, data preserved');
} else {
  console.error('Table structure migration failed');
}

// Directly rebuild table (without preserving data)
MigrationUtil.rebuildTable(db, User);
```

#### Key Methods

| Method | Description |
|------|------|
| `checkTableDiff(db: Database, entityClass: EntityClass): TableDiff` | Compare entity definition and database table structure, return differences |
| `migrateTable(db: Database, entityClass: EntityClass, preserveData?: boolean): boolean` | Migrate table structure to match entity definition, optionally preserve data |
| `rebuildTable(db: Database, entityClass: EntityClass): boolean` | Delete and recreate table (does not preserve data) |

#### Table Diff Structure

```typescript
interface TableDiff {
  addedColumns: string[];    // Column names to add
  alteredColumns: string[];  // Column names to modify
  removedColumns: string[];  // Column names to remove
  needsRebuild: boolean;     // Whether table needs to be rebuilt (some changes cannot be implemented via ALTER TABLE)
}
```

#### Migration Operation Interface

```typescript
interface MigrationOperation {
  up(db: Database.Database): void;    // Execute migration
  down(db: Database.Database): void;  // Rollback migration
}
```

## Database Encryption

QuickLite supports database encryption through the better-sqlite3-multiple-ciphers library.

### 1. Install Encryption Support

```bash
npm install better-sqlite3-multiple-ciphers
```

> Note: After installing better-sqlite3-multiple-ciphers, it will replace the standard better-sqlite3 as the database engine.

### 2. Enable Encryption

```typescript
import { DatabaseManager } from 'quicklite';

// Create an encrypted database
const dbManager = DatabaseManager.getInstance({
  dbPath: 'path/to/database.db',
  enableEncryption: true,  // Enable encryption
  password: 'your-password' // Set password
});

// Check if the system supports encryption
const isSupported = DatabaseManager.supportsEncryption(); // Returns true or false

// Operate on encryption through the encryption manager
dbManager.encryption.changePassword({ password: 'new-password' });  // Change password
dbManager.encryption.decrypt();  // Remove encryption
dbManager.encryption.encrypt({ password: 'password' });  // Encrypt database
```

### 3. Notes

- Enabling encryption requires installing the better-sqlite3-multiple-ciphers library
- If the library is not installed but encryption is attempted, an error will be thrown
- When encryption is enabled, the ChaCha20-Poly1305 HMAC algorithm is used by default
- Encrypted databases can only be opened by providing the correct password
- **Security**: For security reasons, passwords are not stored in memory and are cleared immediately after use