Database Operations

The @ductape/client provides a comprehensive API for working with databases including querying, inserting, updating, deleting, and subscribing to real-time changes.

Connecting to a Database

Before performing database operations, connect to your database:

await ductape.databases.connect({
  database: 'main' // Your database name
});

Querying Data

Basic Query

const result = await ductape.databases.query({
  table: 'users',
  limit: 10
});

console.log('Users:', result.rows);
console.log('Total count:', result.count);

Query with Filters

const result = await ductape.databases.query({
  table: 'products',
  where: {
    category: 'electronics',
    price: { $lt: 1000 },
    inStock: true
  },
  limit: 20
});

Query Operators

const result = await ductape.databases.query({
  table: 'orders',
  where: {
    // Greater than
    total: { $gt: 100 },

    // Less than or equal
    discount: { $lte: 20 },

    // Not equal
    status: { $ne: 'cancelled' },

    // In array
    category: { $in: ['electronics', 'books'] },

    // Not in array
    region: { $nin: ['blocked'] },

    // Contains (for strings)
    name: { $contains: 'phone' },

    // Starts with
    email: { $startsWith: 'admin@' }
  }
});

Sorting and Pagination

const result = await ductape.databases.query({
  table: 'posts',
  where: { published: true },
  orderBy: [
    { column: 'createdAt', order: 'desc' },
    { column: 'title', order: 'asc' }
  ],
  limit: 20,
  offset: 0
});

const result = await ductape.databases.query({
  table: 'posts',
  include: [
    { relation: 'author' },
    { relation: 'comments', limit: 5 }
  ]
});

// Access related data
result.rows.forEach(post => {
  console.log(post.title);
  console.log('Author:', post.author.name);
  console.log('Comments:', post.comments);
});

Select Specific Fields

const result = await ductape.databases.query({
  table: 'users',
  select: ['id', 'name', 'email'],
  limit: 10
});

Inserting Data

Insert Single Record

const result = await ductape.databases.insert({
  table: 'users',
  data: {
    name: 'Alice Johnson',
    email: 'alice@example.com',
    role: 'user'
  }
});

console.log('Created user:', result.rows[0]);

Insert Multiple Records

const result = await ductape.databases.insert({
  table: 'products',
  data: [
    { name: 'Product 1', price: 29.99 },
    { name: 'Product 2', price: 39.99 },
    { name: 'Product 3', price: 49.99 }
  ]
});

console.log(`Inserted ${result.rows.length} products`);

Updating Data

Update with Filter

const result = await ductape.databases.update({
  table: 'users',
  where: { id: 'user-123' },
  data: {
    name: 'Alice Smith',
    updatedAt: new Date()
  }
});

console.log('Updated:', result.rows[0]);

Update Multiple Records

const result = await ductape.databases.update({
  table: 'orders',
  where: {
    status: 'pending',
    createdAt: { $lt: new Date('2024-01-01') }
  },
  data: {
    status: 'expired'
  }
});

console.log(`Updated ${result.count} orders`);

Deleting Data

Delete with Filter

const result = await ductape.databases.delete({
  table: 'sessions',
  where: { userId: 'user-123' }
});

console.log(`Deleted ${result.count} sessions`);

Delete Single Record

const result = await ductape.databases.delete({
  table: 'posts',
  where: { id: 'post-456' }
});

Upsert (Insert or Update)

const result = await ductape.databases.upsert({
  table: 'users',
  where: { email: 'alice@example.com' },
  data: {
    name: 'Alice Johnson',
    email: 'alice@example.com',
    lastLogin: new Date()
  }
});

// If user exists, updates it; otherwise inserts new record

Aggregations

Count

const result = await ductape.databases.aggregate({
  table: 'orders',
  operation: 'count',
  where: { status: 'completed' }
});

console.log('Completed orders:', result.value);

Sum

const result = await ductape.databases.aggregate({
  table: 'orders',
  operation: 'sum',
  column: 'total',
  where: { status: 'completed' }
});

console.log('Total revenue:', result.value);

Average, Min, Max

// Average
const avg = await ductape.databases.aggregate({
  table: 'products',
  operation: 'avg',
  column: 'price'
});

// Minimum
const min = await ductape.databases.aggregate({
  table: 'products',
  operation: 'min',
  column: 'price'
});

// Maximum
const max = await ductape.databases.aggregate({
  table: 'products',
  operation: 'max',
  column: 'price'
});

Real-time Subscriptions

Subscribe to database changes in real-time:

// First, connect to WebSocket
await ductape.connect();

// Subscribe to table changes
const subscription = ductape.databases.subscribe({
  table: 'messages',
  where: { channel: 'general' },
  onChange: (event) => {
    console.log('Change type:', event.type); // 'insert', 'update', 'delete'

    if (event.type === 'insert') {
      console.log('New record:', event.data.new);
    } else if (event.type === 'update') {
      console.log('Before:', event.data.old);
      console.log('After:', event.data.new);
    } else if (event.type === 'delete') {
      console.log('Deleted:', event.data.old);
    }
  }
});

// Later: unsubscribe
subscription.unsubscribe();

const subscription = ductape.databases.subscribe({
  table: 'notifications',
  where: { userId: currentUserId, read: false },
  onChange: (event) => {
    if (event.type === 'insert') {
      showNotification(event.data.new);
    }
  }
});

Transactions

Execute multiple operations atomically:

const result = await ductape.databases.transaction(async (tx) => {
  // Deduct from sender
  await tx.update({
    table: 'accounts',
    where: { id: senderId },
    data: { balance: { $decrement: amount } }
  });

  // Add to receiver
  await tx.update({
    table: 'accounts',
    where: { id: receiverId },
    data: { balance: { $increment: amount } }
  });

  // Record transaction
  await tx.insert({
    table: 'transactions',
    data: {
      from: senderId,
      to: receiverId,
      amount: amount,
      timestamp: new Date()
    }
  });

  return { success: true };
});

TypeScript Support

Use TypeScript for type-safe database operations:

interface User {
  id: string;
  name: string;
  email: string;
  role: 'admin' | 'user';
  createdAt: Date;
}

// Query with types
const result = await ductape.databases.query<User>({
  table: 'users',
  where: { role: 'admin' }
});

// result.rows is typed as User[]
const firstUser: User = result.rows[0];

// Insert with types
const newUser = await ductape.databases.insert<User>({
  table: 'users',
  data: {
    name: 'Bob',
    email: 'bob@example.com',
    role: 'user'
  }
});

Best Practices

Always handle errors: Wrap database operations in try-catch blocks
Use indexes: Ensure frequently queried fields are indexed in your database schema
Limit results: Always use limit to prevent fetching too much data
Use subscriptions wisely: Unsubscribe when components unmount to prevent memory leaks
Batch operations: Use bulk inserts instead of multiple single inserts
Optimize queries: Select only the fields you need with the select option

Connecting to a Database​

Querying Data​

Basic Query​

Query with Filters​

Query Operators​

Sorting and Pagination​

Including Related Data​

Select Specific Fields​

Inserting Data​

Insert Single Record​

Insert Multiple Records​

Updating Data​

Update with Filter​

Update Multiple Records​

Deleting Data​

Delete with Filter​

Delete Single Record​

Upsert (Insert or Update)​

Aggregations​

Count​

Sum​

Average, Min, Max​

Real-time Subscriptions​

Subscribe with Filters​

Transactions​

TypeScript Support​

Best Practices​

Next Steps​