Skip to main content

Query your database from the Lakebase SQL Editor

Beta

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

The Lakebase SQL Editor runs queries on your Lakebase databases directly from the Lakebase App. It offers Postgres-native features such as EXPLAIN/ANALYZE, psql-style meta-commands, and exporting results to CSV/JSON/XLSX.

Use the Lakebase SQL Editor​

To use the SQL Editor:

  1. Open the Lakebase App and select your database project.
  2. Select SQL Editor from the sidebar, then select a branch and database.
  3. Enter a query into the editor and click Run to view the results.

Lakebase SQL Editor

You can use the following query to try the SQL Editor. The query creates a table, adds data, and retrieves the data from the table.

Postgres
CREATE TABLE IF NOT EXISTS playing_with_lakebase(id SERIAL PRIMARY KEY, name TEXT NOT NULL, value REAL);
INSERT INTO playing_with_lakebase(name, value)
SELECT LEFT(md5(i::TEXT), 10), random() FROM generate_series(1, 10) s(i);
SELECT * FROM playing_with_lakebase;

Running multiple query statements at once returns a separate result set for each statement. The result sets are displayed in separate tabs, numbered in order of execution.

To clear the editor, select the contents of the text box and delete it.

Explain and Analyze​

The Lakebase SQL Editor provides Explain and Analyze features.

Lakebase SQL Editor Explain and Analyze

  • The Explain feature runs the specified query with the Postgres EXPLAIN command, which returns the execution plan for the query. The Explain feature only returns a plan with estimates. It does not execute the query.
  • The Analyze feature runs the specified query with EXPLAIN ANALYZE. The ANALYZE parameter causes the query to be executed and returns actual row counts and run times for plan nodes along with the EXPLAIN estimates.

Understanding the information provided by the Explain and Analyze features requires familiarity with the Postgres EXPLAIN command and its ANALYZE parameter. Refer to the EXPLAIN documentation and the Using EXPLAIN topic in the PostgreSQL documentation.

Export data to CSV, JSON and XLSX​

The Lakebase SQL Editor supports exporting your data to JSON, CSV and XLSX. Access the download button from the bottom right corner of the SQL Editor page. The download button only appears when there is a result set to download.

Lakebase SQL Editor Export

Expand results section of the SQL Editor window​

Expand the results section of the SQL Editor window by selecting the expand window button from the bottom right corner of the SQL Editor page.

Lakebase SQL Editor Expand

Meta-commands​

The Lakebase SQL Editor supports using Postgres meta-commands, which act like shortcuts for interacting with your database. If you are already familiar with using meta-commands from the psql command-line interface, you can use many of those same commands in the Lakebase SQL Editor.

Meta-commands can speed up your workflow by providing quick access to database schemas and other critical information without needing to write full SQL queries.

Here are some commonly-used meta-commands within the Lakebase SQL Editor:

  • \dt — List all tables in the current database
  • \d [table_name] — Describe a table's structure
  • \l — List all databases
  • \? — A cheat sheet of available meta-commands
  • \h [NAME] — Get help for any Postgres command (e.g., \h SELECT)
note

Not all psql meta-commands are supported in the SQL Editor. To get a list of supported commands, use \?.

For a complete list of meta-commands and their usage, see Meta-commands in psql.

How to use meta-commands​

To use a meta-command in the SQL Editor, enter the meta-command in the editor (just like a SQL query) and click Run. The result will be displayed in the output pane.

Last updated on