SQL basics [2.5d]
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 runrails dbconsole
.
- Understand the following commands
SELECT
WHERE
ORDER
INNER JOIN
,LEFT JOIN
GROUP BY
LIMIT
UPDATE
INSERT
andINSERT ... 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:
CopyActiveRecord::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.