Database Tables

Manage tables, schemas, indexes, and constraints in your Sandbox databases.

List Tables

Get all tables in a database.

const result = await sandbox.database.listTables('myapp');
console.log(result.tables);

const response = await fetch(`${sandbox.url}/database/myapp/tables`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const result = await response.json();

curl https://sandbox.oblien.com/database/myapp/tables \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "tables": [
    {
      "name": "users",
      "columns": 5,
      "rows": 150,
      "indexes": 2
    },
    {
      "name": "posts",
      "columns": 7,
      "rows": 450,
      "indexes": 3
    }
  ]
}

Get Table Schema

Get detailed schema information for a table.

const schema = await sandbox.database.getTableSchema('myapp', 'users');
console.log(schema.columns);

const response = await fetch(`${sandbox.url}/database/myapp/tables/users/schema`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const schema = await response.json();

curl https://sandbox.oblien.com/database/myapp/tables/users/schema \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "table": "users",
  "columns": [
    {
      "cid": 0,
      "name": "id",
      "type": "INTEGER",
      "notnull": 1,
      "dflt_value": null,
      "pk": 1
    },
    {
      "cid": 1,
      "name": "name",
      "type": "TEXT",
      "notnull": 1,
      "dflt_value": null,
      "pk": 0
    },
    {
      "cid": 2,
      "name": "email",
      "type": "TEXT",
      "notnull": 1,
      "dflt_value": null,
      "pk": 0
    }
  ]
}

Get Full Schema

Get comprehensive schema information including indexes and foreign keys.

const fullSchema = await sandbox.database.getTableSchemaFull('myapp', 'users');
console.log(fullSchema);

const response = await fetch(`${sandbox.url}/database/myapp/tables/users/schema/full`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const fullSchema = await response.json();

curl https://sandbox.oblien.com/database/myapp/tables/users/schema/full \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "table": "users",
  "columns": [...],
  "indexes": [...],
  "foreignKeys": [...]
}

Rename Table

Rename an existing table.

await sandbox.database.renameTable({
  database: 'myapp',
  table: 'users',
  newName: 'customers'
});

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

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

Response:

{
  "success": true,
  "message": "Table renamed successfully",
  "oldName": "users",
  "newName": "customers"
}

Drop Table

Delete a table and all its data.

await sandbox.database.dropTable('myapp', 'old_table');

await fetch(`${sandbox.url}/database/myapp/tables/old_table/drop`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

curl -X POST https://sandbox.oblien.com/database/myapp/tables/old_table/drop \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "message": "Table dropped successfully",
  "table": "old_table"
}

Add Column

Add a new column to an existing table.

await sandbox.database.addColumn({
  database: 'myapp',
  table: 'users',
  name: 'status',
  type: 'TEXT',
  defaultValue: 'active',
  notNull: false
});

Parameters:

database (string, required) - Database name
table (string, required) - Table name
name (string, required) - Column name
type (string, required) - Column type
defaultValue (any) - Default value
notNull (boolean) - NOT NULL constraint
unique (boolean) - UNIQUE constraint

await fetch(`${sandbox.url}/database/myapp/tables/users/columns/add`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    name: 'status',
    type: 'TEXT',
    defaultValue: 'active'
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/tables/users/columns/add \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "status",
    "type": "TEXT",
    "defaultValue": "active"
  }'

Response:

{
  "success": true,
  "message": "Column added successfully",
  "table": "users",
  "column": "status"
}

Manage Indexes

List Indexes

const indexes = await sandbox.database.listIndexes('myapp', 'users');
console.log(indexes.indexes);

const response = await fetch(`${sandbox.url}/database/myapp/tables/users/indexes`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const indexes = await response.json();

curl https://sandbox.oblien.com/database/myapp/tables/users/indexes \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "indexes": [
    {
      "name": "idx_email",
      "unique": 1,
      "columns": ["email"]
    },
    {
      "name": "idx_name",
      "unique": 0,
      "columns": ["name"]
    }
  ]
}

Create Index

await sandbox.database.createIndex({
  database: 'myapp',
  table: 'users',
  name: 'idx_email',
  columns: ['email'],
  unique: true
});

Parameters:

database (string, required) - Database name
table (string, required) - Table name
name (string, required) - Index name
columns (array, required) - Columns to index
unique (boolean) - Create unique index

await fetch(`${sandbox.url}/database/myapp/tables/users/indexes/create`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    name: 'idx_email',
    columns: ['email'],
    unique: true
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/tables/users/indexes/create \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "idx_email",
    "columns": ["email"],
    "unique": true
  }'

Response:

{
  "success": true,
  "message": "Index created successfully",
  "index": "idx_email",
  "table": "users"
}

Drop Index

await sandbox.database.dropIndex('myapp', 'idx_email');

await fetch(`${sandbox.url}/database/myapp/indexes/idx_email/drop`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

curl -X POST https://sandbox.oblien.com/database/myapp/indexes/idx_email/drop \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "message": "Index dropped successfully",
  "index": "idx_email"
}

Foreign Keys

List Foreign Keys

const fks = await sandbox.database.listForeignKeys('myapp', 'posts');
console.log(fks.foreignKeys);

const response = await fetch(`${sandbox.url}/database/myapp/tables/posts/foreign-keys`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const fks = await response.json();

curl https://sandbox.oblien.com/database/myapp/tables/posts/foreign-keys \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "foreignKeys": [
    {
      "id": 0,
      "table": "users",
      "from": "user_id",
      "to": "id",
      "on_update": "NO ACTION",
      "on_delete": "CASCADE"
    }
  ]
}

Toggle Foreign Key Constraints

// Enable foreign keys
await sandbox.database.setForeignKeyConstraints({
  database: 'myapp',
  enabled: true
});

// Disable foreign keys
await sandbox.database.setForeignKeyConstraints({
  database: 'myapp',
  enabled: false
});

await fetch(`${sandbox.url}/database/myapp/foreign-keys/toggle`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    enabled: true
  })
});

curl -X POST https://sandbox.oblien.com/database/myapp/foreign-keys/toggle \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"enabled": true}'

Response:

{
  "success": true,
  "message": "Foreign key constraints enabled",
  "enabled": true
}

Get Foreign Key Status

const status = await sandbox.database.getForeignKeyStatus('myapp');
console.log(status.enabled);

const response = await fetch(`${sandbox.url}/database/myapp/foreign-keys/status`, {
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const status = await response.json();

curl https://sandbox.oblien.com/database/myapp/foreign-keys/status \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "enabled": true
}

Analyze Table

Analyze table statistics for query optimization.

const analysis = await sandbox.database.analyzeTable('myapp', 'users');
console.log(analysis);

const response = await fetch(`${sandbox.url}/database/myapp/tables/users/analyze`, {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${sandbox.token}`
  }
});

const analysis = await response.json();

curl -X POST https://sandbox.oblien.com/database/myapp/tables/users/analyze \
  -H "Authorization: Bearer YOUR_SANDBOX_TOKEN"

Response:

{
  "success": true,
  "message": "Table analyzed successfully",
  "table": "users",
  "analyzed": true
}

Best Practices

Create indexes strategically:
- Index columns used in WHERE, JOIN, and ORDER BY clauses
- Don't over-index as it slows down INSERT/UPDATE operations
- Use composite indexes for multi-column searches
Use meaningful names:
- Name indexes clearly: idx_tablename_columnname
- Use descriptive table and column names
Schema versioning:
- Export schema before major changes
- Document schema changes
- Use migrations for production
Foreign key constraints:
- Enable for data integrity
- Use CASCADE carefully
- Document relationships
Regular analysis:
- Run ANALYZE periodically for large tables
- Monitor index usage
- Remove unused indexes

Next Steps

Database Operations - Basic database operations
Database Queries - Advanced queries
Database Import/Export - Data migration

On this page