DB TypeScript SDK

A TypeScript SDK for database operations, similar to a Python ORM.

Installation

yarn add reflect-metadata

Features

• 🎯 Decorator Support - Define models using TypeScript decorators
• 🔑 Automatic Primary Keys - id field is automatically created if not defined
• 🔗 Foreign Key Relations - Supports defining and handling foreign key relations
• 📝 Full CRUD - Create, read, update, and delete operations
• 🔍 Advanced Queries - Filtering, sorting, aggregation, joins, etc.
• 🚀 Batch Operations - Bulk create and update support
• 🎨 Type Safety - Full TypeScript types, type-safe models

Quick Start

1. Define Models

import { BaseModel, Column } from 'db-ts-sdk';

// Audited base model
export class Audited extends BaseModel {
  @Column({ type: 'date', name: 'created_at' })
  createdAt!: Date;

  @Column({ type: 'date', name: 'updated_at' })
  updatedAt!: Date;
}

// User model
export class User extends Audited {
  static tableName = 'users';

  @Column({ primary: true })
  id!: string;

  @Column()
  email!: string;

  @Column({ name: 'full_name' })
  fullName!: string;

  @Column({ type: 'json', nullable: true })
  profile?: any;

  @Column({ type: 'boolean', name: 'is_active' })
  isActive!: boolean;
}

2. Table Operations

// Create table
await User.createTable();

// Drop table
await User.dropTable();

// Rename table
await User.renameTable('new_users');

// Truncate table
await User.truncateTable();

3. Data Operations

Create Data

// Single insert
const user = await User.create({
  id: '1',
  email: '[email protected]',
  fullName: 'John Doe',
  isActive: true,
  createdAt: new Date(),
  updatedAt: new Date(),
});

// Bulk insert
await User.bulkCreate(
  [
    {
      id: '2',
      email: '[email protected]',
      fullName: 'User 2',
      isActive: true,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
    {
      id: '3',
      email: '[email protected]',
      fullName: 'User 3',
      isActive: false,
      createdAt: new Date(),
      updatedAt: new Date(),
    },
  ],
  32, // Batch size
);

Query Data

import { AND, EQ, OR, GTE } from 'db-ts-sdk';

// Get a single record
const user = await User.get(OR(AND(EQ('email', '[email protected]'))));

// Filter query
const activeUsers = await User.filter({
  filter: OR(AND(EQ('is_active', true))),
  limit: 10,
  offset: 0,
  sort: { column: 'created_at', order: 'DESC' },
});

// Count records
const count = await User.count({
  filter: OR(AND(EQ('is_active', true))),
});

Update Data

// Instance update
const user = await User.get(OR(AND(EQ('id', '1'))));
user.fullName = 'Jane Doe';
await user.save();

// Bulk update
await User.update(OR(AND(EQ('is_active', false))), { isActive: true });

Delete Data

// Instance delete
const user = await User.get(OR(AND(EQ('id', '1'))));
await user.remove();

// Bulk delete
await User.delete(OR(AND(EQ('is_active', false))));

Field Types

The SDK supports various field types:

import {
  StringField,
  TextField,
  IntegerField,
  BooleanField,
  DateTimeField,
  TimestampField,
  DateField,
  JsonField,
  UuidField,
  VectorField,
  ArrayField,
  EnumField,
  MoneyField,
  DecimalField,
  DoubleField,
  ForeignKeyField,
} from 'db-ts-sdk';

Using Decorators

@Column({
  type: 'string',           // Field type
  name: 'column_name',      // Database column name (optional)
  description: 'desc',      // Description (optional)
  primary: false,           // Is primary key (optional)
  nullable: false,          // Nullable (optional)
  auto_increment: false,    // Auto increment (optional)
  default: null             // Default value (optional)
})
fieldName!: string;

Foreign Key Fields

import { ForeignKey } from 'db-ts-sdk';

@ForeignKey('users', 'id', {
  type: 'integer',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE'
})
userId!: number;

Filter Builder

import { AND, OR, EQ, NE, GT, GTE, LT, LTE, IN, LIKE } from 'db-ts-sdk';

// Equals
EQ('field', value);

// Not equals
NE('field', value);

// Greater than
GT('field', value);

// Greater than or equal
GTE('field', value);

// Less than
LT('field', value);

// Less than or equal
LTE('field', value);

// In array
IN('field', [value1, value2]);

// Pattern matching (LIKE)
LIKE('field', '%pattern%');

// Combine conditions
OR(AND(EQ('status', 'active'), GTE('age', 18)), AND(EQ('status', 'pending')));

Advanced Queries

Aggregate Query

import { AggregateFunction } from 'db-ts-sdk';

const results = await User.filter({
  filter: OR(AND(EQ('is_active', true))),
  aggregate: [
    {
      column: 'id',
      function: AggregateFunction.COUNT,
      alias: 'total',
    },
  ],
  groupBy: { columns: ['status'] },
});

Join Query

const results = await User.filter({
  filter: OR(AND(EQ('is_active', true))),
  join: {
    type: 'LEFT',
    table: 'orders',
    on: { 'users.id': 'orders.user_id' },
  },
});

Configuration

In your tsconfig.json, make sure to enable decorator support:

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  }
}

And at the entrypoint of your app, import reflect-metadata:

import 'reflect-metadata';

API Reference

BaseModel Static Methods

• createTable() - Create table
• dropTable() - Drop table
• renameTable(newName) - Rename table
• truncateTable() - Truncate table
• create(data) - Create a record
• bulkCreate(datas, batchSize) - Create records in bulk
• get(filter, select?) - Get a single record
• filter(options) - Filter query
• update(filter, data) - Bulk update
• delete(filter) - Bulk delete
• count(options) - Count records

BaseModel Instance Methods

• save() - Save instance (create or update)
• remove(filter?) - Remove instance
• toString() - Convert to string
• toJSON() - Convert to JSON object

License

MIT