sqlcrud-client

HTTP client library for the sqlcrud SQLite REST API. Works in browsers and Node.js (native fetch, zero runtime dependencies).

Installation

npm install sqlcrud-client

Quick Start

import { SqlCrudClient } from 'sqlcrud-client';

const client = new SqlCrudClient({
  baseUrl: 'http://localhost:3123',
  username: 'admin',
  password: 'password',
});

// List tables
const tables = await client.listTables();

// Create a schema
await client.setSchema('users', {
  name: { type: 'string', length: 100, primary: true },
  email: { type: 'string', unique: true },
  active: { type: 'boolean' },
});

// Create a record (booleans coerced to 1/0 automatically)
const user = await client.createRecord('users', {
  name: 'Alice',
  email: '[email protected]',
  active: true,
});

// Query records
const activeUsers = await client.queryRecords('users', { active: 1 });

// Search with pagination
const page1 = await client.searchRecords('users', {
  filter: { active: 1 },
  page: 1,
  limit: 10,
});
console.log(page1.records, page1.totalCount);

// Count matching records
const { count } = await client.countRecords('users', { active: 1 });

// Update a record
const result = await client.updateRecord('users', { name: 'Alice' }, { email: '[email protected]' });
console.log(result.before, result.after);

// Delete records
await client.deleteRecord('users', { name: 'Alice' });

Constructor

new SqlCrudClient(config)

| Option | Type | Required | Description | |--------|------|----------|-------------| | baseUrl | string | Yes | sqlcrud server URL (e.g. 'http://localhost:3123') | | username | string | No | Username for Basic Auth | | password | string | No | Password for Basic Auth | | headers | object | No | Additional headers sent with every request |

API Methods

Schema Management

| Method | HTTP | Description | |--------|------|-------------| | listTables() | GET /api/tables | Returns string[] of table names | | listModels() | GET /api/models | Returns model info array | | getSchema(model) | GET /api/schema/:model | Returns schema definition object | | setSchema(model, schema) | POST /api/schema/:model | Create or update a schema | | deleteSchema(model) | DELETE /api/schema/:model | Delete schema and drop table |

Record Operations

| Method | HTTP | Description | |--------|------|-------------| | queryRecords(model, params?) | GET /api/record/:model | Query records by field criteria (returns [] if no match) | | createRecord(model, data) | POST /api/record/:model | Create a new record | | updateRecord(model, criteria, data) | PUT /api/record/:model | Update a record (criteria must match exactly one row) | | deleteRecord(model, criteria) | DELETE /api/record/:model | Delete records matching criteria |

Search & Count

| Method | HTTP | Description | |--------|------|-------------| | searchRecords(model, options?) | POST /api/search/:model | Search with filter and pagination | | countRecords(model, params?) | GET /api/record/:model/count | Count records matching criteria |

searchRecords(model, options?) — Search records with optional filtering and pagination.

| Option | Type | Description | |--------|------|-------------| | filter | object | Field criteria for WHERE clause (AND-combined). Supports MongoDB-style operators (see below). | | page | number | Page number (1-based) | | limit | number | Maximum records per page |

Filter Operators

Plain values are treated as exact match ($eq). For more complex queries, use an object with one or more operators:

| Operator | SQL Equivalent | Description | |----------|---------------|-------------| | $eq | = | Equal to | | $ne | != | Not equal to | | $gt | > | Greater than | | $gte | >= | Greater than or equal | | $lt | < | Less than | | $lte | <= | Less than or equal | | $between | BETWEEN ... AND | Inclusive range (takes [low, high] array) | | $like | LIKE | SQL pattern matching (use % and _ wildcards) |

Multiple operators on the same field are combined with AND.

// Exact match (plain value)
await client.searchRecords('products', { filter: { status: 'active' } });

// Range query
await client.searchRecords('products', {
  filter: { price: { $gte: 10, $lte: 100 } }
});

// Multiple operators on same field
await client.searchRecords('products', {
  filter: { price: { $gt: 0, $lt: 50 } }
});

// BETWEEN shorthand
await client.searchRecords('products', {
  filter: { price: { $between: [10, 100] } }
});

// LIKE pattern
await client.searchRecords('users', {
  filter: { name: { $like: 'Alice%' } }
});

// Combined filters (AND)
await client.searchRecords('products', {
  filter: { status: 'active', price: { $gte: 10 }, rating: { $gt: 4 } },
  page: 1,
  limit: 20
});

Returns an object with pagination metadata:

{
  records: [...],      // Array of matching records for the requested page
  totalCount: 42,      // Total matching records (ignoring pagination)
  page: 1,             // Current page number
  limit: 10            // Records per page
}

countRecords(model, params?) — Count records matching field criteria.

Returns { count: number }.

const { count } = await client.countRecords('users', { active: 1 });

Error Handling

All errors extend SqlCrudError for easy instanceof checking:

import { SqlCrudError, SqlCrudAuthError, SqlCrudNotFoundError } from 'sqlcrud-client';

try {
  await client.getSchema('nonexistent');
} catch (err) {
  if (err instanceof SqlCrudAuthError) {
    console.error('Authentication failed');
  } else if (err instanceof SqlCrudNotFoundError) {
    console.error('Model not found');
  } else if (err instanceof SqlCrudError) {
    console.error('Error:', err.message, 'Status:', err.statusCode);
  }
}

| Error Class | When | |-------------|------| | SqlCrudError | Base class for all library errors | | SqlCrudAuthError | 401 Unauthorized | | SqlCrudNotFoundError | 404 Not Found | | SqlCrudValidationError | Client-side validation failure | | SqlCrudServerError | 5xx server error |

All errors expose statusCode (number) and endpoint (string).

Utility Functions

Import standalone (tree-shakeable):

import { validateSchema, coerceBooleans, FIELD_TYPES } from 'sqlcrud-client/utils';

Validation

• validateIdentifier(name, label?) — Validates model/field names against ^[a-zA-Z_][a-zA-Z0-9_]*$
• validateFieldName(name) — Validates field names (identifier + reserved name check)
• validateSchema(schema) — Validates a complete schema definition object

Constants

• FIELD_TYPES — Valid schema field types: 'string', 'integer', 'float', 'boolean', 'json', 'datetime', 'time'
• RESERVED_FIELD_NAMES — Reserved field names: 'model', 'schema'
• IDENTIFIER_PATTERN — Regex: /^[a-zA-Z_][a-zA-Z0-9_]*$/

Helpers

• coerceBooleans(record) — Converts true/false to 1/0 in a record (SQLite compatibility; applied automatically by createRecord and updateRecord)
• buildQueryString(params) — Builds a URL query string from an object

Features

• Boolean coercion — createRecord and updateRecord automatically convert JavaScript booleans to 1/0 for SQLite compatibility. On read, the server coerces 0/1 back to true/false, so you always work with native booleans.
• JSON field support — The sqlcrud server automatically serializes objects and arrays to JSON strings on write and deserializes them on read. Send native JavaScript objects and arrays directly — no manual JSON.stringify/JSON.parse needed.
• Composite primary keys — Schemas may define multiple fields with primary: true; the server creates composite primary key constraints automatically.
• Pagination — searchRecords supports paginated queries with page and limit options, returning totalCount for building paginated UIs.
• Empty query results — queryRecords returns an empty array ([]) when no records match, rather than throwing a SqlCrudNotFoundError.
• Query parameter building — Record query methods build and encode URL query parameters automatically.
• Basic Auth — Credentials from the constructor are sent as Authorization: Basic on every request.
• No dependencies — Uses native fetch (Node.js 18+, all modern browsers).

TypeScript

Type definitions are included. Import normally — your TypeScript compiler will pick up types.d.ts automatically via the exports field in package.json.

import { SqlCrudClient, SchemaDefinition, SqlCrudError } from 'sqlcrud-client';

Requirements

• Node.js >= 18.0.0 or any modern browser with fetch support