### Install PlanetScale CLI

Source: https://github.com/planetscale/database-skills/blob/main/website/index.html

Use this command to install the PlanetScale CLI. Ensure you have Homebrew installed.

```bash
#!/bin/bash

if ! command -v brew &> /dev/null
then
    echo "Homebrew not found. Please install it from https://brew.sh/"
    exit 1
fi

brew install planetscale/tap/planetscale
```

--------------------------------

### Install Block Styling

Source: https://github.com/planetscale/database-skills/blob/main/website/index.html

CSS for the installation block, including styling for labels, the code block itself, and hover effects. It also styles the copy button and feedback message.

```css
/* ─── Install block ─── */ .install-section { text-align: center; text-wrap: balance; margin-top: 3rem; margin-bottom: 3rem; } .install-label { font-family: var(--mono); font-size: 0.75rem; text-transform: uppercase; letter-spacing: 0.2em; color: var(--text-tertiary); margin-bottom: 1rem; } .install-block { display: inline-flex; align-items: center; gap: 1rem; background: var(--bg-card); border: 1px solid var(--border); border-radius: 8px; padding: 0.875rem 1.25rem; transition: border-color 0.2s; cursor: pointer; } .install-block:hover { border-color: var(--border-hover); } .install-block code { font-family: var(--mono); font-size: 0.9rem; color: var(--text-primary); } .install-block code .prompt { color: var(--text-tertiary); } .install-block code .cmd { color: var(--text-secondary); } .install-block code .arg { color: var(--ps-orange); } .copy-btn { background: none; border: none; cursor: pointer; color: var(--text-tertiary); padding: 4px; border-radius: 4px; display: flex; align-items: center; justify-content: center; transition: color 0.2s, background 0.2s; } .copy-btn:hover { color: var(--text-primary); background: rgba(255, 255, 255, 0.05); } .copy-btn svg { width: 16px; height: 16px; } .copy-feedback { font-family: var(--mono); font-size: 0.75rem; color: var(--ps-orange); margin-top: 0.5rem; }
```

--------------------------------

### JavaScript for Copying Install Command

Source: https://github.com/planetscale/database-skills/blob/main/website/index.html

This JavaScript function copies the installation command to the clipboard and provides user feedback. It also includes event listeners for keyboard accessibility.

```javascript
function copyInstall() {
  const cmd = 'npx skills add planetscale/database-skills';
  navigator.clipboard.writeText(cmd).then(() => {
    const el = document.getElementById('copyFeedback');
    el.textContent = 'Copied to clipboard!';
    setTimeout(() => {
      el.textContent = '';
    }, 2000);
  });
}

// Also handle Enter/Space on the install block for keyboard accessibility
document.querySelector('.install-block').addEventListener('keydown', (e) => {
  if (e.key === 'Enter' || e.key === ' ') {
    e.preventDefault();
    copyInstall();
  }
});
```

--------------------------------

### VSchema with Shared Vindex Example

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vschema.md

Illustrates how to configure tables to shard on the same column using a shared vindex for co-located data and single-shard operations.

```json
{
  "sharded": true,
  "vindexes": { "xxhash": { "type": "xxhash" } },
  "tables": {
    "customer": { "column_vindexes": [{ "column": "customer_id", "name": "xxhash" }] },
    "orders":   { "column_vindexes": [{ "column": "customer_id", "name": "xxhash" }] }
  }
}
```

--------------------------------

### Shard-Local Join Example

Source: https://github.com/planetscale/database-skills/blob/main/skills/neki/references/sharding-readiness.md

Demonstrates a correct shard-local join by including the shard key (`tenant_id`) in the join condition. This ensures the join operation is performed within a single shard.

```sql
SELECT o.id, oi.product_id FROM orders o
JOIN order_items oi ON oi.tenant_id = o.tenant_id AND oi.order_id = o.id
WHERE o.tenant_id = $1;
```

--------------------------------

### Add PlanetScale Database Skills

Source: https://github.com/planetscale/database-skills/blob/main/README.md

Use this command to add the database skills repository to your skills.sh environment. Ensure skills.sh is installed and configured.

```bash
npx skills add planetscale/database-skills
```

--------------------------------

### Next-Key Lock Example

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/row-locking-gotchas.md

Demonstrates how `SELECT ... FOR UPDATE` with a range scan acquires next-key locks, locking rows within the range and the gaps between them. Concurrent inserts into these gaps will be blocked.

```sql
-- Locks rows with id 5..10 AND the gaps between them and after the range
SELECT * FROM orders WHERE id BETWEEN 5 AND 10 FOR UPDATE;
-- Another session inserting id=7 blocks until the lock is released.
```

--------------------------------

### Vitess Query Routing Examples

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/query-serving.md

Demonstrates single-shard and scatter query routing. Always include the primary vindex column in WHERE clauses to avoid expensive scatter queries.

```sql
SELECT * FROM orders WHERE customer_id = 42;          -- single-shard (fast)
```

```sql
SELECT * FROM orders WHERE order_date > '2025-01-01'; -- scatter (slow)
```

--------------------------------

### Interpreting Composite Index Usage with key_len

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/explain-analysis.md

This example illustrates how to interpret the `key_len` value in EXPLAIN output to determine how much of a composite index is being used. The calculation depends on column types and nullability.

```sql
-- Index: (status TINYINT, created_at DATETIME)
-- key_len=2 → only status (1+1 null). key_len=8 → both columns used.
```

--------------------------------

### SELECT ... FOR SHARE Example

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/row-locking-gotchas.md

Demonstrates the use of `SELECT ... FOR SHARE` to acquire shared (S) locks, allowing multiple sessions to read concurrently. An `UPDATE` statement attempting to acquire an exclusive (X) lock on the same row will be blocked until shared locks are released.

```sql
-- Session 1: shared lock
SELECT * FROM orders WHERE id = 5 FOR SHARE;

-- Session 2: also allowed (shared lock)
SELECT * FROM orders WHERE id = 5 FOR SHARE;

-- Session 3: blocked until shared locks are released
UPDATE orders SET status = 'processed' WHERE id = 5;
```

--------------------------------

### Create Materialize Workflow

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vreplication.md

Use `vtctldclient` to create a Materialize workflow, setting up continuously-updated materialized views. This example shows creating a sales summary view with a custom SQL expression and table schema.

```bash
vtctldclient Materialize --workflow mat1 --target-keyspace reporting \
  create --source-keyspace commerce --table-settings '[{
    "target_table": "sales_summary",
    "source_expression": "SELECT region, SUM(total) as total_sales FROM orders GROUP BY region",
    "create_ddl": "CREATE TABLE sales_summary (region VARCHAR(64), total_sales DECIMAL(10,2), PRIMARY KEY (region))"
  }]'
```

--------------------------------

### Create Composite Index for Equality and Range Predicates

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/composite-indexes.md

Create a composite index on `tenant_id`, `status`, and `created_at` to efficiently handle queries filtering by tenant and status, and then by a range on `created_at`. The index supports queries starting with `tenant_id` and `status` equality checks.

```sql
-- Query: WHERE tenant_id = ? AND status = ? AND created_at > ?
CREATE INDEX idx_orders_tenant_status_created ON orders (tenant_id, status, created_at);
```

--------------------------------

### Create a Covering Index

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/covering-indexes.md

Define a covering index by listing filter columns first, followed by the columns needed for the query's SELECT list. This example assumes a query selecting user_id, status, and total where user_id is filtered.

```sql
CREATE INDEX idx_orders_cover ON orders (user_id, status, total);
```

--------------------------------

### Composite Primary Key for Many-to-Many Tables

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/primary-keys.md

This example demonstrates a composite primary key for a join table (e.g., user_roles). The most frequently queried column should be listed first in the composite key for optimal performance.

```sql
CREATE TABLE user_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (user_id, role_id)
);
```

--------------------------------

### Index JSON Data with Generated Columns

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/json-column-patterns.md

Create a virtual generated column to index JSON data, as direct indexing of JSON columns is not supported. This example adds an index on the 'type' field extracted from the 'data' JSON column.

```sql
ALTER TABLE events
  ADD COLUMN event_type VARCHAR(50) GENERATED ALWAYS AS (data->>'$.type') VIRTUAL,
  ADD INDEX idx_event_type (event_type);
```

--------------------------------

### Get branch schema

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-api-insights.md

Fetch the current schema definition for a specific database branch. This is helpful for understanding the database structure.

```bash
pscale api "organizations/{org}/databases/{db}/branches/{branch}/schema"
```

--------------------------------

### PlanetScale CLI Database Management

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for listing and creating databases.

```bash
# Databases
pscale database list
```

```bash
pscale database create <name>
```

--------------------------------

### Get Relation Size in PostgreSQL

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/storage-layout.md

Calculates the disk space used by a specific table or index. Useful for identifying large objects.

```sql
SELECT pg_relation_size('tablename');
```

--------------------------------

### Basic pscale api command structure

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-api-insights.md

This is the general syntax for using the `pscale api` command. It allows authenticated API calls using your current login or a service token.

```bash
pscale api "<endpoint>" [--method POST] [--field key=value] [--org <org>]
```

--------------------------------

### Get Database Size in PostgreSQL

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/storage-layout.md

Calculates the total disk space used by a specific database. Useful for monitoring storage consumption.

```sql
SELECT pg_database_size('dbname');
```

--------------------------------

### Discover VSchema with SQL Commands

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vschema.md

Lists tables and vindexes known to VTGate, or shows table creation statements including vindex information, from a VTGate MySQL session.

```sql
-- From a VTGate MySQL session
SHOW VSCHEMA TABLES;           -- list tables known to the VSchema
SHOW VSCHEMA VINDEXES;         -- list vindexes and their types
SHOW CREATE TABLE <table>;     -- includes vindex column info in comments
```

--------------------------------

### Create GIN and BRIN Indexes in PostgreSQL

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/indexing.md

Demonstrates creating GIN indexes for JSONB or array data and BRIN indexes for large, physically ordered time-series data.

```sql
CREATE INDEX metadata_idx ON order USING GIN (metadata);       -- JSONB
```

```sql
CREATE INDEX event_created_idx ON event USING BRIN (created_at); -- time-series
```

--------------------------------

### Add Fulltext Index and Perform Searches

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/fulltext-indexes.md

Demonstrates how to add a fulltext index to a table and perform searches using natural language and boolean modes. Ensure MATCH() columns correspond to the index definition.

```sql
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_body (title, body);
```

```sql
-- Natural language (default, sorted by relevance)
SELECT *, MATCH(title, body) AGAINST('database performance') AS score
FROM articles WHERE MATCH(title, body) AGAINST('database performance');
```

```sql
-- Boolean mode: + required, - excluded, * suffix wildcard, "exact phrase"
WHERE MATCH(title, body) AGAINST('+mysql -postgres +optim*' IN BOOLEAN MODE);
```

--------------------------------

### Get WAL Size

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/wal-operations.md

Use this query to determine the number of WAL files and their total size. This helps in managing disk space and identifying potential issues with WAL accumulation.

```sql
SELECT count(*) AS files, pg_size_pretty(sum(size)) AS total FROM pg_ls_waldir();
```

--------------------------------

### Create, Monitor, and Complete MoveTables Workflow

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vreplication.md

Use `vtctldclient` to create a MoveTables workflow, monitor its status, verify data with VDiff, switch traffic, and complete the workflow. Key flags like `--on-ddl` and `--defer-secondary-keys` can be used for specific scenarios.

```bash
# Create workflow
vtctldclient MoveTables --workflow mv1 --target-keyspace customer \
  create --source-keyspace commerce --tables "customer,orders"

# Monitor, verify, switch, complete
vtctldclient MoveTables --workflow mv1 --target-keyspace customer status
vtctldclient VDiff --workflow mv1 --target-keyspace customer create
vtctldclient MoveTables --workflow mv1 --target-keyspace customer switchtraffic
vtctldclient MoveTables --workflow mv1 --target-keyspace customer complete
```

--------------------------------

### Create and Show VDiff

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vreplication.md

Use `vtctldclient` to create a VDiff workflow for data consistency verification between source and target, and then show the results of the last VDiff operation.

```bash
vtctldclient VDiff --workflow mv1 --target-keyspace customer create
vtctldclient VDiff --workflow mv1 --target-keyspace customer show last
```

--------------------------------

### MySQL DATETIME for Timestamps

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/data-types.md

Use DATETIME for storing timestamps, especially when dates may exceed the 2038 limit of TIMESTAMP. This example shows setting creation and update timestamps.

```sql
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
```

--------------------------------

### Get Total Relation Size in PostgreSQL

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/storage-layout.md

Calculates the total disk space used by a table, including its indexes and TOAST data. Essential for understanding the full storage footprint of a relation.

```sql
SELECT pg_total_relation_size('tablename');
```

--------------------------------

### Order Table with CHECK Constraint for Status

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/schema-design.md

Prefer CHECK constraints over custom ENUM types for data validation as they are easier to modify. This example enforces valid status values for an order.

```sql
CREATE TABLE order (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered'))
);
```

--------------------------------

### Create a new query patterns report

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-api-insights.md

Use this command to initiate the creation of a new query patterns report for a specific database branch. Replace placeholders like `{org}`, `{db}`, and `{branch}` with your actual values.

```bash
pscale api "organizations/{org}/databases/{db}/branches/{branch}/query-patterns-reports" \
  --method POST --org my-org
```

--------------------------------

### Detach and Drop Partitions

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/partitioning.md

Demonstrates detaching a partition concurrently (PG 14+) and then dropping the detached partition. These are destructive operations and require careful human confirmation.

```sql
-- DESTRUCTIVE: confirm with a human before executing
ALTER TABLE event DETACH PARTITION event_2025_01 CONCURRENTLY;
DROP TABLE event_2025_01;
```

--------------------------------

### Index JSON Arrays with Multi-Valued Indexes

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/json-column-patterns.md

For MySQL 8.0.17+, use multi-valued indexes to index elements within JSON arrays. This example indexes an array stored in the 'tags' column.

```sql
ALTER TABLE products
  ADD INDEX idx_tags ((CAST(tags AS CHAR(50) ARRAY)));
```

--------------------------------

### Connecting to Dedicated Primary PgBouncer

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-connection-pooling.md

Connect to a dedicated primary PgBouncer by appending `|pgbouncer-name` to your username. This is recommended for production OLTP write traffic.

```bash
# Dedicated primary PgBouncer (append |pgbouncer-name to user)
psql 'host=xxx.horizon.psdb.cloud port=6432 user=postgres.xxx|write-pool password=pscale_pw_xxx dbname=mydb sslnegotiation=direct sslmode=verify-full sslrootcert=system'
```

--------------------------------

### Create PostgreSQL Extension

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-extensions.md

Use this SQL command to create an extension after it has been enabled in the PlanetScale Dashboard. The `IF NOT EXISTS` clause prevents errors if the extension is already present.

```sql
CREATE EXTENSION IF NOT EXISTS <extension_name>;
```

--------------------------------

### Apply Collation for String Comparisons

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/json-column-patterns.md

Use the COLLATE clause when comparing extracted JSON string values if specific collation behavior is required. This example performs a case-sensitive, accent-sensitive comparison on a 'status' field.

```sql
WHERE data->>'$.status' COLLATE utf8mb4_0900_as_cs = 'Active'
```

--------------------------------

### PlanetScale CLI Service Token Management

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for creating and configuring service tokens for CI/CD, and setting them as environment variables.

```bash
# Create and configure
pscale service-token create
```

```bash
pscale service-token add-access <id> read_branch --database <db>
```

```bash
# Use in CI/CD
export PLANETSCALE_SERVICE_TOKEN_ID="<id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
```

--------------------------------

### Correctly Compare Numeric JSON Values

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/json-column-patterns.md

When comparing JSON values that represent numbers, cast the extracted value to a numeric type to avoid incorrect lexicographical string comparisons. This example compares a 'price' field.

```sql
WHERE CAST(data->>'$.price' AS UNSIGNED) <= 1200
```

--------------------------------

### Perform compressed pg_basebackup

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/backup-recovery.md

Create a compressed physical backup of a PostgreSQL cluster using pg_basebackup. The `-Ft` flag creates a tar archive, `-z` enables gzip compression, and `-P` shows progress.

```bash
pg_basebackup -Ft -z -P
```

--------------------------------

### Connecting to Local PgBouncer

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-connection-pooling.md

Use this command to connect to a local PgBouncer instance. It uses the same credentials as direct connections but directs traffic to port 6432.

```bash
# Local PgBouncer (same credentials, port 6432)
psql 'host=xxx.horizon.psdb.cloud port=6432 user=postgres.xxx password=pscale_pw_xxx dbname=mydb sslnegotiation=direct sslmode=verify-full sslrootcert=system'
```

--------------------------------

### PlanetScale CLI Authentication Commands

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for logging in, logging out, listing organizations, and switching between them.

```bash
pscale auth login                    # Opens browser
```

```bash
pscale auth logout
```

```bash
pscale org list
```

```bash
pscale org switch <name>
```

--------------------------------

### Show MySQL Connection Variables and Status

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/connection-management.md

Use these SQL commands to inspect current `max_connections` limit, the high-water mark for used connections, and the current number of active connections.

```sql
SHOW VARIABLES LIKE 'max_connections';         -- current limit
SHOW STATUS LIKE 'Max_used_connections';        -- high-water mark
SHOW STATUS LIKE 'Threads_connected';           -- current count
```

--------------------------------

### Create Reshard Workflow

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vreplication.md

Use `vtctldclient` to create a Reshard workflow, splitting shards. The process involves creating the workflow, verifying with VDiff, switching traffic, and completing the operation. Shard naming uses hex key ranges.

```bash
# Split 2 shards into 4
vtctldclient Reshard --workflow rs1 --target-keyspace customer \
  create --source-shards "-80,80-" --target-shards "-40,40-80,80-c0,c0-"

vtctldclient VDiff --workflow rs1 --target-keyspace customer create
vtctldclient Reshard --workflow rs1 --target-keyspace customer switchtraffic
vtctldclient Reshard --workflow rs1 --target-keyspace customer complete
```

--------------------------------

### PlanetScale CLI Utility Commands

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for checking latency, listing available regions, and managing backups.

```bash
# Other
pscale ping              # Check latency to regions
```

```bash
pscale region list       # Available regions
```

```bash
pscale backup list <db> <branch>
```

```bash
pscale backup create <db> <branch>
```

--------------------------------

### Order Table with Foreign Key and Index

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/schema-design.md

Define foreign keys with explicit ON DELETE actions like CASCADE or SET NULL. Always create an index on foreign key columns for performance.

```sql
CREATE TABLE order (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customer(id) ON DELETE CASCADE
);
CREATE INDEX order_customer_id_idx ON order (customer_id);
```

--------------------------------

### Connecting to Dedicated Replica PgBouncer

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-connection-pooling.md

Connect to a dedicated replica PgBouncer for read-only workloads. Append `|pgbouncer-name` to your username and use port 6432.

```bash
# Dedicated replica PgBouncer (append |pgbouncer-name to user)
psql 'host=xxx.horizon.psdb.cloud port=6432 user=postgres.xxx|read-bouncer password=pscale_pw_xxx dbname=mydb sslnegotiation=direct sslmode=verify-full sslrootcert=system'
```

--------------------------------

### Execute Schema Change via vtctldclient

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/schema-changes.md

Apply a schema change to a specific keyspace using the vtctldclient command-line tool. This method allows for remote execution and management.

```bash
vtctldclient ApplySchema --ddl-strategy "vitess" \
  --sql "ALTER TABLE demo MODIFY id BIGINT UNSIGNED" commerce
```

--------------------------------

### Discover VSchema with vtctldclient

Source: https://github.com/planetscale/database-skills/blob/main/skills/vitess/references/vschema.md

Retrieves the full VSchema JSON for a keyspace or lists defined vindexes using the vtctldclient command-line tool.

```bash
# Full VSchema JSON for a keyspace
vtctldclient GetVSchema <keyspace>

# List all vindexes defined in a keyspace
vtctldclient GetVSchema <keyspace> | jq '.vindexes'
```

--------------------------------

### PlanetScale CLI Branch Management

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for listing, creating, deleting, and retrieving schema information for branches.

```bash
# Branches
pscale branch list <db>
```

```bash
pscale branch create <db> <branch> [--from <parent>]
```

```bash
pscale branch delete <db> <branch>    # DESTRUCTIVE — always confirm with a human first
```

```bash
pscale branch schema <db> <branch>
```

--------------------------------

### Promote Standby to Primary (SQL)

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/replication.md

Promotes a standby server to become the new primary. This is a one-way operation; the promoted standby cannot rejoin as a standby without a rebuild.

```sql
SELECT pg_promote();
```

--------------------------------

### PlanetScale Postgres Connection String

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-connections.md

Use this connection string format for PlanetScale Postgres. Ensure you replace placeholders with your actual credentials and database name. The `sslmode=verify-full&sslrootcert=system&sslnegotiation=direct` parameters are crucial for secure connections.

```sql
postgresql://<user>:<password>@<host>.horizon.psdb.cloud:5432/<database>?sslmode=verify-full&sslrootcert=system&sslnegotiation=direct
```

--------------------------------

### PlanetScale CLI Connection Commands

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for opening a shell to a database or creating a secure proxy tunnel for GUI tools. The shell command opens psql for Postgres or mysql for Vitess.

```bash
# Connect
pscale shell <db> <branch>           # Opens psql (Postgres) or mysql (Vitess)
```

```bash
pscale connect <db> <branch>         # Proxy for GUI tools (secure tunnel) — Vitess only
```

--------------------------------

### PlanetScale CLI Credential Management

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-commands.md

Commands for creating roles in Postgres databases and passwords in Vitess databases.

```bash
# Credentials
pscale role create <db> <branch> <name>      # Postgres
```

```bash
pscale password create <db> <branch> <name>  # Vitess
```

--------------------------------

### Add Database Skills as Cursor Plugin

Source: https://github.com/planetscale/database-skills/blob/main/README.md

Integrate the database skills into your Cursor chat by adding this repository as a plugin. This command is used within the Cursor chat interface.

```bash
/add-plugin database-skills
```

--------------------------------

### Verify Covering Index with EXPLAIN

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/covering-indexes.md

Use EXPLAIN to check if a query is using a covering index. Look for 'Using index' in the Extra column, which signifies that the query was satisfied entirely from the index.

```sql
EXPLAIN SELECT user_id, status, total FROM orders WHERE user_id = 42;
```

--------------------------------

### Specify INSTANT Algorithm for ALTER TABLE

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/online-ddl.md

Use ALGORITHM=INSTANT to ensure a metadata-only change. This command will fail loudly if INSTANT is not possible, preventing silent fallback to a more blocking method like COPY.

```sql
ALTER TABLE orders ADD COLUMN note VARCHAR(255) DEFAULT NULL, ALGORITHM=INSTANT;
-- Fails loudly if INSTANT isn't possible, rather than silently falling back to COPY.
```

--------------------------------

### MySQL HASH/KEY Partitioning

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/partitioning.md

Partitions a table using HASH or KEY for even data distribution. Supports equality pruning but not range-based pruning.

```sql
PARTITION BY HASH (user_id) PARTITIONS 8;
```

--------------------------------

### Create Range Partitioned Table

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/partitioning.md

Define a parent table and its first two range partitions based on a timestamp. The partition key must be part of the primary key.

```sql
CREATE TABLE event (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at) -- Partition key MUST be part of PK
) PARTITION BY RANGE (created_at);

CREATE TABLE event_2026_01 PARTITION OF event
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE event_2026_02 PARTITION OF event
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
```

--------------------------------

### Download a completed query patterns report

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-api-insights.md

Once a report's status is complete, use this command to download the generated query patterns report. Ensure you have the correct report ID.

```bash
pscale api "organizations/{org}/databases/{db}/branches/{branch}/query-patterns-reports/{id}"
```

--------------------------------

### Configure WAL Archiving in PostgreSQL

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/backup-recovery.md

Enable WAL archiving in PostgreSQL by setting `archive_mode` to `on` and specifying a reliable `archive_command`. Ensure the command returns 0 only upon successful archiving to prevent data loss.

```postgresql
archive_mode=on
archive_command='test ! -f /archive/%f && cp %p /archive/%f'
```

--------------------------------

### MySQL Exchange Partition with Archive Table

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/partitioning.md

Archives a partition by exchanging it with a non-partitioned table that has an identical structure. The target table must not be partitioned.

```sql
CREATE TABLE events_archive LIKE events;
ALTER TABLE events_archive REMOVE PARTITIONING;
ALTER TABLE events EXCHANGE PARTITION p2025_q1 WITH TABLE events_archive;
```

--------------------------------

### Create List Partitioned Table

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/partitioning.md

Define a parent table and its list partitions for categorical data. Includes a default partition to catch unmatched values. The partition key must be part of the primary key.

```sql
CREATE TABLE order (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  region TEXT NOT NULL,
  total NUMERIC(10,2),
  PRIMARY KEY (id, region) -- Partition key MUST be part of PK
) PARTITION BY LIST (region);

CREATE TABLE order_us PARTITION OF order FOR VALUES IN ('us');
CREATE TABLE order_eu PARTITION OF order FOR VALUES IN ('eu');
CREATE TABLE order_default PARTITION OF order DEFAULT;  -- catches unmatched values
```

--------------------------------

### MySQL LIST COLUMNS Partitioning

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/partitioning.md

Partitions a table by LIST COLUMNS based on discrete categories. Ensure all possible values are covered to prevent errors.

```sql
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_americas VALUES IN ('us', 'ca', 'br'),
  PARTITION p_europe  VALUES IN ('uk', 'de', 'fr')
);
```

--------------------------------

### Perform parallel pg_dump backup

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/backup-recovery.md

Create a directory-format backup of a large PostgreSQL database using pg_dump with parallel processing enabled via the `-j` flag for faster execution.

```bash
pg_dump -Fd -j 4
```

--------------------------------

### Index and Unique Constraint Design for Sharding

Source: https://github.com/planetscale/database-skills/blob/main/skills/neki/references/sharding-readiness.md

Illustrates correct and incorrect index and unique constraint definitions for sharded tables. Indexes and unique constraints should lead with the shard key to ensure shard locality.

```sql
CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status, created_at);
ALTER TABLE orders ADD CONSTRAINT uq_order_number UNIQUE (tenant_id, order_number);
```

```sql
CREATE INDEX idx_orders_status ON orders (status, created_at);
ALTER TABLE orders ADD CONSTRAINT uq_order_number UNIQUE (order_number);
```

--------------------------------

### List all query patterns reports

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/ps-cli-api-insights.md

Retrieve a list of all previously generated query patterns reports for a given database branch. This is useful for managing or reviewing historical reports.

```bash
pscale api "organizations/{org}/databases/{db}/branches/{branch}/query-patterns-reports"
```

--------------------------------

### Monitor Checkpoint Statistics

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/wal-operations.md

Query checkpoint statistics to understand the frequency and type of checkpoints. This helps in tuning checkpoint parameters to balance recovery time and I/O load.

```sql
SELECT num_timed, num_requested, write_time, sync_time, buffers_written FROM pg_stat_checkpointer;
```

--------------------------------

### Monitor PgBouncer Connections

Source: https://github.com/planetscale/database-skills/blob/main/skills/postgres/references/pgbouncer-configuration.md

Use this SQL query to view active client backend connections grouped by database and username. This helps in understanding connection usage patterns.

```sql
SELECT datname, usename, COUNT(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY datname, usename;
```

--------------------------------

### Query Routing with Shard Key

Source: https://github.com/planetscale/database-skills/blob/main/skills/neki/references/sharding-readiness.md

Shows correct and incorrect query patterns for sharded tables. Always include the shard key (`tenant_id`) in the WHERE clause to route queries to a single shard.

```sql
SELECT * FROM orders WHERE tenant_id = $1 AND status = 'pending';
```

```sql
SELECT * FROM orders WHERE status = 'pending';
```

--------------------------------

### Basic CSS Reset and Variables

Source: https://github.com/planetscale/database-skills/blob/main/website/index.html

This CSS sets up a basic reset for margin, padding, and box-sizing, and defines custom properties for colors and fonts used throughout the page.

```css
*, *::before, *::after { margin: 0; padding: 0; box-sizing: border-box; } :root { --bg: #101112; --bg-elevated: #18191a; --bg-card: #141516; --border: #1e1f21; --border-hover: #2a2b2d; --text-primary: #ededed; --text-secondary: #888888; --text-tertiary: #555555; --ps-orange: #f35815; --ps-orange-dim: rgba(243, 88, 21, 0.15); --ps-orange-glow: rgba(243, 88, 21, 0.08); --mono: 'SF Mono', 'Fira Code', monospace; --sans: 'Inter', -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif; } html { scroll-behavior: smooth; } body { font-family: var(--mono); background: var(--bg); color: var(--text-primary); -webkit-font-smoothing: antialiased; -moz-osx-font-smoothing: grayscale; overflow-x: hidden; }
```

--------------------------------

### Accelerate Array Membership Queries

Source: https://github.com/planetscale/database-skills/blob/main/skills/mysql/references/json-column-patterns.md

Demonstrates how to query for elements within a JSON array using the MEMBER OF operator, which is accelerated by multi-valued indexes.

```sql
SELECT * FROM products WHERE 'electronics' MEMBER OF (tags);
```