📦 json-as-sql

Use JSON files like a SQL database in Node.js.
Perform full CRUD operations, advanced filtering, sorting, and more — all in a lightweight local .json file.

Perfect for small apps, prototypes, CLIs, local tools, or offline-first projects.

✨ Features

• ✅ No database required — just plain JSON
• ✅ Full CRUD: select, insert, update, delete
• 🔍 Advanced filters: >, <, =, !=, contains
• 📅 Smart date comparisons
• 📊 ORDER BY, LIMIT, OFFSET
• 🧰 Table tools: createTable, dropTable, truncateTable, showTableDetail
• 🧠 Simple & intuitive API

📦 Installation

npm install json-as-sql

🚀 Quick Start

const JsonDB = require('json-as-sql');
const db = new JsonDB('./db.json');

(async () => {
  await db.createTable(['id', 'name', 'age', 'created_at']);

  await db.insertMany([
    { id: 1, name: 'Alice', age: 25, created_at: '2024-01-01' },
    { id: 2, name: 'Bob', age: 30, created_at: '2024-02-15' }
  ]);

  const result = await db.select(
    { age: { op: '>', value: 23 } },
    {
      orderBy: [{ column: 'created_at', direction: 'desc' }],
      limit: 1
    }
  );

  console.log(result);
})();

✅ Core Methods

| Method | Description | |-------------------------|------------------------------------------------------| | createTable(columns) | Creates the JSON file with an empty array | | dropTable() | Deletes the JSON file | | truncateTable() | Clears all data, keeps structure | | insertOne(obj) | Inserts a single object | | insertMany(array) | Inserts an array of objects | | select(where, options)| Reads records with filtering, sorting, limits | | update(where, newData)| Updates records matching filters | | delete(where) | Deletes records matching filters | | showTableDetail() | Shows fields and file path |

🔍 Filters (WHERE)

Use exact match or advanced filters with operators.

Basic filter:

await db.select({ name: 'Alice' });

Advanced filter:

await db.select({
  age: { op: '>=', value: 25 },
  name: { op: 'contains', value: 'li' }
});

Supported Operators:

| Operator | Description | |------------|-------------------------| | = | Equal | | != | Not Equal | | > | Greater Than | | < | Less Than | | >= | Greater Than or Equal | | <= | Less Than or Equal | | contains | Case-insensitive match |

📊 Sorting, Limit, Offset

await db.select(
  {},
  {
    orderBy: [
      { column: 'created_at', direction: 'desc' },
      { column: 'name', direction: 'asc' }
    ],
    limit: 10,
    offset: 5
  }
);

🧾 Inserting Rows

await db.insertOne(
  { name: 'Charlie', age: 28, created_at: '2024-03-10' }
);

await db.insertMany([
  { name: 'Diana', age: 32, created_at: '2024-03-15' },
  { name: 'Eve', age: 27, created_at: '2024-03-18' }
]);

🩹 Truncate Table

Clear all data:

await db.truncateTable();

🔥 Drop Table

Delete the JSON file:

await db.dropTable();

📌 Show Table Details

const detail = await db.showTableDetail();
console.log(detail);
/*
{
  columns: ['name', 'age', 'created_at'],
  path: '/full/path/to/db.json'
}
*/

💡 Pro Tips

• JSON records must be valid objects
• Headers are inferred from the first record
• Ideal for up to ~100k rows depending on memory

🛡 License

MIT

👨‍💼 Author

Made with ❤️ by Vaibhav Panday

Want to contribute? PRs and issues welcome!

💖 If you find this project useful, consider buying me a coffee.