oli-dbjson

Oli-DBjson

A lightweight Node.js library that provides a simple way to store and manage JSON data in a SQLite database.

Oli-dbjson: Simple SQLite-based JSON Storage (Multi-Table Support)

Oli-dbjson is a lightweight Node.js library that provides a simple way to store and manage JSON data in a SQLite database. It abstracts away the direct SQL interactions, allowing you to treat your data as JSON objects across multiple tables.

Features

• Flexible Database Path: Specify a custom path for your SQLite database file.
• Multi-Table Support: Create, manage, and interact with multiple tables within a single database.
• JSON Storage: Stores entire JSON objects in a TEXT column.
• Comprehensive CRUD Operations: Functions for adding, retrieving, searching, updating, and removing data for specific tables.
• Batch Operations: Efficiently add or remove multiple records (addMany, removeMany).
• Advanced Search: Supports ordering results and LIKE queries (search).
• Table Management: Functions to add, remove, and list tables.
• Image Storage (BETA): Store and retrieve images as Base64 strings within your JSON data.
• Promise-based API: All operations return Promises for easy asynchronous handling.
• Basic Logging: Includes colored console logs for better visibility of operations and errors.
• Graceful Shutdown: Handles process exit signals to close the database connection.

Installation

• Install Via Npm: Run this command

  npm install oli-dbjson
  

• Install Dependencies: You'll need sqlite3 and mime:

  npm install sqlite3 mime
  

Usage

1. Initialize the Database (start(dbFilePath))

Before performing any operations, you must initialize the database. This will connect to an existing database or create a new one.

• dbFilePath: (String, optional) The path and name for your database file (e.g., './mydata/app.db'). If omitted, it defaults to 'Data.db' in the current working directory.

const db = require('oli-dbjson'); // Adjust path if your file is named differently
const path = require('path'); // Needed for path.join example

// Option 1: Use default 'Data.db'
db.start();

// Option 2: Specify a custom database file path
// db.start('./my-app-data.db');
// db.start(path.join(__dirname, 'data', 'my-app.db')); // Example with path.join

2. Close the Database Connection (close())

Closes the database connection. It's good practice to close the connection when your application is shutting down. The library also includes process.on('exit') and process.on('SIGINT') handlers for graceful closure.

async function closeDbConnection() {
  try {
    await db.close();
    console.log('Database connection successfully closed.');
  } catch (error) {
    console.error('Error closing database:', error.message);
  }
}

// Call this when your application is done with database operations
// closeDbConnection();

3. Add Table (addTable(tableName))

Creates a new table in the database if it doesn't already exist. Each table will have id and data columns.

• tableName: (String) The name of the table to create.

async function createTables() {
  try {
    await db.addTable('users');
    console.log("Table 'users' ensured.");
    await db.addTable('products');
    console.log("Table 'products' ensured.");
  } catch (error) {
    console.error('Error creating tables:', error.message);
  }
}

// Call this after db.start()
// createTables();

4. Remove Table (removeTable(tableName))

Deletes an entire table from the database. Use with caution, as this will permanently delete all data in the table.

• tableName: (String) The name of the table to remove.

async function dropTableExample(table) {
  try {
    await db.removeTable(table);
    console.log(`Table '${table}' removed successfully.`);
  } catch (error) {
    console.error('Error removing table:', error.message);
  }
}

// dropTableExample('products');

5. List Tables (listTables())

Retrieves a list of all non-system tables in the database. Returns a Promise that resolves with an array of table names.

async function listAllTables() {
  try {
    const tables = await db.listTables();
    console.log('Tables in the database:', tables);
  } catch (error) {
    console.error('Error listing tables:', error.message);
  }
}

// listAllTables();

6. Add Data (add(tableName, data))

Adds a new JSON object to the specified table. Returns a Promise that resolves with the id of the newly inserted record.

• tableName: (String) The name of the table to add data to.
• data: (Object) The JSON object you want to store.

// Example: Adding data to 'users' table
async function addDataExample(table, data) {
  try {
    const recordId = await db.add(table, data);
    console.log(`Successfully added entry with ID: ${recordId} to '${table}' table`);
    return recordId;
  } catch (error) {
    console.error('Error adding data:', error.message);
  }
}

// addDataExample('users', { name: 'Alice', email: '[e-mail]', age: 30 });

7. Add Multiple Data Entries (addMany(tableName, dataArray))

Adds multiple JSON objects to the specified table efficiently. Returns a Promise that resolves with an array of the ids of the newly inserted records.

• tableName: (String) The name of the table to add data to.
• dataArray: (Array of Objects) An array of JSON objects to store.

async function addManyDataExample(table) {
  try {
    const newUsers = [
      { name: 'Frank', email: '[e-mail]', age: 28 },
      { name: 'Grace', email: '[e-mail]', age: 33 },
      { name: 'Heidi', email: '[e-mail]', age: 40 }
    ];
    const insertedIds = await db.addMany(table, newUsers);
    console.log(`Successfully added multiple entries to '${table}' with IDs:`, insertedIds);
  } catch (error) {
    console.error('Error adding many data entries:', error.message);
  }
}

// addManyDataExample('users');

8. Get Data (get(tableName, id))

Retrieves a single record by its id from the specified table. Returns a Promise that resolves with an object containing id and data (parsed JSON), or null if no record is found.

• tableName: (String) The name of the table to retrieve data from.
• id: (Number) The ID of the record to retrieve.

// Example: Getting data from 'users' table
async function getDataExample(table, id) {
  try {
    const record = await db.get(table, id);
    if (record) {
      console.log(`Found record with ID ${record.id} in '${table}':`, record.data);
    } else {
      console.log(`No record found with ID: ${id} in '${table}'`);
    }
  } catch (error) {
    console.error('Error getting data:', error.message);
  }
}

// Assuming you know an ID, e.g., from a previous add operation
// getDataExample('users', 1);
// getDataExample('products', 1);
// getDataExample('users', 99); // Example for a non-existent ID

9. Get All Data (getAll(tableName))

Retrieves all records from the specified table. Returns a Promise that resolves with an array of objects, each containing id and data (parsed JSON).

• tableName: (String) The name of the table to retrieve all data from.

async function getAllDataExample(table) {
  try {
    const allRecords = await db.getAll(table);
    console.log(`All records in '${table}':`, allRecords);
  } catch (error) {
    console.error('Error getting all data:', error.message);
  }
}

// getAllDataExample('users');

10. Search Data (search(tableName, filter, options))

Searches for records in the specified table based on a filter object. It allows you to query based on properties within the stored JSON data. Returns a Promise that resolves with an array of matching records (each containing id and data).

• tableName: (String) The name of the table to search in.
• filter: (Object) An object where keys are JSON properties and values are the exact values to match.
• options: (Object, optional) An object for pagination and ordering.
  • limit: (Number) Maximum number of results to return.
  • offset: (Number) Number of results to skip (default is 0).
  • orderBy: (String) The JSON key to order the results by (e.g., 'age', 'name').
  • orderDir: (String) The order direction: 'ASC' (default) or 'DESC'.
  • useLike: (Boolean) If true, uses LIKE for string comparisons in the filter (e.g., '%value%'). Default is false (exact match).

// Example: Searching data in 'users' table
async function searchDataExamples(table) {
  try {
    // Search for users named 'Alice'
    const alices = await db.search(table, { name: 'Alice' });
    console.log(`Users named Alice in '${table}':`, alices);

    // Search for users with 'example.com' in their email (using useLike)
    const exampleEmails = await db.search(table, { email: 'example.com' }, { useLike: true });
    console.log(`Users with 'example.com' in email in '${table}':`, exampleEmails);

    // Search with limit and offset
    const firstTwoUsers = await db.search(table, {}, { limit: 2, offset: 0 });
    console.log(`First two users in '${table}':`, firstTwo.map(u => u.data.name));

    // Search and order by age descending
    const usersOrderedByAge = await db.search(table, {}, { orderBy: 'age', orderDir: 'DESC' });
    console.log(`Users ordered by age (DESC) in '${table}':`, usersOrderedByAge.map(u => u.data.name + ' (' + u.data.age + ')'));

  } catch (error) {
    console.error('Error searching data:', error.message);
  }
}

// searchDataExamples('users');

11. List Records (list(tableName, filter, options))

An alias for search, providing the same functionality. Retrieves records from a specified table based on a filter and pagination options.

• tableName: (String) The name of the table to list records from.
• filter: (Object, optional) An object where keys are JSON properties and values are the exact values to match.
• options: (Object, optional) An object for pagination.
  • limit: (Number) Maximum number of results to return.
  • offset: (Number) Number of results to skip (default is 0).

async function listRecordsExample(table) {
  try {
    console.log('\n--- Listing Records Example ---');
    const allProducts = await db.list(table);
    console.log(`All products in '${table}':`, allProducts);

    const limitedProducts = await db.list(table, {}, { limit: 1 });
    console.log(`First product in '${table}':`, limitedProducts);
  } catch (error) {
    console.error('Error listing records:', error.message);
  }
}

// listRecordsExample('products');

12. Update Data (update(tableName, id, newData))

Updates the JSON data for a specific record identified by its id in the specified table. The newData object will completely replace the existing JSON data for that record. Returns a Promise that resolves to true on success, or rejects if the ID is not found.

• tableName: (String) The name of the table to update data in.
• id: (Number) The ID of the record to update.
• newData: (Object) The new JSON object that will replace the old one.

// Example: Updating a user in 'users' table
async function updateDataExample(table, id) {
  try {
    const success = await db.update(table, id, {
      name: 'Alice Smith',
      email: '[e-mail]',
      age: 31,
      status: 'active',
      updatedAt: new Date().toISOString()
    });
    if (success) {
      console.log(`Record with ID ${id} in '${table}' updated successfully.`);
      // Verify the update
      const updatedRecord = await db.get(table, id);
      console.log('Updated record data:', updatedRecord.data);
    }
  } catch (error) {
    console.error('Error updating data:', error.message);
  }
}

// updateDataExample('users', 1);

13. Remove Data (remove(tableName, id))

Removes a record from the specified table based on its id. Returns a Promise that resolves to true on success, or rejects if the ID is not found.

• tableName: (String) The name of the table to remove data from.
• id: (Number) The ID of the record to remove.

// Example: Removing a product from 'products' table
async function removeDataExample(table, id) {
  try {
    const success = await db.remove(table, id);
    if (success) {
      console.log(`Record with ID ${id} removed successfully from '${table}'.`);
      // Verify removal
      const record = await db.get(table, id);
      console.log(`Record with ID ${id} in '${table}' after removal:`, record);
    }
  } catch (error) {
    console.error('Error removing data:', error.message);
  }
}

// removeDataExample('products', 1);

14. Remove Multiple Data Entries (removeMany(tableName, ids))

Removes multiple records from the specified table based on an array of their ids.

• tableName: (String) The name of the table to remove data from.
• ids: (Array of Numbers) An array of IDs of the records to remove.

async function removeManyDataExample(table) {
  try {
    // Assuming records with IDs 1 and 2 exist in 'users'
    const success = await db.removeMany(table, [1, 2]);
    if (success) {
      console.log(`Successfully removed multiple entries from '${table}'.`);
    }
  } catch (error) {
    console.error('Error removing many data entries:', error.message);
  }
}

// removeManyDataExample('users');

15. Count Records (count(tableName, filter))

Counts the number of records in a specified table that match an optional filter. Returns 0 if no records match.

• tableName: (String) The name of the table to count records in.
• filter: (Object, optional) An object where keys are JSON properties and values are the exact values to match.

async function countRecordsExample(table) {
  try {
    const totalUsers = await db.count(table);
    console.log(`Total records in '${table}': ${totalUsers}`);

    const londonUsersCount = await db.count(table, { city: 'London' });
    console.log(`Records in '${table}' from London: ${londonUsersCount}`);
  } catch (error) {
    console.error('Error counting records:', error.message);
  }
}

// countRecordsExample('users');

BETA Image Storage Functions

Note: These functions store images as Base64 strings within the SQLite database. This can lead to very large database files and may impact performance for many large images. Consider external file storage (e.g., cloud storage) for production applications with significant image requirements.

16. Add Image (addImg(tableName, filePath, extraData))

Adds an image file to the specified table by converting it to a Base64 string and storing it along with its MIME type and original filename. You can also include additional JSON data.

• tableName: (String) The name of the table to store the image in.
• filePath: (String) The path to the image file on the local filesystem.
• extraData: (Object, optional) Additional JSON data to store alongside the image (e.g., { description: 'User profile pic' }).

const fs = require('fs'); // Needed for creating a dummy image for example

async function addImageExample(table) {
  try {
    // Create a dummy image file for demonstration
    const dummyImagePath = path.join(__dirname, 'dummy_image.png');
    fs.writeFileSync(dummyImagePath, Buffer.from('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNkYAAAAAYAAjCB0C8AAAAASUVORK5CYII=', 'base64'));
    console.log('Created dummy_image.png for demonstration.');

    const imageId = await db.addImg(table, dummyImagePath, {
      description: 'A small dummy image',
      uploadedBy: 'ExampleUser'
    });
    console.log(`Successfully added image with ID: ${imageId} to '${table}' table.`);

    // Clean up dummy image
    fs.unlinkSync(dummyImagePath);
    console.log('Cleaned up dummy_image.png.');
    return imageId;
  } catch (error) {
    console.error('Error adding image:', error.message);
  }
}

// Call this after db.start() and table creation
// addImageExample('images');

17. Get Image (getImg(tableName, id, outputFilePath))

Retrieves an image stored in the database. It returns the image data as a Buffer, along with its MIME type and original name. Optionally, it can write the image to a specified file path.

• tableName: (String) The name of the table where the image is stored.
• id: (Number) The ID of the image record to retrieve.
• outputFilePath: (String, optional) If provided, the image buffer will be written to this file path.

async function getImageExample(table, imageId) {
  try {
    const imageEntry = await db.getImg(table, imageId, path.join(__dirname, `retrieved_image_${imageId}.png`));
    console.log(`Retrieved image with ID ${imageEntry.id}:`);
    console.log(`  Mime Type: ${imageEntry.mimeType}`);
    console.log(`  Original Name: ${imageEntry.name}`);
    console.log(`  Buffer Length: ${imageEntry.buffer.length} bytes`);
    console.log(`  Extra Data:`, imageEntry.data);
  } catch (error) {
    console.error('Error getting image:', error.message);
  }
}

// Assuming an image with ID 1 exists in the 'images' table
// getImageExample('images', 1);

18. Remove Image (removeImg(tableName, id))

Removes an image record from the specified table based on its id. This is an alias for the general remove function.

• tableName: (String) The name of the table where the image is stored.
• id: (Number) The ID of the image record to remove.

async function removeImageExample(table, imageId) {
  try {
    const success = await db.removeImg(table, imageId);
    if (success) {
      console.log(`Image with ID ${imageId} removed successfully from '${table}'.`);
    }
  } catch (error) {
    console.error('Error removing image:', error.message);
  }
}

// Assuming an image with ID 1 exists in the 'images' table
// removeImageExample('images', 1);

Complete Example

Here's a comprehensive script demonstrating most operations with multiple tables, including image storage:

const db = require('oli-dbjson'); // Adjust path as needed
const path = require('path');
const fs = require('fs'); // For dummy image creation/cleanup

async function runFullExample() {
  // Start the database, specifying a custom path for this example
  const customDbPath = path.join(__dirname, 'my_app_db_full.db');
  db.start(customDbPath);

  // Give a little time for DB to initialize
  await new Promise(resolve => setTimeout(resolve, 500));

  console.log('\n--- Listing Tables (Initial) ---');
  await db.listTables().then(tables => console.log('Initial Tables:', tables));

  console.log('\n--- Creating Tables ---');
  await db.addTable('users');
  await db.addTable('products');
  await db.addTable('images'); // New table for images

  console.log('\n--- Listing Tables (After Creation) ---');
  await db.listTables().then(tables => console.log('Tables after creation:', tables));

  console.log('\n--- Adding Data to Users Table ---');
  const userId1 = await db.add('users', { name: 'Alice', email: '[e-mail]', age: 30, city: 'London' });
  const userId2 = await db.add('users', { name: 'Bob', email: '[e-mail]', age: 24, city: 'Paris' });
  const userId3 = await db.add('users', { name: 'Charlie', email: '[e-mail]', age: 30, city: 'London' });

  console.log('\n--- Adding Multiple Data to Users Table ---');
  const newUsers = [
    { name: 'David', email: '[e-mail]', age: 35, city: 'Berlin' },
    { name: 'Eve', email: '[e-mail]', age: 29, city: 'London' }
  ];
  const newUsersIds = await db.addMany('users', newUsers);
  console.log('Added new users with IDs:', newUsersIds);


  console.log('\n--- Adding Data to Products Table ---');
  const productId1 = await db.add('products', { name: 'Laptop Pro', price: 1500, category: 'Electronics' });
  const productId2 = await db.add('products', { name: 'Desk Chair', price: 250, category: 'Furniture' });

  console.log('\n--- Getting Data from Users Table ---');
  const user1 = await db.get('users', userId1);
  console.log(`User ${userId1}:`, user1 ? user1.data : 'Not found');

  console.log('\n--- Getting All Data from Products Table ---');
  const allProducts = await db.getAll('products');
  console.log('All Products:', allProducts);

  console.log('\n--- Searching Users in London (Exact Match) ---');
  const londonUsers = await db.search('users', { city: 'London' });
  console.log('Users in London:', londonUsers.map(u => u.data.name));

  console.log('\n--- Searching Users by Email (LIKE Match) ---');
  const exampleEmails = await db.search('users', { email: 'example.com' }, { useLike: true });
  console.log('Users with "example.com" in email:', exampleEmails.map(u => u.data.name));

  console.log('\n--- Searching Users Ordered by Age (DESC) ---');
  const usersOrderedByAge = await db.search('users', {}, { orderBy: 'age', orderDir: 'DESC' });
  console.log('Users ordered by age (DESC):', usersOrderedByAge.map(u => `${u.data.name} (${u.data.age})`));

  console.log('\n--- Counting Users in London ---');
  const londonUserCount = await db.count('users', { city: 'London' });
  console.log(`Number of users in London: ${londonUserCount}`);

  console.log('\n--- Updating User 2 ---');
  await db.update('users', userId2, { name: 'Robert', email: '[e-mail]', age: 25, city: 'Paris', status: 'updated' });
  const updatedUser2 = await db.get('users', userId2);
  console.log(`Updated User ${userId2}:`, updatedUser2.data);

  console.log('\n--- Removing User 3 ---');
  await db.remove('users', userId3);
  const removedUser3 = await db.get('users', userId3);
  console.log(`User ${userId3} after removal:`, removedUser3 ? removedUser3.data : 'Not found');

  console.log('\n--- Removing Multiple Users (David and Eve) ---');
  const idsToRemove = newUsersIds; // IDs of David and Eve
  await db.removeMany('users', idsToRemove);
  console.log('Removed users with IDs:', idsToRemove);

  console.log('\n--- Adding and Getting an Image (BETA) ---');
  // Create a dummy image file for demonstration
  const dummyImagePath = path.join(__dirname, 'temp_dummy_image.png');
  fs.writeFileSync(dummyImagePath, Buffer.from('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNkYAAAAAYAAjCB0C8AAAAASUVORK5CYII=', 'base64'));
  console.log('Created temp_dummy_image.png for image storage demo.');

  const imageId = await db.addImg('images', dummyImagePath, { description: 'A test image', source: 'example' });
  console.log(`Image added with ID: ${imageId}`);

  const retrievedImage = await db.getImg('images', imageId, path.join(__dirname, `retrieved_img_${imageId}.png`));
  console.log(`Retrieved image with ID ${retrievedImage.id}, saved to file.`);
  console.log(`  Mime Type: ${retrievedImage.mimeType}, Name: ${retrievedImage.name}`);

  // Clean up dummy image and retrieved image
  fs.unlinkSync(dummyImagePath);
  fs.unlinkSync(path.join(__dirname, `retrieved_img_${imageId}.png`));
  console.log('Cleaned up temporary image files.');

  console.log('\n--- Removing the Stored Image ---');
  await db.removeImg('images', imageId);
  console.log(`Image with ID ${imageId} removed from 'images' table.`);


  console.log('\n--- Listing All Users (Final) ---');
  const allRemainingUsers = await db.list('users', {});
  console.log('All remaining users:', allRemainingUsers.map(u => ({ id: u.id, name: u.data.name })));

  console.log('\n--- Removing Products and Images Tables ---');
  await db.removeTable('products');
  await db.removeTable('images');

  console.log('\n--- Listing Tables (Final) ---');
  await db.listTables().then(tables => console.log('Tables at the end:', tables));

  // Close the database connection when done
  await db.close();
}

runFullExample().catch(console.error);

Error Handling

All commands include built-in error handling to prevent crashes or interruptions. Errors are caught and handled quietly, keeping the bot stable and running smoothly.

Notes

• The database file will be created at the path specified in db.start(), or Data.db in the current working directory by default.
• The search and list functions now support orderBy and useLike options for more flexible querying.
• The update function replaces the entire JSON object for a record. If you only want to update specific fields, you would need to get the data first, modify it, and then update it.
• The EventEmitter and polling logic for change_log are present in the provided code, but the change_log table and its update mechanisms are not implemented within this library. This functionality would require additional setup and database triggers to be fully operational.
• Table names and JSON keys used in functions like json_extract are directly embedded in SQL queries. While the library implicitly handles basic table name validation in some places (e.g., isValidTableName which is not explicitly provided in the snippet but often part of such libraries), always ensure that table names and JSON keys passed to these functions are safe and do not come directly from untrusted user input to prevent SQL injection vulnerabilities.