S42-Core SQLite Class

• SQLite Class Documentation
  • Key Features
  • Installation
  • Usage Examples
    • Initialization
    • Creating and Managing Tables
      • Create a Table
      • Drop a Table
      • Add Columns to an Existing Table
    • CRUD Operations
      • Insert Data
      • Select Data
      • Update Data
      • Delete Data
    • Indexing
      • Create an Index
    • Metadata Access
      • Get All Tables
      • Get Table Schema
  • Advantages of Using the SQLite Class
  • Error Handling
  • License

SQLite Class Documentation

The SQLite class is part of the s42-core package and provides a comprehensive and efficient interface for interacting with SQLite databases. It supports common operations like creating, updating, querying, and deleting data, as well as advanced features such as schema management, indexing, and MongoDB-style query translation.

Key Features

• Flexibility: Support for both in-memory and file-based SQLite databases.
• CRUD Operations: Simplified methods for INSERT, SELECT, UPDATE, and DELETE operations.
• Schema Management: Create, drop, and alter tables dynamically.
• Pagination Support: Built-in LIMIT and OFFSET options for efficient query handling.
• Indexing: Create indexes to optimize query performance.
• Metadata Access: Retrieve a list of all tables and their schemas.
• MongoDB-like Queries: Translate MongoDB-style queries to SQL for seamless integration.
• Error Handling: Built-in validations and descriptive error messages to ensure reliability.

---

Installation

To use the SQLite class, install the s42-core package:

npm install s42-core

---

Usage Examples

Initialization

import { SQLite } from 's42-core';

// Create an in-memory database
const sqlite = new SQLite({ type: 'memory' });

// Create a file-based database
const sqliteFile = new SQLite({ type: 'file', filename: 'data.db' });

Creating and Managing Tables

Create a Table

await sqlite.createTable('users', {
  id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
  name: 'TEXT',
  age: 'INTEGER',
});

Drop a Table

await sqlite.dropTable('users');

Add Columns to an Existing Table

await sqlite.addTableColumns('users', {
  email: 'TEXT',
  isActive: 'BOOLEAN',
});

CRUD Operations

Insert Data

await sqlite.insert('users', { name: 'John Doe', age: 30 });

Select Data

const users = await sqlite.select('users', ['id', 'name'], { age: { $gte: 18 } }, { name: 1 }, 10, 0);
console.log(users);

Update Data

await sqlite.update('users', { id: 1 }, { name: 'Jane Doe', age: 28 });

Delete Data

await sqlite.delete('users', { age: { $lt: 18 } });

Indexing

Create an Index

await sqlite.createIndex('users', 'name');

Metadata Access

Get All Tables

const tables = await sqlite.getAllTables();
console.log(tables);

Get Table Schema

const schema = await sqlite.getTableSchema('users');
console.log(schema);

---

Advantages of Using the SQLite Class

1. Ease of Use: Simplifies complex SQLite operations into a clean and intuitive API.
2. Seamless Integration: MongoDB-style query translation enables smooth integration with applications that use MongoDB-like query syntax.
3. Performance Optimization: Features like indexing and pagination ensure efficient data handling.
4. Dynamic Schema Management: Easily modify tables without manual SQL.
5. Error Resilience: Validations and detailed error messages help prevent and debug issues.
6. Extensibility: Built with flexibility to support additional features like transactions and batch operations.
7. Real-Time Query Building: Dynamically construct queries with filtering, sorting, and limiting.

---

Error Handling

The class includes robust error handling:

• Ensures valid table and column names.
• Throws descriptive errors when operations fail.
• Logs errors to help debug issues effectively.

---

License

This project is licensed under the MIT License. See the LICENSE file for details.