Skip to main content

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({
  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​

OperatorDescriptionSupported Databases
$incIncrement numeric valueAll
$mulMultiply numeric valuePostgreSQL, MySQL, MariaDB, MongoDB
$minSet to minimum of current and new valuePostgreSQL, MySQL, MariaDB, MongoDB
$maxSet to maximum of current and new valuePostgreSQL, MySQL, MariaDB, MongoDB
$setExplicitly set a valueAll
$unsetRemove/null a fieldAll
$pushAdd element to arrayAll
$pullRemove element from arrayAll (except DynamoDB)
$addToSetAdd unique element to arrayAll
$popRemove first/last element from arrayPostgreSQL, MySQL, MongoDB
$renameRename a fieldMongoDB
$currentDateSet to current date/timestampMongoDB
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:

const result = await ductape.database.transaction({
  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({
  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​

OptionTypeDescription
tablestringTable name
dataobject | object[]Record(s) to insert
returningbooleanReturn inserted records
onConflictobjectConflict handling configuration
transactionITransactionTransaction to use

Update Options​

OptionTypeDescription
tablestringTable name
dataobjectFields to update
whereobjectFilter conditions
returningbooleanReturn updated records
transactionITransactionTransaction to use

Delete Options​

OptionTypeDescription
tablestringTable name
whereobjectFilter conditions
returningbooleanReturn deleted records
transactionITransactionTransaction to use

Upsert Options​

OptionTypeDescription
tablestringTable name
dataobjectRecord to insert/update
conflictKeysstring[]Unique key columns
updateColumnsstring[]Columns to update on conflict
transactionITransactionTransaction 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​

See Also​