Database Query Skill#
Execute PostgreSQL queries against the Supabase database running in Docker.
Quick Reference#
# Basic query
docker exec supabase_db_ory psql -U postgres -c "SELECT * FROM users LIMIT 5;"
# With specific database
docker exec supabase_db_ory psql -U postgres -d mydb -c "SELECT 1;"
# Interactive mode
docker exec supabase_db_ory psql -U postgres
Schema Exploration#
# List all tables
docker exec supabase_db_ory psql -U postgres -c "\dt"
# Describe table structure
docker exec supabase_db_ory psql -U postgres -c "\d table_name"
# List tables with SQL
docker exec supabase_db_ory psql -U postgres -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';"
# Column details
docker exec supabase_db_ory psql -U postgres -c "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'your_table';"
# Table size
docker exec supabase_db_ory psql -U postgres -c "SELECT pg_size_pretty(pg_total_relation_size('table_name'));"
Operation Rules#
Allowed Operations#
| Operation | Notes |
|---|---|
SELECT | Always safe, use freely |
INSERT | Safe for adding records |
UPDATE | Require WHERE clause, confirm affected rows first |
Caution Required#
Before UPDATE:
- Always include a WHERE clause
- Run a SELECT first to preview affected rows
- Confirm with user if affecting many rows
-- Step 1: Preview
SELECT * FROM users WHERE status = 'inactive';
-- Step 2: Update (after confirmation)
UPDATE users SET archived = true WHERE status = 'inactive';
Forbidden Operations - DO NOT USE THEM UNLESS INSTRUCTED OTHERWISE#
Never execute directly:
| Operation | Reason | Action |
|---|---|---|
DROP | Destroys tables/databases/schemas | Refuse and explain |
DELETE | Data loss risk | Require WHERE + confirmation |
TRUNCATE | Wipes entire tables | Refuse |
CREATE TABLE | Schema change | Redirect to migration |
ALTER TABLE | Schema change | Redirect to migration |
CREATE INDEX | Schema change | Redirect to migration |
CREATE SCHEMA | Schema change | Redirect to migration |
Handling Schema Change Requests#
When user requests schema modifications:
- Explain: Direct DDL execution bypasses version control and can cause deployment issues
- Generate migration: Create proper migration SQL with timestamp
- Provide path: Save to
supabase/migrations/directory
-- Example migration: 20240115120000_add_status_column.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active';
CREATE INDEX idx_users_status ON users(status);
Safe DELETE Pattern#
# Step 1: Count affected rows
docker exec supabase_db_ory psql -U postgres -c "SELECT COUNT(*) FROM orders WHERE created_at < '2023-01-01';"
# Step 2: Preview data
docker exec supabase_db_ory psql -U postgres -c "SELECT id, created_at FROM orders WHERE created_at < '2023-01-01' LIMIT 10;"
# Step 3: Execute only after explicit user confirmation
docker exec supabase_db_ory psql -U postgres -c "DELETE FROM orders WHERE created_at < '2023-01-01';"
Common Patterns#
Insert Data#
docker exec supabase_db_ory psql -U postgres -c "INSERT INTO users (name, email) VALUES ('John', 'john@example.com') RETURNING id;"
Bulk Insert#
docker exec supabase_db_ory psql -U postgres -c "INSERT INTO tags (name) VALUES ('tag1'), ('tag2'), ('tag3');"
Join Query#
docker exec supabase_db_ory psql -U postgres -c "SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id LIMIT 10;"
Check Foreign Keys#
docker exec supabase_db_ory psql -U postgres -c "SELECT tc.table_name, kcu.column_name, ccu.table_name AS foreign_table FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY';"
Troubleshooting#
| Issue | Solution |
|---|---|
| Container not running | docker ps to check, docker start supabase_db_ory |
| Permission denied | Verify user has required privileges |
| Connection refused | Check if PostgreSQL is accepting connections |
| Query timeout | Add LIMIT clause or optimize query |