# rds-database

Part of **RDS**

# ApsaraDB RDS Database Management

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Manage Database | Synchronous | Create, delete, modify, and query databases and their properties within RDS instances. |
| SQL Audit | Synchronous | Query the status of SQL Explorer (SQL Audit) feature. |
| Manage MariaDB Instance | Synchronous | Handle MariaDB-specific database management operations through APIs. |
| Perform Geometric Operations | Synchronous | Comprehensive reference for all geometric SQL functions available in the database. |
| Create Chunk Tables | Synchronous | Create specialized chunk tables for managing large spatial or raster datasets. |
| Create INDEXSURFACE Geometry | Synchronous | Create INDEXSURFACE geometry type objects. |
| Create TRIANGLEFAN Geometry | Synchronous | Create TRIANGLEFAN geometry type objects. |
| Execute SQL Queries | Synchronous | Run standard and trajectory-specific SQL queries against the database. |
| Batch Execute SQL Statements | Synchronous | Execute multiple SQL statements in a single batch operation. |
| Fetch Query Results | Synchronous | Retrieve results from database queries. |
| Calculate Array Similarity | Synchronous | Use the smlar extension to compute similarity between arrays. |
| Execute SQL Query | Synchronous | Execute SQL queries using the SfMesh SQL engine. |

## API Calling Patterns

### Authentication
The primary authentication method is **Bearer Token** via HTTP header.

- **Header format**: `Authorization: Bearer <your_api_key>`
- **Environment variable**: `ALIYUN_ACCESS_KEY_ID` (or `ALIBABA_CLOUD_ACCESS_KEY_ID`)
- While other methods exist (e.g., query parameters), the Bearer Token header is recommended for security and consistency.

### Service Endpoint (Endpoint)
APIs use region-specific endpoints following this pattern:

```text
https://rds.{region}.aliyuncs.com
```

Common regions include:
- `cn-hangzhou`
- `cn-shanghai`
- `cn-beijing`

Some specialized APIs (e.g., Data API) use different base URLs like `https://rds-data.api.alibabacloud.com`.

### Synchronous Pattern
All RDS Database Management APIs follow a synchronous request-response model:

1. Client sends an HTTP request (GET or POST) to the service endpoint with required parameters
2. Server processes the request immediately
3. Server returns a JSON/XML response with results or error details
4. No polling or async handling is needed

Key characteristics:
- Single round-trip communication
- Immediate response (typically < 1 second for simple operations)
- Standard HTTP status codes (200 for success, 4xx/5xx for errors)
- Common parameters include `Action`, `DBInstanceId`, and engine-specific options

## Parameter Reference

### Manage Database

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| Action | String | Yes | - | - | The operation that you want to perform. Set the value to CreateDatabase/DeleteDatabase/ModifyDBDescription/etc. |
| DBInstanceId | String | Yes | - | - | The ID of the instance. |
| DBName | String | Yes | - | 2 to 64 characters; starts with lowercase letter; contains lowercase letters, digits, underscores (_), hyphens (-) | The name of the database. |
| CharacterSetName | String | Yes | - | Engine-specific values (e.g., utf8, gbk for MySQL) | The character set. |
| DBDescription | String | No | - | 2 to 256 characters; cannot start with http:// or https:// | The description of the database. |
| ResourceGroupId | String | No | - | - | The ID of the resource group. |
| PageSize | Integer | No | 30 | One of: 30, 50, 100 | The number of entries to return on each page. |
| PageNumber | Integer | No | 1 | Positive integer | The number of the page to return. |

### Batch Execute SQL Statements

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| SecretArn | string | No | - | - | - |
| ResourceArn | string | No | - | - | - |
| Database | string | No | - | - | - |
| Sql | string | No | - | - | - |
| TransactionId | string | No | - | - | - |
| ParameterSets | array | No | - | - | - |

### Calculate Array Similarity

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| useIntersect | bool | No | - | - | Controls whether only common elements or all elements are used in similarity calculation. |
| formula | text | No | - | - | A custom formula to calculate similarity, referencing predefined variables N.i, N.a, and N.b. |

### Query Supported Character Sets

| Parameter | Type | Required | Default | Constraints | Description |
|-----------|------|----------|---------|-------------|-------------|
| Engine | String | Yes | - | One of: mysql, mssql, PostgreSQL, MariaDB | The database engine that is run on the instance. |
| RegionId | String | Yes | - | - | The region ID of the instance. |

## Code Examples

### Create Database - Bash - All Regions

```bash
http(s)://rds.aliyuncs.com/?Action=CreateDatabase
&DBInstanceId=rm-uf6wjk5xxxxxxxxxx
&DBName=rds_mysql
&CharacterSetName=gbk
<&lt;Common request parameters&gt;
```

### Check SQL Audit Status - Python - All Regions

```python
from aliyunsdkcore.client import AcsClient
from aliyunsdkrds.request.v20140815 import DescribeSQLCollectorPolicyRequest

client = AcsClient('<your-access-key-id>', '<your-access-key-secret>', 'cn-hangzhou')

request = DescribeSQLCollectorPolicyRequest.DescribeSQLCollectorPolicyRequest()
request.set_DBInstanceId('rm-uf6wjk5xxxxxxx')

response = client.do_action_with_exception(request)
print(response)
```

### Calculate Array Similarity - SQL - All Regions

```sql
testdb=> SELECT smlar('{1,4,6}'::int[], '{5,4,6}');
  smlar
----------
 0.666667
(1 row)
```

### Create Chunk Table - SQL - All Regions

```sql
SELECT ST_CreateChunkTable('t_chunk');
```

### Delete Database - Bash - All Regions

```bash
http(s)://rds.aliyuncs.com/? Action=DeleteDatabase
&DBInstanceId=rm-uf6wjk5xxxxxxx 
&DBName=testdb01
<&lt;Common request parameters&gt;
```

### Query Database Information - Bash - All Regions

```bash
http(s)://rds.aliyuncs.com/?Action=DescribeDatabases
&DBInstanceId=rm-uf6wjk5xxxxxxx
<&lt;Common request parameters&gt;
```

### Replicate Databases - Bash - All Regions

```bash
http(s)://rds.aliyuncs.com/?Action=CopyDatabaseBetweenInstances
&DBInstanceId=rm-uf6wjk5xxxxxxx
&TargetDBInstanceId=rm-ut5ajk3xxxxxxx
&DbNames={"test1":"newtest1","test2":"newtest2"}
&BackupId=106523874****
&RestoreTime=2011-06-11T16:00:00Z
&SyncUserPrivilege=NO
&Common request parameters
```

## Response Format

```json
{
  "RequestID" : "5A77D650-27A1-4E08-AD9E-59008EDB6927"
}
```

**Key Fields**:
- `RequestID` — Unique identifier for the API request
- `SQLCollectorStatus` — Status of SQL Explorer feature (Enable/Disable)
- `Databases.Database.Engine` — Database engine type
- `Databases.Database.DBName` — Name of the database
- `Databases.Database.DBStatus` — Current status (Creating, Running, Deleting)
- `CharacterSetNameItems.CharacterSetName` — Supported character sets as JSON array

## Error Handling

| Error Code | Description | Recommended Action |
|------------|-------------|-------------------|
| 400 | EngineMigration.ActionDisabled: Specified action is disabled while custins is in engine migration. | Wait until engine migration completes before performing the operation. |
| 400 | Database.AddError: Create Db failed, please check input value and instance status. | Verify input parameters meet constraints and instance is in Running state. |
| 400 | Database.ConnectError: Database connect error. please check instance status and database processlist. | Check instance status and ensure database connections aren't exhausted. |
| 404 | The specified instance or database does not exist. | Verify DBInstanceId and DBName values are correct. |
| 403 | Permission denied. Ensure the user has the required permissions. | Assign appropriate RAM permissions to the user/account. |
| 500 | Internal server error. | Retry after short delay; contact support if persistent. |

### Rate Limits & Retry
- Standard QPS limit: 100 requests per second per account
- Some operations have lower limits (e.g., 10 QPS for batch operations)
- Implement exponential backoff for retries (start with 1s delay, double each attempt)
- Respect `Retry-After` header if present in 429 responses

## Environment Requirements

- **Python SDK**: `aliyun-python-sdk-rds>=2.1.0`
- **Environment variable**: `export ALIYUN_ACCESS_KEY_ID=your_access_key`
- **Authentication**: Valid Alibaba Cloud credentials with RDS management permissions

## FAQ

Q: How do I authenticate API requests to RDS?
A: Use the Bearer Token authentication method by including `Authorization: Bearer <your_api_key>` in the request header. Store your API key securely in environment variables like `ALIYUN_ACCESS_KEY_ID`.

Q: What's the difference between synchronous and asynchronous RDS operations?
A: All database management APIs in this skill are synchronous - they return immediate responses. Asynchronous operations (like major version upgrades) exist in other RDS domains but aren't covered here.

Q: Can I execute arbitrary SQL through these APIs?
A: Yes, through the BatchExecuteStatement API which supports parameterized queries. However, direct SQL execution requires proper database credentials and network access to the RDS instance.

Q: How do I handle pagination for large result sets?
A: Use `PageSize` (30/50/100) and `PageNumber` parameters in query operations like DescribeDatabases. Iterate through pages until all results are retrieved.

Q: Are there free tier allowances for these APIs?
A: Most database management APIs include 1000 free calls per month. Check specific pricing details for each operation as some may have different quotas.

## Pricing & Billing

### Billing Model
Per-request billing - each API call counts as one request regardless of success/failure or response size.

### Price Reference

| Tier | Input Price | Output Price |
|------|-------------|--------------|
| default | 0.0001 / | 0.0001 / |
| standard | 0.0001 / | 0.0001 / |
| standard | 0.001 / | 0.001 / |

### Free Tier
Monthly free allowance of 1000 API calls for most operations.

### Usage Limits
- Standard QPS: 100 requests per second per account
- Batch operations: 10 requests per second maximum
- Single query size limit: 10,000 characters (for SfMesh queries)

### Billing Notes
- Each API call is billed as one request, even if it fails
- Free tier resets monthly
- Additional storage or compute resources used during operations (like database replication) may incur separate charges