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

TypeScript

// 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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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:

TypeScript

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

Nested Placeholders

Placeholders work in nested structures:

TypeScript

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

Array Placeholders

Use in arrays:

TypeScript

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

Default Values

Provide defaults in input:

TypeScript

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

Managing Actions

Create Action

TypeScript

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:

Field	Type	Description
`name`	string	Display name for the action
`tag`	string	Unique identifier (format: `database:action`)
`tableName`	string	Table to operate on
`operation`	DatabaseActionTypes	Action operation (QUERY, INSERT, etc.)
`template`	object	Query template with placeholders

Optional Fields:

Field	Type	Description
`description`	string	Action description
`filterTemplate`	object	Filter criteria (for UPDATE/DELETE)

Update Action

TypeScript

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

Fetch Action

TypeScript

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

List Actions for Database

TypeScript

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

TypeScript

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

With Type Safety

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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

TypeScript

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:

TypeScript

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

TypeScript

//  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

TypeScript

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

TypeScript

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

TypeScript

//  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

TypeScript

//  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

TypeScript

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

7. Test Actions

TypeScript

// 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

Querying Data - Direct database queries
Writing Data - Insert, update, delete operations
Transactions - Ensure data consistency
Best Practices - Database optimization

Quick Example​

Why Use Actions?​

Action Types​

QUERY - Read Data​

INSERT - Create Records​

UPDATE - Modify Records​

DELETE - Remove Records​

UPSERT - Insert or Update​

AGGREGATE - Aggregations​

RAW_SQL - Custom Queries​

Variable Interpolation​

Basic Placeholders​

Nested Placeholders​

Array Placeholders​

Default Values​

Managing Actions​

Create Action​

Update Action​

Fetch Action​

List Actions for Database​

Execute Actions​

Basic Execution​

With Type Safety​

Error Handling​

Common Patterns​

Paginated Queries​

Search Actions​

Batch Inserts​

Conditional Updates​

Reporting Actions​

Best Practices​

1. Use Descriptive Names​

2. Add Descriptions​

3. Validate Input​

4. Use Consistent Naming​

5. Keep Actions Focused​

6. Limit Result Sets​

7. Test Actions​

Next Steps​

See Also​