Skip to main content

Direct Queries

Direct queries provide full access to all database operations through the SDK. Use this guide to understand the complete API available for database operations.

Connection Context​

Establish a connection once and all subsequent operations inherit the context:

// Connect once
await ductape.database.connect({
  database: 'main-db',
});

// All queries inherit context - no need to specify env/product/database
await ductape.database.query({ table: 'users' });
await ductape.database.insert({ table: 'users', data: {...} });
await ductape.database.aggregate({ table: 'orders', operations: {...} });

Or specify connection parameters explicitly on each call:

await ductape.database.query({
  database: 'main-db',
  table: 'users',
});

Available Methods​

The database service provides these methods:

MethodDescriptionSee Guide
query()Read data with filters, sorting, paginationQuerying
insert()Insert single or multiple recordsWriting Data
update()Update matching recordsWriting Data
delete()Delete matching recordsWriting Data
upsert()Insert or update on conflictWriting Data
raw()Execute raw SQL/queriesQuerying
count()Count recordsAggregations
sum()Sum column valuesAggregations
avg()Average column valuesAggregations
min() / max()Min/max valuesAggregations
groupBy()Group and aggregateAggregations
aggregate()Multiple aggregationsAggregations
transaction()Transaction wrapperTransactions
beginTransaction()Manual transactionTransactions
createTable()Create new tableBelow
alterTable()Modify table structureBelow
dropTable()Delete tableBelow
createIndex()Create indexMigrations
dropIndex()Delete indexMigrations
listIndexes()List table indexesMigrations
runMigration()Run migrationMigrations

Schema Management​

Create Table​

import { SchemaHelpers } from '@ductape/sdk';

await ductape.database.createTable(
  { database: 'main-db' },
  {
    name: 'products',
    columns: [
      SchemaHelpers.id(),
      SchemaHelpers.string('name', 255, false),
      SchemaHelpers.decimal('price', 10, 2),
      SchemaHelpers.boolean('is_active', true),
      ...SchemaHelpers.timestamps(),
    ],
  },
  { ifNotExists: true }
);

Schema Helpers​

Pre-configured column definitions:

import { SchemaHelpers } from '@ductape/sdk';

SchemaHelpers.id()                           // Auto-increment primary key
SchemaHelpers.uuid()                         // UUID primary key
SchemaHelpers.string('name', 255, false)     // String (length, nullable)
SchemaHelpers.text('bio')                    // Text field
SchemaHelpers.integer('age')                 // Integer
SchemaHelpers.bigint('views')                // Big integer
SchemaHelpers.decimal('price', 10, 2)        // Decimal (precision, scale)
SchemaHelpers.boolean('active', true)        // Boolean with default
SchemaHelpers.date('birth_date')             // Date
SchemaHelpers.datetime('scheduled_at')       // Datetime
SchemaHelpers.timestamp('logged_at')         // Timestamp
SchemaHelpers.json('settings')               // JSON
SchemaHelpers.jsonb('metadata')              // JSONB (PostgreSQL)
SchemaHelpers.foreignKey('user_id', 'users') // Foreign key
SchemaHelpers.timestamps()                   // created_at, updated_at
SchemaHelpers.softDelete()                   // deleted_at

Alter Table​

import { ColumnAlterationType, ColumnType } from '@ductape/sdk';

// Add column
await ductape.database.alterTable(
  { database: 'main-db' },
  'products',
  [
    {
      type: ColumnAlterationType.ADD,
      column: { name: 'sku', type: ColumnType.STRING, length: 50 },
    },
  ]
);

// Drop column
await ductape.database.alterTable(
  { database: 'main-db' },
  'products',
  [
    { type: ColumnAlterationType.DROP, columnName: 'old_field' },
  ]
);

// Rename column
await ductape.database.alterTable(
  { database: 'main-db' },
  'products',
  [
    {
      type: ColumnAlterationType.RENAME,
      oldName: 'old_name',
      newName: 'new_name',
    },
  ]
);

Drop Table​

await ductape.database.dropTable(
  { database: 'main-db' },
  'old_table'
);

Get Schema Information​

// List all tables
const tables = await ductape.database.listTables({
  database: 'main-db',
});

// Get table schema
const schema = await ductape.database.getTableSchema(
  { database: 'main-db' },
  'users'
);

console.log('Table:', schema.name);
console.log('Columns:', schema.columns);
console.log('Indexes:', schema.indexes);

// Check if table exists
const exists = await ductape.database.tableExists(
  { database: 'main-db' },
  'users'
);

Building Dynamic Queries​

Conditional Filters​

function searchProducts(filters: {
  category?: string;
  minPrice?: number;
  maxPrice?: number;
  inStock?: boolean;
}) {
  const where: any = {};

  if (filters.category) {
    where.category = filters.category;
  }

  if (filters.minPrice !== undefined || filters.maxPrice !== undefined) {
    where.price = {};
    if (filters.minPrice !== undefined) where.price.$GTE = filters.minPrice;
    if (filters.maxPrice !== undefined) where.price.$LTE = filters.maxPrice;
  }

  if (filters.inStock) {
    where.stock = { $GT: 0 };
  }

  return ductape.database.query({
    table: 'products',
    where: Object.keys(where).length > 0 ? where : undefined,
  });
}

Paginated Results Helper​

async function getPaginatedResults<T>(
  table: string,
  page: number,
  pageSize: number,
  where?: object
): Promise<{
  data: T[];
  total: number;
  page: number;
  totalPages: number;
}> {
  const result = await ductape.database.query({
    table,
    where,
    limit: pageSize,
    offset: (page - 1) * pageSize,
  });

  return {
    data: result.data as T[],
    total: result.count,
    page,
    totalPages: Math.ceil(result.count / pageSize),
  };
}

Error Handling​

import { DatabaseError, DatabaseErrorType } from '@ductape/sdk';

try {
  await ductape.database.insert({
    table: 'users',
    data: { email: 'test@example.com' },
  });
} catch (error) {
  if (error instanceof DatabaseError) {
    switch (error.type) {
      case DatabaseErrorType.CONNECTION_ERROR:
        console.error('Cannot connect to database');
        break;
      case DatabaseErrorType.QUERY_ERROR:
        console.error('Query failed:', error.message);
        break;
      case DatabaseErrorType.UNIQUE_VIOLATION:
        console.error('Duplicate key');
        break;
      case DatabaseErrorType.FOREIGN_KEY_VIOLATION:
        console.error('Invalid reference');
        break;
      default:
        throw error;
    }
  }
}

Next Steps​

See Also​