{"_id":"@data-prism/sqlite-store","_rev":"2-6b33fafacca60e3015c5666e19edb62a","name":"@data-prism/sqlite-store","dist-tags":{"latest":"0.0.2"},"versions":{"0.0.1":{"name":"@data-prism/sqlite-store","version":"0.0.1","keywords":[],"author":{"name":"Jake Sower"},"_id":"@data-prism/sqlite-store@0.0.1","maintainers":[{"name":"blossomlib","email":"kind.salt4683@fastmail.com"}],"dist":{"shasum":"3598445d090731e67944281a06c9fb6a5ee3afa8","tarball":"https://registry.npmjs.org/@data-prism/sqlite-store/-/sqlite-store-0.0.1.tgz","fileCount":18,"integrity":"sha512-VQ+cpvYKTApp8QOtg8/2cmAlFDFtQFdBA5MbC+eZwzf7gepnh/Y5Wv9iRGJtCr1SFaiNfrjI6+rXlC3SLQ5skA==","signatures":[{"sig":"MEUCIQCnfykLhs4+t+Tg9jQpihFva9oy0Fx+kL4Xh6AeyphqOQIgIqJFV1fkOUdXNvS9RZJ775tB7IBnOMoxSCHc79AX+rU=","keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA"}],"unpackedSize":40514},"main":"./dist/src/sqlite-store.js","type":"module","types":"./dist/src/sqlite-store.d.ts","exports":{".":"./dist/src/sqlite-store.js"},"gitHead":"f6583a2150216a1bb65b2caf7e835741b1556308","private":false,"scripts":{"test":"NODE_ENV=test vitest","build":"tsc -p ./tsconfig.json"},"_npmUser":{"name":"blossomlib","email":"kind.salt4683@fastmail.com"},"_npmVersion":"9.5.0","directories":{},"_nodeVersion":"18.15.0","dependencies":{"lodash-es":"^4.17.21","better-sqlite3":"^7.6.2","@data-prism/utils":"*","@data-prism/expression":"^0.0.1","@data-prism/store-core":"*"},"publishConfig":{"access":"public"},"_hasShrinkwrap":false,"devDependencies":{"eslint":"^8.38.0","vitest":"^0.29.8","prettier":"^2.8.7","typescript":"^5.0.4","typescript-eslint":"^0.0.1-alpha.0","eslint-config-prettier":"^8.8.0","eslint-config-standard":"^17.0.0","@typescript-eslint/parser":"^5.57.1","@typescript-eslint/eslint-plugin":"^5.57.1"},"_npmOperationalInternal":{"tmp":"tmp/sqlite-store_0.0.1_1684855495573_0.9009385079517225","host":"s3://npm-registry-packages"}},"0.0.2":{"name":"@data-prism/sqlite-store","version":"0.0.2","deprecated":"This package has been renamed to '@spectragraph/sqlite-store'. Please use '@spectragraph/sqlite-store' instead.","type":"module","main":"./dist/index.cjs.js","module":"./dist/index.esm.js","types":"./dist/index.d.ts","exports":{".":{"types":"./dist/index.d.ts","import":"./dist/index.esm.js","require":"./dist/index.cjs.js"}},"scripts":{"build":"npm run clean && rollup -c && cp index.d.ts dist/","clean":"rm -rf dist","format:check":"prettier --check \"src/**/*.{js,jsx,ts,tsx,json,css,md}\"","format:fix":"prettier --write \"src/**/*.{js,jsx,ts,tsx,json,css,md}\"","lint":"eslint src/ test/","lint:fix":"eslint --fix src/ test/","prepublishOnly":"npm run lint && npm run test","test":"NODE_ENV=test vitest run --coverage","test:watch":"vitest"},"keywords":[],"author":{"name":"Jake Sower"},"devDependencies":{"@data-prism/interface-tests":"*","@rollup/plugin-commonjs":"^28.0.6","@rollup/plugin-json":"^6.1.0","@rollup/plugin-node-resolve":"^16.0.1","@vitest/coverage-istanbul":"^3.2.4","@vitest/coverage-v8":"^3.2.4","rollup":"^4.46.2","vitest":"^3.2.4"},"publishConfig":{"access":"public"},"private":false,"sideEffects":false,"dependencies":{"@data-prism/core":"*","@data-prism/query-helpers":"*","@data-prism/sql-helpers":"*","@data-prism/utils":"*","better-sqlite3":"^12.2.0","es-toolkit":"^1.26.0","json-expressions":"^0.2.1"},"_id":"@data-prism/sqlite-store@0.0.2","gitHead":"d90da54a8b4c84ca0cde531e924f44836e9be871","description":"A SQLite backend store implementation for Data Prism that provides full CRUD operations backed by a SQLite database. Designed for applications that need fast, embedded database storage without external dependencies.","_nodeVersion":"22.18.0","_npmVersion":"10.9.3","dist":{"integrity":"sha512-Bx4BMytJsNpLI/Tu4X4unXbFbn/jiliG8v0Qe/fv3FJpfS/R5rlvmJ9yilTwyddKwEDiu83Nbzm4tF7R2LQuUQ==","shasum":"ae1fb8b52841730fa7e51375a6bbb1d409919452","tarball":"https://registry.npmjs.org/@data-prism/sqlite-store/-/sqlite-store-0.0.2.tgz","fileCount":5,"unpackedSize":1224545,"signatures":[{"keyid":"SHA256:DhQ8wR5APBvFHLF/+Tc+AYvPOdTpcIDqOhxsBHRwC7U","sig":"MEUCIQDHFZlowI9rT32uE9x+IAIFXU1XWgcBOSiF2+5uaPnXCgIgOVHWWo54443V7k3Tr3J5K2xfOPNafi7ZIfX5PbSisqg="}]},"_npmUser":{"name":"blossomlib","email":"kind.salt4683@fastmail.com"},"directories":{},"maintainers":[{"name":"blossomlib","email":"kind.salt4683@fastmail.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages-npm-production","tmp":"tmp/sqlite-store_0.0.2_1757801634530_0.33820792108047426"},"_hasShrinkwrap":false}},"time":{"created":"2023-05-23T15:24:55.501Z","modified":"2025-09-13T22:13:54.949Z","0.0.1":"2023-05-23T15:24:55.730Z","0.0.2":"2025-09-13T22:13:54.759Z"},"author":{"name":"Jake Sower"},"keywords":[],"maintainers":[{"name":"blossomlib","email":"kind.salt4683@fastmail.com"}],"readme":"# Data Prism SQLite Store\n\nA SQLite backend store implementation for Data Prism that provides full CRUD operations backed by a SQLite database. Designed for applications that need fast, embedded database storage without external dependencies.\n\n## Overview\n\nData Prism SQLite Store is built around several key principles:\n\n- **Schema-driven**: Maps Data Prism schemas to SQLite table structures\n- **Performance-optimized**: Translates Data Prism operations to efficient SQLite SQL\n- **Embedded-friendly**: Works with SQLite's serverless, zero-configuration architecture\n- **Lightweight**: Minimal dependencies with focus on performance and reliability\n\n## Installation\n\n```bash\nnpm install @data-prism/sqlite-store\n```\n\nYou'll also need to install the SQLite driver:\n\n```bash\nnpm install better-sqlite3\n```\n\n## Core Concepts\n\n### SQLite Store\n\nThe SQLite store provides full CRUD access to SQLite databases through the Data Prism interface. It's ideal for applications that need embedded database storage with automatic schema management.\n\n```javascript\nimport { createSQLiteStore } from \"@data-prism/sqlite-store\";\nimport Database from \"better-sqlite3\";\n\nconst db = new Database(\"path/to/database.sqlite\");\nconst store = createSQLiteStore(schema, db, {\n  tableMapping: { teams: \"team_table\" }, // optional\n});\n```\n\n### Database Mapping\n\nThe store maps Data Prism resource types to existing SQLite tables. You can provide custom table mappings if your database schema doesn't match Data Prism conventions.\n\n## API Reference\n\n### `createSQLiteStore(schema, db, config?)`\n\nCreates a new SQLite store instance with full CRUD operations.\n\n**Parameters:**\n\n- `schema` (Schema) - The Data Prism schema defining resource types and relationships\n- `db` (Database) - SQLite database instance from better-sqlite3\n- `config.tableMapping` (object, optional) - Custom mapping of resource types to table names\n\n**Returns:** SQLite store instance with query operations\n\n```javascript\nimport { createSQLiteStore } from \"@data-prism/sqlite-store\";\nimport Database from \"better-sqlite3\";\n\nconst db = new Database(\"./data/sports.sqlite\");\n\nconst store = createSQLiteStore(schema, db, {\n  tableMapping: {\n    teams: \"team_data\",\n    matches: \"match_results\",\n  },\n});\n```\n\n### Store Operations\n\n#### `store.query(query)` ✅\n\nExecutes a Data Prism query against the SQLite database, generating efficient SQL.\n\n**Parameters:**\n\n- `query` (RootQuery) - The query to execute\n\n**Returns:** Query results matching the query structure\n\n```javascript\nconst results = await store.query({\n  type: \"teams\",\n  where: {\n    city: { eq: \"Phoenix\" },\n  },\n  select: {\n    name: \"name\",\n    city: \"city\",\n    homeMatches: {\n      select: [\"date\", \"venue\"],\n      order: { date: \"desc\" },\n      limit: 5,\n    },\n  },\n});\n```\n\n#### `store.create(resource)` ✅\n\nCreates a new resource in the database.\n\n**Parameters:**\n\n- `resource` (CreateResource) - The resource to create with type, optional id, attributes, and relationships\n\n**Returns:** Created resource with generated id if not provided\n\n```javascript\nconst newTeam = await store.create({\n  type: \"teams\",\n  attributes: {\n    name: \"Phoenix Suns\",\n    city: \"Phoenix\",\n    founded: 1968,\n  },\n});\n```\n\n#### `store.update(resource)` ✅\n\nUpdates an existing resource in the database.\n\n**Parameters:**\n\n- `resource` (UpdateResource) - The resource to update with type, id, and changed attributes/relationships\n\n**Returns:** Updated resource\n\n```javascript\nconst updatedTeam = await store.update({\n  type: \"teams\",\n  id: \"team-1\",\n  attributes: {\n    city: \"Tempe\",\n  },\n});\n```\n\n#### `store.upsert(resource)` ✅\n\nCreates or updates a resource depending on whether it exists.\n\n**Parameters:**\n\n- `resource` (CreateResource|UpdateResource) - The resource to upsert\n\n**Returns:** Created or updated resource\n\n```javascript\nconst team = await store.upsert({\n  type: \"teams\",\n  id: \"team-1\",\n  attributes: {\n    name: \"Phoenix Suns\",\n    city: \"Phoenix\",\n  },\n});\n```\n\n#### `store.delete(resource)` ✅\n\nDeletes a resource from the database.\n\n**Parameters:**\n\n- `resource` (DeleteResource) - The resource to delete with type and id\n\n**Returns:** Deleted resource reference\n\n```javascript\nconst deleted = await store.delete({\n  type: \"teams\",\n  id: \"team-1\",\n});\n```\n\n## Database Schema Requirements\n\n### Table Structure\n\nYour SQLite tables should follow these conventions for best compatibility:\n\n- Each resource type maps to a table\n- Primary key column named `id`\n- Foreign key columns for relationships follow the pattern `{relationship_name}_id`\n\n### Example Schema\n\n```sql\n-- Teams table\nCREATE TABLE teams (\n  id TEXT PRIMARY KEY,\n  name TEXT NOT NULL,\n  city TEXT,\n  founded INTEGER\n);\n\n-- Matches table  \nCREATE TABLE matches (\n  id TEXT PRIMARY KEY,\n  date TEXT NOT NULL,\n  venue TEXT,\n  home_team_id TEXT REFERENCES teams(id),\n  away_team_id TEXT REFERENCES teams(id)\n);\n```\n\n### Custom Table Mapping\n\nIf your database doesn't follow Data Prism conventions:\n\n```javascript\nconst store = createSQLiteStore(schema, db, {\n  tableMapping: {\n    teams: \"team_master\",\n    matches: \"game_results\",\n  },\n  columnMapping: {\n    \"teams.founded\": \"founding_year\",\n    \"matches.venue\": \"stadium_name\",\n  },\n});\n```\n\n## Query Translation\n\nData Prism queries are translated to optimized SQLite SQL:\n\n### Basic Query\n\n```javascript\n// Data Prism query\nconst query = {\n  type: \"teams\",\n  select: [\"name\", \"city\"],\n  where: { city: { eq: \"Phoenix\" } },\n  limit: 10\n};\n\n// Generated SQL (approximately)\n// SELECT name, city FROM teams WHERE city = 'Phoenix' LIMIT 10;\n```\n\n### Query with Relationships\n\n```javascript\n// Data Prism query\nconst query = {\n  type: \"teams\", \n  select: {\n    name: \"name\",\n    homeMatches: {\n      select: [\"date\", \"venue\"],\n      where: { date: { gte: \"2024-01-01\" } }\n    }\n  }\n};\n\n// Generated SQL uses JOINs for efficient relationship traversal\n```\n\n## Examples\n\n### Basic Setup\n\n```javascript\nimport { createSQLiteStore } from \"@data-prism/sqlite-store\";\nimport Database from \"better-sqlite3\";\n\nconst schema = {\n  resources: {\n    teams: {\n      attributes: {\n        id: { type: \"string\" },\n        name: { type: \"string\" },\n        city: { type: \"string\" },\n        founded: { type: \"integer\" },\n      },\n      relationships: {\n        homeMatches: {\n          type: \"matches\",\n          cardinality: \"many\",\n          inverse: \"homeTeam\",\n        },\n      },\n    },\n    matches: {\n      attributes: {\n        id: { type: \"string\" },\n        date: { type: \"string\" },\n        venue: { type: \"string\" },\n      },\n      relationships: {\n        homeTeam: {\n          type: \"teams\",\n          cardinality: \"one\", \n          inverse: \"homeMatches\",\n        },\n      },\n    },\n  },\n};\n\n// Open SQLite database\nconst db = new Database(\"./sports.sqlite\");\nconst store = createSQLiteStore(schema, db);\n```\n\n### Simple Queries\n\n```javascript\n// Get all teams\nconst allTeams = await store.query({\n  type: \"teams\",\n  select: [\"name\", \"city\"],\n});\n\n// Get teams in specific city\nconst phoenixTeams = await store.query({\n  type: \"teams\",\n  where: { city: { eq: \"Phoenix\" } },\n  select: [\"name\", \"founded\"],\n  order: { founded: \"desc\" },\n});\n\n// Get team by ID\nconst team = await store.query({\n  type: \"teams\",\n  id: \"team-1\",\n  select: [\"name\", \"city\", \"founded\"],\n});\n```\n\n### Relationship Queries\n\n```javascript\n// Teams with their recent matches\nconst teamsWithMatches = await store.query({\n  type: \"teams\",\n  select: {\n    name: \"name\",\n    city: \"city\",\n    homeMatches: {\n      select: [\"date\", \"venue\"],\n      where: { date: { gte: \"2024-01-01\" } },\n      order: { date: \"desc\" },\n      limit: 5,\n    },\n  },\n});\n\n// Matches with team information\nconst matchesWithTeams = await store.query({\n  type: \"matches\",\n  select: {\n    date: \"date\",\n    venue: \"venue\", \n    homeTeam: {\n      select: [\"name\", \"city\"],\n    },\n    awayTeam: {\n      select: [\"name\", \"city\"],\n    },\n  },\n  order: { date: \"desc\" },\n  limit: 10,\n});\n```\n\n### Complex Filtering\n\n```javascript\n// Teams founded after 2000 in specific cities\nconst modernTeams = await store.query({\n  type: \"teams\",\n  where: {\n    and: [\n      { founded: { gte: 2000 } },\n      { city: { in: [\"Phoenix\", \"Scottsdale\", \"Tempe\"] } },\n    ],\n  },\n  select: [\"name\", \"city\", \"founded\"],\n  order: { founded: \"asc\" },\n});\n\n// Recent home matches for active teams\nconst recentHomeMatches = await store.query({\n  type: \"matches\",\n  where: {\n    and: [\n      { date: { gte: \"2024-01-01\" } },\n      { homeTeam: { exists: true } },\n    ],\n  },\n  select: {\n    date: \"date\",\n    venue: \"venue\",\n    homeTeam: {\n      select: [\"name\"],\n    },\n  },\n  order: { date: \"desc\" },\n  limit: 20,\n});\n```\n\n### Write Operations\n\n```javascript\n// Create a new team\nconst newTeam = await store.create({\n  type: \"teams\",\n  attributes: {\n    name: \"Phoenix Suns\",\n    city: \"Phoenix\", \n    founded: 1968,\n  },\n});\n\n// Create a match with relationships\nconst newMatch = await store.create({\n  type: \"matches\",\n  attributes: {\n    date: \"2024-03-15\",\n    venue: \"Footprint Center\",\n  },\n  relationships: {\n    homeTeam: { type: \"teams\", id: newTeam.id },\n  },\n});\n\n// Update team information\nconst updatedTeam = await store.update({\n  type: \"teams\", \n  id: newTeam.id,\n  attributes: {\n    city: \"Tempe\", // Team moved cities\n  },\n});\n\n// Upsert operation (create or update)\nconst team = await store.upsert({\n  type: \"teams\",\n  id: \"custom-team-id\",\n  attributes: {\n    name: \"Arizona Cardinals\",\n    city: \"Glendale\",\n    founded: 1898,\n  },\n});\n\n// Delete a team\nawait store.delete({\n  type: \"teams\",\n  id: \"team-to-remove\",\n});\n```\n\n## Performance Considerations\n\n### Indexing\n\nEnsure your SQLite database has appropriate indexes for common queries:\n\n```sql\n-- Indexes for common query patterns\nCREATE INDEX idx_teams_city ON teams(city);\nCREATE INDEX idx_teams_founded ON teams(founded);\nCREATE INDEX idx_matches_date ON matches(date);\nCREATE INDEX idx_matches_home_team ON matches(home_team_id);\n```\n\n### Query Optimization\n\n- Use specific selects rather than selecting all attributes\n- Add appropriate where clauses to limit result sets\n- Use limit/offset for pagination with large datasets\n- Consider using composite indexes for multi-column queries\n\n### Database Configuration\n\nFor optimal performance, configure SQLite appropriately:\n\n```javascript\nconst db = new Database(\"./data.sqlite\");\n\n// Configure SQLite for performance\ndb.pragma(\"journal_mode = WAL\");\ndb.pragma(\"synchronous = NORMAL\");\ndb.pragma(\"cache_size = 10000\");\n```\n\n## Limitations\n\n### Current Limitations\n\n- **Table mapping**: Requires existing SQLite tables that match your schema\n- **Schema evolution**: Manual table updates needed when schema changes\n\n### Future Enhancements\n\n- Automatic table creation and migration from Data Prism schemas\n- Advanced SQLite-specific optimizations\n- Support for SQLite extensions and custom functions\n- Built-in database seeding utilities\n\n## Related Packages\n\n- `@data-prism/core` - Core Data Prism functionality and schema definitions\n- `@data-prism/interface-tests` - Test suite for validating store implementations\n- `@data-prism/postgres-store` - Full-featured PostgreSQL store implementation\n- `@data-prism/memory-store` - In-memory store for development and testing\n","readmeFilename":"README.md","description":"A SQLite backend store implementation for Data Prism that provides full CRUD operations backed by a SQLite database. Designed for applications that need fast, embedded database storage without external dependencies."}