📦 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.