SQL, Ruby, & Rails

SQL — Database

SQL or Structured Query Language, is the standard used by developers to communicate with relational databases. A relational database is a structured set of data stored in a computer which is organized according to the model provided. The implementation of the these two with a website application can greatly increase the potential of the application. Before we can realize this potential, we must first establish a connection between our database and our web application. Establishing this connection typically involves the developer taking the following steps:

  1. Require/import/load whatever interface/library is required
  2. Establish/open a connection to the database
  3. Query the database
  4. Close connection to the database

As you can imagine, having to do this over and over in a web application tends to make our code very WET (repeating code) in the process.

Rails — SQL

As a web framework, Rails, does a lot to take away the guesswork or otherwise required leg work when building a website. One such form of this is how it provides developers the ability to freely interact with a database. It accomplishes this by having steps 1, 2, and 4 as mentioned earlier already coded into its framework. The ability to concentrate on step 3 that Rails provides, gives developers the opportunity to develop more powerful and complex SQL statements unencumbered by the other steps typically involved.

Ruby — Rails — SQL

In the chart below, are some common Active Records Methods that can be implemented into Ruby to query the Database.

The methods are a lot easier to read and implement than the method used below which is typically seen in other languages.

require 'pg'
data_connection = PG::Connection.open(:dbname => 'sportsdb', :user => 'jjohnson')
data_hash = data_connection.exec("SELECT * FROM Players WHERE Id = 1;")

The code above is how you would need to interact with a PostgreSQL database if Rails wasn’t involved. The first line:

require 'pg'

Fulfills the first step mentioned earlier by providing the necessary interface, the ‘pg’ library or PostgreSQL library. The next line:

data_connection = PG::Connection.open(:dbname => 'sportsdb', :user => 'jjohnson')

Establishes the connection to the PostgreSQL database by passing in the database name and username. In some cases, the password might also need to be passed in as well. This is then followed by the following line:

data_hash = data_connection.exec("SELECT * FROM Players WHERE Id = 1;")

Which sets the variable, data_hash, equal to the hash returned by the following SQL statement:

FROM Players
WHERE Id == 1


"SELECT * FROM Players WHERE Id = 1;"

Finally the last line:


Closes the connection to the database. Instead of having to write 4 lines of code, Rails lets Ruby simply enter the following to return the same data:

data_hash = Player.where(Id: 1)

Not only is this more efficient in terms of development, but it also is a lot easier to read. In the future if that code needs to be refactored or if a developer needed to determine what it is doing at that moment, it shouldn’t be very hard for them to interpret it.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.