lines-db

A data management library that treats JSONL (JSON Lines) files as tables. Perfect for managing application seed data and testing.

Features

• 📝 Load JSONL files as database tables
• ✅ CLI tools for validation and data migration
• 🔄 Automatic schema inference
• 📦 JSON column support with automatic serialization/deserialization
• ✅ Built-in validation using StandardSchema (Valibot, Zod, etc.)
• 🎯 Automatic type inference from table names
• 🔄 Bidirectional schema transformations
• 💾 Auto-sync to JSONL files
• 🛡️ Type-safe with TypeScript
• Node.js 22.5+ support

VS Code Extension

A VS Code extension is available that provides syntax highlighting and validation for JSONL files with schema support.

Install from VS Code Marketplace

Installation

npm install @toiroakr/lines-db
# or
pnpm add @toiroakr/lines-db

CLI Usage

Setting Up Schemas

Create schema files alongside your JSONL files:

Directory structure:

data/
  ├── users.jsonl
  ├── users.schema.ts
  ├── products.jsonl
  └── products.schema.ts

Example schema (users.schema.ts):

import * as v from 'valibot';
import { defineSchema } from '@toiroakr/lines-db';

export const schema = defineSchema(
  v.object({
    id: v.pipe(v.number(), v.integer(), v.minValue(1)),
    name: v.pipe(v.string(), v.minLength(1)),
    age: v.pipe(v.number(), v.integer(), v.minValue(0), v.maxValue(150)),
    email: v.pipe(v.string(), v.email()),
  }),
);
export default schema;

Supported validation libraries:

• Any library implementing StandardSchema

Validate JSONL Files

Validate your JSONL files against their schemas:

npx lines-db validate <path>

Example:

# Validate all JSONL files in ./data directory
npx lines-db validate ./data

# Validate a specific file
npx lines-db validate ./data/users.jsonl

# Verbose output
npx lines-db validate ./data --verbose

This command will:

• For directories: Find all .jsonl files in the directory
• For files: Validate the specified .jsonl file
• Load corresponding .schema.ts files
• Validate each record against the schema
• Report validation errors with detailed messages

Migrate Data

Transform data in JSONL files with validation:

npx lines-db migrate <file> <transform> [options]

Example:

# Update all ages by adding 1
npx lines-db migrate ./data/users.jsonl "(row) => ({ ...row, age: row.age + 1 })"

# Migrate with filter
npx lines-db migrate ./data/users.jsonl "(row) => ({ ...row, active: true })" --filter "{ age: (age) => age > 18 }"

# Save transformed data on error
npx lines-db migrate ./data/users.jsonl "(row) => ({ ...row, age: row.age + 1 })" --errorOutput ./migrated.jsonl

Options:

• --filter, -f <expr> - Filter expression to select rows
• --errorOutput, -e <path> - Save transformed data to file if migration fails
• --verbose, -v - Show detailed error messages

The migration runs in a transaction and validates all transformed rows before committing.

TypeScript Usage

Generate Types

Generate TypeScript types from your schemas for type-safe database access:

npx lines-db generate <dataDir>

Example:

# Generate types (creates ./data/db.ts by default)
npx lines-db generate ./data

Add to package.json:

"scripts": {
  "db:validate": "lines-db validate ./data",
  "db:generate": "lines-db generate ./data"
}

Quick Start

1. Create a JSONL file (./data/users.jsonl):

{"id":1,"name":"Alice","age":30,"email":"[email protected]"}
{"id":2,"name":"Bob","age":25,"email":"[email protected]"}
{"id":3,"name":"Charlie","age":35,"email":"[email protected]"}

2. Use in TypeScript:

import { LinesDB } from '@toiroakr/lines-db';

const db = LinesDB.create({ dataDir: './data' });
await db.initialize();

// Find all users
const users = db.find('users');
console.log(users); // [{ id: 1, name: "Alice", ... }, ...]

// Find a specific user
const user = db.findOne('users', { id: 1 });
console.log(user); // { id: 1, name: "Alice", age: 30, ... }

// Find with conditions
const adults = db.find('users', { age: (age) => age >= 30 });

await db.close();

Using Generated Types

After running npx lines-db generate ./data:

import { LinesDB } from '@toiroakr/lines-db';
import { config } from './data/db.js';

const db = LinesDB.create(config);
await db.initialize();

// ✨ Type is automatically inferred!
const users = db.find('users');

// ✨ Type-safe operations
db.insert('users', {
  id: 10,
  name: 'Alice',
  age: 30,
  email: '[email protected]',
});

await db.close();

Core API

Query Operations:

• find(table, where?) - Find all matching records
• findOne(table, where?) - Find a single record
• query(sql, params?) - Execute raw SQL query

Modify Operations:

• insert(table, data) - Insert a single record
• update(table, data, where) - Update matching records
• delete(table, where) - Delete matching records

Batch Operations:

• batchInsert(table, data[]) - Insert multiple records
• batchUpdate(table, updates[]) - Update multiple records
• batchDelete(table, where) - Delete multiple records

Transaction & Schema:

• transaction(fn) - Execute operations in a transaction
• getSchema(table) - Get table schema
• getTableNames() - Get all table names

Where Conditions:

// Simple equality
db.find('users', { age: 30 });

// Multiple conditions (AND)
db.find('users', { age: 30, name: 'Alice' });

// Advanced conditions
db.find('users', {
  age: (age) => age > 25,
  name: (name) => name.startsWith('A'),
});

JSON Columns

Objects and arrays are automatically handled as JSON columns:

db.insert('orders', {
  id: 1,
  items: [{ name: 'Laptop', quantity: 1 }],
  metadata: { source: 'web' },
});

const order = db.findOne('orders', { id: 1 });
console.log(order.items[0].name); // "Laptop"

Schema Transformations

When your schema transforms data types (e.g., parsing date strings into Date objects), you need to provide a backward transformation to save data back to JSONL files.

Why? JSONL files store strings like "2024-01-01", but your app works with Date objects. You need to convert both ways.

Example:

import * as v from 'valibot';
import { defineSchema } from '@toiroakr/lines-db';

const eventSchema = v.pipe(
  v.object({
    id: v.number(),
    // Transform: string → Date (when reading)
    date: v.pipe(
      v.string(),
      v.isoDate(),
      v.transform((str) => new Date(str)),
    ),
  }),
);

// Provide backward transformation: Date → string (when writing)
export const schema = defineSchema(eventSchema, (output) => ({
  ...output,
  date: output.date.toISOString(), // Convert Date back to string
}));

In your JSONL file (events.jsonl):

{
  "id": 1,
  "date": "2024-01-01T00:00:00.000Z"
}

In your TypeScript code:

const event = db.findOne('events', { id: 1 });
console.log(event.date instanceof Date); // true
console.log(event.date.getFullYear()); // 2024

Transactions

Operations outside transactions are auto-synced:

db.insert('users', { id: 10, name: 'Alice', age: 30 });
// ↑ Automatically synced to users.jsonl

Batch operations with transactions:

await db.transaction(async (tx) => {
  tx.insert('users', { id: 10, name: 'Alice', age: 30 });
  tx.update('users', { age: 31 }, { id: 1 });
  // All changes synced atomically on commit
});

Configuration

interface DatabaseConfig {
  dataDir: string; // Directory containing JSONL files
}

const db = LinesDB.create({ dataDir: './data' });

Type Mapping

| JSON Type | Column Type | SQLite Storage | | ---------------- | ----------- | -------------- | | number (integer) | INTEGER | INTEGER | | number (float) | REAL | REAL | | string | TEXT | TEXT | | boolean | INTEGER | INTEGER | | object | JSON | TEXT | | array | JSON | TEXT |

License

MIT