Database Queries

Execute SQL queries, manage transactions, and perform data operations in your Sandbox databases.

Update Data

Update rows in a table.

await sandbox.database.updateRow({
  database: 'myapp',
  table: 'users',
  data: {
    name: 'John Smith',
    age: 31
  },
  where: {
    email: 'john@example.com'
  }
});

Parameters:

database (string, required) - Database name
table (string, required) - Table name
data (object, required) - New values
where (object, required) - Conditions

await fetch(`${sandbox.url}/database/myapp/tables/users/update`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    data: {
      name: 'John Smith',
      age: 31
    },
    where: {
      email: 'john@example.com'
    }
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/tables/users/update \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {"name": "John Smith", "age": 31},
    "where": {"email": "john@example.com"}
  }'

Response:

{
  "success": true,
  "message": "Row updated successfully",
  "rowsAffected": 1
}

Delete Data

Delete rows from a table.

await sandbox.database.deleteRow({
  database: 'myapp',
  table: 'users',
  where: {
    id: 5
  }
});

Parameters:

database (string, required) - Database name
table (string, required) - Table name
where (object, required) - Conditions

await fetch(`${sandbox.url}/database/myapp/tables/users/delete`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    where: {
      id: 5
    }
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/tables/users/delete \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type": 'application/json" \
  -d '{"where": {"id": 5}}'

Response:

{
  "success": true,
  "message": "Row deleted successfully",
  "rowsAffected": 1
}

Execute Query

Run custom SQL queries for complex operations.

// SELECT query
const result = await sandbox.database.executeQuery({
  database: 'myapp',
  query: 'SELECT * FROM users WHERE age > ? AND status = ?',
  params: [25, 'active']
});

console.log(result.rows);

Parameters:

database (string, required) - Database name
query (string, required) - SQL query
params (array) - Query parameters

const response = await fetch(`${sandbox.url}/database/myapp/query`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    query: 'SELECT * FROM users WHERE age > ? AND status = ?',
    params: [25, 'active']
  })
});

const result = await response.json();

curl -X POST https://sandbox.oblien.com/database/myapp/query \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT * FROM users WHERE age > ?",
    "params": [25]
  }'

Response:

{
  "success": true,
  "rows": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "john@example.com",
      "age": 30,
      "status": "active"
    }
  ],
  "rowCount": 1
}

Query Examples

JOIN Query

const result = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT 
      users.name, 
      posts.title, 
      posts.created_at 
    FROM users 
    JOIN posts ON users.id = posts.user_id 
    WHERE users.status = ?
    ORDER BY posts.created_at DESC
  `,
  params: ['active']
});

await fetch(`${sandbox.url}/database/myapp/query`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    query: 'SELECT users.name, posts.title FROM users JOIN posts ON users.id = posts.user_id',
    params: []
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/query \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT users.name, posts.title FROM users JOIN posts ON users.id = posts.user_id"
  }'

Aggregate Functions

const stats = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT 
      COUNT(*) as total_users,
      AVG(age) as average_age,
      MIN(age) as youngest,
      MAX(age) as oldest
    FROM users
    WHERE status = ?
  `,
  params: ['active']
});

console.log(stats.rows[0]);

await fetch(`${sandbox.url}/database/myapp/query`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    query: 'SELECT COUNT(*) as total, AVG(age) as avg_age FROM users',
    params: []
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/query \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT COUNT(*) as total FROM users"
  }'

GROUP BY Query

const grouped = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT 
      status,
      COUNT(*) as count,
      AVG(age) as avg_age
    FROM users
    GROUP BY status
    HAVING count > ?
  `,
  params: [10]
});

await fetch(`${sandbox.url}/database/myapp/query`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    query: 'SELECT status, COUNT(*) as count FROM users GROUP BY status',
    params: []
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/query \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT status, COUNT(*) FROM users GROUP BY status"
  }'

Execute Transaction

Run multiple queries atomically - all succeed or all fail.

await sandbox.database.executeTransaction({
  database: 'myapp',
  queries: [
    {
      query: 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      params: ['Alice Brown', 'alice@example.com', 28]
    },
    {
      query: 'UPDATE accounts SET balance = balance - ? WHERE user_id = ?',
      params: [100, 1]
    },
    {
      query: 'INSERT INTO transactions (user_id, amount, type) VALUES (?, ?, ?)',
      params: [1, 100, 'withdrawal']
    }
  ]
});

Parameters:

database (string, required) - Database name
queries (array, required) - Array of query objects
- query (string) - SQL query
- params (array) - Query parameters

await fetch(`${sandbox.url}/database/myapp/transaction`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    queries: [
      {
        query: 'INSERT INTO users (name, email) VALUES (?, ?)',
        params: ['Alice', 'alice@example.com']
      },
      {
        query: 'UPDATE accounts SET balance = balance - ? WHERE user_id = ?',
        params: [100, 1]
      }
    ]
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/transaction \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "queries": [
      {
        "query": "INSERT INTO users (name, email) VALUES (?, ?)",
        "params": ["Alice", "alice@example.com"]
      }
    ]
  }'

Response:

{
  "success": true,
  "message": "Transaction completed successfully",
  "queriesExecuted": 3
}

Transaction Example

try {
  // Transfer money between accounts atomically
  await sandbox.database.executeTransaction({
    database: 'myapp',
    queries: [
      {
        query: 'UPDATE accounts SET balance = balance - ? WHERE id = ?',
        params: [500, 1] // Deduct from account 1
      },
      {
        query: 'UPDATE accounts SET balance = balance + ? WHERE id = ?',
        params: [500, 2] // Add to account 2
      },
      {
        query: `
          INSERT INTO transactions (from_account, to_account, amount, timestamp)
          VALUES (?, ?, ?, datetime('now'))
        `,
        params: [1, 2, 500]
      }
    ]
  });
  
  console.log('Transfer completed successfully');
} catch (error) {
  console.error('Transaction failed:', error.message);
  // All queries will be rolled back automatically
}

try {
  const response = await fetch(`${sandbox.url}/database/myapp/transaction`, {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${sandbox.token}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      queries: [
        {
          query: 'UPDATE accounts SET balance = balance - ? WHERE id = ?',
          params: [500, 1]
        },
        {
          query: 'UPDATE accounts SET balance = balance + ? WHERE id = ?',
          params: [500, 2]
        }
      ]
    })
  });
  
  const result = await response.json();
  if (!result.success) {
    console.error('Transaction failed');
  }
} catch (error) {
  console.error('Error:', error);
}

curl -X POST https://sandbox.oblien.com/database/myapp/transaction \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "queries": [
      {
        "query": "UPDATE accounts SET balance = balance - ? WHERE id = ?",
        "params": [500, 1]
      },
      {
        "query": "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        "params": [500, 2]
      }
    ]
  }'

Advanced Query Patterns

Subqueries

const result = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT name, email
    FROM users
    WHERE id IN (
      SELECT user_id 
      FROM posts 
      WHERE created_at > datetime('now', '-7 days')
    )
  `
});

CASE Statements

const categorized = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT 
      name,
      age,
      CASE
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
      END as age_category
    FROM users
  `
});

Window Functions

const ranked = await sandbox.database.executeQuery({
  database: 'myapp',
  query: `
    SELECT 
      name,
      score,
      RANK() OVER (ORDER BY score DESC) as rank
    FROM users
    WHERE status = 'active'
  `
});

Best Practices

Always use parameterized queries:

// ✅ Good
executeQuery({
  query: 'SELECT * FROM users WHERE id = ?',
  params: [userId]
})

// ❌ Bad - SQL injection risk
executeQuery({
  query: `SELECT * FROM users WHERE id = ${userId}`
})

Use transactions for related operations:
- Financial operations
- Multi-table updates
- Data consistency requirements
Optimize queries:
- Use indexes for WHERE clauses
- Avoid SELECT * when possible
- Use EXPLAIN to analyze query plans

Handle errors gracefully:

try {
  const result = await sandbox.database.executeQuery({...});
} catch (error) {
  console.error('Query failed:', error.message);
  // Handle error appropriately
}

Limit result sets:

const result = await sandbox.database.executeQuery({
  database: 'myapp',
  query: 'SELECT * FROM users ORDER BY created_at DESC LIMIT ?',
  params: [100]
});

Next Steps

Database Operations - Basic database operations
Database Tables - Schema management
Database Import/Export - Data migration

On this page