SpectraGraph SQLite Store

A SQLite backend store implementation for SpectraGraph that provides full CRUD operations backed by a SQLite database. Designed for applications that need fast, embedded database storage without external dependencies.

Overview

SpectraGraph SQLite Store is built around several key principles:

• Schema-driven: Maps SpectraGraph schemas to SQLite table structures
• Performance-optimized: Translates SpectraGraph operations to efficient SQLite SQL
• Embedded-friendly: Works with SQLite's serverless, zero-configuration architecture
• Lightweight: Minimal dependencies with focus on performance and reliability

Installation

npm install @spectragraph/sqlite-store

You'll also need to install the SQLite driver:

npm install better-sqlite3

Core Concepts

SQLite Store

The SQLite store provides full CRUD access to SQLite databases through the SpectraGraph interface. It's ideal for applications that need embedded database storage with automatic schema management.

import { createSQLiteStore } from "@spectragraph/sqlite-store";
import Database from "better-sqlite3";

const db = new Database("path/to/database.sqlite");
const store = createSQLiteStore(schema, db, {
  tableMapping: { teams: "team_table" }, // optional
});

Database Mapping

The store maps SpectraGraph resource types to existing SQLite tables. You can provide custom table mappings if your database schema doesn't match SpectraGraph conventions.

API Reference

createSQLiteStore(schema, db, config?)

Creates a new SQLite store instance with full CRUD operations.

Parameters:

• schema (Schema) - The SpectraGraph schema defining resource types and relationships
• db (Database) - SQLite database instance from better-sqlite3
• config.tableMapping (object, optional) - Custom mapping of resource types to table names

Returns: SQLite store instance with query operations

import { createSQLiteStore } from "@spectragraph/sqlite-store";
import Database from "better-sqlite3";

const db = new Database("./data/sports.sqlite");

const store = createSQLiteStore(schema, db, {
  tableMapping: {
    teams: "team_data",
    matches: "match_results",
  },
});

Store Operations

store.query(query) ✅

Executes a SpectraGraph query against the SQLite database, generating efficient SQL.

Parameters:

• query (RootQuery) - The query to execute

Returns: Query results matching the query structure

const results = await store.query({
  type: "teams",
  where: {
    city: { eq: "Phoenix" },
  },
  select: {
    name: "name",
    city: "city",
    homeMatches: {
      select: ["date", "venue"],
      order: { date: "desc" },
      limit: 5,
    },
  },
});

store.create(resource) ✅

Creates a new resource in the database.

Parameters:

• resource (CreateResource) - The resource to create with type, optional id, attributes, and relationships

Returns: Created resource with generated id if not provided

const newTeam = await store.create({
  type: "teams",
  attributes: {
    name: "Phoenix Suns",
    city: "Phoenix",
    founded: 1968,
  },
});

store.update(resource) ✅

Updates an existing resource in the database.

Parameters:

• resource (UpdateResource) - The resource to update with type, id, and changed attributes/relationships

Returns: Updated resource

const updatedTeam = await store.update({
  type: "teams",
  id: "team-1",
  attributes: {
    city: "Tempe",
  },
});

store.upsert(resource) ✅

Creates or updates a resource depending on whether it exists.

Parameters:

• resource (CreateResource|UpdateResource) - The resource to upsert

Returns: Created or updated resource

const team = await store.upsert({
  type: "teams",
  id: "team-1",
  attributes: {
    name: "Phoenix Suns",
    city: "Phoenix",
  },
});

store.delete(resource) ✅

Deletes a resource from the database.

Parameters:

• resource (DeleteResource) - The resource to delete with type and id

Returns: Deleted resource reference

const deleted = await store.delete({
  type: "teams",
  id: "team-1",
});

Database Schema Requirements

Table Structure

Your SQLite tables should follow these conventions for best compatibility:

• Each resource type maps to a table
• Primary key column named id
• Foreign key columns for relationships follow the pattern {relationship_name}_id

Example Schema

-- Teams table
CREATE TABLE teams (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  founded INTEGER
);

-- Matches table  
CREATE TABLE matches (
  id TEXT PRIMARY KEY,
  date TEXT NOT NULL,
  venue TEXT,
  home_team_id TEXT REFERENCES teams(id),
  away_team_id TEXT REFERENCES teams(id)
);

Custom Table Mapping

If your database doesn't follow SpectraGraph conventions:

const store = createSQLiteStore(schema, db, {
  tableMapping: {
    teams: "team_master",
    matches: "game_results",
  },
  columnMapping: {
    "teams.founded": "founding_year",
    "matches.venue": "stadium_name",
  },
});

Query Translation

SpectraGraph queries are translated to optimized SQLite SQL:

Basic Query

// SpectraGraph query
const query = {
  type: "teams",
  select: ["name", "city"],
  where: { city: { eq: "Phoenix" } },
  limit: 10
};

// Generated SQL (approximately)
// SELECT name, city FROM teams WHERE city = 'Phoenix' LIMIT 10;

Query with Relationships

// SpectraGraph query
const query = {
  type: "teams", 
  select: {
    name: "name",
    homeMatches: {
      select: ["date", "venue"],
      where: { date: { gte: "2024-01-01" } }
    }
  }
};

// Generated SQL uses JOINs for efficient relationship traversal

Examples

Basic Setup

import { createSQLiteStore } from "@spectragraph/sqlite-store";
import Database from "better-sqlite3";

const schema = {
  resources: {
    teams: {
      attributes: {
        id: { type: "string" },
        name: { type: "string" },
        city: { type: "string" },
        founded: { type: "integer" },
      },
      relationships: {
        homeMatches: {
          type: "matches",
          cardinality: "many",
          inverse: "homeTeam",
        },
      },
    },
    matches: {
      attributes: {
        id: { type: "string" },
        date: { type: "string" },
        venue: { type: "string" },
      },
      relationships: {
        homeTeam: {
          type: "teams",
          cardinality: "one", 
          inverse: "homeMatches",
        },
      },
    },
  },
};

// Open SQLite database
const db = new Database("./sports.sqlite");
const store = createSQLiteStore(schema, db);

Simple Queries

// Get all teams
const allTeams = await store.query({
  type: "teams",
  select: ["name", "city"],
});

// Get teams in specific city
const phoenixTeams = await store.query({
  type: "teams",
  where: { city: { eq: "Phoenix" } },
  select: ["name", "founded"],
  order: { founded: "desc" },
});

// Get team by ID
const team = await store.query({
  type: "teams",
  id: "team-1",
  select: ["name", "city", "founded"],
});

Relationship Queries

// Teams with their recent matches
const teamsWithMatches = await store.query({
  type: "teams",
  select: {
    name: "name",
    city: "city",
    homeMatches: {
      select: ["date", "venue"],
      where: { date: { gte: "2024-01-01" } },
      order: { date: "desc" },
      limit: 5,
    },
  },
});

// Matches with team information
const matchesWithTeams = await store.query({
  type: "matches",
  select: {
    date: "date",
    venue: "venue", 
    homeTeam: {
      select: ["name", "city"],
    },
    awayTeam: {
      select: ["name", "city"],
    },
  },
  order: { date: "desc" },
  limit: 10,
});

Complex Filtering

// Teams founded after 2000 in specific cities
const modernTeams = await store.query({
  type: "teams",
  where: {
    and: [
      { founded: { gte: 2000 } },
      { city: { in: ["Phoenix", "Scottsdale", "Tempe"] } },
    ],
  },
  select: ["name", "city", "founded"],
  order: { founded: "asc" },
});

// Recent home matches for active teams
const recentHomeMatches = await store.query({
  type: "matches",
  where: {
    and: [
      { date: { gte: "2024-01-01" } },
      { homeTeam: { exists: true } },
    ],
  },
  select: {
    date: "date",
    venue: "venue",
    homeTeam: {
      select: ["name"],
    },
  },
  order: { date: "desc" },
  limit: 20,
});

Write Operations

// Create a new team
const newTeam = await store.create({
  type: "teams",
  attributes: {
    name: "Phoenix Suns",
    city: "Phoenix", 
    founded: 1968,
  },
});

// Create a match with relationships
const newMatch = await store.create({
  type: "matches",
  attributes: {
    date: "2024-03-15",
    venue: "Footprint Center",
  },
  relationships: {
    homeTeam: { type: "teams", id: newTeam.id },
  },
});

// Update team information
const updatedTeam = await store.update({
  type: "teams", 
  id: newTeam.id,
  attributes: {
    city: "Tempe", // Team moved cities
  },
});

// Upsert operation (create or update)
const team = await store.upsert({
  type: "teams",
  id: "custom-team-id",
  attributes: {
    name: "Arizona Cardinals",
    city: "Glendale",
    founded: 1898,
  },
});

// Delete a team
await store.delete({
  type: "teams",
  id: "team-to-remove",
});

Performance Considerations

Indexing

Ensure your SQLite database has appropriate indexes for common queries:

-- Indexes for common query patterns
CREATE INDEX idx_teams_city ON teams(city);
CREATE INDEX idx_teams_founded ON teams(founded);
CREATE INDEX idx_matches_date ON matches(date);
CREATE INDEX idx_matches_home_team ON matches(home_team_id);

Query Optimization

• Use specific selects rather than selecting all attributes
• Add appropriate where clauses to limit result sets
• Use limit/offset for pagination with large datasets
• Consider using composite indexes for multi-column queries

Database Configuration

For optimal performance, configure SQLite appropriately:

const db = new Database("./data.sqlite");

// Configure SQLite for performance
db.pragma("journal_mode = WAL");
db.pragma("synchronous = NORMAL");
db.pragma("cache_size = 10000");

Limitations

Current Limitations

• Table mapping: Requires existing SQLite tables that match your schema
• Schema evolution: Manual table updates needed when schema changes

Future Enhancements

• Automatic table creation and migration from SpectraGraph schemas
• Advanced SQLite-specific optimizations
• Support for SQLite extensions and custom functions
• Built-in database seeding utilities

Related Packages

• @spectragraph/core - Core SpectraGraph functionality and schema definitions
• @spectragraph/interface-tests - Test suite for validating store implementations
• @spectragraph/postgres-store - Full-featured PostgreSQL store implementation
• @spectragraph/memory-store - In-memory store for development and testing