Skip to main content

Database Actions

Create reusable query templates that can be executed with different input parameters. Database actions allow you to define queries once and reuse them across your application with variable interpolation.

Quick Example​

// Create an action template
await ductape.database.action.create({
  name: 'Get Users Paginated',
  tag: 'postgresdb:get-users-paginated',
  tableName: 'users',
  operation: DatabaseActionTypes.QUERY,
  description: 'Fetch paginated list of active users',
  template: {
    where: {
      is_active: true,
    },
    limit: '{{limit}}',
    offset: '{{offset}}',
    orderBy: [
      {
        column: '{{orderColumn}}',
        order: '{{orderDirection}}',
      },
    ],
  },
});

// Execute the action with different inputs
const users = await ductape.database.execute({
  database: 'postgresdb',
  action: 'get-users-paginated',
  input: {
    limit: 25,
    offset: 0,
    orderColumn: 'created_at',
    orderDirection: 'DESC',
  },
});

Why Use Actions?​

Benefits:

  • Reusability - Define query logic once, use everywhere
  • Type Safety - Template validation at creation time
  • Variable Interpolation - Dynamic queries with {{placeholder}} syntax
  • Maintainability - Update query logic in one place
  • Security - Centralized query management
  • Testing - Easy to test query templates

Action Types​

QUERY - Read Data​

Fetch records from a table:

await ductape.database.action.create({
  name: 'Get Active Users',
  tag: 'postgresdb:get-active-users',
  tableName: 'users',
  operation: DatabaseActionTypes.QUERY,
  template: {
    where: {
      status: '{{status}}',
      created_at: { $GTE: '{{startDate}}' },
    },
    select: ['id', 'name', 'email', 'created_at'],
    limit: '{{limit}}',
    orderBy: [{ column: 'created_at', order: 'DESC' }],
  },
});

// Execute
const users = await ductape.database.execute({
  database: 'postgresdb',
  action: 'get-active-users',
  input: {
    status: 'active',
    startDate: '2024-01-01',
    limit: 50,
  },
});

INSERT - Create Records​

Insert new records:

await ductape.database.action.create({
  name: 'Create User',
  tag: 'postgresdb:create-user',
  tableName: 'users',
  operation: DatabaseActionTypes.INSERT,
  template: {
    records: [
      {
        name: '{{name}}',
        email: '{{email}}',
        status: 'active',
        created_at: '{{createdAt}}',
      },
    ],
  },
});

// Execute
await ductape.database.execute({
  database: 'postgresdb',
  action: 'create-user',
  input: {
    name: 'Alice Johnson',
    email: 'alice@example.com',
    createdAt: new Date().toISOString(),
  },
});

UPDATE - Modify Records​

Update existing records:

await ductape.database.action.create({
  name: 'Update User Status',
  tag: 'postgresdb:update-user-status',
  tableName: 'users',
  operation: DatabaseActionTypes.UPDATE,
  template: {
    status: '{{status}}',
    updated_at: '{{updatedAt}}',
  },
  filterTemplate: {
    id: '{{userId}}',
  },
});

// Execute
await ductape.database.execute({
  database: 'postgresdb',
  action: 'update-user-status',
  input: {
    userId: 123,
    status: 'suspended',
    updatedAt: new Date().toISOString(),
  },
});

DELETE - Remove Records​

Delete records:

await ductape.database.action.create({
  name: 'Delete Old Sessions',
  tag: 'postgresdb:delete-old-sessions',
  tableName: 'sessions',
  operation: DatabaseActionTypes.DELETE,
  filterTemplate: {
    expires_at: { $LT: '{{expirationDate}}' },
  },
});

// Execute
await ductape.database.execute({
  database: 'postgresdb',
  action: 'delete-old-sessions',
  input: {
    expirationDate: new Date().toISOString(),
  },
});

UPSERT - Insert or Update​

Insert new record or update if exists:

await ductape.database.action.create({
  name: 'Upsert User Preferences',
  tag: 'postgresdb:upsert-user-prefs',
  tableName: 'user_preferences',
  operation: DatabaseActionTypes.UPSERT,
  template: {
    records: [
      {
        user_id: '{{userId}}',
        theme: '{{theme}}',
        notifications: '{{notifications}}',
        updated_at: '{{updatedAt}}',
      },
    ],
    conflictColumns: ['user_id'],
  },
});

// Execute
await ductape.database.execute({
  database: 'postgresdb',
  action: 'upsert-user-prefs',
  input: {
    userId: 123,
    theme: 'dark',
    notifications: true,
    updatedAt: new Date().toISOString(),
  },
});

AGGREGATE - Aggregations​

Perform aggregations using the operations object with $COUNT, $SUM, $AVG, $MIN, $MAX:

await ductape.database.action.create({
  name: 'User Statistics',
  tag: 'postgresdb:user-statistics',
  tableName: 'users',
  operation: DatabaseActionTypes.QUERY,
  template: {
    where: {
      status: '{{status}}',
      created_at: { $GTE: '{{startDate}}' },
    },
    operations: {
      total_count: { $COUNT: '*' },
      avg_age: { $AVG: 'age' },
      min_age: { $MIN: 'age' },
      max_age: { $MAX: 'age' },
    },
  },
});

// Execute
const result = await ductape.database.execute({
  database: 'postgresdb',
  action: 'user-statistics',
  input: {
    status: 'active',
    startDate: '2024-01-01',
  },
});

console.log('Statistics:', result);
// { total_count: 150, avg_age: 32.5, min_age: 18, max_age: 65 }

Single Aggregation:

await ductape.database.action.create({
  name: 'Total Sales',
  tag: 'postgresdb:total-sales',
  tableName: 'orders',
  operation: DatabaseActionTypes.QUERY,
  template: {
    where: {
      status: 'completed',
      created_at: {
        $GTE: '{{startDate}}',
        $LTE: '{{endDate}}',
      },
    },
    operations: {
      total_revenue: { $SUM: 'total' },
    },
  },
});

RAW_SQL - Custom Queries​

Execute raw SQL:

await ductape.database.action.create({
  name: 'Complex User Report',
  tag: 'postgresdb:complex-user-report',
  tableName: 'users', // Required but not used for raw SQL
  operation: DatabaseActionTypes.RAW_SQL,
  template: {
    query: `
      SELECT
        u.id,
        u.name,
        COUNT(o.id) as order_count,
        SUM(o.total) as total_spent
      FROM users u
      LEFT JOIN orders o ON u.id = o.user_id
      WHERE u.status = $1
        AND o.created_at >= $2
      GROUP BY u.id, u.name
      HAVING COUNT(o.id) > $3
      ORDER BY total_spent DESC
      LIMIT $4
    `,
    params: ['{{status}}', '{{startDate}}', '{{minOrders}}', '{{limit}}'],
  },
});

// Execute
const report = await ductape.database.execute({
  database: 'postgresdb',
  action: 'complex-user-report',
  input: {
    status: 'active',
    startDate: '2024-01-01',
    minOrders: 5,
    limit: 100,
  },
});

Variable Interpolation​

Basic Placeholders​

Use {{variableName}} syntax:

template: {
  where: {
    status: '{{status}}',
    age: { $GT: '{{minAge}}' },
  },
  limit: '{{limit}}',
}

Nested Placeholders​

Placeholders work in nested structures:

template: {
  where: {
    $OR: {
      email: '{{email}}',
      username: '{{username}}',
    },
  },
  select: ['{{field1}}', '{{field2}}', '{{field3}}'],
}

Array Placeholders​

Use in arrays:

template: {
  where: {
    status: { $IN: ['{{status1}}', '{{status2}}', '{{status3}}'] },
  },
}

Default Values​

Provide defaults in input:

await ductape.database.execute({
  database: 'postgresdb',
  action: 'get-users',
  input: {
    limit: 50, // Default limit
    offset: 0, // Default offset
  },
});

Managing Actions​

Create Action​

await ductape.database.action.create({
  name: 'Action Name',
  tag: 'database-tag:action-tag', // Format: database:action
  tableName: 'table_name',
  operation: DatabaseActionTypes.QUERY,
  description: 'Optional description',
  template: {
    // Your query template
  },
});

Required Fields:

FieldTypeDescription
namestringDisplay name for the action
tagstringUnique identifier (format: database:action)
tableNamestringTable to operate on
operationDatabaseActionTypesAction operation (QUERY, INSERT, etc.)
templateobjectQuery template with placeholders

Optional Fields:

FieldTypeDescription
descriptionstringAction description
filterTemplateobjectFilter criteria (for UPDATE/DELETE)

Update Action​

await ductape.database.action.update({
  tag: 'postgresdb:get-users',
  template: {
    // Updated template
    where: {
      status: '{{status}}',
    },
    limit: '{{limit}}',
  },
});

Fetch Action​

const action = await ductape.database.action.fetch('postgresdb:get-users');
console.log('Action:', action);

List Actions for Database​

const actions = await ductape.database.action.fetchAll('postgresdb');
console.log(`Found ${actions.length} actions`);

actions.forEach(action => {
  console.log(`${action.tag}: ${action.name} (${action.type})`);
});

Execute Actions​

Basic Execution​

const result = await ductape.database.execute({
  database: 'postgresdb',
  action: 'get-users',
  input: {
    status: 'active',
    limit: 50,
  },
});

With Type Safety​

interface User {
  id: number;
  name: string;
  email: string;
  status: string;
}

const users = await ductape.database.execute<User[]>({
  database: 'postgresdb',
  action: 'get-users',
  input: { status: 'active' },
});

// TypeScript knows users is User[]
users.forEach(user => {
  console.log(`${user.name} - ${user.email}`);
});

Error Handling​

try {
  const users = await ductape.database.execute({
    database: 'postgresdb',
    action: 'get-users',
    input: { status: 'active' },
  });
} catch (error) {
  if (error.message.includes('not found')) {
    console.error('Action not found');
  } else if (error.message.includes('validation')) {
    console.error('Invalid input parameters');
  } else {
    console.error('Execution failed:', error.message);
  }
}

Common Patterns​

Paginated Queries​

await ductape.database.action.create({
  name: 'Get Products Paginated',
  tag: 'postgresdb:get-products-paginated',
  tableName: 'products',
  operation: DatabaseActionTypes.QUERY,
  template: {
    where: {
      category: '{{category}}',
      in_stock: true,
    },
    select: ['id', 'name', 'price', 'stock'],
    limit: '{{limit}}',
    offset: '{{offset}}',
    orderBy: [
      { column: '{{sortBy}}', order: '{{sortOrder}}' },
    ],
  },
});

// Pagination helper
async function getProductsPage(
  category: string,
  page: number = 1,
  pageSize: number = 20
) {
  return ductape.database.execute({
    database: 'postgresdb',
    action: 'get-products-paginated',
    input: {
      category,
      limit: pageSize,
      offset: (page - 1) * pageSize,
      sortBy: 'name',
      sortOrder: 'ASC',
    },
  });
}

Search Actions​

await ductape.database.action.create({
  name: 'Search Users',
  tag: 'postgresdb:search-users',
  tableName: 'users',
  operation: DatabaseActionTypes.QUERY,
  template: {
    where: {
      $OR: {
        name: { $CONTAINS: '{{query}}' },
        email: { $CONTAINS: '{{query}}' },
      },
      status: 'active',
    },
    limit: '{{limit}}',
  },
});

Batch Inserts​

await ductape.database.action.create({
  name: 'Bulk Create Orders',
  tag: 'postgresdb:bulk-create-orders',
  tableName: 'orders',
  operation: DatabaseActionTypes.INSERT,
  template: {
    records: '{{orders}}', // Array of order objects
  },
});

// Execute with array
await ductape.database.execute({
  database: 'postgresdb',
  action: 'bulk-create-orders',
  input: {
    orders: [
      { user_id: 1, total: 99.99, status: 'pending' },
      { user_id: 2, total: 149.99, status: 'pending' },
      { user_id: 3, total: 79.99, status: 'pending' },
    ],
  },
});

Conditional Updates​

await ductape.database.action.create({
  name: 'Deactivate Inactive Users',
  tag: 'postgresdb:deactivate-inactive-users',
  tableName: 'users',
  operation: DatabaseActionTypes.UPDATE,
  template: {
    status: 'inactive',
    deactivated_at: '{{deactivatedAt}}',
  },
  filterTemplate: {
    last_login: { $LT: '{{cutoffDate}}' },
    status: 'active',
  },
});

Reporting Actions​

Use aggregations with operations for analytics:

await ductape.database.action.create({
  name: 'Sales Summary',
  tag: 'postgresdb:sales-summary',
  tableName: 'orders',
  operation: DatabaseActionTypes.AGGREGATE,
  template: {
    where: {
      status: 'completed',
      created_at: {
        $GTE: '{{startDate}}',
        $LTE: '{{endDate}}',
      },
    },
    operations: {
      total_revenue: { $SUM: 'total' },
      order_count: { $COUNT: 'id' },
      avg_order_value: { $AVG: 'total' },
      highest_order: { $MAX: 'total' },
      lowest_order: { $MIN: 'total' },
    },
  },
});

// Execute
const summary = await ductape.database.execute({
  database: 'postgresdb',
  action: 'sales-summary',
  input: {
    startDate: '2024-01-01',
    endDate: '2024-12-31',
  },
});

console.log('Summary:', summary);
// {
//   total_revenue: 125000.50,
//   order_count: 850,
//   avg_order_value: 147.06,
//   highest_order: 2500.00,
//   lowest_order: 9.99
// }

Best Practices​

1. Use Descriptive Names​

//  Good - clear purpose
await ductape.database.action.create({
  name: 'Get Active Users Created This Month',
  tag: 'postgresdb:get-active-users-this-month',
  // ...
});

// ❌ Avoid - vague
await ductape.database.action.create({
  name: 'Get Users',
  tag: 'postgresdb:users',
  // ...
});

2. Add Descriptions​

await ductape.database.action.create({
  name: 'Get Overdue Invoices',
  tag: 'postgresdb:get-overdue-invoices',
  description: 'Fetches all unpaid invoices past their due date for reminder emails',
  // ...
});

3. Validate Input​

function validateInput(input: any) {
  if (!input.limit || input.limit > 1000) {
    throw new Error('Limit must be between 1 and 1000');
  }
  if (input.offset < 0) {
    throw new Error('Offset must be non-negative');
  }
}

const users = await ductape.database.execute({
  database: 'postgresdb',
  action: 'get-users',
  input: validateInput(userInput),
});

4. Use Consistent Naming​

//  Good - consistent pattern
'postgresdb:get-users'
'postgresdb:create-user'
'postgresdb:update-user'
'postgresdb:delete-user'

// ❌ Avoid - inconsistent
'postgresdb:getUsers'
'postgresdb:user-create'
'postgresdb:UpdateUser'
'postgresdb:del_user'

5. Keep Actions Focused​

//  Good - single purpose
await ductape.database.action.create({
  name: 'Get User Orders',
  tag: 'postgresdb:get-user-orders',
  operation: DatabaseActionTypes.QUERY,
  // ... fetch only orders
});

// ❌ Avoid - too complex
await ductape.database.action.create({
  name: 'Get User Complete Profile',
  tag: 'postgresdb:get-user-profile',
  operation: DatabaseActionTypes.RAW_SQL,
  template: {
    query: `
      SELECT users.*, orders.*, addresses.*, payments.*
      FROM users
      LEFT JOIN orders ...
      LEFT JOIN addresses ...
      LEFT JOIN payments ...
      -- Too many joins, should be separate actions
    `,
  },
});

6. Limit Result Sets​

//  Always include limit
template: {
  where: { status: '{{status}}' },
  limit: '{{limit}}', // Prevent unbounded queries
}

7. Test Actions​

// Test action with various inputs
describe('get-users-paginated', () => {
  it('should fetch first page', async () => {
    const users = await ductape.database.execute({
      database: 'testdb',
      action: 'get-users-paginated',
      input: { limit: 10, offset: 0 },
    });

    expect(users).toHaveLength(10);
  });

  it('should respect offset', async () => {
    const page2 = await ductape.database.execute({
      database: 'testdb',
      action: 'get-users-paginated',
      input: { limit: 10, offset: 10 },
    });

    expect(page2[0].id).toBeGreaterThan(10);
  });
});

Next Steps​

See Also​