SQL editor

​

In Studio

• Schema, Saved and History tabs on the left: Schema is the table browser with Find a table; Saved holds saved queries; History your recent runs.
• An editor that completes table and column names as you type.
• A builder for people who would rather click: choose a Table, Columns, a Join type, Operator and values, Combine with AND/OR, and Order by. The builder writes the SQL for you.
• Results below, with No rows. when empty. Long values are truncated; open a row to see everything.
• Saved queries, shared with your team: give a query a Name (“Orders per day”) and a Description (“What it answers”), and load it later. Delete with the trash action.
• Show how the database would run this query (explain): the plan, to see whether an index is used.

​

Create tables

​

Useful queries

-- Duplicates before a unique index
SELECT email, count(*) FROM customers GROUP BY email HAVING count(*) > 1;

-- Rows that would break a new NOT NULL
SELECT count(*) FROM orders WHERE customer_id IS NULL;

-- Biggest customers
SELECT customer_id, sum(total) AS spent FROM orders GROUP BY 1 ORDER BY spent DESC LIMIT 10;

-- Backfill a new column
UPDATE orders SET currency = 'USD' WHERE currency IS NULL;

​

Safety habits

1. Select before you update. Run the SELECT with the same WHERE, check the count, then change SELECT to UPDATE.
2. Take a backup before a large change. See Backups.
3. Writes bypass access rules and events at the SQL level. If you need events to fire, use the table editor, an import, or enable Capture changes made outside the API on the resource (Events and realtime).
4. Run risky statements in development first.

​

Reading a plan

• Index scan / index only scan: good: the index is used.
• Sequential scan on a big table: no usable index. Add one that matches the WHERE and ORDER BY (Indexes).
• A sort on millions of rows: add an index that already has the order.