Skip to content

QueryBuild without SQL

Query Builder

The Query Builder in DBCode lets you construct SELECT queries visually. Add tables to a canvas, configure joins by dragging between columns, pick columns, set filters, and see the generated SQL update in real time. You can also use natural language to describe what you need and let AI build or modify the query for you.

Query Builder with two joined tables, selected columns, filters, and SQL preview

There are three ways to open the Query Builder:

  1. Tree icon — Click the Query Builder icon next to a database in the DB Explorer
  2. Right-click — Right-click a database and select Query Builder
  3. Command Palette — Run DBCode: Query Builder. If no connection is in context, you’ll be prompted to select one
  4. From a table — Right-click a table, view, materialized view or synonym, including one in Favorites (or use its hover icon), and select Query Builder, or click the Query Builder button in its data view toolbar. The builder opens with it already on the canvas

The Query Builder opens as a full editor tab with the connection’s schema pre-loaded.

The Query Builder is divided into four areas:

  • Toolbar (top) — AI input bar, Run/Explain button, Save, and Open in Editor
  • Canvas (left) — Drag-and-drop area where tables and the joins between them appear
  • Config Panel (right) — Stacked sections for column selection, filters, GROUP BY, HAVING, ORDER BY, DISTINCT, and LIMIT
  • SQL Preview (bottom) — Read-only, syntax-highlighted SQL generated from the current model. Updates live as you make changes. Resizable by dragging the top border

Query Builder interface showing three joined tables, selected columns, a filter, and generated SQL

  1. Click the Add Table button on the canvas
  2. Search for a table by name in the dropdown — it filters as you type
  3. Select a table to place it on the canvas

The list also has views, materialized views, foreign tables and synonyms, each marked with its icon. They work on the canvas like tables. A synonym shows the columns of the table it points to. Join a view by dragging between columns, as views carry no foreign keys to join on automatically.

Each table shows the table name, its columns with data types, and primary/foreign key indicators. A table with no relationship to the others is placed to the right of the existing tables; a table with a foreign key to one already on the canvas is laid out next to it. You can drag tables to reposition them on the canvas.

Hover a table’s header to see its full name, including the database, for example sakila.film.

The minimap in the corner of the canvas shows every table. Drag its view to move around the canvas, or scroll over it to zoom.

To remove a table, select it and press Delete or Backspace.

To swap a table on the canvas for another one, open the table’s menu and select Change table…, then pick the new table, from any database. The table keeps its position and alias, and every column the new table also has stays in place: selected columns, filters, joins, GROUP BY and ORDER BY. Column names match regardless of case. A HAVING expression picked from the aggregate list switches to the new table too.

Selected columns, filters, joins, GROUP BY and ORDER BY entries that refer to a column the new table doesn’t have are marked with a warning, with a tooltip naming the missing column. The column shows as a marked row on the table, and a join on it draws a red line. Run is disabled until you fix or remove each marked item, and the SQL toolbar lists the missing columns next to it. The same warnings appear when you open a saved query that refers to a column that has since been dropped. A column written in a different case, for example by AI assist, takes the table’s own spelling.

Tick a column’s checkbox on its table to add it to the SELECT clause. The Columns section in the Config Panel then lists the selected columns in order:

  • Drag to reorder: grip handles on the left let you reorder selected columns, including across tables
  • Alias: click the alias field to give a column an output name
  • Aggregate: choose an aggregate function (COUNT, SUM, AVG, MIN, MAX, plus COUNT DISTINCT, SUM DISTINCT and AVG DISTINCT) from the dropdown. When you apply an aggregate, GROUP BY is configured automatically for non-aggregated columns
  • Duplicate: click + on a column’s row to add the same column again, for example once plain and once with COUNT. Each copy gets its own alias

Click + Add column for a searchable menu of every column. Each table has a select-all checkbox to add or remove all of its columns at once, handy for wide tables and available even when only one table is on the canvas.

When you add a second table that has a foreign key relationship with an existing table, DBCode automatically suggests a join.

Two tables on the canvas connected by an INNER JOIN with selected columns highlighted

To create a join manually:

  1. Drag from a column on one table to a column on another
  2. The join appears as an edge connecting the two tables

To join on more than one column pair, drag each pair in turn. Every pair draws its own line between the tables. The join type label and the remove button sit on the first line and apply to the whole join, which is generated as a single ON ... AND ... clause.

A join stays drawn when its column is hidden because the table is collapsed with Show less: the line ends on the table’s Show all row instead.

Hover any join line to highlight it and see its condition, for example LEFT JOIN: film.film_id = inventory.film_id. On a join with several column pairs, the hovered line also shows a small × that removes just that pair (its tooltip names the pair), while the remove button on the join label removes the whole join.

Click a join edge to see the join type picker. Hover over a type to preview its Venn diagram and description, then click to select:

  • INNER JOIN — Only matching rows from both tables
  • LEFT JOIN — All rows from the left table, matching rows from the right
  • RIGHT JOIN — All rows from the right table, matching rows from the left
  • FULL JOIN — All rows from both tables
  • CROSS JOIN — Every combination of rows (Cartesian product)

To remove a join, select the edge and press Delete or Backspace.

The Where section lets you build WHERE clause conditions without writing SQL:

  1. Click + condition
  2. Pick a column. The picker lists every column grouped by table, and typing the first letters of a column name narrows the list
  3. Choose an operator: =, !=, >, <, >=, <=, LIKE, NOT LIKE, CONTAINS, NOT CONTAINS, IN, NOT IN, IS NULL, IS NOT NULL, BETWEEN, or NOT BETWEEN
  4. Enter a value, or click the list icon next to the value to pick one

DBCode quotes each value based on the column’s type: text is quoted ('GER'), numbers stay bare (18), and IN lists are split on commas and quoted element by element ('EU', 'US'). To pass a value through verbatim, such as a function call like NOW(), toggle the fx button on that condition. For text comparisons, the Aa button makes the match case-insensitive by wrapping both sides in UPPER().

The list icon next to a condition’s value opens a picker. Click Load values to fetch the column’s existing values from the database (the first 500, sorted), then click one to use it. For IN and NOT IN, tick as many values as you need. Enum columns show their values straight away without running a query.

For =, !=, <, >, <= and >=, the same picker has a Columns section. Pick a column there to compare against it, for example rental_rate > replacement_cost. The value slot then shows the chosen column; click its clear button to type a value again.

The conditions in a group are joined by a single Match operator, AND or OR, selected at the top of the group. To mix AND and OR, nest a group: click + group to add a sub-group with its own Match operator. This produces parenthesised SQL, for example language = 'GER' AND (region = 'EU' OR region = 'US').

Drag the grip handle on any condition or group to reorder it, or to move it into or out of a group. Removing the last condition from a group removes the empty group automatically.

The Group By section lets you group results by one or more columns. Select columns from the dropdown to add them. When you apply aggregate functions to columns in the Columns section, non-aggregated selected columns are automatically added to GROUP BY. Removing the last aggregate removes them again.

The Having section adds conditions that filter grouped results (like WHERE, but applied after aggregation). Each condition has an expression, an operator and a value. Type the expression, or click the list icon next to it to pick one of the aggregate columns in the SELECT. Picking one fills in the expression with that aggregate, such as COUNT(film.film_id), replacing what was there.

HAVING conditions combine the same way as filters: set the group’s Match operator to AND or OR, click + group to nest a group, and drag conditions to reorder or move them between groups. A condition with no expression is left out of the SQL.

The Order By section controls result sorting:

  1. Click Add to add a sort column
  2. Pick the column from the searchable list. Aggregates and aliases from the SELECT list appear in a final group
  3. Toggle between ASC (ascending) and DESC (descending)
  4. Drag to reorder when multiple sort columns are defined
  • DISTINCT — Toggle the checkbox to eliminate duplicate rows
  • LIMIT — Enter a number to cap the result set size

A new query starts with the DISTINCT and LIMIT you last used on the same connection.

The Output section turns the query into a statement that writes its results to a new table. Tick Write result to new table, then enter the table name and, if your database has them, its database or schema. DBCode generates CREATE TABLE ... AS SELECT, or SELECT ... INTO on databases that use that form.

Where the database supports them, two options appear:

  • OR REPLACE: replaces the table if it already exists.
  • IF NOT EXISTS: creates the table only when it doesn’t exist yet.

When you tick Write result to new table, the database, schema and options you last used on the same connection are filled in.

The table name can include placeholders, which the SQL fills in:

  • {sourcetable}: the first table in the query
  • {database}: the current database
  • {user}: the connection’s user, or your operating system user when the connection has none
  • {timestamp}: the time you ticked Write result to new table or last edited the name, as yyyymmddthhmmss

For example, {sourcetable}_{timestamp} writes to film_20261110t143000. Type { in the table name to list them, keep typing to narrow the list, and press Enter or Tab to insert one. The icon next to the table name opens the same list. A name with a placeholder is remembered for the connection along with the other Output choices, and a saved query keeps the placeholders and fills them in again each time you open it.

The text input at the top of the toolbar accepts natural language descriptions. Type what you need and press Enter:

  • Build from scratch — “Show me all customers with orders over $100 in the last 30 days”
  • Modify existing — “Add a date filter for last month” or “Change to LEFT JOIN”
  • Aggregate — “Show total sales by category”

The AI reads your current query model and schema, then returns an updated model. The canvas, config panel, and SQL preview all update to reflect the changes. You can undo AI changes with Ctrl+Z / Cmd+Z.

Click the Run button or press Ctrl+Enter to execute the generated SQL. Ctrl+Enter is the run key on macOS too, the same as in the SQL Editor. Use the dropdown for Explain / Analyze where supported by your database. Results appear in the standard Results Pane, just like running a query from the SQL Editor.

To use a different key, open VS Code’s Keyboard Shortcuts editor, search for “DBCode” and change DBCode: Run Query Builder Query.

The Query Builder tab stays open so you can refine and re-run.

Click Save to Library to save the query builder state to your Library panel:

  • First save — Prompts for a name and scope (Project or Personal), then stores the full visual state (tables, joins, filters, columns, positions) along with the generated SQL
  • Subsequent saves — Updates the saved item silently (no prompt)
  • Save As New — Creates a copy with a new name

Opening a saved query builder from the Library restores the full visual state — table positions, joins, column selections, filters, and all configuration. If the query builder is already open, clicking the saved item reveals the existing tab instead of opening a duplicate.

Click Open in Editor to copy the generated SQL into a new SQL Editor tab bound to the same connection. This is useful when you want to hand-edit the query or save it as a .sql file.

Where supported, tick Fully qualify names in the Options section to add the database name to every table, for example database.table or database.schema.table. This covers FROM, every JOIN and the output table. Untick it to write tables in the current database by their shorter name. DBCode remembers the setting for all your Query Builders.

The Query Builder works with all databases supported by DBCode. SQL generation adapts to each dialect automatically:

  • Identifier quoting (double quotes, backticks, or square brackets)
  • LIMIT vs TOP vs FETCH FIRST for row limiting
  • Schema qualification where applicable

All changes to the query model support undo and redo:

  • Ctrl+Z / Cmd+Z — Undo
  • Ctrl+Shift+Z / Cmd+Shift+Z — Redo

This includes AI-generated changes, so you can safely experiment and revert.