02-crud-basics

CRUD – Basics

Previous chapter | Next chapter

CRUD is an acronym for the four verbs we use to operate on data: Create, Read, Update and Delete. Active Record automatically creates methods to allow an application to read and manipulate data stored within its tables.

See CRUD: Reading and writing data for a complete reference.

Installation

Install sqlite3 for Ruby:

make install

Database file

A database in SQLite is a single disk file.

By convention, we will locate it in db/development.sqlite3 and db/test.sqlite3 when running specs.

To initialize the table structure, we have db/create.sql and db/drop.sql statements.

Create command example:

sqlite3 db/development.sqlite3 < db/create.sql

Drop command example:

sqlite3 db/development.sqlite3 < db/drop.sql

Simply run make db-create and make db-drop from the Makefile to set up or tear down the tables.

While it is not related to code, it will help to see the picture of a good organization.

---

See SQLite – Create table and SQLite – Drop table for a complete reference.

Connecting to the database

DB = SQLite3::Database.new('db/development.sqlite3')

Retrieving data from the database

Everything is a list. Rows and columns.

Giving the following pokemons table with id, pokemon_number and name:

- [1, 1, "Bulbasaur"]
- [2, 2, "Ivysaur"]
- [3, 3, "Venusaur"]

If you ask to get all Pokémon:

[
  [1, 1, "Bulbasaur"],
  [2, 2, "Ivysaur"],
  [3, 3, "Venusaur"]
]

If you ask to get the Pokémon count:

[
  [3]
]

I won’t give you the answer, but think SQL works like King Crimson in this case. 😂

Retrieving data as Ruby objects

Welcome to Ruby!

By default, sqlite3 gem returns a list with array-like objects.

rows = DB.execute <<~SQL
  SELECT * FROM "pokemons"
SQL

Output example:

[
  [1, 1, "Bulbasaur"],
  [2, 2, "Ivysaur"],
  [3, 3, "Venusaur"]
]

You can turn them into hash-like structures with:

DB.results_as_hash = true

And get instead:

[
  { "id": 1, "pokemon_number": 1, "name": "Bulbasaur" },
  { "id": 2, "pokemon_number": 2, "name": "Ivysaur" },
  { "id": 3, "pokemon_number": 3, "name": "Venusaur" }
]

I said array-like (and hash-like :p), because the rows are special objects:

DB.execute("select * from pokemons").first.class # SQLite3::ResultSet::ArrayWithTypesAndFields
DB.execute("select * from pokemons").first.types # ["INTEGER", "INTEGER", "TEXT"]
DB.execute("select * from pokemons").first.fields # ["id", "pokemon_number", "name"]

ProTip! SQL keywords can be lowercase and identifiers unquoted if they are not reserved keywords.

The SQLite – Keywords document can be useful, if you question on a keyword or the quoting.

---

See SQLite3::ResultSet::ArrayWithTypesAndFields and SQLite3::ResultSet::HashWithTypesAndFields for a complete reference.

Understanding SQL and the magic around the SQLite gem

DB.results_as_hash = false
DB.execute("select count(id) from pokemons") # [[151]]

DB.results_as_hash = false
DB.execute("select name from pokemons") # [["Bulbasaur"], ["Ivysaur"], ["Venusaur"], ...]
DB.execute("select name, count(id) from pokemons") # [["Bulbasaur", 151]] 🤯

DB.results_as_hash = false
DB.execute("select count(id) from pokemons") # [[151]]
DB.execute("select count(id) from pokemons")[0] # [151]
DB.execute("select count(id) from pokemons")[0][0] # 151

DB.results_as_hash = true
DB.execute("select count(id) from pokemons") # [{ "count(id)": 151 }]
DB.execute("select COUNT(id) from pokemons") # [{ "COUNT(id)": 151 }] 🤔
DB.execute("select count(id) from pokemons")[0] # { "count(id)": 151 }
DB.execute("select count(id) from pokemons")[0][0] # 151 🤯

It was my first impression when I realized my count method worked with DB.results_as_hash set to true.

Inserting data

statement = DB.prepare <<~SQL
  INSERT INTO "pokemons" ("pokemon_number", "name")
  VALUES (:pokemon_number, :name)
SQL

statement.execute(pokemon_number: 152, name: 'Chikorita')

ProTip! Use ? placeholders or named parameters.

You can get the ID of the last inserted record with:

id = DB.last_insert_row_id

Updating data

statement = DB.prepare <<~SQL
  UPDATE "pokemons"
  SET "pokemon_number" = :pokemon_number, "name" = :name
  WHERE "id" = :id
SQL

statement.execute(id: 152, pokemon_number: 152, name: 'Chika')

Deleting data

statement = DB.prepare <<~SQL
  DELETE FROM "pokemons"
  WHERE "id" = ?
SQL

statement.execute(152)

Seeding data

Simply run make db-seed to seed data or make db-setup to create the database and seed, from the Makefile.

See db/seeds.rb for more details.

Try it yourself

Inspect the database with SQLite Browser and play with the REPL.

Setup

make db-setup

Browser

make db-browse

Playground

make play

ProTip! Open and edit repl/init.rb to initialize code before entering interactive mode.

See SQLite3/Ruby and its documentation for the possible methods.

Challenge

Implement the CRUD for a Pokemon model, as described in the CRUD: Reading and writing data document.

Example

pikachu = Pokemon.new do |pokemon|
  pokemon.pokemon_number = 25
  pokemon.name = 'Pikachu'
end

pikachu.id
# ⇒ nil (The Pokémon is not persisted yet)

pikachu.save
# Persist the record

pikachu.id
# ⇒ 1 (Expected result, the database has inserted a row, store the ID in memory)

pikachu.name = 'Pika'
pikachu.save
# Update the record in the database

Run the specs with make test to ensure your code works as expected.

Quick start

Open app/models/pokemon.rb, spec/models/pokemon_spec.rb and data/pokemon.json.

Solution

Download solution.