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.
Given the welcoming nature of ruby on rails to new developers, I think it is not uncommon that you can get pretty far developing rails apps without learning some fairly basic things about interacting with your database.
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
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
\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
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:
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:
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.
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:
By comparison, in our psql console we might do:
select * from users;
What if we don't want all of the users in our table? We might do something with AR like this:
We can do something similar in our psql console by doing this:
select * from users limit 5;
What if we want to delete a user? With AR we might do something like this:
In our psql console by comparison, we might do something like:
delete from users where id=1;
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.