|
1
|
--- |
|
2
|
name: database |
|
3
|
description: Create and manage Replit's built-in PostgreSQL databases, check status, execute SQL queries with safety checks, and run read-only queries against the production database. Use when the user wants to check prod data, debug database issues in production, or asks to "check the prod db", "query production", "look at live data", or "see what's in the database on the deployed app". Also use when the user asks how to apply development schema changes to the production database, e.g. "push dev to prod", "migrate the production database", "sync the schema", "production is missing a column", or reports a deployed app failing with "column does not exist" / "relation does not exist". |
|
4
|
--- |
|
5
|
|
|
6
|
# Database Skill |
|
7
|
|
|
8
|
Manage PostgreSQL databases and execute SQL queries safely in your development and production environments. |
|
9
|
|
|
10
|
## When to Use |
|
11
|
|
|
12
|
Use this skill when: |
|
13
|
|
|
14
|
- Creating a new PostgreSQL database for your project |
|
15
|
- Checking if a database is provisioned and accessible |
|
16
|
- Running SQL queries against the development or production database |
|
17
|
- Querying data warehouses (BigQuery, Databricks, Snowflake). For Databricks, use the `databricks-m2m` connector (not the plain `databricks` connector). |
|
18
|
|
|
19
|
## When NOT to Use |
|
20
|
|
|
21
|
- Schema migrations in production environments — see "Production schema changes" below |
|
22
|
- Direct modifications to Stripe tables (use Stripe API instead) |
|
23
|
- Converting a pre-existing database over to Replit, unless a user explicitly asks you to. |
|
24
|
|
|
25
|
## Production schema changes |
|
26
|
|
|
27
|
For projects that use Replit's managed PostgreSQL, the production database schema is managed automatically by Replit's Publish flow. When the user clicks Publish, Replit diffs the development schema against production, asks the user to resolve any renames in the Publish UI, and applies the diff to production. This is the supported path for changing the production schema; the agent must not write any code or scripts to migrate the production database. |
|
28
|
|
|
29
|
For the full set of rules on what the agent must and must not do here — including specific patterns to avoid (custom migration scripts, deploy-build hooks, startup-time DDL) and the correct dev-side flow — read `.local/skills/database/references/database-migrations-on-publish.md`. |
|
30
|
|
|
31
|
## Reference Documents |
|
32
|
|
|
33
|
- `.local/skills/database/references/database-migrations-on-publish.md` — Full detail on the publish-time schema migration flow, including the two automatic application points (post-merge → dev, publish → prod), the SQL diff and rename-confirmation behavior, and the canonical failure mode this guidance prevents. Read this when the user reports production is missing a column or table, asks to "push dev to prod" / "migrate the production database" / "sync the schema", or reports the deployed app failing with "column does not exist" / "relation does not exist" errors. |
|
34
|
|
|
35
|
## Available Functions |
|
36
|
|
|
37
|
### checkDatabase() |
|
38
|
|
|
39
|
Check if the PostgreSQL database is provisioned and accessible. |
|
40
|
|
|
41
|
**Parameters:** None |
|
42
|
|
|
43
|
**Returns:** Dict with `provisioned` (bool) and `message` (str) |
|
44
|
|
|
45
|
**Example:** |
|
46
|
|
|
47
|
```javascript |
|
48
|
const status = await checkDatabase(); |
|
49
|
if (status.provisioned) { |
|
50
|
console.log("Database is ready!"); |
|
51
|
} else { |
|
52
|
console.log(status.message); |
|
53
|
// Consider calling createDatabase() |
|
54
|
} |
|
55
|
``` |
|
56
|
|
|
57
|
### createDatabase() |
|
58
|
|
|
59
|
Create or verify a PostgreSQL database exists for the project. |
|
60
|
|
|
61
|
**Parameters:** None |
|
62
|
|
|
63
|
**Returns:** Dict with: |
|
64
|
|
|
65
|
- `success` (bool): Whether operation succeeded |
|
66
|
- `message` (str): Status message |
|
67
|
- `alreadyExisted` (bool): True if database already existed |
|
68
|
- `secretKeys` (list): Environment variables set (DATABASE_URL, PGHOST, etc.) |
|
69
|
|
|
70
|
**Example:** |
|
71
|
|
|
72
|
```javascript |
|
73
|
const result = await createDatabase(); |
|
74
|
if (result.success) { |
|
75
|
console.log(`Database ready! Environment variables: ${result.secretKeys}`); |
|
76
|
// Now you can use DATABASE_URL in your application |
|
77
|
} |
|
78
|
``` |
|
79
|
|
|
80
|
### executeSql() |
|
81
|
|
|
82
|
Execute a SQL query with safety checks. |
|
83
|
|
|
84
|
**Parameters:** |
|
85
|
|
|
86
|
- `sqlQuery` (str, required): The SQL query to execute. Use `$1`, `$2`, etc. placeholders when using parameterized queries. |
|
87
|
- `params` (array, optional): Parameter values for parameterized queries. When provided, values are bound separately from the SQL string, **preventing SQL injection**. Each `$N` placeholder in `sqlQuery` corresponds to the Nth element in this array (1-indexed). Supported types: string, number, boolean, null. **Two restrictions:** |
|
88
|
- **Single-statement only.** When `params` is provided (including `params: []`), `sqlQuery` MUST be a single SQL statement. Semicolon-delimited multi-statement scripts (`BEGIN; ...; COMMIT;`, migration batches, etc.) are rejected on the parameterized path. Send them as a single `executeSql` call WITHOUT `params` — separate calls do NOT share a transaction or session, so splitting would silently lose atomicity. Validate any interpolated values against a strict allowlist before embedding them. |
|
89
|
- **`replit_database` target only.** Data warehouse targets (bigquery, databricks, snowflake) do not support parameter binding — passing `params` with those targets raises an error. |
|
90
|
- `target` (str, default "replit_database"): Target database: "replit_database", "bigquery", "databricks", or "snowflake" |
|
91
|
- `environment` (str, default "development"): "development" runs against the development database (all SQL operations supported). "production" runs READ-ONLY queries against a replica of the production database (only SELECT queries allowed). Production is only supported for the "replit_database" target. "production" database, depending on when the user last deployed, may have outdated schemas. |
|
92
|
- `sampleSize` (int, optional): Sample size for warehouse queries (only for bigquery/databricks/snowflake) |
|
93
|
|
|
94
|
**Returns:** Dict with: |
|
95
|
|
|
96
|
- `success` (bool): Whether query succeeded |
|
97
|
- `output` (str): Query output/results |
|
98
|
- `exitCode` (int): Exit code (0 = success) |
|
99
|
- `exitReason` (str | None): Reason for exit if failed |
|
100
|
|
|
101
|
**CRITICAL: When `params` is supported, ALWAYS use it for any user input or variable.** |
|
102
|
This applies when **all** of these are true: |
|
103
|
|
|
104
|
- `target` is `replit_database` (the default). |
|
105
|
- `sqlQuery` is a single SQL statement (no semicolons separating multiple statements). |
|
106
|
|
|
107
|
In that case, never use string interpolation or concatenation to build SQL with dynamic literal values — pass them through `params`. This prevents SQL injection. |
|
108
|
|
|
109
|
```javascript |
|
110
|
// BAD - vulnerable to SQL injection: |
|
111
|
const result = await executeSql({ sqlQuery: `SELECT * FROM users WHERE id = ${userId}` }); |
|
112
|
|
|
113
|
// GOOD - use parameterized queries: |
|
114
|
const result = await executeSql({ |
|
115
|
sqlQuery: "SELECT * FROM users WHERE id = $1", |
|
116
|
params: [userId] |
|
117
|
}); |
|
118
|
``` |
|
119
|
|
|
120
|
**Note: `params` only binds literal values** (strings, numbers, booleans, null, or arrays of those scalar literal values for `ANY($1::type[])`). PostgreSQL placeholders cannot substitute identifiers (table or column names), SQL keywords (sort directions like `ASC`/`DESC`, operators), or other syntactic elements. For dynamic identifiers or keywords, validate the value against a strict allowlist (e.g. a fixed set of allowed table names, or a regex like `/^[a-z_][a-z0-9_]*$/`), then interpolate the validated value into the SQL string. Never interpolate free-form user text into identifier positions. |
|
121
|
|
|
122
|
```javascript |
|
123
|
// GOOD - pass arrays as one parameter and cast the placeholder for ANY(...): |
|
124
|
await executeSql({ |
|
125
|
sqlQuery: "SELECT * FROM resources WHERE category = ANY($1::text[])", |
|
126
|
params: [["Anxiety", "Depression", "Trauma"]] |
|
127
|
}); |
|
128
|
``` |
|
129
|
|
|
130
|
```javascript |
|
131
|
// BAD - $1 cannot bind a table name; this query fails to parse: |
|
132
|
await executeSql({ sqlQuery: "SELECT * FROM $1 WHERE id = $2", params: [tableName, userId] }); |
|
133
|
|
|
134
|
// GOOD - allowlist + interpolate identifiers, $N for values: |
|
135
|
const ALLOWED_TABLES = ["users", "orders", "products"]; |
|
136
|
if (!ALLOWED_TABLES.includes(tableName)) throw new Error("invalid table"); |
|
137
|
await executeSql({ |
|
138
|
sqlQuery: `SELECT * FROM ${tableName} WHERE id = $1`, |
|
139
|
params: [userId] |
|
140
|
}); |
|
141
|
``` |
|
142
|
|
|
143
|
**When `params` is NOT supported on `replit_database`** (multi-statement scripts only — `BEGIN; ...; COMMIT;`, migrations, batched DDL): |
|
144
|
|
|
145
|
1. **For independent statements that don't need shared transaction or session state**, you can split them into separate `executeSql` calls each with its own `params` (e.g., a sequence of unrelated INSERTs into different tables). Note that separate `executeSql` calls do NOT share a connection, transaction, or session — temp tables, `SET LOCAL` GUCs, and `BEGIN`/`COMMIT` will not carry across calls. |
|
146
|
2. **For scripts that DO need transactional atomicity or session state** (most multi-statement writes, migrations, scripts that depend on temp tables), send the whole script as a single `executeSql` call without `params`. Validate any interpolated values against a strict allowlist (e.g. integers only, identifier whitelist) before embedding them in the SQL string. Treat any interpolated value as a potential injection vector — never interpolate free-form user text. |
|
147
|
|
|
148
|
**For warehouse targets** (`bigquery`, `databricks`, `snowflake`), `params` is NEVER supported. The runtime rejects any warehouse query that includes `params`, whether single-statement or multi-statement. You MUST either: (a) reject the user input if it cannot be validated, or (b) validate the value against a strict allowlist (e.g. integers only, fixed identifier set) before interpolating it into the SQL string. Never interpolate free-form user text into warehouse SQL. |
|
149
|
|
|
150
|
**Example:** |
|
151
|
|
|
152
|
```javascript |
|
153
|
// Simple SELECT with parameters |
|
154
|
const result = await executeSql({ |
|
155
|
sqlQuery: "SELECT * FROM users WHERE id = $1", |
|
156
|
params: [userId] |
|
157
|
}); |
|
158
|
if (result.success) { |
|
159
|
console.log(result.output); |
|
160
|
} |
|
161
|
|
|
162
|
// Static query without user input (no params needed) |
|
163
|
const result1b = await executeSql({ sqlQuery: "SELECT * FROM users LIMIT 5" }); |
|
164
|
|
|
165
|
// CREATE TABLE (static DDL, no params needed) |
|
166
|
const result2 = await executeSql({ |
|
167
|
sqlQuery: ` |
|
168
|
CREATE TABLE IF NOT EXISTS products ( |
|
169
|
id SERIAL PRIMARY KEY, |
|
170
|
name VARCHAR(255) NOT NULL, |
|
171
|
price DECIMAL(10, 2) |
|
172
|
) |
|
173
|
` |
|
174
|
}); |
|
175
|
|
|
176
|
// INSERT data with parameters |
|
177
|
const result3 = await executeSql({ |
|
178
|
sqlQuery: "INSERT INTO products (name, price) VALUES ($1, $2)", |
|
179
|
params: [productName, productPrice] |
|
180
|
}); |
|
181
|
|
|
182
|
// UPDATE with parameters |
|
183
|
const result3b = await executeSql({ |
|
184
|
sqlQuery: "UPDATE products SET price = $1 WHERE name = $2", |
|
185
|
params: [newPrice, productName] |
|
186
|
}); |
|
187
|
|
|
188
|
// DELETE with parameters |
|
189
|
const result3c = await executeSql({ |
|
190
|
sqlQuery: "DELETE FROM products WHERE id = $1", |
|
191
|
params: [productId] |
|
192
|
}); |
|
193
|
|
|
194
|
// Read-only production query with parameters |
|
195
|
const result4 = await executeSql({ |
|
196
|
sqlQuery: "SELECT * FROM users WHERE active = $1", |
|
197
|
params: [true], |
|
198
|
environment: "production" |
|
199
|
}); |
|
200
|
|
|
201
|
// Data warehouse query — project exact columns + partition filter + LIMIT. |
|
202
|
// `sampleSize` only caps rows returned to the client; it does NOT reduce the |
|
203
|
// bytes the warehouse scans, so a bare `SELECT *` still costs the full scan. |
|
204
|
// (Note: `params` is NOT supported for warehouse targets; if a date or other |
|
205
|
// filter value were dynamic, validate it as the expected type before |
|
206
|
// interpolating.) |
|
207
|
const result5 = await executeSql({ |
|
208
|
sqlQuery: `SELECT order_id, customer_id, total_amount, order_date |
|
209
|
FROM sales_data |
|
210
|
WHERE order_date >= '2024-01-01' |
|
211
|
AND order_date < '2025-01-01' |
|
212
|
LIMIT 100`, |
|
213
|
target: "bigquery", |
|
214
|
sampleSize: 100 |
|
215
|
}); |
|
216
|
|
|
217
|
// Multi-statement transactional scripts (BEGIN ... COMMIT, temp tables, |
|
218
|
// SET LOCAL, etc.) MUST run as a single executeSql call WITHOUT `params` |
|
219
|
// — separate calls do NOT share a connection or transaction, so |
|
220
|
// splitting a transaction across multiple calls would silently lose |
|
221
|
// atomicity. If the script needs user-controlled values, validate them |
|
222
|
// against a strict allowlist (integers only, identifier whitelist) |
|
223
|
// before interpolating; never interpolate free-form user text. |
|
224
|
const result = await executeSql({ |
|
225
|
sqlQuery: ` |
|
226
|
BEGIN; |
|
227
|
UPDATE users SET active = false WHERE last_login < '2024-01-01'; |
|
228
|
UPDATE audit_log SET archived = true WHERE created_at < '2024-01-01'; |
|
229
|
COMMIT; |
|
230
|
`, |
|
231
|
}); |
|
232
|
``` |
|
233
|
|
|
234
|
## Safety Features |
|
235
|
|
|
236
|
1. **Environment Isolation**: Development queries run against the development database; production queries are READ-ONLY against a read replica |
|
237
|
2. **Stripe Protection**: Mutations to Stripe schema tables (stripe.*) are blocked |
|
238
|
3. **Discussion Mode**: Mutating queries are blocked in Planning/Discussion mode |
|
239
|
4. **Destructive Query Protection**: DROP, TRUNCATE, etc. are blocked via the skill callback path (use the tool interface directly for destructive operations that require user confirmation) |
|
240
|
|
|
241
|
## CRITICAL: Data Warehouse Cost Control |
|
242
|
|
|
243
|
Warehouse targets (`bigquery`, `databricks`, `snowflake`) are billed per byte scanned. |
|
244
|
One careless `SELECT *` against a fact table can cost tens of dollars; a dashboard |
|
245
|
that re-runs warehouse queries every few seconds can burn thousands of dollars per day. |
|
246
|
**Treat `executeSql` as an expensive debugging/exploration tool when `target` is a warehouse.** |
|
247
|
|
|
248
|
### Use `executeSql` against a warehouse ONLY for these cases |
|
249
|
|
|
250
|
1. **Schema discovery** — `INFORMATION_SCHEMA.TABLES`, `INFORMATION_SCHEMA.COLUMNS`, |
|
251
|
`DESCRIBE TABLE`, and similar metadata queries to understand the structure. |
|
252
|
2. **Small sample reads** with an explicit `LIMIT` (`LIMIT 5`–`LIMIT 100`) to inspect |
|
253
|
value shapes while designing an app or answering an ad-hoc question. |
|
254
|
3. **One-off analytical questions** that the user explicitly asked in chat and that |
|
255
|
cannot be answered from cached data. |
|
256
|
|
|
257
|
### Do NOT use `executeSql` against a warehouse for these cases |
|
258
|
|
|
259
|
- Power a data app's runtime queries. The app's API server must run those queries |
|
260
|
itself, with DB-backed caching (TTL matched to the dashboard's lowest refresh |
|
261
|
interval — 5 minutes by default). See the `data-visualization` skill's |
|
262
|
`common-database.md` for the `api_cache` schema and helpers. |
|
263
|
- Re-run the same query repeatedly during a session — cache the result locally |
|
264
|
(in a JavaScript variable or a CSV file under `.agents/outputs/`) and reuse it. |
|
265
|
- Perform full table scans when an aggregated, partitioned, or `LIMIT`-bounded |
|
266
|
query would answer the same question. |
|
267
|
|
|
268
|
### Required discipline for every warehouse query |
|
269
|
|
|
270
|
- **Project exact columns** — never `SELECT *` on wide tables. |
|
271
|
- **Always `LIMIT`** when exploring. |
|
272
|
- **Partition / cluster filter** — scope by the partition or cluster column |
|
273
|
(`WHERE event_date >= ...`) so the warehouse prunes data and you are billed |
|
274
|
for a tiny slice, not the whole table. |
|
275
|
- **Prefer pre-aggregated tables** (e.g. `_daily`, `_summary`) over raw event tables. |
|
276
|
- **Diff-only reads** — for incremental/refresh queries, filter on `WHERE updated_at > :last_seen` |
|
277
|
and persist `last_seen` so subsequent queries only scan the delta. |
|
278
|
|
|
279
|
### If the user is building a data app (dashboard, report, explorer) |
|
280
|
|
|
281
|
Hand off to the `data-visualization` skill. Use `executeSql` only for the initial |
|
282
|
schema discovery and a few sample queries. The runtime queries belong in the app's |
|
283
|
API server with DB-backed caching — not in repeated `executeSql` calls from the agent |
|
284
|
loop. |
|
285
|
|
|
286
|
## Best Practices |
|
287
|
|
|
288
|
1. **Prefer the built-in database**: Replit's built-in PostgreSQL database is always preferred over external services like Supabase. It supports rollback and integrates directly with the Replit product. Only use external database services if the user has specific requirements. The `pg` package should be installed already. |
|
289
|
2. **Check before creating**: Call `checkDatabase()` before `createDatabase()` to avoid unnecessary operations |
|
290
|
3. **ALWAYS use parameterized queries with user input on the `replit_database` single-statement path**: When `target` is `replit_database` (the default) and `sqlQuery` is a single statement, you MUST use the `params` argument with `$1`, `$2`, etc. placeholders for any user-provided literal value. NEVER use string interpolation, template literals, or concatenation for values in that case. For dynamic identifiers (table/column names) or SQL keywords (sort directions, operators), validate against a strict allowlist before interpolating since `params` cannot bind those. For warehouse targets and multi-statement transactional scripts where `params` is not supported, send the whole script as a single `executeSql` call and validate any interpolated values against a strict allowlist; only split into separate calls when the statements are truly independent and don't share transaction or session state. |
|
291
|
4. **Test queries first**: Run SELECT queries before INSERT/UPDATE/DELETE |
|
292
|
5. **Keep backups**: Important data should be backed up before destructive operations |
|
293
|
|
|
294
|
## Environment Variables |
|
295
|
|
|
296
|
After creating a database, these environment variables are available: |
|
297
|
|
|
298
|
- `DATABASE_URL`: Full connection string |
|
299
|
- `PGHOST`: Database host |
|
300
|
- `PGPORT`: Database port (5432) |
|
301
|
- `PGUSER`: Database username |
|
302
|
- `PGPASSWORD`: Database password |
|
303
|
- `PGDATABASE`: Database name |
|
304
|
|
|
305
|
## Example Workflow |
|
306
|
|
|
307
|
```javascript |
|
308
|
// 1. Check if database exists |
|
309
|
const status = await checkDatabase(); |
|
310
|
|
|
311
|
if (!status.provisioned) { |
|
312
|
// 2. Create database |
|
313
|
const createResult = await createDatabase(); |
|
314
|
if (!createResult.success) { |
|
315
|
console.log(`Failed: ${createResult.message}`); |
|
316
|
} |
|
317
|
} |
|
318
|
|
|
319
|
// 3. Create schema |
|
320
|
await executeSql({ |
|
321
|
sqlQuery: ` |
|
322
|
CREATE TABLE IF NOT EXISTS users ( |
|
323
|
id SERIAL PRIMARY KEY, |
|
324
|
email VARCHAR(255) UNIQUE NOT NULL, |
|
325
|
created_at TIMESTAMP DEFAULT NOW() |
|
326
|
) |
|
327
|
` |
|
328
|
}); |
|
329
|
|
|
330
|
// 4. Insert data (using parameterized query) |
|
331
|
await executeSql({ |
|
332
|
sqlQuery: "INSERT INTO users (email) VALUES ($1)", |
|
333
|
params: ['user@example.com'] |
|
334
|
}); |
|
335
|
|
|
336
|
// 5. Query data |
|
337
|
const result = await executeSql({ sqlQuery: "SELECT * FROM users" }); |
|
338
|
console.log(result.output); |
|
339
|
``` |
|
340
|
|
|
341
|
## Limitations |
|
342
|
|
|
343
|
- Production queries are READ-ONLY (SELECT only) — INSERT, UPDATE, DELETE, and DDL statements will fail |
|
344
|
- Production environment is only supported for the "replit_database" target (not data warehouses) |
|
345
|
- Cannot modify Stripe schema tables (read-only) |
|
346
|
- Destructive queries (DROP, TRUNCATE, etc.) are blocked via the skill callback path |
|
347
|
- Mutating queries blocked in Planning mode |
|
348
|
|
|
349
|
## Rollbacks |
|
350
|
|
|
351
|
As stated in the diagnostic skills, the development database support rollbacks. Open that skill for more information. |