The CRUD on the Backend of Active Record/Sinatra

I told you that CRUD will return in Active Record (refer to my other blog about The SQL to CRUD: The Use of Structured Query Language to Create, Read, Update, and Destroy). The time is now and CRUD is back. Today I will talk about CRUD in the context of Active Record and Sinatra.

Again, a table of muscles will be used, since it lends itself well to creating a database. Let us follow CRUD in Active Record to the letter.

First we will create a table by creating a migration. Migrations are created in order to create or modify a table. I will not go into too much detail about migrations because that is another topic of discussion.

rake db:create_migration NAME=create_muscles

Once we create our migration a file is created which is appropriately named and timestamped (20230203201213_create_muscles.rb). The following code is added.

class CreateMuscles < ActiveRecord::Migration[6.1]
  def change
    create_table :muscles do |t|
      t.string :name
      t.string :origin
      t.string :insertion 
      t.string :action 
      t.string :innervation 
      t.string :blood_supply
      t.string :url
      t.integer :bodypart_id
    end
  end
end

Note that this class Create Muscles inherits methods from ActiveRecord::Migration. It creates a table with the columns name, origin, insertion, action, innervation, blood_supply, url, and bodypart_id. All these are strings except bodypart_id which is an integer which refers to the primary key in another table called body_parts which is created in the same aforementioned way.

class CreateBodyparts < ActiveRecord::Migration[6.1]
  def change
    create_table :bodyparts do |t|
      t.string :name 
    end
  end
end

body_parts has a one to many relationship with muscles. See the ERD (entity relationship diagram) below.

Speaking of that, let us finish the migration with this command.

rake db:migrate

Now our schema is created which looks like this.

ActiveRecord::Schema.define(version: 2023_02_03_202632) do

  create_table "bodyparts", force: :cascade do |t|
    t.string "name"
  end

  create_table "muscles", force: :cascade do |t|
    t.string "name"
    t.string "origin"
    t.string "insertion"
    t.string "action"
    t.string "innervation"
    t.string "blood_supply"
    t.string "url"
    t.integer "bodypart_id"
  end

end

Now we create the models. The models are classes which inherit methods from ActiveRecord::Base. These classes correspond to tables. Instances which correspond to rows can and will be created from these classes. Look at the models below for Bodypart and Muscle. These are in separate files but I display them in one code block for the reader. Since each model inherits methods there is very little code to write but the relationships are clearly defined to allow the tables to relate to each other. It is like real life. Body parts have many muscles and a muscle typically belongs to one body part.

class Bodypart < ActiveRecord::Base
    has_many :muscles
end

class Muscle < ActiveRecord::Base
    belongs_to :bodypart
end

Now that our database is set up, I can finally talk about CRUD methods used to manipulate databases.

Create:

Now that we have our models established we can talk about the "C" in CRUD, create.

The new method creates a new instance while the save method saves an instance to the database. It is important to be aware that it is not enough to create a new instance but that instance has to be saved (persisted) to the data base. Lets take a generic model called User which has attributes name and job.

User.new(name: "Goku", job: "Saiyan with a level of over 9000")
User.name = "Goku"
User.job = "Saiyan with a level of over 9000"

This creates an instance of Goku but does not persist him to the database. It means that the data exists but has not been added to the table. It's like a ball player with no team.

User.save

This adds Goku to the database.

Now, this is all well and good, but there is an easier way to do this. The create method can be used. It combines the new and save methods into one. I demonstrate this using the create method to create an instance of muscle and persist it to the muscles database as demonstrated below.

Muscle.create(
 name: "deltoid",
 origin: "lateral one-third of the clavicle, acromion, the lower lip of the crest of the spine of the scapula",
 insertion: "deltoid tuberosity of the humerus",
 action: "abducts arm; anterior fibers flex & medially rotate the arm; posterior fibers extend & laterally rotate the arm",
 innervation: "axillary nerve (C5,6)",
 blood_supply: "posterior circumflex humeral a.",
 url:
  "https://cdn-aolkg.nitrocdn.com/JEsNUzsMoDdLqhSXkopLhNFWnBniacqf/assets/images/optimized/rev-5038be8/wp-content/uploads/2014/02/deltoids-muscle-attachments.jpg",
 bodypart_id: 264
)
=> #<Muscle:0x0000564b01685f40
 id: 415,
 name: "deltoid",
 origin: "lateral one-third of the clavicle, acromion, the lower lip of the crest of the spine of the scapula",
 insertion: "deltoid tuberosity of the humerus",
 action: "abducts arm; anterior fibers flex & medially rotate the arm; posterior fibers extend & laterally rotate the arm",
 innervation: "axillary nerve (C5,6)",
 blood_supply: "posterior circumflex humeral a.",
 url:
  "https://cdn-aolkg.nitrocdn.com/JEsNUzsMoDdLqhSXkopLhNFWnBniacqf/assets/images/optimized/rev-5038be8/wp-content/uploads/2014/02/deltoids-muscle-attachments.jpg",
 bodypart_id: 264>

We have just created a muscle and added it to the database. Note that an id is automatically generated, it is autoincrementing.

Read:

Now that we can create rows of data in a table it is imperative that we can access this data. This is the "R" is CRUD which is read. There are many methods here but I will highlight the commonly used ones. Let us use the Muscle model. Think of the database as an array of instances (technically the data from the instances) stored in a table.

• Muscle.all returns a collection with all muscles.
• Muscle.first returns the first muscle.
• Muscle.find(3) returns the muscle with an id number of 3.
• Muscle.find_by(name: "deltoid") finds a muscle with the name of deltoid. This can be used for any attribute within that class.
• Muscle.where(bodypart_id: 5, action: "flex the knee").order(name: :desc) finds all muscles located in a body part with the identifier of 5 which flex the knee in descending order by name.

Update:

We can read and find data in a table now. Let us talk about the "U" in CRUD, update. We can update a single record by attribute(s) using the update method.

deltoid = Muscle.find_by(name: "deltoid")
deltoid.update(bodypart_id: 250)

We can also update several records at the same time using the update_all method.

User.update_all(job: "Super Saiyans with level > 9000")

Delete:

The "D" is CRUD is delete/destroy. The methods are straightforward. For a single record we can use the destroy method. For multiple records we can use the destroy_by or destroy_all method.

biceps = Muscle.find_by(name: "biceps brachii")
biceps.destroy

Muscle.destroy_by(name: "biceps brachii") //deletes all muscles named "biceps brachii"

Muscle.destroy_all //deletes all muscles

Now we can perform CRUD methods on our records in the database. With the addition of Sinatra we can create a backend with our records which can be accessed from the front end. We can use CRUD methods to do this. Here is a quick overview. Note that below we are using and HTTP method combined with a relative path to make changes to the backend server. The combination of an HTTP method and a relative path is called a route.

Create: the use of post

    post '/muscles' do
      new_muscle = Muscle.create(
        name: params[:name], 
        origin: params[:origin], 
        insertion: params[:insertion], 
        action: params[:action], 
        innervation: params[:innervation], 
        blood_supply: params[:blood_supply], 
        url: params[:url],
        bodypart_id: params[:bodypart_id].to_i
        )
      new_muscle.to_json
    end

This post method involves creating (create method) a new instance of muscle in the class Muscle and adds it to the database. Its value is assigned to new_muscle. A unique id is assigned to it. Its attributes are assigned values from the params hash (sent by the frontend). It then converts it to JSON format (serialization) and persists it to the backend with a relative path of /muscles using the to_json method.

Read: the use of get

    get '/muscles' do
      muscles = Muscle.all.order(:name)
      muscles.to_json
    end

This get method involves accessing all (all method) of the muscles in the table muscles and converting it to an array of instances in alphabetical order (using the order method) by the attribute name. This array is assigned to the variable muscles. It then converts it to JSON format (serialization) and persists it to the backend with a relative path of /muscles using the to_json method.

Update: the use of patch

    patch '/muscles/:id' do
      updated_muscle = Muscle.find_by(id: params[:id])
      updated_muscle.update(
        origin: params[:origin], 
        insertion: params[:insertion], 
        action: params[:action], 
        innervation: params[:innervation], 
        blood_supply: params[:blood_supply]
      )
      updated_muscle.to_json
    end

This patch method involves finding an instance of Muscle with the id value from the params hash. The update method is used to update that instance of muscle with by assigning attributes values from params. The value of this instance is assigned to updated_muscle. It then converts it to JSON format (serialization) and persists it to the backend with a relative path of /muscles using the to_json method.

Delete: the use of destroy

    delete '/muscles/:id' do
      muscle = Muscle.find(params[:id])
      muscle.destroy
      muscle.to_json
    end

This delete method involves finding an instance of Muscle with the id value from the params hash. This instance is assigned to muscle. It is subsequently deleted from the database using the destroy method and serialized using to_json. It is deleted from the backend.

Now we can use CRUD methods to create and manipulate both the database and the server. Now with a functional React frontend I have created an interactive muscle anatomy chart which looks like this.

This the power of CRUD combined with a Ruby based Active Record/Sinatra backend and a React frontend. Thank you. The CRUD prequel will come soon to bring an end to the trilogy.