Connect with psql

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.

psql is the native command-line client for PostgreSQL. It provides an interactive session for sending commands to Postgres and running ad-hoc queries. For more information about psql, see the psql reference in the PostgreSQL documentation.

Prerequisites

• psql version 14 or higher installed on your system
• A Lakebase database project with a Postgres role configured

note

We recommend using native Postgres password authentication when connecting with psql. Native Postgres passwords don't expire hourly like OAuth tokens, making them better suited for applications that cannot refresh credentials frequently. See Authentication overview for details about authentication methods.

Install psql

If you don't have psql installed, follow these steps:

MacOS (Intel)

Bash

brew install libpq
echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

MacOS (Apple Silicon)

Bash

brew install libpq
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Linux

Bash

sudo apt update
sudo apt install postgresql-client

Windows

Download and install PostgreSQL from postgresql.org/download/windows. Ensure psql is included in the installation.

Connect to your database

The easiest way to connect using psql is with a connection string.

1. Navigate to the Lakebase App and select your database project
2. Click Connect to open the database connection modal
3. Select the branch, compute, and database you want to connect to
4. Select a Postgres role from the dropdown (we recommend using a native Postgres role with password authentication)
5. Copy the connection string

Run the psql client with the connection string:

Bash

psql 'postgresql://role_name:password@ep-abc-123.databricks.com/databricks_postgres?sslmode=require'

Replace the example values with your actual connection details.

note

Lakebase requires that all connections use SSL/TLS encryption. The sslmode=require parameter is included in all connection strings by default.

Run queries

After establishing a connection, you can run SQL queries directly:

SQL

CREATE TABLE my_table AS SELECT now();
SELECT * FROM my_table;

Meta-commands

psql supports meta-commands that provide shortcuts for interacting with your database. These commands start with a backslash (\) and can significantly speed up your workflow by providing quick access to database schemas and other critical information without needing to write full SQL queries.

Benefits of meta-commands

Meta-commands can significantly speed up your workflow by:

• Providing quick access to database schemas and structure
• Viewing database objects without writing full SQL queries
• Managing database connections and sessions
• Getting help on SQL commands

Common meta-commands

Here are some frequently used meta-commands:

• \l — List all databases
• \c DATABASE_NAME — Connect to a different database
• \dt — List all tables in the current database
• \d TABLE_NAME — Describe a table's structure
• \di — List indexes
• \dv — List views
• \du — List roles
• \dn — List schemas
• \df — List functions
• \? — Show a cheat sheet of available meta-commands
• \h [NAME] — Get help for any Postgres command (e.g., \h SELECT)
• \q — Quit psql

Informational meta-commands

For detailed information about database objects (options: S = show system objects, + = additional detail):

Bash

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \dA[+]  [PATTERN]      list access methods
  \dAc[+] [AMPTRN [TYPEPTRN]]  list operator classes
  \dAf[+] [AMPTRN [TYPEPTRN]]  list operator families
  \dAo[+] [AMPTRN [OPFPTRN]]   list operators of operator families
  \dAp[+] [AMPTRN [OPFPTRN]]   list support functions of operator families
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dconfig[+] [PATTERN]  list configuration parameters
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \dD[S+] [PATTERN]      list domains
  \ddp    [PATTERN]      list default privileges
  \dE[S+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \det[+] [PATTERN]      list foreign tables
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
                         list [only agg/normal/procedure/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[S+] [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl[+]                 list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
                         list operators
  \dO[S+] [PATTERN]      list collations
  \dp[S]  [PATTERN]      list table, view, and sequence access privileges
  \dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]
  \drds [ROLEPTRN [DBPTRN]] list per-database role settings
  \drg[S] [PATTERN]      list role grants
  \dRp[+] [PATTERN]      list replication publications
  \dRs[+] [PATTERN]      list replication subscriptions
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dx[+]  [PATTERN]      list extensions
  \dX     [PATTERN]      list extended statistics
  \dy[+]  [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \lo_list[+]            list large objects
  \sf[+]  FUNCNAME       show a function's definition
  \sv[+]  VIEWNAME       show a view's definition
  \z[S]   [PATTERN]      same as \dp

The + modifier adds additional details, and the S modifier includes system objects.

For a complete list of meta-commands, run \? in psql or see psql Meta-Commands in the PostgreSQL documentation.

Connection options

You can also specify connection details using individual parameters:

Bash

psql -h ep-abc-123.databricks.com -p 5432 -U lakebase -d lakebase

Or set environment variables:

Bash

export PGHOST=ep-abc-123.databricks.com
export PGPORT=5432
export PGDATABASE=lakebase
export PGUSER=lakebase
export PGPASSWORD=your-password

psql

For more information about connection strings and authentication options, see Connect to your database project.