Working with data

1. 1
   In Workbench, open Data, then the Documents tab.
2. 2
   Click Connect a database at the top of the Workspace tree.
3. 3
   On the I have a database tab, give it a Name and paste its Connection URI, which looks like postgresql://user:password@host:5432/dbname. Supabase, Neon, Amazon RDS and your own server all work the same way.
4. 4
   Pick the schema to show if asked. The database appears in the tree with its tables under it. Click a table to open it.

The menu next to the database in the tree has three items you will use on this page: Change schema… to show a different schema, Connection settings… for the switches below, and Activity… for the log.

A new connection is read-only. You can browse, query and export, and nothing else: BotSpot's connection to it is locked so it cannot write even by accident. Two switches in Connection settings… unlock more, one step at a time.

• Writable lets rows be added, edited and deleted, and CSV files imported.
• Allow schema changes lets tables, columns, indexes and foreign keys be created, renamed and deleted. It needs Writable on first. Turning Writable off turns this off too.

The table header shows a Writable or Read only badge, and the toolbar only shows the buttons the switches allow. Turn Allow schema changes off again when the schema work is done.

Only organisation owners and admins can turn these switches on, or use what they unlock. See Who can do what.

Needs: Writable.

1. 1
   Open the table and click + Row.
2. 2
   A blank row appears at the top. Each box shows the column's type as a hint.
3. 3
   Fill in the values and click Save. The X cancels.

Leave a box empty and the database fills in that column's default, such as an auto-generated ID, or NULL. If a value is the wrong type or breaks a rule on the table, the row is not added and the message says what the database objected to.

Adding many rows at once? Use CSV import.

Edit a value

Needs: Writable.

1. 1
   Double-click the cell.
2. 2
   Type the new value. Enter saves, Escape cancels. Clearing the cell stores NULL.

Primary key cells cannot be edited.

Delete a row

Needs: Writable. Click the bin at the end of the row and confirm.

Delete a column

Needs: Allow schema changes.

1. 1
   Hover the column name in the header. A pencil and a bin appear.
2. 2
   Click the bin and confirm. Every row loses that column's value.

The primary key column, marked PK, cannot be deleted.

Delete a table

Needs: Allow schema changes.

1. 1
   Open the table and click Delete table in the toolbar.
2. 2
   Type the table's name to confirm.
3. 3
   Leave Also drop anything that depends on it off. If another table points at this one with a foreign key, the delete is then refused, which tells you something depends on it. Tick the box only if you have seen that refusal and still want both gone.

Needs: Allow schema changes.

• Table: click the pencil next to the table name at the top, type the new name in Rename table, and save.
• Column: hover the column name, click the pencil, type the new name and press Enter. Escape cancels. The primary key column cannot be renamed.

Renaming changes the real database. Anything else that uses the old name, such as reports, other apps, or saved queries, has to be updated too.

Import a CSV into a table

Needs: Writable. The table must already exist; create it first if it does not.

1. 1
   Open the table and click Import CSV.
2. 2
   Drag a .csv file onto the box, or click browse. The first line of the file must be the column headings.
3. 3
   BotSpot shows how many rows it found. Next to each heading in your file, pick the table column it goes into, or Skip.
4. 4
   Start the import. Rows go in batches of 500 and a progress count shows how far it has got.

If a batch is refused, for example because one value is the wrong type, the import stops and the message says how many rows went in before it. Those rows stay in the table. Fix the file, remove the rows that already went in, and import the rest.

Export a table to CSV

Anyone in your organisation can do this, on any connected database. Open the table and click Export CSV. The file downloads with every row, up to 50,000 rows. The message tells you how many were exported; if the table has more, it says you got the first 50,000.

Anyone in your organisation can do this, on any connected database, read-only or not.

1. 1
   Open any table and click Console.
2. 2
   Type a query, for example SELECT * FROM orders WHERE status = 'open' ORDER BY created_at DESC.
3. 3
   Click Run, or press ⌘⏎ (Ctrl+Enter on Windows). The result appears below with its row count.

• SELECT and WITH only, one statement at a time. You can query any table in the schema, and join them.
• Results stop at the connection's row limit (1,000 rows unless it was set differently) and say (truncated) when there were more.
• A query that runs too long is stopped.

Why only SELECT? A typo in a SELECT costs you a slow query. A typo in an UPDATE or DELETE can wipe a table. Every change in BotSpot goes through the forms on this page instead, which check the table and columns exist, change exactly one row by its primary key, and record it in the activity log.

Create a table

Needs: Allow schema changes.

1. 1
   In the Workspace tree, click the + next to the database (Create table…).
2. 2
   Enter a Table name, for example customers.
3. 3
   Add one line per column: a name and a type. Tick PK on the primary key column.
4. 4
   Add column adds another line. Click Create table when done. The table appears in the tree.

Always give a table a primary key. Without one, its rows cannot be edited or deleted here. The easiest is a column named id of type Auto-generated ID (UUID) or Auto-increment ID (integer), ticked as PK; the database then fills it in for every new row.

Add a column to an existing table

Needs: Allow schema changes.

1. 1
   Open the table and click + Column.
2. 2
   Enter the Column name and pick a Type.
3. 3
   Optionally set a Default value. Every existing row gets it; leave it blank for NULL.

Column types

Other schema changes on this page: rename, delete a column or table, indexes and foreign keys. Changing a column's type is not available yet.

An index makes lookups and sorting on a column fast, the way a book's index saves reading every page. Add one on columns you often filter or sort by, such as an email or a date. Needs: Allow schema changes.

1. 1
   Open the table and click Indexes. Existing indexes are listed, including the primary key's.
2. 2
   Click Create index and tick the column or columns.
3. 3
   Tick Unique if no two rows may share the same value, for example an email. Give it a name, or leave the suggested one.
4. 4
   Click Create.

• The bin next to an index drops it. The primary key's index cannot be dropped.
• A Unique index is refused if the table already has duplicate values in those columns. Remove the duplicates first.
• While an index is being built, changes to that table wait. On a large table, create indexes at a quiet time.

Every change made through BotSpot to a connected database is recorded: rows added, edited, deleted and imported, and every table, column, index and foreign key change. Failed attempts are recorded too, with the reason.

1. 1
   In the Workspace tree, open the menu next to the database.
2. 2
   Choose Activity….

Each line says what was done and to which table, with a green tick if it worked or a red cross and the error if it did not, newest first. Anyone in the organisation can read it; reading it changes nothing.

The log covers changes made through BotSpot. Changes made to the same database from another tool or app are not in it.

A foreign key links a column in one table to a row in another, for example each order's customer_id to a row in customers. The database then refuses an order that points at a customer who does not exist. Needs: Allow schema changes.

1. 1
   Open the table that holds the link (here, orders) and click Foreign keys.
2. 2
   Click Add foreign key and tick the column (customer_id).
3. 3
   Under References table, pick the table it points to (customers). The link always goes to that table's primary key, so tick as many columns as its primary key has, usually one.
4. 4
   Choose what happens When a referenced row is deleted, optionally name it, and click Create.

• The column types must match the primary key's, and every existing value must already point at a real row. Otherwise the database refuses and says why.
• The bin next to a foreign key drops it. The data stays; only the link is removed.
• A table that others point at cannot be deleted until those links are dropped, unless you tick Also drop anything that depends on it.

Two things decide whether a change is allowed, and both must say yes:

• The database's switches: Writable, and Allow schema changes. These say what may be done to this database at all.
• Your role in the organisation: this says who may do it, and who may flip those switches.

These are organisation roles. A connected database belongs to the whole organisation, not to one workspace, so a workspace role such as workspace admin does not add anything here. Rows that need Writable or Allow schema changes also need that switch on, whoever you are.

A member can still see the buttons on a writable database. When they try, BotSpot refuses and says they are not allowed; nothing is changed and nothing is half-done.

Giving someone access: make them an organisation admin. Today these two permissions are the same for every organisation and cannot be handed to members one by one. If your organisation needs that, tell us.