JDB - Just Database: A Type-Safe Database Access Framework

JDB is a small but powerful TypeScript database abstraction layer that allows you to define database schemas and execute type-safe queries using a functional-style API. It provides adapters for SQLite, MySQL, and PostgreSQL, with a consistent interface across all supported databases.

Core Design Philosophy

JDB follows these key principles:

1. Type Safety: Strong TypeScript typing ensures you get compile-time errors for query mistakes
2. Functional Style: Method chaining for query building
3. Schema-First Approach: Define your table structure upfront for type inference
4. Database Agnostic: Works with multiple database backends

Getting Started

Installation

npm install @jsway/jdb

You'll also need to install the appropriate database driver:

• For SQLite: npm install better-sqlite3
• For MySQL: npm install mysql2
• For PostgreSQL: npm install pg

Basic Setup

import { source, adapter, int, varchar } from '@jsway/jdb';

// Create a database connection with schema definitions
const db = source(adapter('sqlite://:memory:'), {
  users: {
    id: int().sequence.pk,
    name: varchar(100),
    email: varchar(100).unique,
    age: int().optional,
  },
  posts: {
    id: int().sequence.pk,
    userId: int().references('users'),
    title: varchar(200),
    content: text(),
  },
});

// Access typed dataset
const { users, posts } = db.datasets;

Schema Definition

The schema definition is the foundation of JDB's type safety:

// Column types
int()            // Integer column
number()         // Floating point number
varchar(size)    // Variable character string with max length
text()           // Text column (long string)
bool()           // Boolean column
date()           // Date column
time()           // Time column
datetime()       // Date and time column

// Column modifiers
.pk              // Primary key
.sequence        // Auto-incrementing column
.optional        // Allow NULL values (not null by default)
.indexed         // Add index on column
.unique          // Add unique constraint
.default(value)  // Set default value
.references(table, column?, actions?) // Foreign key reference
.generated(expr, 'STORED'|'VIRTUAL')  // Generated column

Foreign Key References

JDB provides powerful foreign key reference capabilities through the .references() method:

// Basic foreign key reference
userId: int().references('users');

// Reference with explicit target column
categoryId: int().references('categories', 'id');

// Reference with actions for ON DELETE and ON UPDATE
postId: int().references('posts', 'id', {
  onDelete: 'CASCADE',
  onUpdate: 'RESTRICT',
});

The available reference actions are:

• 'CASCADE' - Automatically delete/update related rows
• 'RESTRICT' - Prevent deletion/updates if references exist
• 'SET NULL' - Set the referencing column to NULL
• 'SET DEFAULT' - Set the referencing column to its default value

Indexes and Unique Constraints

JDB offers multiple ways to add indexes and unique constraints to your schema:

Table-Level Indexes

You can define indexes at the table level using the ...index and ...unique spread operators:

// Create a database with table-level indexes
const db = source(adapter('sqlite://:memory:'), {
  users: {
    id: int().sequence.pk,
    name: varchar(100),
    email: varchar(100),
    created_at: datetime(),
    // Table-level indexes
    ...index('email'),
    ...index(['name', 'created_at']), // Composite index
    ...unique('email'), // Unique constraint
    ...unique(['name', 'created_at']), // Composite unique constraint
  },
});

Column-Level Indexes

As shown earlier, you can also add indexes directly at the column level:

const db = source(adapter('sqlite://:memory:'), {
  users: {
    id: int().sequence.pk,
    email: varchar(100).indexed, // Single column index
    username: varchar(50).unique, // Unique constraint on column
  },
});

Index Options

When using the table-level approach, you can specify single columns or multiple columns:

// Single column index
...index('created_at')

// Composite index
...index(['first_name', 'last_name'])

// Single column unique constraint
...unique('username')

// Composite unique constraint
...unique(['city', 'state', 'postal_code'])

The table-level approach is particularly useful for composite indexes that span multiple columns.

Dataset Methods

The dataset is the primary interface for interacting with tables in JDB. Here's a comprehensive breakdown of available methods:

Retrieval Methods

// Fetch all records
await users.all();

// Get a single record (returns first match or null)
await users.get();

// Get all values from a specific column
await users.values('name');

// Get a single value from a column
await users.value('name');

// Count records
await users.count();

// Check if records exist
await users.exists();

Filtering Methods

// Simple equality condition
await users({ name: 'John' }).all();
// OR using where method
await users.where({ name: 'John' }).all();

// Multiple conditions (AND)
await users({ name: 'John', age: 30 }).all();

// Comparison operators directly in objects
await users({ 'age >': 18 }).all();
await users({ 'age >=': 21 }).all();
await users({ 'age <': 65 }).all();
await users({ 'age <=': 30 }).all();
await users({ 'age <>': 25 }).all(); // Not equal

// Array of values (IN operator)
await users({ age: [18, 19, 20] }).all();
// (NOT IN operator)
await users({ 'age <>': [18, 21] }).all(); // age NOT IN [18, 21]

// Like operator (using % for wildcard)
await users({ name: '%John%' }).all();

// Raw SQL conditions using safe strings when needed
import { safe } from '@jsway/jdb';
await users.where(safe('name'), '=', 'John').all();
await users.where(safe('age > 18')).all();

// OR conditions
await users.where([{ name: 'John' }, { name: 'Jane' }]).all();

// Complex conditions
await users
  .where({ 'age >': 18 })
  .where([{ name: 'John' }, { name: 'Jane' }])
  .all();

Sorting Methods

// Ascending order
await users.ascending('name').all();
// OR
await users.order('name', 'ASC').all();

// Descending order
await users.descending('name').all();
// OR
await users.order('name', 'DESC').all();

// Multiple sort columns
await users.ascending('age').descending('name').all();

Pagination Methods

// Limit results
await users.limit(10).all();

// Limit with offset
await users.limit(10, 20).all(); // Skip 20, take 10

Grouping

// Group by one or more columns
await users.group('age').all();

Joining Methods

// Left join
await posts
  .leftJoin(users, 'author', {
    'author.id': 'userId',
  })
  .all();

// Inner join
await posts
  .innerJoin(users, 'author', {
    'author.id': 'userId',
  })
  .all();

// Join with options
await posts
  .join(users, {
    as: 'author',
    type: 'LEFT',
    on: { 'author.id': 'userId' },
  })
  .all();

Column Selection

JDB provides flexible ways to control which columns are included in query results:

// Default: select all columns from the root table
await users.all(); // Selects all columns from 'users' table

// Select specific columns with renaming
await users.select({ name: 'name', userAge: 'age' }).all();

// Select a single column as string
await users.select('name').all();

// Multiple columns as array
await users.select(['name', 'email']).all();

// Raw SQL expressions using safe()
await users.select({ fullName: safe('first_name || " " || last_name') }).all();

// Column aliases - adds columns to the default selection
await users.alias({ fullName: safe('name || " " || email') }).all();
// This returns all columns from users PLUS the fullName computed column

// Selecting columns from joined tables
await posts
  .leftJoin(users, 'author', { 'author.id': 'userId' })
  .select({
    title: 'title', // from posts table (main dataset)
    content: 'content', // from posts table (main dataset)
    authorName: 'author.name', // from joined users table using the alias
    authorEmail: 'author.email', // from joined users table using the alias
  })
  .all();

// Combining columns from both tables with alias()
await posts
  .leftJoin(users, 'author', { 'author.id': 'userId' })
  .alias({
    authorName: 'author.name', // adds author.name as authorName
    postCount: safe('COUNT(author.id)'), // adds a computed column
  })
  .all(); // returns all columns from posts + the aliased columns

Key differences between select() and alias():

• select(): Replaces the default column selection with only your specified columns
• alias(): Adds your specified columns to the default selection without removing existing ones

This difference becomes particularly important when working with joins where you want to add computed columns but keep all the base columns.

Subqueries and Complex Queries

// Using from to create subquery
const activeUsers = users.where({ active: true });
await posts.from(activeUsers, 'author').all();

// Get SQL statement
const [sql, bindings] = posts.toStatement();

Data Modification Methods

// Insert a new record
await users.insert({ name: 'John', email: '[email protected]' });

// Update records
await users.where({ id: 1 }).set({ name: 'Updated Name' });

// Put (insert or update if exists)
await users.put({ id: 1, name: 'John', email: '[email protected]' });

// Delete records
await users.where({ id: 1 }).delete();

Transaction Support

All query termination methods accept a transaction object as their first argument.

These termination methods include:

• read operations: get, all, value, values, count
• write operations: set, put, insert, delete, update

await db.transaction(async (tx) => {
  const userId = await users.insert(tx, { name: 'John' });
  await posts.insert(tx, { userId, title: 'First Post' });
});

Advanced Features

Custom Dataset Creation

// Create dataset for table not in schema
const logs = db.dataset('logs', {
  id: int().sequence.pk,
  message: text(),
  timestamp: datetime().default('CURRENT_TIMESTAMP'),
});

// Create untyped dataset (any columns)
const analytics = db.freeDataset('analytics');

Migrations

JDB provides built-in migration support:

import { migrate } from '@jsway/jdb';

// Migrations as object
await migrate(db, {
  addUserFields: async (tx) => {
    await tx.addColumn('users', {
      verified: bool().default(false),
    });
  },
  createLogsTable: async (tx) => {
    await tx.createTable('logs', {
      id: int().sequence.pk,
      message: text(),
      timestamp: datetime().default('CURRENT_TIMESTAMP'),
    });
  },
});

// Or from directory
await migrate(db, './migrations');

Note: If the migrations table doesn't exist, JDB will automatically bootstrap the database by creating all tables based on your defined schema instead of running any migrations. This makes initial setup seamless without requiring separate initialization code.

Schema Manipulation (DDL)

await db.transaction(async (tx) => {
  // Create table
  await tx.createTable('logs', {
    id: int().sequence.pk,
    message: text(),
  });

  // Check if table exists
  const tableExists = await tx.tableExists('logs');
  console.log('Logs table exists:', tableExists); // true

  // Add column
  await tx.addColumn('logs', {
    timestamp: datetime().default('CURRENT_TIMESTAMP'),
  });

  // Rename column
  await tx.renameColumn('logs', 'message', 'content');

  // Drop column
  await tx.dropColumn('logs', 'timestamp');

  // Create index
  await tx.createIndex('logs', index('content'));

  // Drop index
  await tx.dropIndex('logs', 'content');

  // Rename table
  await tx.renameTable('logs', 'audit_logs');

  // Drop table
  await tx.dropTable('logs');
});

Key Design Strengths

1. Progressive Type Narrowing: JDB's type system progressively narrows types as you chain methods
2. Dialect Abstraction: Database-specific SQL is generated through dialects
3. Safety First: Use of prepared statements to prevent SQL injection
4. Transaction Support: First-class transaction handling
5. Connection Management: Automatic connection pooling and cleanup

Tips for Effective Use

1. Define comprehensive schemas upfront for maximum type safety
2. Use transactions for operations that need atomicity
3. Leverage the type system by using TypeScript strict mode
4. Use raw SQL with the safe() function when needed, but sparingly

JDB strikes an excellent balance between type safety, flexibility, and performance. It's particularly suited for applications where compile-time checking can prevent runtime errors in database interactions.