# 📦 json-as-sql

[![npm version](https://img.shields.io/npm/v/json-as-sql.svg)](https://www.npmjs.com/package/json-as-sql)
[![npm downloads](https://img.shields.io/npm/dm/json-as-sql.svg)](https://www.npmjs.com/package/json-as-sql)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

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

```bash
npm install json-as-sql
```

---

## 🚀 Quick Start

```js
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:

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

### Advanced filter:

```js
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

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

---

## 🧾 Inserting Rows

```js
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:

```js
await db.truncateTable();
```

---

## 🔥 Drop Table

Delete the JSON file:

```js
await db.dropTable();
```

---

## 📌 Show Table Details

```js
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](https://buymeacoffee.com/vaibhavpanday).