Skip to content

Table designer

  • PostgreSQL
  • MySQL
  • MariaDB

The table designer creates a table or changes an existing one without writing DDL by hand. You edit the design in tabs, problems show as you type, and Save shows the exact script, with warnings for changes that could lose data, before anything runs.

The table designer for shop.products with the Review and save dialog open. Two data-loss warnings have been checked: narrowing price to numeric(8,2) affects no rows, and dropping weight_kg affects 86 rows. The script runs in one transaction, dropping weight_kg, altering the price type and adding a finish column.The table designer for shop.products with the Review and save dialog open. Two data-loss warnings have been checked: narrowing price to numeric(8,2) affects no rows, and dropping weight_kg affects 86 rows. The script runs in one transaction, dropping weight_kg, altering the price type and adding a finish column.
Review the ALTER script and see how many rows each risky change affects before you save.
  • New table. In the sidebar, right-click a Tables folder (or use its Actions button) and choose New table…. A table’s own menu has New table… too, for a table in the same schema. In the Objects tab, while it lists tables, use the New table button. From the command palette (Ctrl + Shift + P, Cmd on macOS), run Table: Create Table… and pick where the table goes.
  • Existing table. Right-click the table and choose Design table, or select it in the Objects tab and use the Design table button. In an ER diagram, choose Design in the table’s details.

With the keyboard, select a row in the sidebar, or a table in the Objects tab:

Action macOS Windows and Linux
Design the selected table Cmd + D Ctrl + D
New table in the selected database, schema, folder or table’s schema Cmd + N Ctrl + N

On PostgreSQL, tables live in a schema, so press the new-table keys on a schema or its Tables folder, not on the database. The menus show these keys beside Design table and New table…. See Keyboard shortcuts.

Type the table name in the toolbar, then work through the tabs:

Tab What you edit
Columns Name, type, Not null, Key, Default and Comment; identity (GENERATED BY DEFAULT or GENERATED ALWAYS) on PostgreSQL, AUTO_INCREMENT on MySQL and MariaDB
Indexes Name, columns or expressions, method; on PostgreSQL a partial index condition and included columns
Foreign keys Columns, referenced table and columns, On update and On delete; deferrable on PostgreSQL
Unique Unique constraints
Checks Check constraints with their condition
Triggers Name, timing and definition
Partitions Partition method and key, shown when the server supports partitions
Options Table options of the engine
Comment The table comment
SQL preview The script a save would run

Choose Add column, Add index, Add foreign key, Add unique constraint, Add check or Add trigger to add a row to a tab.

The design is checked as you type. A tab with errors shows how many, and the bar at the bottom lists the problems to fix before saving; click one to go to its tab. Save stays disabled while there are errors.

Revert throws away your edits. Reload reads the table from the server again. For an existing table, Open data opens its rows.

  1. Choose Save. The Review and save dialog shows how many statements will run.
  2. Read the warnings, grouped as Data loss, May fail on existing data and Notes.
  3. For a warning about existing rows, choose Check to count the rows it affects, and Show rows to list them.
  4. Read the script, then choose Run script.

A renamed column is written as a rename (RENAME COLUMN on PostgreSQL, the engine’s own form on MySQL and MariaDB), not as a drop and an add, so its data stays. Renaming name to full_name and adding a phone column produces these statements:

ALTER TABLE "shop"."customers" RENAME COLUMN "name" TO "full_name";
ALTER TABLE "shop"."customers" ADD COLUMN "phone" text;

On PostgreSQL the script runs in one transaction. MySQL and MariaDB commit each DDL statement at once: if one fails, the ones before it stay applied. The review says so.

On a connection marked production, the button reads Run on production.

  1. Right-click the table in the sidebar and choose Drop table…, or select it in the Objects tab and use the Drop table button.
  2. Querybara checks what depends on the table, then shows the script.
  3. Choose Drop table.

On PostgreSQL, a view that depends on the table blocks the drop until you drop the view. A foreign key in another table that references it blocks the drop on every engine. On MySQL and MariaDB, a view that uses the table is listed as a warning, because it stops working.

Both remove every row and keep the table. Right-click the table in the sidebar or the Objects tab:

Item Runs The auto-increment counter
Empty table… DELETE FROM the table keeps its value
Truncate table… TRUNCATE TABLE starts over

On PostgreSQL, Truncate table… adds RESTART IDENTITY, which restarts the table’s identity and serial sequences. MySQL and MariaDB reset AUTO_INCREMENT on TRUNCATE themselves.

A confirmation shows the exact statement first; choose Empty or Truncate to run it:

TRUNCATE TABLE "shop"."order_items" RESTART IDENTITY;

A foreign key in another table that references the table makes TRUNCATE fail. DELETE follows that foreign key’s On delete rule: it fails while referencing rows exist, unless the rule cascades or sets null. A table data tab of the table reloads afterwards, unless it holds changes you have not applied.

  1. Right-click the table and choose Duplicate table, then Structure and data… or Structure only….
  2. Edit the Name of the copy if you like. It starts as orders_copy for orders, or orders_copy_2 and on when that name is taken. A name already used by a table or view in the schema is refused.
  3. Read the script, which follows the name as you type, and choose Duplicate.

The copy goes in the same schema. CREATE TABLE … LIKE (INCLUDING ALL on PostgreSQL) gives it the columns, defaults, primary key, unique keys, indexes and checks of the table. Foreign keys and triggers are not copied. With the data, INSERT … SELECT copies every column the server does not compute; generated columns compute their values again. On MySQL, for the orders table:

CREATE TABLE `shop`.`orders_copy` LIKE `shop`.`orders`;
INSERT INTO `shop`.`orders_copy` (`id`, `customer_id`, `status`, `placed_at`, `total`, `note`) SELECT `id`, `customer_id`, `status`, `placed_at`, `total`, `note` FROM `shop`.`orders`;

On PostgreSQL the script runs in one transaction. The copy keeps the values of identity columns, and its own identity sequences move past them, so the next row gets the next number. A serial column of the copy still uses the sequence of the original table. On MySQL and MariaDB, if the rows fail to copy, the new empty table stays: the message says the first statement was applied.

On a connection marked production, Empty, Truncate and Duplicate ask again before they run. A read-only connection does not offer them.

querybara ddl prints the schema as a DDL script in dependency order.

Terminal window
querybara ddl postgres://[email protected]/shop --schema shop --out shop.sql

See querybara ddl for every flag.

Documents Querybara 0.1.19 · built from89dc752