📘 ExcelDB

A simple Excel-based database for Node.js.
Use Excel (.xlsx) files like JSON storage with CRUD, filtering, sorting, and pagination — no external database required.

🚀 Installation

npm install excel-server-db
# or
yarn add excel-server-db

⚡ Quick Start

import ExcelDB from "excel-server-db";

const db = new ExcelDB("mydb.xlsx");

interface User {
  id?: number; // id is auto-assigned if omitted
  name: string;
  age: number;
}

(async () => {
  // Insert data (auto-id)
  const alice = await db.insert<User>("users", { name: "Alice", age: 25 });
  const bob = await db.insert<User>("users", { name: "Bob", age: 30 });

  // Get all users
  const users = await db.getAll<User>("users");
  console.log(users);

  // Get by ID
  const a1 = await db.getById<User>("users", alice.id!);
  console.log(a1);

  // Update (or upsert if not exists)
  await db.update<User>("users", alice.id!, { id: alice.id!, ...alice, age: 26 });

  // Delete
  await db.deleteById<User>("users", bob.id!);

  // Export JSON
  const json = await db.exportJSON<User>("users");
  console.log(json);

  // Import JSON (overwrites the sheet)
  await db.importJSON<User>("users", [
    { id: 1, name: "Alice", age: 28 },
    { id: 2, name: "Charlie", age: 22 },
  ]);

  // List sheet names
  const sheets = await db.listSheets();
  console.log(sheets);
})();

🧩 Simple Examples

Insert a User

await db.insert("users", { name: "John", age: 20 });

Fetch All Users

const users = await db.getAll("users");
console.log(users);

Update User

await db.update("users", 1, { id: 1, name: "John Updated", age: 21 });

Delete User

await db.deleteById("users", 1);

Filter + Sort + Paginate

const results = await db.getAll("users", {
  filter: { age: 25 },   // exact match filter
  sortBy: "id",
  order: "desc",
  page: 1,
  limit: 5,
});

⚡ Next.js Example (App Router)

Here’s how you can integrate excel-server-db inside a Next.js 15 App Router project.

API Route – /app/api/users/route.ts

import { NextResponse } from "next/server";
import ExcelDB from "excel-server-db";

const db = new ExcelDB("mydb.xlsx");

export async function GET() {
  const users = await db.getAll("users");
  return NextResponse.json(users);
}

export async function POST(req: Request) {
  const body = await req.json();
  const user = await db.insert("users", body);
  return NextResponse.json(user);
}

export async function PUT(req: Request) {
  const body = await req.json(); // expects { id, ...fields }
  await db.update("users", body.id, body);
  return NextResponse.json({ ok: true });
}

export async function DELETE(req: Request) {
  const { id } = await req.json();
  await db.deleteById("users", Number(id));
  return NextResponse.json({ ok: true });
}

Page Component – /app/users/page.tsx

"use client";
import { useState, useEffect } from "react";

type User = { id?: number; name: string; age: number };

export default function UsersPage() {
  const [users, setUsers] = useState<User[]>([]);
  const [name, setName] = useState("");
  const [age, setAge] = useState("");

  const refresh = async () => {
    const res = await fetch("/api/users");
    setUsers(await res.json());
  };

  useEffect(() => { refresh(); }, []);

  const addUser = async () => {
    await fetch("/api/users", {
      method: "POST",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ name, age: Number(age) }),
    });
    setName(""); setAge("");
    refresh();
  };

  const updateFirst = async () => {
    if (!users.length) return;
    const first = users[0];
    await fetch("/api/users", {
      method: "PUT",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ id: first.id, name: first.name, age: (first.age ?? 0) + 1 }),
    });
    refresh();
  };

  const deleteLast = async () => {
    if (!users.length) return;
    const last = users[users.length - 1];
    await fetch("/api/users", {
      method: "DELETE",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({ id: last.id }),
    });
    refresh();
  };

  return (
    <div className="p-6 max-w-xl mx-auto">
      <h1 className="text-2xl font-bold mb-4">Users</h1>

      <div className="mb-4 space-x-2">
        <input className="border p-2" placeholder="Name" value={name} onChange={(e) => setName(e.target.value)} />
        <input className="border p-2" placeholder="Age" value={age} onChange={(e) => setAge(e.target.value)} />
        <button className="bg-blue-600 text-white px-3 py-2 rounded" onClick={addUser}>Add</button>
        <button className="bg-yellow-500 text-white px-3 py-2 rounded" onClick={updateFirst}>+1 Age (first)</button>
        <button className="bg-red-600 text-white px-3 py-2 rounded" onClick={deleteLast}>Delete last</button>
      </div>

      <ul className="space-y-2">
        {users.map((u) => (
          <li key={u.id} className="border p-2 rounded">{u.id}. {u.name} — {u.age}</li>
        ))}
      </ul>
    </div>
  );
}

Tip: The Excel file (mydb.xlsx) is created automatically on first write. Sheets are created lazily when first used.

📂 API Reference (with Examples)

Below are all public methods with usage examples. All methods are async and return Promises.

new ExcelDB(filePath: string)

Create or open an Excel workbook on disk. If the file doesn’t exist, it’s created with one sheet.

import ExcelDB from "excel-server-db";
const db = new ExcelDB("mydb.xlsx");

insert<T>(sheetName: string, data: T): Promise<T>

Insert a new row. If data.id is missing, an auto-incremented id is assigned.

type User = { id?: number; name: string; age: number };

const u1 = await db.insert<User>("users", { name: "Maya", age: 24 });
// -> { id: 1, name: "Maya", age: 24 }

const u2 = await db.insert<User>("users", { id: 10, name: "Sam", age: 31 });
// -> respects provided id: { id: 10, name: "Sam", age: 31 }

getAll<T>(sheetName: string, options?): Promise<T[]>

Get all rows, with optional filter, sort, and pagination.

type User = { id: number; name: string; age: number; role?: string };

// 1) All rows
const all = await db.getAll<User>("users");

// 2) Exact-match filtering (all admins age 25)
const filtered = await db.getAll<User>("users", {
  filter: { role: "admin", age: 25 },
});

// 3) Sorting (by age descending)
const sorted = await db.getAll<User>("users", {
  sortBy: "age",
  order: "desc",
});

// 4) Pagination (page 2, 10 per page)
const paged = await db.getAll<User>("users", {
  page: 2,
  limit: 10,
});

// 5) Combined
const combo = await db.getAll<User>("users", {
  filter: { role: "member" },
  sortBy: "name",
  order: "asc",
  page: 1,
  limit: 20,
});

Note: Filtering is exact-equality on the provided keys.

getById<T extends { id: number }>(sheetName: string, id: number): Promise<T | null>

Find a single row by its id. Returns null if not found.

const row = await db.getById<User>("users", 5);
if (!row) console.log("Not found");

update<T extends { id: number }>(sheetName: string, id: number, data: T): Promise<void>

Update an existing row (matched by data.id). If the row doesn’t exist, it’s inserted (upsert).

// Update existing
await db.update<User>("users", 1, { id: 1, name: "Maya K", age: 25 });

// Upsert new (if id 99 doesn't exist, it will be added)
await db.update<User>("users", 99, { id: 99, name: "New User", age: 18 });

deleteById<T extends { id: number }>(sheetName: string, id: number): Promise<void>

Delete a row by id (no error if it didn’t exist).

await db.deleteById<User>("users", 2);

exportJSON<T>(sheetName: string): Promise<T[]>

Read the entire sheet as JSON.

const dump = await db.exportJSON<User>("users");
// e.g. [ { id:1, name:"Maya", age:25 }, { id:2, ... } ]

importJSON<T>(sheetName: string, data: T[]): Promise<void>

Overwrite the sheet with the provided JSON array.

await db.importJSON<User>("users", [
  { id: 1, name: "Reset A", age: 20 },
  { id: 2, name: "Reset B", age: 22 },
]);

listSheets(): Promise<string[]>

List all sheet names in the workbook.

const sheets = await db.listSheets(); // ["users", "orders", ...]

🔐 Features

• Auto ID handling (like json-server)
• Safe concurrent writes with a queue
• Filtering, sorting, and pagination
• JSON import/export
• Multiple sheets support
• TypeScript generics for strong typing

📌 Use Cases

• Prototyping without a real DB
• Local-first apps
• CLI tools
• Small CMS / admin dashboards
• Data migration & bulk editing with Excel

📝 Notes & Tips

• File creation: The Excel file is created automatically on first write.
• Sheet creation: A sheet is created the first time you access it.
• IDs: Prefer numeric id fields for best results.
• Equality filters: filter uses exact-equality comparisons.
• Atomic writes: Writes are queued to avoid corruption during concurrent operations.

📄 License

MIT © 2025