Getting to know your Rails dbconsole (PostgreSQL)

September 1, 2021

Two elephants in a field of grass at sunset

Today I thought we could cover the commands that I use in psql or bin/rails dbconsole the most often when poking around to see the structure of an applications database.

Launching the console

Launching your Rails's postgres console can be kicked off with bin/rails dbconsole:

$ bin/rails dbconsole <

psql (13.3)
Type "help" for help.

[local] anhari@bestreads_development=#

Another way to get in here is to use psql directly and passing it the name of your database:

$ psql bestreads_development

psql (13.3)
Type "help" for help.

[local] anhari@bestreads_development=#

Running queries

Inside of this shell you're able to run queries against your database:

[local] anhari@bestreads_development=# select * from contracts limit 1;
┌─[ RECORD 1 ]────────────────────╥────────────────────────────┐
│ id                              ║ 57                         │
│ total                           ║ 9,599.00                   │
│ created_at                      ║ 2018-11-08 14:46:58.034737 │
│ updated_at                      ║ 2021-06-17 21:36:55.680756 │
└─────────────────────────────────╨────────────────────────────┘

Finding and executing commands

When we launched the console, postgres brought the help command to our attention:

psql (13.3)
Type "help" for help.

[local] anhari@bestreads_development=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Learning about SQL commands

The shell comes with a whole host of commands that are escaped by a \ (remember this is a SQL environment, so we need to escape commands).

Using \h we can learn about any SQL command available in postgres, and even get a link to the documentation for our current version (in my case, Postgres 13).

Without arguments, it gives you a scrollable list:

[local] anhari@bestreads_development=# \h
Available help:
  ABORT                            CREATE USER
  ALTER AGGREGATE                  CREATE USER MAPPING
  ALTER COLLATION                  CREATE VIEW
  ALTER CONVERSION                 DEALLOCATE
  ALTER DATABASE                   DECLARE
  ALTER DEFAULT PRIVILEGES         DELETE
  ALTER DOMAIN                     DISCARD
  ALTER EVENT TRIGGER              DO
  ALTER EXTENSION                  DROP ACCESS METHOD
  ALTER FOREIGN DATA WRAPPER       DROP AGGREGATE
  ALTER FOREIGN TABLE              DROP CAST
  ALTER FUNCTION                   DROP COLLATION
  ALTER GROUP                      DROP CONVERSION
  ALTER INDEX                      DROP DATABASE
  ALTER LANGUAGE                   DROP DOMAIN
  ALTER LARGE OBJECT               DROP EVENT TRIGGER
  ALTER MATERIALIZED VIEW          DROP EXTENSION
  ALTER OPERATOR                   DROP FOREIGN DATA WRAPPER
  ALTER OPERATOR CLASS             DROP FOREIGN TABLE
  ALTER OPERATOR FAMILY            DROP FUNCTION
  ALTER POLICY                     DROP GROUP
  ALTER PROCEDURE                  DROP INDEX
  ALTER PUBLICATION                DROP LANGUAGE
  ALTER ROLE                       DROP MATERIALIZED VIEW
  ALTER ROUTINE                    DROP OPERATOR
  ALTER RULE                       DROP OPERATOR CLASS
  ALTER SCHEMA                     DROP OPERATOR FAMILY
  ALTER SEQUENCE                   DROP OWNED
  ALTER SERVER                     DROP POLICY
  ALTER STATISTICS                 DROP PROCEDURE
:

With an argument, it gives you information on a specific command:

[local] anhari@bestreads_development=# \h CREATE VIEW;
Command:     CREATE VIEW
Description: define a new view
Syntax:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

URL: https://www.postgresql.org/docs/13/sql-createview.html

Exploring the structure of your database with psql commands

psql ships with a bunch of commands that help us learn about the current structure of our database. We can find these commands using \?.

[more] - > \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [-n] [STRING]    write string to standard output (-n for no newline)
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [-n] [STRING]   write string to \o output stream (-n for no newline)
  \warn [-n] [STRING]    write string to standard error (-n for no newline)

Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block

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
  \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
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[anptw][S+] [PATRN] list [only agg/normal/procedures/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]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \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
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+]  FUNCNAME       show a function's definition
  \sv[+]  VIEWNAME       show a view's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (border|columns|csv_fieldsep|expanded|fieldsep|
                         fieldsep_zero|footer|format|linestyle|null|
                         numericlocale|pager|pager_min_lines|recordsep|
                         recordsep_zero|tableattr|title|tuples_only|
                         unicode_border_linestyle|unicode_column_linestyle|
                         unicode_header_linestyle)
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently auto)

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "bestreads_development")
  \conninfo              display information about current connection
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently on)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations

The commands I use most often

That's a lot. I mostly use the following subset of commands in my everyday work.

\l to list all of the databases in my postgres instance
\d+ to list all tables, views, and sequences in the db
\d+ table_name to display information on a result from the command above

[local] anhari@bestreads_development=# \d+ books;
                                                               Table "public.books"
┌─────────────╥────────────────────────────────╥───────────╥──────────╥───────────────────────────────────╥──────────╥──────────────╥─────────────┐
│   Column    ║              Type              ║ Collation ║ Nullable ║              Default              ║ Storage  ║ Stats target ║ Description │
╞═════════════╬════════════════════════════════╬═══════════╬══════════╬═══════════════════════════════════╬══════════╬══════════════╬═════════════╡
│ id          ║ bigint                         ║           ║ not null ║ nextval('books_id_seq'::regclass) ║ plain    ║              ║             │
│ title       ║ character varying              ║           ║ not null ║                                   ║ extended ║              ║             │
│ description ║ text                           ║           ║          ║                                   ║ extended ║              ║             │
│ created_at  ║ timestamp(6) without time zone ║           ║ not null ║                                   ║ plain    ║              ║             │
│ updated_at  ║ timestamp(6) without time zone ║           ║ not null ║                                   ║ plain    ║              ║             │
└─────────────╨────────────────────────────────╨───────────╨──────────╨───────────────────────────────────╨──────────╨──────────────╨─────────────┘
Indexes:
    "books_pkey" PRIMARY KEY, btree (id)
    "index_books_on_title" btree (title)
Access method: heap

\dD to list any custom domains
\di to list indexes
\dm to list materialized views
\dt to list just tables
\dT to list data types (mostly for enums)
\dv to list views
\dx to list any installed extensions such as citext or isn