10/10/2019

PostgreSQL Tips For Rails Developers

A few handy postgreSQL and SQL tips to help give you a competitive edge in your ruby on rails development.

Creating a new database for your rails app usually doesn't require much more than a quick rails db:create command, and ActiveRecord basically allows you to interact with your data without needing to know a single thing about SQL.

But learning SQL and learning how to better interface with your data will give you a competitive edge, I think. Even better, learning about this stuff helps demistify some of the magic happening under the hood in your rails app.

In this post, I'm going to share some simple commands for interacting with postgreSQL that I think will give you a leg up when you are building your ruby on rails apps. I'm focusing on postgreSQL specifically because this is my preference when building rails apps, and I think there's a decent chance it might be yours too.

Open a new postgreSQL console

psql - Using the psql command in your terminal opens the door to an interactive postgreSQL console. You can think of this in a similar way to how you might use rails c to open a rails console from your terminal. You can jump into the psql console with a specific database simply by doing psql -d your-database-name, where your-database-name is the name of your database for a rails app. If you don't know off the top of your head or have forgotten the specific database name for the rails app you want to try this with, try taking a look in config/database.yml in your app.

You can also open a db console that opens directly to the database of a rails app with the command rails dbconsole.

Get a list of all your databases

\l - Once you have created a new interactive postgres console with psql, you can view a list of all your postgres databases by typing in \l.

\c - You can additionally switch to another database other than the current one with \c, by doing \c another-database-name where another-database-name is one of your other databases.

View all your tables for the current database

\dt - You can view all of your tables for the current database simply by typing \dt

For example, let's say we have an app called openflights with a development database we have titled as openflights_development. In this app we have 3 tables: airlines, reviews, and users. when we do \dt we will see this:

One thing you may find interesting here - when we list our database tables for the database from this view, we see that we have some additonal tables beyond what we manually created. These extra tables are the ar_internal_metadata table and the schema_migrations table. You can learn more about these additional tables here and here.

View info for a specific table

\d - You can view the information and columns on a specific table easily by using \d. For example, let's say you have a rails app using devise and have a users table in your app. You can enter \d users into your terminal and you should see something that looks similar to this in the output listing all of the columns for that table:

SQL Commands

From within our psql console, We can also start crafting SQL commands to interact with our data. Let's look at a few side by side examples of ActiveRecord and SQL queries.

Select

Let's say we want to select all of our users from our users table. From inside our rails console, we might do this with Active Record by doing:

User.all

By comparison, in our psql console we might do:

select * from users;

Limit

What if we don't want all of the users in our table? We might do something with AR like this:

User.limit(5)

We can do something similar in our psql console by doing this:

select * from users limit 5;

Delete

What if we want to delete a user? With AR we might do something like this:

User.find(1).destroy

In our psql console by comparison, we might do something like:

delete from users where id=1;

Quiting

To exit from the psql console in your terminal, simply use \q. For additional options, try \? to view a list of additional commands that are available from within the psql console.