SQL is the language used to talk to a database.

Goals

• Understand what SQL does
• Understand what "MySQL" and "PostgreSQL" are
• Understand how we connect to a database:
  • The database runs on a database server, usually on another machine. For local development, the server is your PC.
  • To speak to the server, you need a local client for MySQL or PostgreSQL installed on your system.
  • To connect to a server, the client needs the server's hostname, a username and a password.
  • You Rails app includes a gem that serves as a database client. It also needs a hostname, username and password to connect to. It takes this information from config/database.yml.
  • If you would like to open a database shell without re-entering the credentials in your database.yml, you can run rails dbconsole.
• Understand the following commands
  • SELECT
    • WHERE
    • ORDER
    • INNER JOIN, LEFT JOIN
    • GROUP BY
    • LIMIT
  • UPDATE
  • INSERT and INSERT ... SELECT
  • DELETE
  • EXPLAIN ANALYZE
• Understand what a "transaction" is
• Understand what an "index" is
  • What is its purpose?
  • When can it be used?
  • What is a "multiple column index"? Does the order of the columns matter?
• Understand the default naming conventions for database tables in Rails applications
• Know how to get a database dump from a staging system

Resources

You don't need to read or use all of the resources below. Just pick some that work for you.

• SQLZOO Tutorial Archive : Follow the exercises in the "Tutorials" section
• Joins Visualizer Archive : Explains the differences between various types of JOIN statements
• SQL Fiddle Archive : Lets you play with databases from within your browser
• SQL in PostgreSQL Archive : PostgreSQL supports many powerful extensions of the SQL standard
• Convention over configuration in ActiveRecord Archive
• Differences between transactions and locking: Clears up a common misunderstanding

Exercises

ActiveRecord schema conventions

The file db/schema.rb is useful get a first impression of a Rails app's database model. It contains a compact description of all tables and columns in the app's database:

Copy
ActiveRecord::Schema.define(version: 2021_07_05_075620) do

  create_table "bookings", force: :cascade do |t|
    t.bigint "desk_id"
    t.datetime "start_time"
    t.datetime "end_time"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["desk_id"], name: "index_bookings_on_desk_id"
    t.index ["user_id"], name: "index_bookings_on_user_id"
  end

  create_table "desks", force: :cascade do |t|
    t.string "code"
    t.text "notes"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.boolean "bookable"
    t.index ["bookable"], name: "index_desks_on_bookable"
    t.index ["code"], name: "index_desks_on_code"
    t.index ["room_id"], name: "index_desks_on_room_id"
  end

end

Each of the create_table blocks corresponds to a CREATE TABLE Archive statement that can be used to re-create the database structure.

Tip

Most Rails apps follow ActiveRecord conventions Archive when designing their table and columns. Because there is a standard way of modelling and naming things, it's easy to guess how a Rails app stores its data.

Browse through the schema.rb of all sample apps that you checked out at the beginning of this curriculum. Just by looking at the table and column names you should get an idea what these applications do.

SQL console

In an earlier lesson you cloned the code for the makandra cards app.

Import the staging database into your local database. There is a geordi task Archive task to help you with that.

Start the server and sign up as a new user.

Now do the following things in a database console:

• Find the user table
• Which columns does it have? Which indexes?
• Change the name of your user
• Add yourself to another deck
• Find out how many users there are
• Find out which user has received how many "thanks" ("tributes") with a single query
• Now do the same query only for the "makandropedia" deck
• Does this query use an index? If not, could we add one to speed it up?

Relational modelling

Come up with a database schema for the fictional apps below.

To describe the schema you can use any format that your mentor understands. For instance, an ER diagram Archive , the format of schema.rb above, or a set of CREATE TABLE statements.

It is sufficient to only use the most essential record properties for this exercise. E.g. in most cases it is enough for a user to have a name, e-mail and password (hash), even though in a real schema there may be many more columns.

A schema is easier to use the fewer tables it has. Try to minimize the number of tables you need.

Trello light

• Users can sign in
• Boards with customizable columns
• A board can have many Cards
• Users can be invited to boards for access

Facebook light

• Users can sign in
• Users can become "Facebook friends" with other users
• Users can write posts
• Posts are only visible for friends

GMail light

• Users can sign in
• Users can read and write e-mails
• E-mails can be saved as drafts before sending
• E-mails can be assigned tags
• Commonly used recipients can be saved in an address book

PostgreSQL conversion

Your MovieDB application probably uses SQLite, which is the default database chosen by Rails. However, the file-based SQLite is rarely used in production apps, where it is important to distribute databases over the network.

Change your MovieDB application so it uses PostgreSQL instead. PostgreSQL is our database of choice for practically all projects.