Sir YES Sir… Life on The Command Line
Growing as a dev can be a very broad and very specific process. Hours can be spent learning the newest React hook functionality, or animation transitions associated with css. On the other hand fruitful hours can be spent looking at and thinking about HTTPS requests, or truly understanding how a database works with your application build. The command line is something that I recognize as a broad growing process. Becoming more comfortable in the terminal is a long term goal of mine that is not specific to any programming language but is vital to a developers growth and comfort with computers.
I have been building my APIs in the language Ruby and with the framework Rails sitting on top. Rails is a very powerful framework with loads of abstraction to make API development a more user friendly process. A few of the powerful tools that come with the most recent version of Rails include Webpacker and Active Record. So best practice and very common practice for developers (DevOps I am looking at you) is keeping up with the most current version of a given language, framework, or dependency. The power of Active Record has been vital to getting my goofy little projects off the ground but it has also been a crutch I have leaned too hard on.
PostgreSQL has been the database of choice for my API builds primarily because it works so well with Heroku when deploying projects online. However outside of installing the gem in my Gemfile and having an understanding of how SQL queries work I was 100% relying on Active Record to interact with my database. Not anymore!
To access PostgreSQL from your terminal command line you must use the psql tool and command psql postgres. This logs you into your postgres. I then run a common bash command \du which shows the disk usage of a directory. Since no file is specified it displays the current folder hierarchy. So looking at this response from postgres jamesl.ardery is the owner of this postgres account. All of my roles are listed as well.
However this does not actually list the postgres databases that I have on my computer from previous projects. In order to access those I need to run \l.
Here is a full list of the APIs I have built in rails that use a postgreSQL database. I did not omit tests when creating a new rails API which is why you are seeing a _development and _test version of each db. To connect one of the APIs I have built to the postgres database you run the command \c followed by the database.
This is an important step to understand. In order to “spin up” my rails API that I have built I run rails s. What Active Record (ORM) is doing in the background is actually finding the postgres database that is on my local machine that is ALREADY SPINNING 🤯 and simply connecting the ruby API files to it. The way postgreSQL and MySQL install by default on a local device is to spin when the machine is powered on. So my postgres db is always running in the background when my computer is on. However running rails s takes the API built and uses my computer as the host (localhost) and then designates a port (3000) to connect the API to my postgres database.
Once I have connected the API to postgres I can start to poke around. Running \dt will display all tables in the database.
\d (table name) will display the columns and rows associated with that table.
You now are pretty much ready to write queries! So if wanted to see a list of all states in the database run select name from states then run ;
You can also use the * symbol which is universal in bash for “any” which will display any rows and columns from the state table.
One last little command line tool is the % symbol. Smack that bad boy after something in your query to specify if it starts with that symbol. If you put a % in front then it looks to see if the item contains that symbol!
I will leave it here. This is the first of many deeper adventures into working in the terminal. I feel closer to my postgres database and the APIs I have built so far. Hope you all enjoy!
Resources:
