The SQL to CRUD: The Use of Structured Query Language to Create, Read, Update, and Destroy

When I first learned about CRUD, it was in reference to server communication in JavaScript using fetch. CRUD stands for create, read, update, and delete/destroy. I learned about HTTP verbs such as POST, GET, PATCH, and DELETE which correspond to each letter of CRUD respectively.

In order to learn about how Active Record which is an object relational mapper (ORM) works, we need to learn about structured query language (SQL). This gives us insight to how Active Record works under the hood. We can use the CRUD approach to learn about SQL. In this way it is like a sequel to learning CRUD but in a different environment. The first movie would be CRUD: You Just Got Served (server communication). This movie sequel is CRUD the SQL (structured query language).

Now that we have established that our narrative will be about SQL, we should talk about what it does. It basically is the language used to build databases and it is used in relational database management systems. It inserts, reads, updates, and destroys data. This is how Active Record works on the down low. The code that we write using Active Record uses SQL.

Let us talk about database structure. In Ruby we have classes and instances (objects derived from classes). The table is the class. Each row (also known as a record) is an instance. Each column is an object key which contains a value.

The example I would like to use are the muscles of the human body. Our table will be called muscles. Each muscle has a name, origin, insertion, action, innervation, and blood supply. I would also like to have an image URL to display this muscle. The muscles in the body lend themselves well to creation of a database. There are 650 muscles in the body so there is no shortage of data to be organized and manipulated.

The "C" in CRUD is create.

We can create tables. The general syntax is :

CREATE TABLE table_name;

So to create a table for the nice beautiful muscles of the human body we would type:

CREATE TABLE muscles;

Now we have created a table for muscles. But wait not so fast, there are no columns yet. It looks like we are missing a few things. This is how it needs to be.

CREATE TABLE muscles (
  id INTEGER PRIMARY KEY,
  name TEXT,
  origin TEXT,
  insertion TEXT,
  action TEXT,
  innervation TEXT,
  blood_supply TEXT, 
  url TEXT,
);

Let us dissect the above code. We used CREATE TABLE as a command to create a new table called 'muscles'. A list of column names was created with the type of data they will be storing. TEXT means plain text. INTEGER means a whole number. Every row (instance) that is created should be defined with an id INTEGER PRIMARY KEY. Our database rows must be indexed by a number. This makes our data much easier to access, organize, and update.

This is how our table would look.

Under create, we can also insert data into our tables like this:

INSERT INTO muscles (name, origin, insertion, action, innervation, blood_supply, url) 
VALUES ('deltoid', 'clavicle, scapula', 'humerus', 'abducts, flexes, and extends shoulder, 'axillary nerve', 'branches of axillary artery', 'http://image.deltoid');

The INSERT INTO command is used, followed by the name of the table into which we want to insert data. Then, in parentheses, the column names that we will be filling with data are placed. This is followed by the VALUES keyword, which is accompanied by a parentheses enclosed list of the values that correspond to each column name in the exact order.

Note, that we did not specify the "id" column or value. Recall that we created a table with the id column as the INTEGER PRIMARY KEY. When we insert data the "id" column is automatically given a value. Primary Key columns are autoincrementing.

Here is what our table looks like below. Note that I inserted another row so that we can use it later.

The "R" in CRUD is Read.

We use SELECT statements to access data from our table. Here is what a basic select statement looks like.

SELECT [names of columns we are going to select] FROM [table we are selecting from];

Suppose we want to select the data from all of the columns and rows of our muscles table. To do this we would pass the name of each column and the name of the table.

SELECT name, origin, insertion, action, innervation, blood_supply, url FROM muscles;

This would return all the data in our muscles table.

deltoid|clavicle, scapula|humerus|abducts, flexes, and extends shoulder|axillary nerve|branches of axillary artery|http://image.deltoid
biceps|scapula|humerus|flexes elbow, supinates forearm|musculocutaneous nerve|branches of brachial artery   |http://image.biceps

The above command worked but it was a little too much to write. A faster way to get data from every column in our table is to use a special selector, known as the 'wildcard' selector *. This means to get all the data from all the columns for all of the muscles. Like this:

SELECT * FROM muscles;

It is good that we can access all data from the table using an asterisk, I mean the "wildcard selector" or "*" for short. However, we can narrow our search by column names and based on conditions.

Selecting by column names:

SELECT column FROM table;

For example if we want to select the name column from muscles, we can do this:

SELECT name FROM muscles;

Which returns this:

deltoid
biceps

We can even select more than one column at a time. For example, we can access the muscles and their actions.

SELECT name, action FROM muscles

Selecting based on conditions: the use of WHERE

Here is a prototypical SELECT statement using WHERE.

SELECT * FROM [table name] WHERE [column name] = [some value];

Let us look for the row which contains our deltoid muscle (one of my favorite muscles to train).

SELECT * FROM muscles WHERE name = "deltoid";

This will return the values of all columns in the row containing the name "deltoid".

deltoid|clavicle, scapula|humerus|abducts, flexes, and extends shoulder|axillary nerve  |branches of axillary artery|   http://image.deltoid

Also be aware that comparison operators such as < or > can be used in addition to the equality operator =.

SELECT name FROM dragon_ball_z_table WHERE level > 9000

=> GOKU

The "U" in CRUD is Update.
Here we use the UPDATE keyword. A boilerplate UPDATE statement looks like this.

UPDATE [table name] SET [column name] = [new value] WHERE [column name] = [value];

It looks like I entered the wrong blood supply for the deltoid muscle. Its blood supply is actually the posterior circumflex humeral artery. So now I will update that row to change the blood supply.

UPDATE muscles SET blood_supply = "posterior circumflex humeral artery" WHERE blood_supply = "branches of axillary artery";

The "D" in CRUD is Delete.

We can delete rows from tables using a DELETE statement. Here is a template for a delete statement.

DELETE FROM [table name] WHERE [column name] = [value];

If we want to remove biceps from our table since I don't feel like I have to train arms anymore we can do this.

DELETE FROM muscles WHERE id=2;

Note I am using the Primary Key column to select the row to delete. As best practice it is good to access data by id number since these will always be unique.

Deleting a table is very easy.

DROP TABLE table_name;

In summary, one can perform all four CRUD actions with SQL.

• Create using CREATE TABLE and INSERT commands.
• Read using SELECT commands.
• Update using UPDATE commands.
• Delete using DELETE and DROP TABLE commands.

I think I'm in the mood to create another sequel to CRUD. Stay tuned. CRUD will return in Active Record.