# db_postgres A RocketRide database node that answers natural-language questions against a PostgreSQL database and inserts structured pipeline data into tables. ## What it does Plays two roles in a pipeline. As a pipeline node, it receives natural-language questions on the `questions` lane, asks a connected LLM to translate them into SQL, executes the query, and emits the results; it also accepts structured data on the `answers` lane and inserts it into the configured table. As a tool node, agents call it directly through three functions: `get_data`, `get_schema`, and `get_sql`. Uses SQLAlchemy with the psycopg2 driver (`psycopg2-binary`). The connection string is built as `postgresql+psycopg2://user:password@host/database`; user, password, and database are URL-encoded so reserved characters (`@`, `/`, `#`, `:`) are safe, and the host may carry an explicit port (e.g. `localhost:5433`). Safety defaults: only `SELECT` statements are permitted for queries (whitelist check, see SQL safety below), generated SQL is validated with `EXPLAIN` against the live database before execution, and raw SQL execution (`QuestionType.EXECUTE`) is disabled by default via `allow_execute`. The same implementation also ships as a Supabase preset (`services.supabase.json`, protocol `db_supabase://`): Supabase is managed Postgres, so it is a branded configuration, not separate code. --- ## Connections | Connection | Required | Description | | ---------- | -------- | ---------------------------------------------- | | `llm` | yes | LLM used to generate SQL from natural language | --- ## Configuration ### Lanes | Lane in | Lane out | Description | | ----------- | -------- | -------------------------------------------------------------- | | `questions` | `table` | Translate question to SQL, execute, return as a markdown table | | `questions` | `text` | Translate question to SQL, execute, return as text | | `questions` | `answers`| Translate question to SQL, execute, return as answers | | `answers` | (none) | Parse structured rows and insert into the table | If the LLM decides a question is not a database query, its text response is emitted instead of query results. Two special question types are handled on the `questions` lane: - **`QuestionType.DIALECT`**: emits `{"dialect": "postgres"}` on the `answers` lane so SDK callers can branch on the underlying engine. - **`QuestionType.EXECUTE`**: runs the question text as raw SQL (read or write, no LLM, no safety check). Gated by `allow_execute`; when disabled the request is logged and dropped. `SELECT` results are capped at 25,000 rows; write statements report `affected_rows`. ### Fields | Field | Type | Description | |---|---|---| | `host` | string | Default "localhost". Host name or IP address of the PostgreSQL server, optionally including a port (e.g. localhost:5433) | | `user` | string | Default "postgres". User to connect to the PostgreSQL server | | `password` | string | Password to connect to the PostgreSQL server | | `database` | string | Default "postgres". Name of database | | `table` | string | Default "table". Name of table | | `db_description` | string | Default empty. What is this database used for? Describe its content and purpose, this helps the LLM generate more accurate queries. | | `max_attempts` | integer | Default 5. Maximum number of times to re-ask the LLM if EXPLAIN rejects the generated SQL | | `allow_execute` | boolean | Default false. Permit QuestionType.EXECUTE callers to run raw SQL without LLM translation or safety checks. Leave OFF unless a trusted application explicitly needs to issue SQL directly. | | `profile` | string | Default "default". | The single `default` profile presets `database` to `postgres`. --- ## Available tools When connected to an agent, the node exposes three functions (named under the configured server prefix, default `postgres`): | Tool | Description | | ------------ | ----------------------------------------------------------------------------------------------------------------- | | `get_data` | Natural language to SQL, executes it, returns rows plus the generated SQL (default 250 rows, max 25,000 via `limit`) | | `get_schema` | Returns tables, columns, types, primary keys, and foreign keys, for the full database or one table | | `get_sql` | Natural language to SQL only, no execution | `get_data` and `get_sql` return `valid: false` with an `error` (unsafe SQL) or an `answer` (the question was not a database query) when no executable query is produced. --- ## SQL safety & validation Generated SQL passes two gates before execution: 1. **Whitelist check**: only statements beginning with `SELECT` (optionally prefixed by `EXPLAIN`) are allowed; everything else is rejected. Comments are stripped first so comment-based bypasses are neutralised, every statement in a multi-statement input is checked, `SELECT ... INTO OUTFILE/DUMPFILE` is blocked, and `WITH` (CTE) is deliberately excluded because PostgreSQL accepts CTE-into-mutation (e.g. `WITH x AS (...) DELETE ...`). 2. **`EXPLAIN` validation**: the query is validated against the live database. If `EXPLAIN` rejects it, the rejected SQL and the database error are fed back to the LLM for a corrected query, up to `max_attempts` times (default 5). Insert operations never go through SQL generation; they use the `answers` lane. --- ## Data insertion Rows arriving on the `answers` lane are inserted into the configured `table`: - The table is auto-created from the shape of the first batch if it does not exist (column types inferred from the data). - Incoming keys are matched to columns case-insensitively (`UserName` maps to `username`); schema columns missing from the data are inserted as `NULL`. - Lists and dicts are serialised as JSON strings; booleans are stored as `0`/`1`. - Each batch is inserted in a single transaction: on failure it is rolled back and the error re-raised. --- ## Supabase preset `services.supabase.json` registers the same node as Supabase (`db_supabase://`, prefix `supabase`). The connection is encrypted over TLS. Operational notes: - **Use the Supavisor pooler** from the Supabase dashboard (Connect button): `aws-0-.pooler.supabase.com:6543` (transaction) or `:5432` (session). It works over IPv4. - The **direct connection** (`db..supabase.co:5432`) is IPv6-only and will fail to resolve on networks without IPv6. - For the pooler, the user must include your project ref: `postgres.`. Without the suffix the pooler returns `no tenant identifier`. For the direct connection it is just `postgres`. - The database password comes from your Supabase project (Project Settings -> Database); the database name defaults to `postgres`. --- ## Schema ### PostgreSQL (`services.json`) | Field | Type | Description | Default | |---|---|---|---| | `postgresdb.allow_execute` | `boolean` | **Allow direct query execution**
Permit QuestionType.EXECUTE callers to run raw SQL without LLM translation or safety checks. Leave OFF unless a trusted application explicitly needs to issue SQL directly. | `false` | | `postgresdb.database` | `string` | **Database name**
Name of database | `"postgres"` | | `postgresdb.db_description` | `string` | **Database description**
What is this database used for? Describe its content and purpose, this helps the LLM generate more accurate queries. | `""` | | `postgresdb.host` | `string` | **PostgreSQL host**
Host name or IP address of the PostgreSQL server, optionally including a port (e.g. localhost:5433) | `"localhost"` | | `postgresdb.max_attempts` | `integer` | **Max validation attempts**
Maximum number of times to re-ask the LLM if EXPLAIN rejects the generated SQL | `5` | | `postgresdb.password` | `string` | **Password**
Password to connect to the PostgreSQL server | | | `postgresdb.profile` | `string` | | `"default"` | | `postgresdb.table` | `string` | **Table name**
Name of table | `"table"` | | `postgresdb.user` | `string` | **User**
User to connect to the PostgreSQL server | `"postgres"` | ### Supabase (`services.supabase.json`) | Field | Type | Description | Default | |---|---|---|---| | `postgresdb.allow_execute` | `boolean` | **Allow direct query execution**
Permit QuestionType.EXECUTE callers to run raw SQL without LLM translation or safety checks. Leave OFF unless a trusted application explicitly needs to issue SQL directly. | `false` | | `postgresdb.database` | `string` | **Database name**
Name of database (Supabase default is 'postgres') | `"postgres"` | | `postgresdb.db_description` | `string` | **Database description**
What is this database used for? Describe its content and purpose, this helps the LLM generate more accurate queries. | `""` | | `postgresdb.host` | `string` | **Supabase host**
From the Supabase dashboard (Connect button), including the port. Recommended: the Supavisor pooler (works over IPv4) -> aws-0-.pooler.supabase.com:6543 (transaction) or :5432 (session). The Direct connection (db..supabase.co:5432) is IPv6-only and will fail to resolve on networks without IPv6. | | | `postgresdb.max_attempts` | `integer` | **Max validation attempts**
Maximum number of times to re-ask the LLM if EXPLAIN rejects the generated SQL | `5` | | `postgresdb.password` | `string` | **Password**
Database password from your Supabase project (Project Settings -> Database) | | | `postgresdb.profile` | `string` | | `"default"` | | `postgresdb.table` | `string` | **Table name**
Name of table | `"table"` | | `postgresdb.user` | `string` | **User**
Database user. For the pooler (recommended) it MUST include your project ref: postgres., without the . suffix the pooler returns 'no tenant identifier'. For the direct connection it is just: postgres | `"postgres"` | ## Dependencies - `psycopg2-binary` `==2.9.12` ## Source [ View source](https://github.com/rocketride-org/rocketride-server/tree/develop/nodes/src/nodes/db_postgres)