@rym-lib/rdb-command

A lightweight database abstraction layer providing unified CRUD operations for relational databases.

Installation

``bash
npm install @rym-lib/rdb-command coral-sql
`

For Prisma connector:
`bash
npm install @prisma/client
`

Quick Start

`typescript
import { DataBase } from '@rym-lib/rdb-command'
import { PrismaConnector } from '@rym-lib/rdb-command/connector/prisma'
import { PrismaClient } from '@prisma/client'

// Setup
const prisma = new PrismaClient()
const connector = new PrismaConnector(prisma)
const logger = {
debug: console.log,
info: console.info,
warning: console.warn,
error: console.error,
critical: console.error,
}

const db = new DataBase(connector, logger)

// Basic CRUD operations
type User = {
id: string
name: string
email: string
}

// Create a record
await db.create('users', {
id: 'user-1',
name: 'John Doe',
email: 'john@example.com'
})

// Find a record
const user = await db.find('users', { id: 'user-1' })
console.log(user) // { id: 'user-1', name: 'John Doe', email: 'john@example.com' }

// Update a record
await db.update('users', { id: 'user-1' }, { name: 'Jane Doe' })

// Delete a record
await db.delete('users', { id: 'user-1' })
`

Advanced Usage

$3

Find a record or create it if it doesn't exist:

`typescript
const user = await db.findOrCreate(
'users',
{ email: 'john@example.com' }, // search condition
{
id: 'user-1',
name: 'John Doe',
email: 'john@example.com'
} // data to create if not found
)
`

$3

Update a record or create it if it doesn't exist:

`typescript
await db.updateOrCreate(
'users',
{ id: 'user-1' }, // search condition
{ name: 'Updated Name' }, // update data
{
id: 'user-1',
name: 'John Doe',
email: 'john@example.com'
} // create data if not found
)
`

$3

Execute multiple operations in a transaction:

`typescript
await db.txn(async (txDb) => {
await txDb.create('orders', {
id: 'order-1',
userId: 'user-1',
total: 100
})

await txDb.update('users', { id: 'user-1' }, {
lastOrderDate: new Date()
})
})
`

$3

Add custom middleware to preprocess SQL queries:

`typescript
const loggingMiddleware = {
preprocess: async (payload, options, context) => {
context.logger.info(Executing: ${payload.sql})
return payload
}
}

db.use(loggingMiddleware)
`

$3

Synchronize a list of records with the database:

`typescript
// Sync with where condition and key-based comparison
const result = await db.sync('users', { department: 'sales' }, [
{ email: 'john@example.com', name: 'John Doe' },
{ email: 'jane@example.com', name: 'Jane Smith' }
], {
key: 'email', // Compare by email field only
pk: {
column: 'id',
generator: () => crypto.randomUUID()
},
noDeleteUnmatched: false // Delete records not in input list (default)
})

console.log(Created: ${result.created.length})
console.log(Unchanged: ${result.unchanged.length})
console.log(Deleted: ${result.deleted.length})

// Sync with full property comparison (no key specified)
const result2 = await db.sync('user_settings', {}, [
{ userId: 'user-1', theme: 'dark', language: 'en' },
{ userId: 'user-2', theme: 'light', language: 'ja' }
])

// Sync without deleting unmatched records
const result3 = await db.sync('user_roles', { userId: 'user-1' }, [
{ userId: 'user-1', roleId: 'admin' }
], {
key: ['userId', 'roleId'],
noDeleteUnmatched: true
})
`

API Reference

$3

The main class providing database operations.

#### Constructor

`typescript
new DataBase(connector: DataBaseConnectorPort, logger: DataBaseLogger, options?: SQLBuilderToSQLInputOptions)
`

- connector: Database connector implementation
- logger: Logger instance for debugging
- options: SQL builder options (e.g., { quote: null } to disable column quoting)

#### Methods

##### find(table: string, where: WhereType, options?: DataBaseCommandOptionsPartial): Promise

Find a single record matching the where condition.

##### create(table: string, data: Record, options?: DataBaseCommandOptionsPartial): Promise

Create a new record.

##### update(table: string, where: WhereType, data: Record, options?: DataBaseCommandOptionsPartial): Promise

Update records matching the where condition.

##### delete(table: string, where: WhereType, options?: DataBaseCommandOptionsPartial): Promise

Delete records matching the where condition.

##### findOrCreate(table: string, where: WhereType, data: Record, options?: DataBaseCommandOptionsPartial): Promise

Find a record or create it if not found.

##### updateOrCreate(table: string, where: WhereType, update: Record, create: Record, options?: DataBaseCommandOptionsPartial): Promise

Update a record or create it if not found.

##### sync(table: string, where: WhereType, records: Array>, options?: SyncOptions): Promise>

Synchronize a list of records with database records matching the where condition.

Parameters:
- table: Target table name
- where: Condition to filter existing records (use {} for all records)
- records: Array of records to sync
- options: Sync options

Options:
- key?: string | string[]: Fields to use for record comparison (default: compare all properties)
- pk?: { column: string, generator?: () => string | number }: Primary key configuration for new records
- noDeleteUnmatched?: boolean: Don't delete unmatched records (default: false)

Returns: SyncResult with arrays of created, unchanged, and deleted records.

##### txn(fn: (db: DataBasePort) => Promise): Promise

Execute operations within a transaction.

##### use(middleware: DataBaseMiddleware): this

Add middleware to the database instance.

$3

#### PrismaConnector

Connector for Prisma ORM integration.

`typescript
import { PrismaConnector } from '@rym-lib/rdb-command/connector/prisma'
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
const connector = new PrismaConnector(prisma)
`

$3

Implement the DataBaseConnectorPort interface to create custom connectors:

`typescript
interface DataBaseConnectorPort {
execute(sql: string, replacements: unknown[]): Promise
query(sql: string, replacements: unknown[]): Promise
transaction(exec: TransactionCallback): Promise
}
`

Configuration

$3

You can configure SQL generation behavior:

`typescript
const db = new DataBase(connector, logger, {
quote: null, // Disable column quoting
placeholder: '?' // Use ? instead of $1, $2, etc.
})
``

Dependencies

- coral-sql: SQL query builder library
- @prisma/client: Required when using PrismaConnector (peer dependency)

License

MIT