SpectralDB

SpectralDB is a lightweight, file‑based data store that lets you treat Markdown (and other front‑matter‑compatible) files as a mini database. Each record lives in its own file with a YAML (or TOML/JSON) front matter block for fields and a body section for unstructured content. Inspired by Drizzle and static‑site generators, SpectralDB gives you a type‑safe API in TypeScript, a simple query builder, and optional integration with Zod for runtime validation.

Features

• Schema‑first design – Describe your collections with spectralTable() and fluent field builders (text(), integer(), boolean(), arrayOf(), body()). One field must be marked as the primary key.
• Type‑safe CRUD – Insert, update, select and delete records with compile‑time type inference for both front matter and body fields. Required columns, optional columns and defaults are enforced at runtime.
• Query builder & conditions – Filter, sort and limit results using a simple fluent API reminiscent of SQL. Conditions (eq, lt, and, etc.) let you express complex predicates.
• Front matter defaults & optional fields – Supply default values (.defaultTo(value)) or mark fields optional (.optional()) just like in Drizzle. Default values are applied automatically when inserting.
• Body syntax support – Define the body column with a specific syntax (e.g., body('md') for Markdown or body('xml')). Files are persisted with the corresponding extension.
• Reflection API – Introspect your database schema at runtime using db.register() and db.reflect(). Get detailed metadata about tables, columns, and types.
• Migration Support – Evolve your schema effortlessly. Use db.migrate(table) to backfill default values for new required fields and remove obsolete data from existing records.
• Zod integration – Generate Zod schemas from your table definitions with createInsertSchema() and createSelectSchema(). Use standard Zod modifiers (.omit(), .pick(), .extend(), .partial()) to tailor validation to specific contexts.
• Distributed Storage & Indexing – Records are indexed on startup and can live in any subdirectory. SpectralDB tracks their location using path metadata, allowing you to organize files however you like (e.g., by date, category) while querying them as a single collection.
• No external database – Records are just files on disk. This makes SpectralDB ideal for static websites, note‑taking systems, project management boards, or any scenario where storing structured data in Git makes sense.

Installation

SpectralDB is distributed as a TypeScript project. To get started:

1. Install from npm:

   You can install SpectralDB directly from npm:

   npm install spectraldb

2. Build (if cloning from source):

   If you are cloning the repository, navigate into the folder and install dependencies:

   cd spectraldb
   pnpm install
   pnpm run build  # or `pnpm tsc -p .` to compile TypeScript to JavaScript

   This will create a dist/ directory containing compiled JavaScript modules.

3. Use in your project:

   You can import SpectralDB in your Node.js environment. For example:

   // ESM: import { SpectralDB, spectralTable, text, integer, body } from 'spectraldb';
   const { SpectralDB, spectralTable, text, integer, boolean, body, arrayOf, createInsertSchema } = require('spectraldb');
      
   // Define a table
   const posts = spectralTable('posts', {
     id: integer().primaryKey(),
     title: text(),
     published: boolean().defaultTo(false),
     tags: arrayOf('string').optional(),
     content: body('md'),
   });
      
   // Instantiate the DB
   const db = new SpectralDB({ rootDir: './data' });
      
   // Register for reflection (optional)
   db.register(posts);
   
   // Insert a record
   await db.insert(posts, {
     id: 1,
     title: 'Hello world',
     tags: ['intro'],
     content: '# Hello world\n\nThis post lives in Markdown.',
   });
      
   // Query records
   const all = await db.selectFrom(posts).execute();
   console.log(all);

Optional: Using the Zod integration

If you want runtime validation of your inputs, SpectralDB includes a lightweight Zod‑compatible implementation:

const { createInsertSchema, z } = require('./spectraldb/dist/index.js');

// Generate a Zod schema for inserts
const insertSchema = createInsertSchema(posts);

// Validate user input
const valid = insertSchema.parse({
  id: 2,
  title: 'Second post',
  content: '# Another post',
});
// Throws if required fields are missing or types are incorrect

// Modify the schema on the fly
const partial = insertSchema.partial();
partial.parse({}); // OK: all fields optional

const onlyIdTitle = insertSchema.pick(['id', 'title']);
onlyIdTitle.parse({ id: 3, title: 'Short' }); // Valid, content not required here

const extended = insertSchema.extend({ extra: z.string() });
extended.parse({ id: 4, title: 'Ext', content: '#', extra: 'field' }); // Valid

Note that this Zod implementation is a minimal subset sufficient for SpectralDB’s needs. It implements primitive types (string, number, boolean), arrays, objects, and basic modifiers (.optional(), .default(), .omit(), .pick(), .extend(), .partial()). You can import z from spectraldb/dist/index.js to define custom schemas.

Type Inference

SpectralDB exports helper types to infer the shape of your data directly from the table definition, similar to other ORMs.

• InferSelectModel<typeof table>: The full record shape returned by queries.
• InferInsertModel<typeof table>: The shape expected when inserting a new record.
• InferUpdateModel<typeof table>: The shape expected when updating a record (all fields optional).

import type { InferSelectModel, InferInsertModel, InferUpdateModel } from 'spectraldb';

// Full row type (for Select)
type Post = InferSelectModel<typeof posts>;
// {
//   id: number;
//   title: string;
//   published: boolean;
//   tags: string[] | undefined;
//   content: string;
//   path: string;
// }

// Insert type (for Insert)
type NewPost = InferInsertModel<typeof posts>;
// {
//   id: number;
//   title: string;
//   published?: boolean; // optional because it has a default
//   tags?: string[];     // optional
//   content: string;     // required
// }

// Update type (for Update)
type UpdatePost = InferUpdateModel<typeof posts>;
// {
//   title?: string;
//   published?: boolean;
//   tags?: string[];
//   content?: string;
//   // id cannot be updated
// }

Tutorial

This section walks through building a simple blog system with SpectralDB.

1. Define your tables

Use spectralTable() to describe your collections. Each key corresponds to a column; one column must be marked with .primaryKey(). Use .defaultTo() for default values and .optional() for fields that can be omitted.

const posts = spectralTable('posts', {
  id: integer().primaryKey(),      // Primary key and filename
  title: text(),                   // Required string
  published: boolean().defaultTo(false), // Boolean with a default
  tags: arrayOf('string').optional(),    // Optional array of strings
  content: body('md'),             // Body column stored as markdown
});

2. Create a database instance

Specify a root directory where record files will be stored. Each table will create a subdirectory under this root.

const db = new SpectralDB({ rootDir: './data' });

3. Insert records

Call insert() with the table definition and an object containing front‑matter values and body content. Fields without defaults or .optional() must be provided.

await db.insert(posts, {
  id: 1,
  title: 'First post',
  tags: ['intro', 'welcome'],
  content: '# First post\n\nThis is stored in a file called `1.md`.',
});

This will create a file at ./data/posts/1.md with the following contents:

---
type: posts
path: posts
id: 1
title: First post
published: false
tags:
  - intro
  - welcome
---

# First post

This is stored in a file called `1.md`.

4. Query and update records

Use the query builder to filter, sort and limit results. Conditions are provided via helper functions in the conditions namespace.

// Get all posts
const allPosts = await db.selectFrom(posts).execute();

// Find published posts
const publishedPosts = await db
  .selectFrom(posts)
  .where(conditions.eq(posts.fields.published, true))
  .execute();

// Update a record
await db.update(posts, 1, {
  published: true,
  content: '# Updated post\n\nNow published!',
});

// Delete a record
await db.delete(posts, 1);

5. Validate user input with Zod

Generate a Zod schema directly from your table definition:

const insertSchema = createInsertSchema(posts);

// This will throw if title is missing or id is the wrong type
insertSchema.parse({ id: 10, title: 'Title', content: '# body' });

// Make all fields optional (e.g., for partial updates)
const partialInsert = insertSchema.partial();
partialInsert.parse({}); // OK

// Pick only a subset of fields
const pickTitle = insertSchema.pick(['id', 'title']);
pickTitle.parse({ id: 11, title: 'Only Title' }); // OK

6. Migrations

As your application evolves, you may need to add new fields or remove old ones. SpectralDB handles this with db.migrate():

// New schema with a 'category' field
const postsV2 = spectralTable('posts', {
  id: integer().primaryKey(),
  title: text(),
  category: text().defaultTo('General'), // New field with default
  content: body('md'),
});

// Run migration
const result = await db.migrate(postsV2);
console.log(`Migrated ${result.updatedRecords} records`);

This will automatically scan all existing records, backfill missing metadata (type, path), add the category: 'General' field where missing, and remove any fields not present in the new schema.

7. Reflection

You can introspect your registered tables at runtime:

db.register(posts);
const metadata = db.reflect();
console.log(metadata); 
// Output: [{ name: 'posts', columns: [...], ... }]

Running tests

The project includes a comprehensive test suite covering CRUD operations and schema inference. After installing dependencies:

cd spectraldb
pnpm run build
pnpm test # Runs integration and unit tests using Vitest

The tests create temporary directories, insert sample records, perform queries, run Zod validation scenarios and verify expected behaviour. All tests should pass without modification.

Contributing & Roadmap

SpectralDB is currently in early development. Contributions are welcome! Potential improvements include:

• Adding nested object and union types to the Zod stub
• Supporting multiple body columns or custom parsers
• Incremental indexing for faster reads in large repositories
• CLI tools for migrations and data introspection

Feel free to fork the project and experiment. If you have questions or suggestions, open an issue or start a discussion!