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`:

``` shell
$ 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:

``` shell
$ 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:

``` sql
[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:

``` sql
[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:

``` sql
[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 `\?`.

``` sql
[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

``` sql
[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]

[citext]: https://www.postgresql.org/docs/13/citext.html
[custom domains]: https://michaelanhari.com/blog/adding-custom-data-types-to-your-postgres-database-in-rails
[enums]: https://github.com/alassek/activerecord-pg_enum
[isn]: https://www.postgresql.org/docs/13/isn.html

Let's go over a few commands that are helpful when poking around in the Postgres console

Two elephants in a field of grass at sunset

Getting to know your Rails dbconsole (PostgreSQL)

The JavaScript community has made so many strides in improving the developer experience since I started learning web development in 2014. Being responsible for browser support is not an easy task. However, with the widespread adoption of evergreen browsers, the JavaScript community has been able to ship a lot of quality of life improvements over the years as we've been able to drop support for dated browser versions.

One of those steps was the invention of [webpack], which allowed the stitching together of all kinds of things to deliver to the browser in a single bundle. It quickly became the defacto standard and was adopted by both the Ruby and Elixir communities, but it came with new issues of its own. Long compile times, complex configurations, breaking builds, etc.

It looks like the days of webpack being the default method of shipping JavaScript for Rails and Phoenix is coming to an end soon. The Rails team has built [a gem] that whill allows Rails 7 to take advantage of [import maps], and the phoenix team has started the move to [esbuild] in version 1.6. Some developers might be tempted to say "good riddance", but I think it's important to remember that it solved the problem we faced at the time. Time passed and now we have more options. So, thank you webpack.

Besides, depending on the JavaScript needs of your application it still may be your best bet. But, as a developer who tends to write mostly server-rendered apps, a path to reducing frontend complexity is a welcome one.

[webpack]: https://webpack.js.org
[a gem]: https://github.com/rails/importmap-rails/
[import maps]: https://github.com/WICG/import-maps
[esbuild]: https://esbuild.github.io

A few backend frameworks are looking to replace webpack with something less heavy-handed, Rails included.

A cute dachshund standing in a box

Goodbye webpack?

Grabbing the count of related records tied to a parent is a common task in web development. For example, displaying the number of comments on a blog post, or the amount of deliveries your expecting this afternoon at your warehouse. For our post today, let's say we have a `Topic` model that can have many `Article` records.

``` ruby
class Topic < ApplicationRecord
  has_many :articles
end

class Article < ApplicationRecord
  belongs_to :topic
end
```

## To the database!

Using the `.count` method from `ActiveRecord` will always send a request to your underlying database:

```
Topic.first.articles.count
# => SELECT COUNT(*) FROM "articles" WHERE topic_id = 1;
```

## Caching the value to avoid extraneous trips

Rails ships with caching the values for these count requests onto the parent model as `relations_count` integer columns. In our case, we could add `articles_count` to our `Topic` model and populate the counts:

``` ruby
class AddArticlesCountToTopics < ActiveRecord::Migration[6.0]
  def up
    add_column :topics, :articles_count, :integer, default: 0, null: false

    execute <<-SQL
      UPDATE topics SET articles_count = (
        SELECT COUNT(*) FROM article WHERE topic_id = topics.id
      )
    SQL 
  end

  def down
    remove_column :topics, :articles_count
  end
end
```

then, all we need to do is let Rails know to that this value needs to be cached on the `belongs_to` method call in `Article`:

``` ruby
class Article < ApplicationRecord
  belongs_to :topic, counter_cache: true
end
```

Done! Rails will now keep our `topics.articles_count` number up to date, and we can use `topic.articles_count` instead of `topic.articles.count` to avoid a trip to the database.

Counting the amount of records that exist is a very common task for your database. Rails has a built-in mechanism for caching this value to reduce the number of round trips.

5 vertical stacks of various coins

Adding counter caches to existing models in Rails

In a previous article, we talked about [guaranteeing unique values for a database column in Rails]. Today let's expand upon that and see how we can do the same, but not across the entire table.

For example, let's say we have a table of `Article`s that belong to a `User`:

``` ruby
class Article < ApplicationRecord
  belongs_to :user
end

class User < ApplicationRecord
  has_many :articles
end
```

and that we want to guarantee that each article for a given user has a unique `title`. In other words, Bob and Sally can both write articles titled "How to Adopt a Retired Racing Greyhound" but neither of them can write two articles with that title.

## Validating at the ORM level with ActiveRecord

Validating this in our Rails model is pretty straight forward.

``` ruby 
class Article < ApplicationRecord
  belongs_to :user

  validates :title, uniqueness: { scope: :user_id }
end
```

This scopes our `uniqueness` validation to values that are unique for the record's `user_id`.

## Ensuring uniqueness at the database level

Adding database-level protections for this feature involves creating a unique index that is based on multiple columns (`title` and `user_id`):

``` ruby
class AddUniqueIndexForArticleTitlesAndUserIds < ActiveRecord::Migration[6.0]
   def change
     add_index :articles, [:title, :user_id], unique: true
   end
 end
```

And there you have it! Your records will be properly validated, and uniquely scoped to each user.

[guaranteeing unique values for a database column in Rails]: https://www.michaelanhari.com/blog/guaranteeing-unique-values-for-a-database-column-in-rails

Sometimes you want to validate that a column is a unique for a given relationship and not across the entire table.

Four avocado halves on a board, three showing the skin and one showing the stone

Ensuring a column is unique for an entity in Rails

Sometimes you want to have an acronym in your codebase that you'd like to reference using all capital letters (i.e. `CMS` instead of `Cms`).

Unfortunately, Ruby will look for a class named `Cms` in a file named `cms.rb` and will tripped up if you try to name the class `CMS`.

Enter [`ActiveSupport::Inflector`].

## Letting Rails know about the acronyms in your codebase

Adding an acronym involves opening your `config/initializers/inflections.rb` [initializer] and using the `acronym` method.

``` ruby
ActiveSupport::Inflector.inflections(:en) do |inflect|
  inflect.acronym "CMS"
end
```

After restarting your server so that it picks up the new inflection, Rails will recognize `CMS` in `app/models/cms.rb` and you can use it when namespacing models as well (`CMS::Article`).

[`ActiveSupport::Inflector`]: https://api.rubyonrails.org/classes/ActiveSupport/Inflector/Inflections.html#method-i-acronym
[initializer]: https://guides.rubyonrails.org/configuring.html#initializers

Acronyms are strange to implement in Ruby, but are easy to manage in Rails when leveraging ActiveSupport.

A big pile of letter stamps

How to use acronyms in the classes of your Rails application

Sometimes you want to group areas of your application under one roof. For example, let's say you're building a multisided marketplace or building a [CMS] as part of your application. Let's pretend we're doing the latter.

Without namespacing, all of your resources would live in `app/models` whether a part of the CMS or not. If we wanted to group the resources managed by the CMS portion of the application we could namespace them in a module.

## Creating models under a namespace

The Rails generators support namespacing code using the `namespace/resource` pattern.

``` shell
bin/rails generate model cms/article title:string body:text
```

This generates a `Cms` module at `app/models/cms.rb` that defines the table prefix for the namespace:

``` ruby
module Cms
  def self.table_name_prefix
    'cms_'
  end
```

The underlying table in PostgreSQL would be named `cms_articles`, and the model in `app/models/cms/article.rb` would look like the following:

``` ruby
module Cms
  class Article < ApplicationRecord
  end
end
```

## Namespacing controllers and views

Let's say the resources for our CMS should be managed under the `http://localhost:3000/cms` path in the URI. The Rails router makes this easy.

``` ruby
# frozen_string_literal: true

Rails.application.routes.draw do
  namespace :cms do
    resources :articles
  end
end
```

and our controller would look something like this `app/controllers/cms/articles.rb`:

``` ruby
module Cms
  class ArticlesController < ApplicationController
    def index
      # code for /cms/articles here
    end
  end
end
```

and our corresponding views would live in `app/views/cms/articles/index.html.erb`

## Conclusion

Namespacing our models has made a code base with several hundred models easier to navigate and reason about, and I think it is a pattern worth considering. Tomorrow we'll talk about setting up Rails to handle the acronyms in your codebase so that we can reference our CMS module as `CMS` instead of `Cms`.

[CMS]: https://en.wikipedia.org/wiki/Content_management_system

Sometimes you want to namespace areas of your application to group related code together, and Rails has support for this baked in.

A wall of shipping containers stacked in a grid.

Using namespaces to organize your Rails code base 

When working in Ruby debuggers like [pry], [byebug], or [irb] you might want to grab the value for your last command but you forgot to store it to a variable.

Have no fear! The value returned by the last command always gets stored in an underscore variable `_`:

``` shell
> Executing task: bin/rails console <

Running via Spring preloader in process 3997
Loading development environment (Rails 6.1.4)
bestreads-development> Book.count
   (2.1ms)  SELECT COUNT(*) FROM "books"
  => 10201
bestreads-development> _
  => 10201
```

[pry]: http://pry.github.io
[byebug]: https://github.com/deivid-rodriguez/byebug
[irb]: https://ruby-doc.org/stdlib-2.4.0/libdoc/irb/rdoc/IRB.html

Sometimes you want to access the value returned by the last command you wrote into a Ruby REPL. There’s a trick for that.

A top down view of an unplugged mechanical keyboard

Grabbing the last result in common Rails debuggers

Rails provides a robust solution to [internationalization (i18n)] [^1], but it can be leveraged for a lot use cases even when your application only needs to support a single language.

One such example, is customing the default behavior of the [`submit` form helper]. This helper tries to convert the name of your model into some helpful text for a form's submission button.

For example, if we have the following form where `@article` is an instance of our `Article` model:

``` html
<%= form_for @article do |f| %>
  <%= f.submit %>
<% end %>
```

our button will read "Create article" if `@article` hasn't been persisted, and "Update article" if it has.

But what if you want to change it to read as `Create blog post` and `Update blog post`?

You could try passing a string to the `submit` function, but that wouldn't change the text when the form is used with new/existing objects.

``` html

<%= form_for @article do |f| %>
  <%= f.submit "Create article" %>
<% end %>
```

## i18n to the rescue

Rails's internationalization system allows us to define what this model should be referred to as when it's name is being used for human-readable use cases like our button.

In `config/locales/en.yml` you can set the following:

``` yaml
activerecord:
     models:
       article: Blog Post
```       

`submit` will now use "Blog Post" instead of "Article" when used without an argument:

``` html

<%= form_for @article do |f| %>
  <%= f.submit %>
<% end %>
```

[internationalization (i18n)]: https://en.wikipedia.org/wiki/Internationalization_and_localization
[`submit` form helper]: https://api.rubyonrails.org/classes/ActionView/Helpers/FormBuilder.html#method-i-submit

[^1]: __i18n__ comes from the fact that there are `18` letters between the `i` and the `n` in `"internationalization"`

The Rails submit form helper populates the button's text by using a humanized/readable version of the model's class name. Sometimes that isn't idea, but i18n has our backs.

A globe of Earth sitting on a desk

Using i18n to customize submit button text in Rails

In Rails you can define methods that filter query results, commonly referred to as "scopes", by using the [scope] method from `ActiveRecord` and by adding a class method to your model.

Using the `scope` method involves passing it a [lambda]:

``` ruby
class Post
  scope :published, -> { where(published: true) }
end
```

This is essentially syntactic sugar for defining a class method:

``` ruby
class Post < ApplicationRecord
  def self.published
    where(published: true)
  end
end
```

## One important difference when using scope

Sometimes you'll want a conditional statement in your scope to ensure they work when their parameters aren't present.

For example:

``` ruby
class Post < ApplicationRecord
  scope :created_before, ->(time) { where("created_at < ?", time) if time.present? }
end
```

This will behave similarly to a class method like before:

``` ruby
class Post < ApplicationRecord
  def self.created_before(time)
    where("created_at < ?", time) if time.present?
  end
end
```

__However, there is one important difference between the two__. Directly from the Rails guides:

> A scope will always return an `ActiveRecord::Relation` object, even if the conditional evaluates to `false`, whereas a class method, will return `nil`. This can cause `NoMethodError` when chaining class methods with conditionals, if any of the conditionals return `false`.

This is why I have started to write my scope methods using `scope`. Keeping your `ActiveRecord` query methods chainable keeps your application flowing.

[scope]: https://api.rubyonrails.org/classes/ActiveRecord/Scoping/Named/ClassMethods.html#method-i-scope
[lambda]: https://scoutapm.com/blog/how-to-use-lambdas-in-ruby

ActiveRecord scopes can be defined using the scope method or class methods on your model using the self keyword, but there is an important difference between the two.

A telescope facing a floor to ceiling window.

Why I no longer define ActiveRecord scopes with class methods

Rails developers are very familiar with hopping into the application console using `bin/rails console` (or `bundle exec rails console`) to run quick metrics. 

``` shell
> Executing task: bin/rails console <

Running via Spring preloader in process 3997
Loading development environment (Rails 6.1.4)
bestreads-development> Book.count
   (2.1ms)  SELECT COUNT(*) FROM "books"
  => 10201
```

However, it wasn't until recently that I started using the equivalent command for loading the console of my database (Postgres), `rails dbconsole`:

``` shell
> Executing task: bin/rails dbconsole <

psql (13.3)
Type "help" for help.

[local] anhari@bestreads_development=# select count(*) from books;
┌───────┐
│ count │
╞═══════╡
│ 10201 │
└───────┘
(1 row)

Time: 1.968 ms
[local] anhari@bestreads_development=# 
```

This is a great environment for testing out things on the fly before you follow-through with creating migration files to cement your changes.

For example, let's say you were trying to optimize a query on your `books` table that was filtering a book by it's `title` attribute. You might hop into your dbconsole and try something like this:

``` sql
explain analyze select * from books where title = 'The Lord of the Rings';
create index index_books_on_title on books(title);
explain analyze select * from books where title = 'The Lord of the Rings';
drop index index_books_on_title;
```

Let's break down the commands:

1. Filtering the books table and looking for LOTR using the `title` column, and using `EXPLAIN ANALYZE` to see the query plan and run the actual query to guage performance.
2. Create a btree index on `books.title`
3. Re-run the query using `EXPLAIN ANALYZE` and read the new query plan and guage performance
4. Drop the index so that we can create it via a Rails migration

Rails ships with a command for loading up the REPL for your database that can be useful for testing out the effectiveness of adding indexes to lower query response times.

Hello 👋🏻

Recent Articles