@argos-js/data

High-performance database layer, Universal Schema Normalizer, Active Record ORM, and Transactional SQL Migration Engine for Argos-JS.

• 🔌 Multi-driver & dialect support: SQLite, PostgreSQL, MySQL, MariaDB, MS SQL Server (T-SQL), Oracle (PL/SQL), Redis, and Edge/KV
• 🌐 Connection Manager & Registry: Seamless multi-connection pool management (DatabaseManager), named connection selection, and model-level routing
• 🔄 Database Introspection & Streaming Sync (DatabaseSynchronizer): Cross-dialect zero-downtime database migration engine (SQLite ➔ Postgres/MySQL/MaveDB) with foreign key isolation, chunked streaming, and sequence alignment
• 📦 Canonical Database Dump & Restore (DatabaseDumper / DatabaseRestorer): Collation-safe agnostic dump bundle (.argos) with streaming .ndjson chunks, zero vendor lock-in, and automatic identity/sequence recalibration
• 🗑️ Native Declarative Soft Deletes: Automatic query scoping (deleted_at IS NULL), query modifiers (.withTrashed(), .onlyTrashed()), and lifecycle helpers (.restore(), .forceDelete())
• 🧠 Advanced Data Types, Vectors (AI) & Generated Columns: Native jsonb, vector(dim) (pgvector), inet, macAddress, money, enum, .as(...) generated columns, and specialized indexes (GIN, HNSW, Full-Text)
• 📐 Universal Schema Normalizer (defineTable) compiling to native Drizzle schemas across all drivers
• 🧠 Active Record Models (Model) with CRUD, queries, and pagination
• 🛡️ Safe-sync table synchronization (options.sync / db.syncTables)
• ⚡ Transactional SQL Migration Engine (Migrator) with automatic batching, audit history, and rollback capabilities
• 🌱 Database Seeder Engine (DatabaseSeeder): Isolated per-file transactional execution, chronological ordering, and duration profiling

Transactional SQL Migration Engine

@argos-js/data provides a robust, driver-agnostic migration engine designed for production safety.

Audit Table (_argos_migrations)

The engine automatically ensures the existence of the internal _argos_migrations tracking table:

| Column | Type | Description | |--------|------|-------------| | id | INTEGER / SERIAL | Auto-incrementing primary key | | name | VARCHAR(255) / TEXT | Unique migration file name | | batch | INTEGER | Execution batch group number | | executed_at | TIMESTAMP | Execution timestamp (defaults to current timestamp) |

Migration Lifecycle & API

import { createDatabase, Migrator } from '@argos-js/data';

const db = createDatabase({
  driver: 'sqlite',
  database: './storage/database.sqlite',
});
await db.connect();

const migrator = new Migrator(db, {
  migrationsDir: './app/database/migrations',
});

1. Run Pending Migrations (runPendingMigrations)

Executes all pending migrations in chronological order. Each migration runs inside an isolated database transaction. If any migration fails, its transaction rolls back immediately and stops execution.

const result = await migrator.runPendingMigrations();
// {
//   migrated: ['20261002084000_create_products_table'],
//   batch: 1,
//   steps: [{ name: '20261002084000_create_products_table', durationMs: 12 }]
// }

2. Rollback Last Batch (rollbackLastBatch)

Rolls back the most recent migration batch. Migrations in that batch are executed in reverse chronological order (newest to oldest), running their down(db) functions within a transaction, and removing their records from _argos_migrations.

const result = await migrator.rollbackLastBatch();
// {
//   rolledBack: ['20261002084000_create_products_table'],
//   batch: 1,
//   steps: [{ name: '20261002084000_create_products_table', durationMs: 8 }]
// }

3. Migration Status (getMigrationStatus)

Inspects both the filesystem and the database to produce an exact status report of all migrations:

const status = await migrator.getMigrationStatus();
// [
//   {
//     name: '20261002084000_create_products_table',
//     status: 'applied',
//     batch: 1,
//     executed_at: '2026-10-02 11:45:00',
//     fullPath: '/path/to/app/database/migrations/20261002084000_create_products_table.ts'
//   },
//   {
//     name: '20261002090000_add_tags_to_products',
//     status: 'pending',
//     batch: null,
//     executed_at: null,
//     fullPath: '/path/to/app/database/migrations/20261002090000_add_tags_to_products.ts'
//   }
// ]

Database Migrations Guide

Argos-JS provides a robust, dialect-agnostic database migration engine powered by a fluent Schema Builder. It removes manual DDL incompatibilities across SQLite, PostgreSQL, and MySQL, while supporting multi-tenant SaaS patterns out of the box.

1. Migration CLI Lifecycle

Manage migrations through the unified Argos CLI:

# Generate a new migration with the fluent Schema Builder template
argos create migration create_organizations_table
# (or shorthand)
argos c migration create_organizations_table

# Apply all pending migrations in transactional order
argos db migrate

# Revert the latest batch of applied migrations
argos db rollback

# Inspect status of all migrations (applied / pending with execution times)
argos db status

2. Fluent Schema Builder (SchemaBuilder & TableBlueprint)

The fluent schema builder allows you to write migrations once and deploy them safely across any supported database engine.

Migration File Structure

import type { MigrationContext } from '@argos-js/data';

/**
 * Migration: create_invoices_table
 */
export async function up({ schema, db }: MigrationContext): Promise<void> {
  await schema.createTable('invoices', (table) => {
    // Agnostic primary key
    table.id();

    // Multi-tenant organization reference (UUID / UUIDv7)
    table.uuidV7('organization_id').notNull().index('idx_invoices_org');

    // Human-friendly sequential invoice number scoped per organization
    table.integer('sequence_number').notNull();
    table.string('invoice_code', 50).notNull();

    // Financial decimal with precision & scale
    table.decimal('amount', 12, 2).notNull();
    table.string('currency', 3).default('BRL').notNull();

    // Status and boolean flags
    table.string('status', 30).default('draft').notNull();
    table.boolean('is_paid').default(false).notNull();

    // Description text (nullable)
    table.text('notes').nullable();

    // Dialect-specific timestamps (created_at, updated_at)
    table.timestamps();

    // Multi-column unique constraint
    table.unique(['organization_id', 'sequence_number'], 'uniq_org_invoice_seq');
  });
}

export async function down({ schema }: MigrationContext): Promise<void> {
  await schema.dropTableIfExists('invoices');
}

Agnostic Dialect Compilation

The Schema Builder compiles appropriate types for each database engine automatically:

| Blueprint Method | SQLite | PostgreSQL | MySQL | |---|---|---|---| | table.id() | INTEGER PRIMARY KEY AUTOINCREMENT | BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY | BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY | | table.uuid(col) / uuidV7(col) | TEXT | UUID | VARCHAR(36) | | table.string(col, len) | VARCHAR(len) | VARCHAR(len) | VARCHAR(len) | | table.text(col) | TEXT | TEXT | TEXT | | table.integer(col) | INTEGER | INTEGER | INT | | table.decimal(col, p, s) | DECIMAL(p, s) | DECIMAL(p, s) | DECIMAL(p, s) | | table.boolean(col) | BOOLEAN | BOOLEAN | BOOLEAN | | table.sequence(col, opts) | INTEGER ou VARCHAR(64) | INTEGER ou VARCHAR(64) | INT ou VARCHAR(64) | | table.timestamps() | created_at TIMESTAMP..., updated_at TIMESTAMP... | created_at TIMESTAMPTZ..., updated_at TIMESTAMPTZ... | created_at TIMESTAMP..., updated_at TIMESTAMP... ON UPDATE CURRENT_TIMESTAMP |

Chainable Column Modifiers

• .notNull(): Marks column as NOT NULL.
• .nullable(): Allows NULL values.
• .unique(): Adds inline UNIQUE constraint.
• .default(value): Sets default value (TRUE/FALSE, strings, numbers, or CURRENT_TIMESTAMP).
• .index(indexName?): Generates an index statement for the column.
• .primaryKey(): Designates the column as part of the primary key.

Altering Tables (alterTable)

export async function up({ schema }: MigrationContext): Promise<void> {
  await schema.alterTable('invoices', (table) => {
    table.string('billing_email', 255);
    table.dropColumn('legacy_flag');
  });
}

3. Declarative sequence for Multi-Tenant / SaaS Architectures

Exposing global auto-incrementing IDs in public URLs creates severe security vulnerabilities (enumeration attacks, leaking competitor transaction volumes).

Argos-JS solves this natively through the sequence column type:

1. Internal Primary Key: t.uuidV7('id').primaryKey() for secure, unguessable, ordered UUIDs.
2. Declarative Sequence: t.sequence({ by: 'organization_id', prefix: 'INV-', pad: 4 }) for human-friendly numbers (INV-0001, INV-0002) scoped per tenant.

Declarative Schema Definition

import { defineTable } from '@argos-js/data';

export const InvoicesTable = defineTable('invoices', (t) => ({
  id: t.uuidV7('id').primaryKey(),
  orgId: t.uuid('organization_id').notNull(),

  // Fully automated: Argos generates and formats within the insert transaction
  invoiceCode: t.sequence({ by: 'organization_id', prefix: 'INV-', pad: 4 }),
  
  // Or numeric-only sequence (1, 2, 3...):
  // sequenceNumber: t.sequence({ by: 'organization_id' }),

  amount: t.decimal('amount', 10, 2).notNull(),
  createdAt: t.timestamp('created_at'),
}));

Zero-Boilerplate Controller Usage

In your controllers, you do not need to call any sequence generator manually:

app.post('/api/invoices', async (ctx) => {
  const organizationId = ctx.state.organizationId;
  const data = await ctx.validate({ amount: v.number().positive() });

  // Just call create!
  // Argos automatically:
  // 1. Opens an atomic transaction
  // 2. Computes the next sequence for this organization
  // 3. Formats to "INV-0001"
  // 4. Inserts the record
  // 5. If the insert fails, the transaction rolls back so numbers are NEVER skipped!
  const invoice = await InvoiceModel.create({
    orgId: organizationId,
    amount: data.amount,
  });

  return ctx.json({ invoice }, 201);
});

Advanced / Direct Helper (getNextTenantSequence)

For custom procedures or manual batch tasks, you can also query the sequence directly:

import { getNextTenantSequence, formatSequence } from '@argos-js/data';

const nextNum = await getNextTenantSequence(ctx.db, organizationId, 'invoices');
const code = formatSequence(nextNum, { prefix: 'INV-', pad: 4 }); // "INV-0001"

The internal control table _argos_tenant_sequences utilizes row-level transactional locks (ON CONFLICT ... RETURNING / FOR UPDATE), guaranteeing strictly consecutive numbering without collisions under heavy concurrency.

Multi-Connection Architecture (Connection Manager / Registry)

@argos-js/data includes a built-in Connection Manager and Registry pattern (DatabaseManager), enabling enterprise-grade multi-database architectures:

• Multi-tenant architectures with segregated databases per organization.
• Read/write replica splitting or dedicated reporting/analytics warehouses.
• Legacy system data synchronization alongside modern application stores.

1. Registering Multiple Connections

Configure named connections in your application bootstrap via databasePlugin:

import { ArgosApplication } from '@argos-js/core';
import { databasePlugin } from '@argos-js/data';

const app = new ArgosApplication();

await app.register(databasePlugin, {
  default: 'primary',
  connections: {
    primary: {
      driver: 'postgres',
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    },
    analytics: {
      driver: 'postgres',
      connectionString: process.env.ANALYTICS_DB_URL,
    },
    cache_store: {
      driver: 'sqlite',
      database: './storage/cache.sqlite',
    },
  },
});

2. Transparent db Proxy & Named Connections

The exported db singleton serves as a transparent proxy to the configured default connection while providing on-demand access to named connection pools via db.connection(name):

import { db } from '@argos-js/data';

// 1. Transparent delegation to the default connection ("primary")
const users = await db.query('SELECT * FROM users WHERE active = $1', [true]);
await db.table('orders').insert({ total: 150.0 });

// 2. Querying a specific named connection
const report = await db.connection('analytics').query(
  'SELECT metric_name, SUM(value) as total FROM event_metrics GROUP BY metric_name'
);

// 3. Executing DDL or queries on a local SQLite cache store
await db.connection('cache_store').execute(
  'INSERT INTO cache_entries (key, val) VALUES (?, ?)',
  ['session_123', JSON.stringify({ userId: 1 })]
);

3. Model-Level Connection Binding (static connection)

Active Record models can bind to specific named databases via static connection. All CRUD and query builder operations (all(), find(), where(), create(), save(), delete()) automatically route to the configured database:

import { Model } from '@argos-js/data';

// Binds to default connection ("primary")
export class User extends Model {
  public static override tableName = 'users';

  public id!: number;
  public name!: string;
  public email!: string;
}

// Binds explicitly to the "analytics" connection
export class AuditLog extends Model {
  public static override tableName = 'audit_logs';
  public static override connection = 'analytics';

  public id?: number;
  public action!: string;
  public performedAt!: string;
}

// In your application logic or HTTP controllers:
app.post('/api/users', async (ctx) => {
  const { name, email } = await ctx.validate({
    name: v.string().min(2),
    email: v.string().email(),
  });

  // Saved to "primary" database
  const user = await User.create({ name, email });

  // Saved to "analytics" database
  await AuditLog.create({
    action: `USER_CREATED:${user.id}`,
    performedAt: new Date().toISOString(),
  });

  return ctx.json({ user }, 201);
});

4. Running Migrations on Secondary Connections

The Argos CLI supports running migrations against any named database connection configured in your config/database.ts:

# Run migrations on the default connection
argos db migrate

# Run migrations specifically on the "analytics" connection
argos db migrate --connection=analytics

# Rollback last migration batch on the "analytics" connection
argos db rollback --connection=analytics

# Inspect migration status for the "analytics" connection
argos db status --connection=analytics

5. Graceful Shutdown & Pool Management

All active connection pools are safely closed on application shutdown or manually via:

import { databaseManager } from '@argos-js/data';

// Closes all pooled connections (PostgreSQL, MySQL, SQLite)
await databaseManager.closeAll();

Database Introspection & Cross-Dialect Streaming Sync (argos db sync)

@argos-js/data includes a built-in Introspection and Streaming Synchronization engine (DatabaseIntrospector and DatabaseSynchronizer), enabling seamless cross-dialect database migrations (e.g., migrating a local development SQLite database into cloud PostgreSQL, MySQL, or MaveDB with zero data loss).

How It Works

The synchronization runs in four discrete, safe phases:

1. Phase 1 (Structure): Introspects the source database (DatabaseIntrospector), parses tables, columns, data types, primary keys, nullability, unique keys, and indices. It then creates matching tables in the target database using TableBlueprint, automatically mapping types to the target dialect.
2. Phase 2 (Constraint Isolation): Temporarily disables foreign key constraint checks in the target database (PRAGMA foreign_keys = OFF, SET session_replication_role = 'replica', or SET FOREIGN_KEY_CHECKS = 0) to prevent insertion order deadlocks.
3. Phase 3 (Streaming Data Migration): Streams records in paginated cursor/offset batches (batchSize = 1000 default), normalizes values (converting integer booleans, ISO date strings, JSON structures), and executes transactional bulk inserts.
4. Phase 4 (Post-Sync & Sequence Alignment): Re-enables foreign key checks and realigns auto-increment sequences / serial counters in the target database to match the highest inserted ID. Multi-tenant sequences in _argos_tenant_sequences are preserved with exact parity.

Programmatic API

import { DatabaseSynchronizer, databaseManager } from '@argos-js/data';

const synchronizer = new DatabaseSynchronizer();

const result = await synchronizer.sync({
  from: 'sqlite_local',        // Connection name or DatabaseClient instance
  to: 'postgres_production',    // Connection name or DatabaseClient instance
  batchSize: 2000,
  truncate: true,              // Empties destination tables before sync
  onProgress: (progress) => {
    console.log(`[Phase: ${progress.phase}] Current table: ${progress.currentTable}`);
  },
});

console.log(`Migrated ${result.rowsMigrated} rows across ${result.tablesMigrated} tables in ${result.durationMs}ms`);

CLI Command

# Sync from default connection to production
argos db sync --to production

# Sync from named local connection to cloud postgres with truncate
argos db sync --from sqlite_dev --to cloud_postgres --truncate

# Transparent alias
argos sync --from sqlite_dev --to mysql_prod --batch-size 5000

Database Seeder Engine (DatabaseSeeder)

@argos-js/data provides an isolated, transactional database seeder engine to populate development, test, and initial production environments with dummy or catalog data.

Key Capabilities

• 🔒 Per-file Transaction Isolation: Each seed file executes within its own dedicated database transaction. If a seed throws an unhandled error, its changes roll back atomically without leaving corrupted records.
• 🕒 Ordered Chronological Execution: Seeds in app/database/seeds/ are executed in strict alphabetical/chronological order (e.g. 20261002140000_initial_roles.seed.ts, 20261002140500_default_admin.seed.ts).
• 🎯 Specific Seed Execution: Execute a single seed file or pattern using the specificSeed option.
• ⏱️ Duration Profiling: Automatically tracks and reports execution duration for every individual seed file as well as the total run time.
• 🌐 Multi-Connection Aware: Target any registered connection from DatabaseManager dynamically.

Seed Context & File Structure

Each seed file exports an async run function accepting a SeedContext:

import type { SeedContext } from '@argos-js/data';

export async function run({ db }: SeedContext): Promise<void> {
  // Use transactional db client or Active Record models
  await db.execute("INSERT INTO roles (name) VALUES ('admin'), ('editor')");
}

Programmatic Usage

import { createDatabase, DatabaseSeeder } from '@argos-js/data';

const db = createDatabase({
  driver: 'sqlite',
  database: './storage/database.sqlite',
});
await db.connect();

const seeder = new DatabaseSeeder(db, {
  seedsDir: './app/database/seeds',
});

// Run all pending seeds
const result = await seeder.run();
console.log(`Seeded ${result.seeded.length} files in ${result.totalDurationMs}ms`);

// Run a specific seed file
await seeder.run({
  specificSeed: 'initial_roles',
});

// Run against a specific connection configured in DatabaseManager
await seeder.run({
  connection: 'secondary',
});

Supported SQL Dialects & Agnostic Mapping Matrix

@argos-js/data natively supports multi-engine schemas with zero vendor lock-in. TableBlueprint and DatabaseIntrospector map agnostic types to native SQL types across all supported engines:

| Agnostic Type | SQLite | PostgreSQL | MySQL | MariaDB | MS SQL Server (T-SQL) | Oracle (PL/SQL) | |---|---|---|---|---|---|---| | table.id() | INTEGER PRIMARY KEY AUTOINCREMENT | BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY | BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY | BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY | BIGINT IDENTITY(1,1) PRIMARY KEY | NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY | | table.boolean() | BOOLEAN (0/1) | BOOLEAN | BOOLEAN | BOOLEAN | BIT | NUMBER(1) CHECK ({col} IN (0, 1)) | | table.string(len) | VARCHAR(len) | VARCHAR(len) | VARCHAR(len) | VARCHAR(len) | NVARCHAR(len) | VARCHAR2(len) | | table.text() | TEXT | TEXT | TEXT | TEXT | NVARCHAR(MAX) | CLOB | | table.integer() | INTEGER | INTEGER | INT | INT | INT | NUMBER(10) | | table.bigint() | INTEGER | BIGINT | BIGINT | BIGINT | BIGINT | NUMBER(19) | | table.decimal(p, s) | DECIMAL(p, s) | DECIMAL(p, s) | DECIMAL(p, s) | DECIMAL(p, s) | DECIMAL(p, s) | NUMBER(p, s) | | table.timestamps() | TIMESTAMP DEFAULT CURRENT_TIMESTAMP | TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP | TIMESTAMP DEFAULT CURRENT_TIMESTAMP | TIMESTAMP DEFAULT CURRENT_TIMESTAMP | DATETIME2(6) DEFAULT SYSUTCDATETIME() | TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP | | FK Isolation | PRAGMA foreign_keys = OFF/ON | SET session_replication_role = 'replica'/'origin' | SET FOREIGN_KEY_CHECKS = 0/1 | SET FOREIGN_KEY_CHECKS = 0/1 | ALTER TABLE {t} NOCHECK/CHECK CONSTRAINT ALL | ALTER TABLE {t} DISABLE/ENABLE CONSTRAINT {c} |

Collation Safety (MariaDB): Automatically sanitizes incompatible MySQL 8 collations (utf8mb4_0900_ai_ci) to ensure portable, error-free exports and imports across MariaDB and older MySQL engines.

Canonical Database Dump & Restore (DatabaseDumper / DatabaseRestorer)

Provides cross-dialect database snapshot and restore capabilities via an agnostic .argos package bundle:

Bundle Structure (<name>.argos/)

dump_20261002150000.argos/
├── manifest.json       # Metadata: version, sourceDialect, totalRecords, tables record counts
├── schema.json         # Normalized IntrospectedSchema (collation-safe, vendor-agnostic)
└── data/               # Streaming chunked newline-delimited JSON
    ├── departments.ndjson
    └── employees.ndjson

Programmatic Usage

import { createDatabase, DatabaseDumper, DatabaseRestorer } from '@argos-js/data';

const sourceDb = createDatabase({ driver: 'sqlite', database: './storage/dev.sqlite' });
const targetDb = createDatabase({ driver: 'postgres', connectionString: 'postgres://user:pass@localhost/prod' });

// 1. Export database to canonical .argos package
const dumper = new DatabaseDumper();
const dumpResult = await dumper.dump({
  connection: sourceDb,
  outputPath: './storage/dumps/backup.argos',
  batchSize: 1000,
  onProgress: (p) => console.log(`[Dump] ${p.tableName}: ${p.transferredRows}/${p.totalRows}`),
});

// 2. Restore package into destination database
const restorer = new DatabaseRestorer();
const restoreResult = await restorer.restore({
  connection: targetDb,
  inputPath: './storage/dumps/backup.argos',
  truncate: true,
  onProgress: (p) => console.log(`[Restore] ${p.tableName}: ${p.transferredRows}/${p.totalRows}`),
});

Soft Deletes Guide

@argos-js/data provides declarative, native, and modern Soft Deletes support across migrations, universal schema definitions (defineTable), and Active Record Model.

When soft deletes are enabled, calling .delete() or .destroy() will not remove records physically from the database. Instead, it updates the deleted_at timestamp. All standard queries automatically filter out soft-deleted records (WHERE deleted_at IS NULL) unless explicitly requested otherwise.

1. Schema & Migrations

In Migrations (TableBlueprint)

You can define soft deletes via table.softDeletes() or as an option inside table.timestamps():

import { Migration, TableBlueprint } from '@argos-js/data';

export const up: Migration = async (db) => {
  await db.schema.createTable('products', (table: TableBlueprint) => {
    table.id();
    table.string('name');
    table.decimal('price', 10, 2);
    
    // Explicit soft delete column with automatic index (idx_products_deleted_at)
    table.softDeletes(); // adds nullable 'deleted_at' + index

    // Or via timestamps options:
    // table.timestamps({ softDeletes: true }); // created_at, updated_at, deleted_at
  });
};

The DDL generates the appropriate nullable timestamp column across all dialects (sqlite, postgres, mysql, mariadb, mssql, oracle) along with the automatic index idx_{table}_{column}.

In Universal Schema (defineTable)

You can activate soft deletes declaratively on the table definition:

import { defineTable, column } from '@argos-js/data';

export const products = defineTable('products', {
  id: column.id(),
  name: column.string(255),
  price: column.decimal(10, 2),
}, {
  softDeletes: true, // auto-provisions deleted_at and deletedAt fields
});

2. Active Record Model

Activate soft deletes on your model by setting public static softDeletes = true; or linking it to a defineTable with { softDeletes: true }:

import { Model } from '@argos-js/data';

export class Product extends Model {
  public static table = 'products';
  public static softDeletes = true;

  public declare id: number;
  public declare name: string;
  public declare deleted_at?: string | null;
}

Query Scoping

All reading operations (all(), find(), where(), first()) implicitly scope queries to only active records (WHERE deleted_at IS NULL):

// Only active records (deleted_at IS NULL)
const activeProducts = await Product.all();
const product = await Product.find(1);
const cheapProducts = await Product.where('price', '<', 50).get();

Query Modifiers

• .withTrashed(): Includes both active and soft-deleted records.
• .onlyTrashed(): Returns strictly records that have been soft-deleted (WHERE deleted_at IS NOT NULL).

// Include soft-deleted records
const allProductsIncludingTrashed = await Product.withTrashed().get();
const trashedProduct = await Product.withTrashed().where('id', 1).first();

// Only retrieve soft-deleted records
const trashBin = await Product.onlyTrashed().get();

Mutations & Lifecycle

• .delete() / .destroy(): Performs logical deletion (UPDATE ... SET deleted_at = CURRENT_TIMESTAMP).
• .restore(): Restores a soft-deleted record back to active state (UPDATE ... SET deleted_at = NULL).
• .forceDelete(): Performs physical, permanent deletion (DELETE FROM ... WHERE ...).

// 1. Soft Delete an instance or by query
const product = await Product.find(1);
await product.delete(); // updates deleted_at on DB and instance

await Product.where('price', '>', 1000).delete(); // bulk soft-delete

// 2. Restore
const trashed = await Product.onlyTrashed().where('id', 1).first();
await trashed.restore(); // restored! deleted_at is now null

// Or bulk restore:
await Product.where('name', 'Vintage Wine').restore();

// 3. Permanent Physical Delete
const obsolete = await Product.withTrashed().where('id', 2).first();
await obsolete.forceDelete(); // physically removed via DELETE FROM

3. Introspection, Sync & Dumps

The introspector (DatabaseIntrospector) and synchronizer (DatabaseSynchronizer) automatically recognize deleted_at as a soft_delete column type and ensure soft-deleted records and their exact timestamps are faithfully preserved during cross-database sync, canonical dumps, and restores.

Advanced Data Types, Vectors (AI) & Specialized Indexes

@argos-js/data provides cross-dialect support for modern and advanced data types, AI vector embeddings, computed/generated columns, and specialized indexes (GIN, HNSW, Full-Text).

1. Advanced Column Types

| Method | Postgres | MySQL / MariaDB | SQLite | MSSQL | Oracle | |---|---|---|---|---|---| | table.jsonb(name) | JSONB | JSON | TEXT | NVARCHAR(MAX) | CLOB | | table.vector(name, dim) | vector({dim}) | BLOB | BLOB | VARBINARY(MAX) | BLOB | | table.inet(name) | INET | VARCHAR(45) | VARCHAR(45) | NVARCHAR(45) | VARCHAR2(45) | | table.macAddress(name) | MACADDR | VARCHAR(17) | VARCHAR(17) | NVARCHAR(17) | VARCHAR2(17) | | table.enum(name, values) | VARCHAR(255) CHECK | ENUM(...) | TEXT CHECK | NVARCHAR(255) CHECK | VARCHAR2(255) CHECK | | table.money(name, p, s) | DECIMAL(19, 4) | DECIMAL(19, 4) | DECIMAL(19, 4) | DECIMAL(19, 4) | NUMBER(19, 4) | | table.date(name) | DATE | DATE | DATE | DATE | DATE | | table.time(name) | TIME | TIME | TIME | TIME | VARCHAR2(8) |

2. Generated (Computed) Columns (.as(...))

You can define stored or virtual computed columns using .as(expression, options):

await db.schema.createTable('invoices', (table) => {
  table.id();
  table.decimal('subtotal', 10, 2);
  table.decimal('tax', 10, 2);
  
  // Compiles to STORED generated column across dialects (PERSISTED in MSSQL)
  table.decimal('total', 10, 2).as('subtotal + tax', { mode: 'stored' });
});

The synchronizer and canonical dumper automatically identify generated columns and safely exclude them from INSERT operations during migrations and restores.

3. Specialized Indexes & Extensions

Enable PostgreSQL Extensions

await schema.enableExtension('vector'); // executes CREATE EXTENSION IF NOT EXISTS "vector"

Specialized Indexes (GIN, GIST, HNSW, Full-Text)

await db.schema.createTable('articles', (table) => {
  table.id();
  table.string('title');
  table.jsonb('metadata');
  table.vector('embedding', 1536);

  // GIN Index
  table.index('metadata', { using: 'gin', name: 'idx_articles_metadata_gin' });

  // Vector Search Index (HNSW with cosine similarity)
  table.vectorIndex('embedding', { metric: 'cosine', method: 'hnsw' });

  // Full-Text Search Index
  table.fullText(['title'], { name: 'idx_articles_title_ft' });
});

4. Query Helpers & Vector Search

The QueryBuilder and Active Record Model include native helpers for advanced querying:

// 1. Vector Proximity Search (Cosine <=> in pgvector)
const similarArticles = await Article.nearest('embedding', [0.05, 0.12, 0.99], {
  metric: 'cosine',
  limit: 5,
}).get();

// 2. Cross-Dialect JSON Querying (json_extract / ->> / JSON_UNQUOTE / JSON_VALUE)
const techArticles = await Article.whereJson('metadata.category', 'tech').get();

// 3. Dialect-Agnostic Full-Text Search (@@ plainto_tsquery / MATCH AGAINST / LIKE fallback)
const results = await Article.search('title', 'Distributed').get();

License

MIT