SQL basics [2.5d]

SQL is the language used to talk to a database.


  • 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.
    • Your 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
      • GROUP BY
      • LIMIT
    • UPDATE
    • DELETE
  • 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?
    • Note that your app should rarely make a reading query that is not backed by an index.
  • Understand the default naming conventions for database tables in Rails applications
  • Know how to get a database dump from a staging system


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


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:

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"

  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"


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


Most Rails apps follow ActiveRecord conventions Show archive.org snapshot 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 Show archive.org snapshot 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 Show archive.org snapshot , 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
Henning Koch over 7 years ago