Registry / database / better-sqlite3-schema

better-sqlite3-schema

library3.1.10jsnpmunverified

A library for migrating nested and multi-dimensional JSON data to/from SQLite databases using better-sqlite3-helper. Version 3.1.10 is stable, with active maintenance. It supports both runtime composition and code generation for ~50% speed improvement. Key differentiators: schema-driven normalization, bulk inserts with transactions, and caching of normalized values to reduce duplication. Suitable for large datasets (e.g., 8GiB proxy logs) with significant storage savings (e.g., 11% of plain text size). Includes TypeScript types via shipped definitions.

database

npm install better-sqlite3-schema

github ↗package ↗homepage ↗

INSTALL

IMPORT

SIG · BETTER-SQLITE3-SCH

better-sqlite3-schema

databasejavascriptv3.1.10

harness data pending

Install & Compatibility

Where this runs

No compatibility data collected yet for this library.

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

TableSchema

✓ import { TableSchema } from 'better-sqlite3-schema'

✗ const TableSchema = require('better-sqlite3-schema').TableSchema

ESM only; TypeScript types are bundled.

makeSchemaScanner

✓ import { makeSchemaScanner } from 'better-sqlite3-schema'

✗ import makeSchemaScanner from 'better-sqlite3-schema'

Named export, not default export.

makeInsertRowFnFromSchema

✓ import { makeInsertRowFnFromSchema } from 'better-sqlite3-schema'

Functional approach helper for composing insert functions.

makeDeduplicatedInsertRowFnFromSchema

✓ import { makeDeduplicatedInsertRowFnFromSchema } from 'better-sqlite3-schema'

Provides deduplication during inserts.

makeSelectRowFnFromSchema

✓ import { makeSelectRowFnFromSchema } from 'better-sqlite3-schema'

Select helper from schema definition.

makeSelectRefFieldArray

✓ import { makeSelectRefFieldArray } from 'better-sqlite3-schema'

Select helper for reference field arrays.

makeGetRefValueFnFromSchema

✓ import { makeGetRefValueFnFromSchema } from 'better-sqlite3-schema'

Getter for reference values.

Demonstrates defining TableSchema, creating tables via makeSchemaScanner, inserting a row with makeInsertRowFnFromSchema, and querying back using better-sqlite3 directly.

import { TableSchema, makeSchemaScanner, makeInsertRowFnFromSchema } from 'better-sqlite3-schema' import Database from 'better-sqlite3' // Define schemas for nested JSON data const postSchema: TableSchema = { table: 'post', fields: { pid: 'integer', tid: 'integer', uid: 'integer', content: 'text', }, } const threadSchema: TableSchema = { table: 'thread', fields: { tid: 'integer', subject: 'text', uid: 'integer', }, refFields: ['type'], } // Open database and create tables const db = new Database(':memory:') const scanner = makeSchemaScanner(db, [threadSchema, postSchema]) scanner.createTables() const insertThread = makeInsertRowFnFromSchema(db, threadSchema) insertThread({ tid: 1, subject: 'Hello', uid: 42, type: 'general' }) // Read back const row = db.prepare('SELECT * FROM thread WHERE tid = ?').get(1) console.log(row)

Debug

Known issues

gotchaThe library is ESM-only. CommonJS require() will not work and may cause an error.

fix

Use dynamic import() in CommonJS projects or switch to ES modules.

affects: >=3.0.0

deprecatedmakePredefinedInsertRowFn and makeGeneralInsertRowFn are deprecated in favor of the functional approach using makeInsertRowFnFromSchema.

fix

Migrate to makeInsertRowFnFromSchema or makeDeduplicatedInsertRowFnFromSchema.

affects: >=3.0.0

gotchaPeer dependencies @beenotung/better-sqlite3-helper, @types/better-sqlite3, and @types/integer must be installed manually. Missing them can cause TypeScript compilation errors or runtime failures.

fix

Run: npm install @beenotung/better-sqlite3-helper @types/better-sqlite3 @types/integer

affects: >=2.0.0

gotchaTableSchema fields use column names without table prefix. Ensure refFields values match column names in referenced tables.

fix

Use consistent naming: e.g., refField 'type' refers to column 'type' in the same or related table, but must be defined as a field in the parent schema.

affects: >=1.0.0

gotchaThe library assumes a specific SQLite PRAGMA configuration for optimal performance (synchronous OFF, journal_mode MEMORY, large cache). Not setting these may degrade import speed significantly.

fix

Set PRAGMA synchronous = OFF, PRAGMA journal_mode = MEMORY, and increase cache_size before bulk operations.

affects: >=1.0.0

gotchaBulk insert uses transactions with default batch size (8K). Exceeding available memory with too many cached normalized values can cause out-of-memory errors.

fix

Monitor memory usage and reduce batch size or implement custom caching strategy for very large datasets.

affects: >=1.0.0

Errors

Common errors & fixes

Cannot find module '@beenotung/better-sqlite3-helper' or its corresponding type declarations.

Missing peer dependency @beenotung/better-sqlite3-helper.

fix

Install the missing peer dependency: npm install @beenotung/better-sqlite3-helper

TypeError: makeSchemaScanner is not a function

Incorrect import (default import instead of named import).

fix

Use named import: import { makeSchemaScanner } from 'better-sqlite3-schema'

ReferenceError: module is not defined in ES module scope

Trying to use require() in an ESM project.

fix

Switch to import syntax or use dynamic import: const pkg = await import('better-sqlite3-schema')

SQLITE_ERROR: table thread already exists

Running makeSchemaScanner's createTables on existing tables without dropping them first.

fix

Drop tables before creation or use IF NOT EXISTS: db.exec('CREATE TABLE IF NOT EXISTS thread (...)')

Upgrade

Version history

3.1.10latest on npm

Audit

Dependencies

@beenotung/better-sqlite3-helperrequiredPeer dependency: provides the underlying database connection and helper utilities used by better-sqlite3-schema.

@types/better-sqlite3optionalPeer dependency: required for TypeScript type definitions when using better-sqlite3.

@types/integeroptionalPeer dependency: provides TypeScript types for the integer package used internally.

Agent activity

9 hits · last 30 days

node

OpenAI (training)

Resources

githubhttps://github.com/beenotung/better-sqlite3-schema ↗

packagebetter-sqlite3-schema ↗

homepagehttps://github.com/beenotung/better-sqlite3-schema#readme ↗