A Visual Query Builder for PostgreSQL

A reporting query needs columns out of two tables in a normalized PostgreSQL schema. Writing it by hand means looking up the foreign key first and then retyping its columns into an ON clause. DbSchema's Query Builder assembles that SELECT on the diagram instead: tick the columns you want, click the arrow beside a column to add the table its foreign key points at, and the generated SQL appears at the bottom of the builder.

The examples run on PostgreSQL 18 against these two tables:

CREATE TABLE users (
  user_id int PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE tasks (
  task_id int PRIMARY KEY,
  title text NOT NULL,
  created_by int NOT NULL,
  parent_task_id int,
  done boolean NOT NULL,
  CONSTRAINT tasks_created_by_fkey FOREIGN KEY (created_by) REFERENCES users,
  CONSTRAINT tasks_parent_task_id_fkey FOREIGN KEY (parent_task_id) REFERENCES tasks
);
INSERT INTO users VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO tasks VALUES
  (10, 'Import the catalog', 1, NULL, true),
  (11, 'Map the columns', 1, 10, false),
  (12, 'Load the sample rows', 2, 10, false),
  (13, 'Write the release note', 2, NULL, false);

What the Query Builder puts in the SELECT list

Three actions open DbSchema's Query Builder: click a table header in the diagram, drag a table from the diagram into an empty builder, or choose New Query Builder from the Editors menu. DbSchema read both tables and both foreign keys out of the PostgreSQL catalog when it reverse-engineered the connection, so everything the query needs is already on the canvas to be pointed at.

Tick the checkbox next to a column in DbSchema's Query Builder and it enters the SELECT list; untick it and it leaves. DbSchema regenerates the SQL at the bottom of the builder on every change, so ticking task_id, title and done gives you this:

SELECT task_id, title, done FROM tasks;

Conditions and aggregates are right-click menus on those same columns in DbSchema. Right-click done and choose Filter to put a WHERE condition on it, with the comparison operator and the value set in the filter dialog. For a count of the open ones per person, switch on Group By with the toggle button in the Query Builder toolbar, then right-click task_id and choose Aggregate to apply COUNT; MIN, MAX, SUM and AVG sit in the same menu. Ticked columns that carry no aggregate become the GROUP BY columns:

SELECT created_by, count(task_id) FROM tasks WHERE done = false GROUP BY created_by;

Following a foreign key writes the join

A join condition in PostgreSQL is specified in the ON or USING clause, or implicitly by the word NATURAL[1], and a foreign key already says which columns belong in it. Click the small arrow icon next to a column in the Query Builder and DbSchema follows that foreign key, adds the related table to the canvas, and writes the predicate from the key. Starting at users and following tasks_created_by_fkey builds this:

SELECT u.name, t.title
FROM users u
INNER JOIN tasks t ON t.created_by = u.user_id;

Linus is absent, because an inner join keeps a row of users only where some row of tasks satisfies the condition. Click the join type label on the connecting line and DbSchema rewrites the query as a LEFT JOIN, which adds a row with nulls in the tasks columns for every user that matched nothing:

SELECT u.name, t.title
FROM users u
LEFT JOIN tasks t ON t.created_by = u.user_id;

The third label on that control is EXISTS, and it answers a narrower question: which users have at least one task, each named once, with no column of tasks in the result.

SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM tasks t WHERE t.created_by = u.user_id);

A RIGHT JOIN is the converse of a left join[1], so put the table you want every row of on the left of the connector and pick LEFT JOIN, and you have written it. A FULL JOIN, which keeps the unmatched rows of both tables, goes into the SQL Editor that runs against the same connection; press Ctrl+Space there and DbSchema completes table names, column names, keywords and functions from the connected schema. One query built end to end on the canvas is walked through in building a query across joined tables.

Joins are not the only route across 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, which is the ground covered in exploring master-detail data across foreign keys.

Where the WITH RECURSIVE statement goes

tasks_parent_task_id_fkey points tasks at itself, so the table carries a hierarchy: a task with a parent, whose parent may have a parent of its own. DbSchema draws that key as a line from the table back to itself, and the Query Builder assembles and runs the anchor SELECT, the one that reads the roots, so you can check them before writing anything longer. The recursion is not something a canvas expresses, and the statement for it goes into the SQL Editor beside 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, and only the recursive term may refer to the query's own output[2]:

WITH RECURSIVE subtasks AS (
  SELECT task_id, title, parent_task_id, 1 AS depth
  FROM tasks
  WHERE parent_task_id IS NULL
  UNION ALL
  SELECT t.task_id, t.title, t.parent_task_id, s.depth + 1
  FROM tasks t
  JOIN subtasks s ON t.parent_task_id = s.task_id
)
SELECT task_id, title, depth FROM subtasks ORDER BY depth, task_id;

RECURSIVE lets you write the query recursively, but PostgreSQL evaluates it iteratively. It runs the non-recursive term, puts those rows in a working table, and then, for as long as the working table is not empty, runs the recursive term over the current contents and replaces the working table with the rows that came back[2]. Two consequences follow for the query above. The unioned terms have to agree on column types, which is why depth is an integer on both sides. And a cycle among the parent links would keep the working table filled forever, so UNION, which discards duplicate rows where UNION ALL keeps them, is the brake to reach for when the data is not guaranteed acyclic. The JOIN subtasks s ON t.parent_task_id = s.task_id line is an ordinary inner join onto the working table, and it reads the way any of the PostgreSQL joins do.

What the model file keeps between sessions

A Query Builder opens inside the diagram, is saved to the model file, and is reopened from the Editors menu. Close one and DbSchema asks whether to keep it in the design model or drop it permanently, so the joins your team mapped on Friday come back on Monday with the same tables and the same join types.

Saving the model writes a .dbs file and sends nothing to PostgreSQL. The file is plain XML holding the schema structure, the diagrams, the virtual foreign keys and the comments, which is what makes a saved query reviewable in a pull request rather than merely storable. DbSchema has a Git client for exactly that: open the Model menu, choose Git — Collaborative Design, and the dialog clones a repository, stages and commits files, pushes, pulls, stashes and creates branches. A database password is a separate matter, saved only when you tick Remember on the connection dialog and kept on your own computer, so what travels with the model is the queries and the schema. Saving the model to a file is a Pro edition feature, and the wider version of this workflow is in querying a database without writing SQL.

Running the query and reading the rows

Running the query sends the generated SELECT to PostgreSQL over the JDBC connection and fills the result pane under the canvas. Nothing in the schema moves, because a SELECT reads. To take the whole answer away with you, click Save in the result pane: DbSchema re-executes the query and writes every row to a file, so the rows past the end of the screen are in the export too.

Where the answer is not one result set but a row and everything hanging off it, the Relational Data Editor is the other half of DbSchema. Open it from the Editors menu with New Relational Data Editor, or right-click a table header in the diagram and choose Open in Relational Data Editor; it appears in the Tools panel at the bottom of the screen. Click the foreign key button on a table header panel and DbSchema descends into that child table, opening a pane already filtered to the row selected in the parent. Click a different parent row and every child pane reloads, cascading as many levels deep as you need. Click a column header in any pane to open its filter dialog, on several columns at once if that is what narrows the view.

The Relational Data Editor also writes, which the query canvas never does. Insert adds a row, Edit or a double-click changes a cell, Delete removes the selected row, and Commit is what carries any of it to PostgreSQL, while Rollback discards what is pending.

What the query canvas can change in the database

The Query Builder emits SELECT statements, so what it changes is the result on your screen. Editing the diagram is offline in the same way: adding a column or drawing a foreign key there changes the .dbs model file, and PostgreSQL learns about it only when you open Schema → Synchronize Model with Database in DbSchema, read the generated SQL statements, which you can edit before they run, and click Execute.

For a connection that must never write, DbSchema closes the door at the source. Tick Read Only Connection on the Settings tab of the connection dialog and DbSchema opens the connection in read-only mode, so the database refuses every change made through it. The same tab carries Highlight, which colors a connection as Production in the application, so you can see at a glance which server you are pointed at. Set up that way, you open a production model, map joins across it and prototype reporting queries with the write paths shut at the server. That arrangement is what are visual query builders safe on production works through in full.

Sharing one model file across the analyst team

A .dbs file that already holds the joins your team agreed on is worth committing next to the code that queries the same tables. An analyst who pulls it opens the Query Builder as you left it instead of rebuilding it, and because the model is independent of any one server, the same file opens against a local, a staging or a production PostgreSQL connection.

Which edition covers this

DbSchema's Community edition is free and covers every database it supports, PostgreSQL among them: connecting, reverse-engineering, the interactive diagrams and the SQL editor. The Query Builder, relational data browse, saving the model to a file, schema synchronization and the HTML5 documentation are Pro, and the 15-day trial covers all of them. Which database tool edition a team actually needs works through that choice, and the same workflow on MySQL is in a visual query builder for MySQL.

Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, and open the Query Builder from the Editors menu: tick the columns of one table, then click the arrow beside a foreign key column and let DbSchema write the join. The Query Builder and the Relational Data Editor are Pro edition features, with a 15-day trial.

Frequently asked questions

Can a query builder handle PostgreSQL recursive CTEs?

DbSchema's Query Builder assembles SELECT statements from tables and foreign keys, and the WITH RECURSIVE statement goes into the SQL Editor beside it. Execute Query there runs the statement at the cursor, delimited by semicolons, and shows the rows as a table. Run Script runs the whole editor content instead and prints the output as plain text, with several result sets in the same pane.

How does the query builder handle PostgreSQL JOINs?

DbSchema writes the ON clause from the foreign key you follow, and the join type label on the connector switches the query between INNER JOIN, LEFT JOIN and EXISTS. Where the schema declares no foreign key, drag one column onto another in the diagram to create a virtual foreign key: it lives in the .dbs model file rather than in PostgreSQL, and the Query Builder joins on it exactly like a declared one.

Is my query and editor state saved when I close DbSchema?

DbSchema saves a Query Builder and a SQL Editor alike inside the .dbs design model, and closing one asks whether to keep it or drop it. To remove one you kept, right-click it in the structure tree under Diagrams and choose Drop.

What can the Query Builder change in my PostgreSQL database?

Nothing in the data or the schema: the Query Builder builds SELECT statements. A write reaches PostgreSQL through DbSchema's SQL Editor or its Relational Data Editor, where an INSERT, UPDATE or DELETE waits for a click on Commit. The SQL Editor's Run Script button also offers Auto-Commit, which commits each statement as it runs.

Is there a limit to how many tables I can explore at once?

DbSchema's Relational Data Editor cascades through an unlimited number of tables in a single view, so what bounds a cascade is the foreign keys in the schema rather than a count of panes.

Sources

1. postgresql.org
2. postgresql.org