A Visual Query Builder for PostgreSQL
How DbSchema builds PostgreSQL SELECTs and joins from the diagram, where the recursive CTE actually goes, and what it will not do to production.
On this page
What Is a PostgreSQL Visual Query Builder?
A PostgreSQL visual query builder is a graphical workspace that transforms interactive canvas actions into syntactically valid SQL statements in real time. Instead of manually writing SELECT, FROM, WHERE, and GROUP BY clauses, data analysts select tables from a visual schema diagram, check required columns, and configure filters through interactive dialogs.
Writing SQL queries by hand frequently leads to syntax errors, misspelled column identifiers, missing table qualifiers, and invalid data type comparisons. A visual builder connects to the PostgreSQL instance using a standard JDBC driver and introspects the database catalog, so every table, view, primary key, and foreign key appears on an interactive design canvas ready to be pointed at rather than typed out.
- Visual column projection: Select table columns with checkboxes to automatically populate the SQL SELECT field list.
- Real-time SQL synchronization: Inspect generated PostgreSQL queries in an integrated code pane that updates instantly as you adjust canvas elements.
- Automated clause management: Define WHERE filter conditions, ORDER BY sort rules, and GROUP BY aggregations through dedicated visual menus.
- Foreign-key table selection: Once the first table is on the canvas, DbSchema offers only the tables reachable by a foreign key and names the key it would join on.
The strongest builders operate on an offline-first design model file, allowing data analysts to assemble and refine query logic without maintaining an active database connection. When connected to a live PostgreSQL database, the generated SQL executes immediately to retrieve datasets for analysis.
Abstracting Complex JOINs for Data Analysts
Data analysts routinely query normalized PostgreSQL databases that distribute related data across multiple tables. Hand-crafting multi-table queries requires identifying primary-to-foreign key relationships, choosing the correct join type (INNER, LEFT, RIGHT, or FULL OUTER), and writing precise join predicates, because PostgreSQL specifies the join condition in the ON or USING clause (or implicitly through NATURAL)[1].
Visual Join Definition and Multi-Table Navigation
On the canvas, clicking a column's foreign-key arrow adds the related table and writes the join for you - DbSchema names the key it will use, as in “Join users via tasks_created_by_fkey”, rather than asking you to type a predicate. Clicking the join-type label on the connector switches between Inner Join, Left Join, Full Join, Exists and Not Exists, and the SQL preview updates as you go. There is no RIGHT JOIN entry on that control: reverse the table order and use Left Join, or write it by hand in the SQL Editor. For one query built end to end this way, see building a query across joined tables.
| Join Type | PostgreSQL SQL Syntax | Result Set Behavior | In DbSchema's Query Builder |
|---|---|---|---|
| INNER JOIN | table_a INNER JOIN table_b ON table_a.id = table_b.a_id | Returns records with matching keys in both tables | The default when you follow a foreign-key arrow; the connector reads Inner Join |
| LEFT JOIN | table_a LEFT JOIN table_b ON table_a.id = table_b.a_id | Returns all left-table records and matched right-table records | Click the connector's join-type label and choose Left Join |
| RIGHT JOIN | table_a RIGHT JOIN table_b ON table_a.id = table_b.a_id | Returns all right-table records and matched left-table records | Not offered on the connector - reverse the table order and use Left Join, or type it in the SQL Editor |
| FULL OUTER JOIN | table_a FULL JOIN table_b ON table_a.id = table_b.a_id | Returns all rows when a match exists in either table | Click the connector's join-type label and choose Full Join |
Joins are not the only way to walk a schema. DbSchema's Relational Data Editor browses the rows themselves, cascading from a selected parent row into every child table a foreign key touches - the ground covered in exploring master-detail data across foreign keys.
Handling PostgreSQL Recursive Queries Visually
Hierarchical structures such as organizational charts, product categories and network graphs need recursive queries, and this is where a visual canvas stops being the tool: no query builder, DbSchema's included, generates a WITH RECURSIVE statement for you. It is still worth knowing exactly what that statement has to contain, because you will be typing it into the SQL Editor sitting next to the builder. The general form of a recursive WITH query is always a non-recursive term, then UNION (or UNION ALL), then a recursive term, where only the recursive term can contain a reference to the query's own output[2].
Deconstructing Recursive CTE Components
Constructing recursive queries manually is error-prone. Although RECURSIVE lets a query be specified recursively, PostgreSQL evaluates it iteratively: the non-recursive term is evaluated first and its rows are placed in a temporary working table, then, so long as that working table is not empty, the recursive term runs again against its current contents[2]. Analysts therefore have to align column data types across the unioned terms and guard against endless iteration when the data contains cycles, which is why UNION (which discards duplicate rows) or a LIMIT on the parent query is often used as a brake.
- Anchor Member: Executes the non-recursive term that retrieves the root records of the hierarchy.
- Union Operator: Combines the non-recursive term with the recursive term using UNION or UNION ALL, and with UNION (but not UNION ALL) PostgreSQL discards duplicate rows[2].
- Recursive Member: Joins the recursive self-reference back to base tables to fetch subsequent child levels.
- Termination Logic: Restricts recursion depth through explicit WHERE criteria or cycle-detection columns.
DbSchema does not build the recursive term for you, and no visual builder does. What it gives you is the shape: a self-referencing foreign key such as tasks_parent_task_id_fkey is drawn on the ER diagram, Diagram > Find Cyclic Dependencies lists every table taking part in a loop, and the Query Builder will assemble and run the anchor SELECT so you can check the root rows before writing anything. The WITH RECURSIVE statement itself goes into the SQL Editor, which takes free-text SQL against the same connection, keeps each statement in SQL History and renders the execution plan through Explain. It is the same reasoning that governs how PostgreSQL joins behave once the anchor and recursive terms are unioned.
Saving Query and Editor State within the Model
DbSchema's own documentation is explicit here: the Query Builder “opens inside the diagram, is saved to the model file, and can be reopened from the Editors menu”, and SQL Editors are “saved inside the model file” in the same way. Close one and DbSchema asks whether to keep it in the design model or drop it permanently; reopen the model and the Editors menu offers each saved editor under its own Restore submenu. Saving a model to a .dbs file is a Pro edition feature.
Git Version Control and Offline Query Collaboration
The .dbs file is plain, indented XML, which is what makes a saved query reviewable in a pull request rather than merely storable. DbSchema ships a Git client in the Model menu - clone, branch, stage, commit, push, stash - so the model travels the same path as the code that depends on it.
- Local XML file persistence: Stores diagram coordinates, table filters, join criteria and SQL code in a standalone .dbs file.
- Offline query authoring: Build, test, and document complex queries without an active network connection to PostgreSQL.
- Git team workflows: Track changes to queries and schema models through the Git client in DbSchema's Model menu, over an ordinary branch-and-pull-request flow.
- Session state preservation: Reopen the tool to find all query builder tabs, layouts, and filters intact.
One caveat worth stating plainly: connection credentials are not in the .dbs file - DbSchema keeps those encrypted under its own configuration directory - so sharing a model shares the queries and the schema, never the access. For the wider picture of working a database this way, see querying a database without writing SQL.
Executing Queries and Exploring Results
Running a query transmits the generated SQL command to PostgreSQL over JDBC and populates an interactive tabular result grid. Analysts can inspect datasets, verify column projections, and review aggregate outputs immediately within the same application interface.
Inline Filtering and Relational Data Browsing
Run sends the generated SQL over JDBC and the rows land in a paged grid under the canvas, with a status line reporting how many were read and how long it took. The Relational Data Editor is the other half of this: it opens a grid per table and cascades from the selected parent row into every foreign key that touches it, in both directions, an unlimited number of tables deep.
| Exploration Feature | Operational Functionality | Data Analyst Benefit |
|---|---|---|
| Inline Grid Filtering | Open the filter dialog on a column header, set an operator and a value; the condition is applied server-side in the generated WHERE clause | Narrow the grid without rewriting the SELECT by hand |
| Relational Data Browsing | Cascade through child tables across foreign keys and virtual relations | Follow one parent row into an unlimited number of related tables in a single view |
| Data Export Formats | Save the full result set to a file from the result pane; the query is re-executed so rows past the end of the screen are included | Hand a complete extract to a spreadsheet or a reporting pipeline |
| In-Place Record Editing | Double-click a cell to edit it, or use Insert Record and Delete Record in the grid | Nothing reaches the database until an explicit Commit; Rollback discards the pending changes |
These visual exploration capabilities enable data analysts to validate query accuracy against live records and export verified datasets directly into analytical dashboards and spreadsheets.
What the Tool Does Not Let You Do to Production
This is the part to settle before pointing anything at production. DbSchema's Query Builder constructs SELECT statements and nothing else - its toolbar carries Include, Edit, Inherit, Run, Run as Script, Stop, Filter & Order, Group By and Explain, and no DDL verb - so a table cannot be dropped, altered or truncated from the query canvas.
Production Safety Safeguards and Schema Isolation
That is a narrower blast radius, not a removed one, and the difference matters. DbSchema will run whatever DDL you type into the SQL Editor, and the Relational Data Editor inserts, edits and deletes rows once you press Commit. Two guards close the gap and neither is on by default: tick Read Only Connection on the connection dialog's Settings tab so the server itself refuses every write made through DbSchema, and keep schema changes on the separate Synchronize path, which shows you the generated script before it runs. Are visual query builders safe on production works through the rest.
- SELECT only in the builder: the query canvas emits SELECT and carries no DDL verb, so DROP, TRUNCATE and ALTER TABLE are not reachable from it.
- Offline model decoupling: Editing the diagram changes only the local .dbs model file until you generate and run a synchronization script.
- Read Only Connection: a checkbox on the connection dialog's Settings tab that makes the database refuse every schema and data change made through DbSchema. It is off by default.
- Explicit DDL boundaries: Schema changes go through Schema Synchronization, which renders the generated script for review before anything is executed.
Set up that way, an analyst can open a production model, map joins across it and prototype reporting queries with the write paths closed. Left at the defaults, the same session can write - which is why the connection setting deserves more attention than the canvas does.
Deploying Shared Models for the Analyst Team
A .dbs file that already holds the joins your team agreed on is worth sharing. Keep the shared .dbs model files in a Git repository and an analyst pulls a pre-built Query Builder rather than rebuilding it - against a local, staging or production PostgreSQL connection, since the model is not tied to the server it was reverse-engineered from.
Edition Capabilities and Team Licensing
Be exact about which edition this asks for. DbSchema's Community Edition is free forever and covers all databases, connecting and reverse-engineering, interactive diagrams and a SQL editor - every supported engine, PostgreSQL included, is in Community, so no engine is behind a paywall. The visual query builder and relational data browse, saving your work to a file and designing offline, schema synchronization and migration scripts, and HTML5, PDF and Markdown documentation are Pro. The workflow in this article is therefore a Pro workflow; the 15-day trial covers it, and which database tool edition a team actually needs works through the trade-off.
- Step 1: Download DbSchema and install it on Windows, macOS or Linux.
- Step 2: Connect to your PostgreSQL database over JDBC and reverse-engineer the tables and foreign keys.
- Step 3: Open the Query Builder from the Editors menu and build the SELECT by ticking columns and following foreign keys; type any WITH RECURSIVE statement into the SQL Editor beside it.
- Step 4: Commit the .dbs model file to Git so the team opens the same query layouts.
Download DbSchema and open the model against your own PostgreSQL database: Community Edition is free forever, and the 15-day Pro trial covers the Query Builder and the Relational Data Editor. If you run both engines, the MySQL walkthrough of the same workflow is a useful comparison.
Frequently asked questions
What is a visual query builder for PostgreSQL?
DbSchema's Visual Query Builder lets a data analyst assemble a SQL SELECT statement with the mouse: tick the columns you want, follow a foreign-key arrow to add the next table, and read the generated PostgreSQL in the live preview. It is a Pro edition feature.
Can a query builder handle PostgreSQL recursive CTEs?
Not the recursive part. PostgreSQL expresses recursion with WITH RECURSIVE, combining a non-recursive term and a recursive term, and DbSchema's Query Builder does not generate that statement - it builds SELECTs from tables and foreign keys. Build and run the anchor query in the builder to check the root rows, then write the WITH RECURSIVE statement in the SQL Editor against the same connection.
Is my query and editor state saved when I close the tool?
Yes. DbSchema saves the Query Builder and the SQL Editors inside the .dbs design model; when you close one it asks whether to keep it in the model or drop it permanently. Reopen the model and the Editors menu offers each saved editor under Restore. Saving a model to a file is a Pro edition feature.
Can the visual query builder accidentally alter my production database?
Not from the query canvas: it generates SELECT statements and carries no DDL verb. The rest of DbSchema is not read-only, though - the SQL Editor runs whatever you type, and the Relational Data Editor writes rows once you press Commit. Tick Read Only Connection on the connection dialog's Settings tab to have the server refuse writes; it is off by default.
How does the query builder handle PostgreSQL JOINs?
Click a column's foreign-key arrow and DbSchema adds the related table with the join already written, naming the key it used. Click the join-type label on the connector to switch between Inner Join, Left Join, Full Join, Exists and Not Exists. RIGHT JOIN is not on that list - reverse the table order and use Left Join, or write it in the SQL Editor.
Is there a limit to how many tables I can explore at once?
DbSchema's Relational Data Editor spans an unlimited number of related tables. Select a record in a parent pane and the child panes reload to show only the rows related to it, cascading through as many levels as you need, and back up to the parents as well.
Sources
Build the query on the diagram, not in a text box
DbSchema reverse-engineers your PostgreSQL schema, builds SELECTs by following foreign keys, and saves every query and editor inside the model file. Community Edition is free; the visual query builder is Pro, with a 15-day trial.
