Writing Data

Learn how to insert, update, upsert, and delete data using Ductape's database API. This guide covers single records, bulk operations, and advanced write patterns.

Insert Operations

Insert a Single Record

const result = await ductape.database.insert({
  table: 'users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    status: 'active',
    created_at: new Date(),
  },
  returning: true, // Return the inserted record
});

console.log('Inserted ID:', result.insertedIds[0]);
console.log('Inserted data:', result.data);

Insert Multiple Records

const result = await ductape.database.insert({
  table: 'users',
  data: [
    { name: 'User 1', email: 'user1@example.com' },
    { name: 'User 2', email: 'user2@example.com' },
    { name: 'User 3', email: 'user3@example.com' },
  ],
});

console.log('Inserted count:', result.count);
console.log('Inserted IDs:', result.insertedIds);

Insert with Conflict Handling (Upsert)

Handle duplicate key conflicts gracefully:

const result = await ductape.database.insert({
  table: 'users',
  data: {
    email: 'john@example.com',
    name: 'John Doe',
    status: 'active',
  },
  onConflict: {
    columns: ['email'],        // Conflict detection columns
    action: 'update',          // 'update' or 'ignore'
    update: ['name', 'status'], // Columns to update on conflict
  },
});

Insert with Connection Parameters

const result = await ductape.database.insert({
  env: 'prd',
  product: 'my-app',
  database: 'users-db',
  table: 'users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
  },
});

Update Operations

Update Matching Records

const result = await ductape.database.update({
  table: 'users',
  data: {
    status: 'inactive',
    updated_at: new Date(),
  },
  where: {
    last_login: { $lt: new Date('2023-01-01') },
  },
  returning: true,
});

console.log('Updated count:', result.count);
console.log('Updated records:', result.data);

Update Operators

Ductape provides special operators for atomic update operations. These operators work across all supported databases (PostgreSQL, MySQL, MariaDB, MongoDB, DynamoDB, Cassandra) and use lowercase naming following the Mongoose/MongoDB convention.

Numeric Operators

// $inc - Increment a numeric value
await ductape.database.update({
  table: 'products',
  data: {
    stock: { $inc: 10 }, // Add 10 to stock
    views: { $inc: 1 },  // Increment view count
  },
  where: { id: productId },
});

// Decrement using negative value
await ductape.database.update({
  table: 'products',
  data: {
    stock: { $inc: -5 }, // Subtract 5 from stock
  },
  where: { id: productId },
});

// $mul - Multiply a numeric value
await ductape.database.update({
  table: 'products',
  data: {
    price: { $mul: 1.1 }, // Increase price by 10%
  },
  where: { category: 'electronics' },
});

// $min - Set to minimum (only update if new value is less than current)
await ductape.database.update({
  table: 'products',
  data: {
    lowest_price: { $min: currentPrice }, // Track lowest price seen
  },
  where: { id: productId },
});

// $max - Set to maximum (only update if new value is greater than current)
await ductape.database.update({
  table: 'products',
  data: {
    highest_price: { $max: currentPrice }, // Track highest price seen
  },
  where: { id: productId },
});

Field Operators

// $set - Explicitly set a value (useful when you need to distinguish from regular updates)
await ductape.database.update({
  table: 'users',
  data: {
    settings: { $set: { theme: 'dark', language: 'en' } },
  },
  where: { id: userId },
});

// $unset - Remove/null a field
await ductape.database.update({
  table: 'users',
  data: {
    temporary_token: { $unset: true }, // Set to NULL / remove field
  },
  where: { id: userId },
});

Array Operators

These operators work with array/list columns. Behavior varies by database:

PostgreSQL: Works with native array types
MySQL/MariaDB: Works with JSON arrays
MongoDB: Native array support
DynamoDB: Works with List and Set types
Cassandra: Works with list and set types

// $push - Add an element to an array
await ductape.database.update({
  table: 'users',
  data: {
    tags: { $push: 'premium' }, // Add 'premium' to tags array
  },
  where: { id: userId },
});

// $pull - Remove an element from an array
await ductape.database.update({
  table: 'users',
  data: {
    tags: { $pull: 'trial' }, // Remove 'trial' from tags array
  },
  where: { id: userId },
});

// $addToSet - Add element only if it doesn't exist (unique add)
await ductape.database.update({
  table: 'users',
  data: {
    roles: { $addToSet: 'editor' }, // Add 'editor' only if not present
  },
  where: { id: userId },
});

Operator Reference

Operator	Description	Supported Databases
`$inc`	Increment numeric value	All
`$mul`	Multiply numeric value	PostgreSQL, MySQL, MariaDB, MongoDB
`$min`	Set to minimum of current and new value	PostgreSQL, MySQL, MariaDB, MongoDB
`$max`	Set to maximum of current and new value	PostgreSQL, MySQL, MariaDB, MongoDB
`$set`	Explicitly set a value	All
`$unset`	Remove/null a field	All
`$push`	Add element to array	All
`$pull`	Remove element from array	All (except DynamoDB)
`$addToSet`	Add unique element to array	All
`$pop`	Remove first/last element from array	PostgreSQL, MySQL, MongoDB
`$rename`	Rename a field	MongoDB
`$currentDate`	Set to current date/timestamp	MongoDB

Backwards Compatibility

Uppercase operators (e.g., $INC, $SET) are still supported for backwards compatibility, but lowercase is recommended.

Combining Multiple Operators

await ductape.database.update({
  table: 'game_stats',
  data: {
    score: { $inc: 100 },           // Increment score
    high_score: { $max: newScore }, // Update high score if higher
    games_played: { $inc: 1 },      // Increment game count
    achievements: { $addToSet: 'first_win' }, // Add achievement
    updated_at: new Date(),         // Regular field update
  },
  where: { player_id: playerId },
});

Update with Complex Conditions

await ductape.database.update({
  table: 'orders',
  data: {
    status: 'cancelled',
    cancelled_at: new Date(),
    cancellation_reason: 'Customer request',
  },
  where: {
    $and: [
      { status: { $in: ['pending', 'processing'] } },
      { created_at: { $lt: new Date(Date.now() - 7 * 24 * 60 * 60 * 1000) } },
    ],
  },
});

Update Single Record by ID

await ductape.database.update({
  table: 'users',
  data: {
    name: 'Jane Doe',
    email: 'jane@example.com',
  },
  where: { id: userId },
  returning: true,
});

Upsert Operations

Insert a record or update if it already exists:

Basic Upsert

const result = await ductape.database.upsert({
  table: 'user_preferences',
  data: {
    user_id: 123,
    theme: 'dark',
    language: 'en',
    notifications: true,
  },
  conflictKeys: ['user_id'], // Unique key to check
});

console.log('Operation:', result.operation); // 'inserted' or 'updated'
console.log('Affected rows:', result.count);

Upsert with Specific Update Columns

const result = await ductape.database.upsert({
  table: 'product_inventory',
  data: {
    product_id: 'prod-123',
    warehouse_id: 'wh-1',
    quantity: 100,
    last_restocked: new Date(),
  },
  conflictKeys: ['product_id', 'warehouse_id'],
  updateColumns: ['quantity', 'last_restocked'], // Only update these on conflict
});

Delete Operations

Delete Matching Records

const result = await ductape.database.delete({
  table: 'users',
  where: {
    status: 'deleted',
  },
});

console.log('Deleted count:', result.count);

Delete with Complex Conditions

await ductape.database.delete({
  table: 'sessions',
  where: {
    $and: [
      { expires_at: { $lt: new Date() } },
      { user_id: { $isNotNull: true } },
    ],
  },
});

Delete Single Record

await ductape.database.delete({
  table: 'users',
  where: { id: userId },
});

Soft Delete Pattern

Instead of permanently deleting, mark records as deleted:

// Soft delete
await ductape.database.update({
  table: 'users',
  data: {
    deleted_at: new Date(),
    status: 'deleted',
  },
  where: { id: userId },
});

// Query excluding soft-deleted records
const activeUsers = await ductape.database.query({
  table: 'users',
  where: {
    deleted_at: { $isNull: true },
  },
});

Transactions

Wrap multiple write operations in a transaction:

Using the Callback API (Recommended)

const result = await ductape.database.transaction({
  env: 'prd',
  product: 'my-app',
  database: 'main-db',
}, async (transaction) => {
  // Insert order
  const order = await ductape.database.insert({
    table: 'orders',
    data: {
      customer_id: customerId,
      total: 99.99,
      status: 'pending',
    },
    transaction,
  });

  // Insert order items
  await ductape.database.insert({
    table: 'order_items',
    data: items.map(item => ({
      order_id: order.insertedIds[0],
      product_id: item.productId,
      quantity: item.quantity,
      price: item.price,
    })),
    transaction,
  });

  // Update inventory
  for (const item of items) {
    await ductape.database.update({
      table: 'products',
      data: { stock: { $dec: item.quantity } },
      where: { id: item.productId },
      transaction,
    });
  }

  return order;
});

Manual Transaction Control

const transaction = await ductape.database.beginTransaction({
  env: 'prd',
  product: 'my-app',
  database: 'main-db',
});

try {
  await ductape.database.insert({
    table: 'accounts',
    data: { balance: 1000 },
    transaction,
  });

  await ductape.database.update({
    table: 'accounts',
    data: { balance: { $dec: 100 } },
    where: { id: sourceAccountId },
    transaction,
  });

  await transaction.commit();
} catch (error) {
  await transaction.rollback();
  throw error;
}

Write Result Structures

Insert Result

interface IInsertResult<T> {
  data: T[];              // Inserted records (if returning: true)
  count: number;          // Number of inserted records
  insertedIds: any[];     // IDs of inserted records
}

Update Result

interface IUpdateResult<T> {
  data: T[];              // Updated records (if returning: true)
  count: number;          // Number of updated records
}

Delete Result

interface IDeleteResult {
  count: number;          // Number of deleted records
}

Upsert Result

interface IUpsertResult<T> {
  data: T[];              // Affected records
  count: number;          // Number of affected records
  operation: 'inserted' | 'updated';
}

Options Reference

Insert Options

Option	Type	Description
`table`	string	Table name
`data`	object \| object[]	Record(s) to insert
`returning`	boolean	Return inserted records
`onConflict`	object	Conflict handling configuration
`transaction`	ITransaction	Transaction to use

Update Options

Option	Type	Description
`table`	string	Table name
`data`	object	Fields to update
`where`	object	Filter conditions
`returning`	boolean	Return updated records
`transaction`	ITransaction	Transaction to use

Delete Options

Option	Type	Description
`table`	string	Table name
`where`	object	Filter conditions
`returning`	boolean	Return deleted records
`transaction`	ITransaction	Transaction to use

Upsert Options

Option	Type	Description
`table`	string	Table name
`data`	object	Record to insert/update
`conflictKeys`	string[]	Unique key columns
`updateColumns`	string[]	Columns to update on conflict
`transaction`	ITransaction	Transaction to use

Best Practices

// Good: Multiple related changes in a transaction
await ductape.database.transaction({ ... }, async (trx) => {
  await ductape.database.insert({ table: 'orders', data: order, transaction: trx });
  await ductape.database.insert({ table: 'order_items', data: items, transaction: trx });
  await ductape.database.update({ table: 'inventory', data: updates, transaction: trx });
});

// Bad: Related changes without transaction
await ductape.database.insert({ table: 'orders', data: order });
await ductape.database.insert({ table: 'order_items', data: items }); // Might fail, leaving orphaned order

2. Use Returning for Immediate Data Access

// Good: Get inserted data immediately
const result = await ductape.database.insert({
  table: 'users',
  data: userData,
  returning: true,
});
const newUser = result.data[0];

// Avoid: Separate query after insert
const insertResult = await ductape.database.insert({ table: 'users', data: userData });
const newUser = await ductape.database.query({
  table: 'users',
  where: { id: insertResult.insertedIds[0] }
});

3. Validate Before Writing

// Validate data before database operations
function validateUser(data: any) {
  if (!data.email || !data.email.includes('@')) {
    throw new Error('Invalid email');
  }
  if (!data.name || data.name.length < 2) {
    throw new Error('Name too short');
  }
}

validateUser(userData);
await ductape.database.insert({ table: 'users', data: userData });

4. Use Soft Deletes for Important Data

// Instead of hard delete
await ductape.database.delete({ table: 'users', where: { id: userId } });

// Use soft delete
await ductape.database.update({
  table: 'users',
  data: { deleted_at: new Date(), status: 'deleted' },
  where: { id: userId },
});

5. Handle Conflicts Gracefully

try {
  await ductape.database.insert({
    table: 'users',
    data: { email: 'test@example.com', name: 'Test' },
  });
} catch (error) {
  if (error.code === 'UNIQUE_VIOLATION') {
    // Handle duplicate email
    console.log('Email already exists');
  } else {
    throw error;
  }
}

// Or use upsert for automatic handling
await ductape.database.upsert({
  table: 'users',
  data: { email: 'test@example.com', name: 'Test' },
  conflictKeys: ['email'],
});

Next Steps

Aggregations - Perform calculations on your data
Transactions - Deep dive into transaction management
Best Practices - Production-ready patterns

Insert Operations​

Insert a Single Record​

Insert Multiple Records​

Insert with Conflict Handling (Upsert)​

Insert with Connection Parameters​

Update Operations​

Update Matching Records​

Update Operators​

Numeric Operators​

Field Operators​

Array Operators​

Operator Reference​

Combining Multiple Operators​

Update with Complex Conditions​

Update Single Record by ID​

Upsert Operations​

Basic Upsert​

Upsert with Specific Update Columns​

Delete Operations​

Delete Matching Records​

Delete with Complex Conditions​

Delete Single Record​

Soft Delete Pattern​

Transactions​

Using the Callback API (Recommended)​

Manual Transaction Control​

Write Result Structures​

Insert Result​

Update Result​

Delete Result​

Upsert Result​

Options Reference​

Insert Options​

Update Options​

Delete Options​

Upsert Options​

Best Practices​

1. Always Use Transactions for Related Changes​

2. Use Returning for Immediate Data Access​

3. Validate Before Writing​

4. Use Soft Deletes for Important Data​

5. Handle Conflicts Gracefully​

Next Steps​

See Also​