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.
- 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
- If you would like to open a database shell without re-entering the credentials in your
database.yml, you can run
- Understand the following commands
INSERT ... SELECT
- 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
You don't need to read or use all of the resources below. Just pick some that work for you.
- : Follow the exercises in the "Tutorials" section
: Explains the differences between various types of
- : Lets you play with databases from within your browser
- : PostgreSQL supports many powerful extensions of the SQL standard
- Differences between transactions and locking: Clears up a common misunderstanding
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" 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
statement that can be used to re-create the database structure.
Most Rails apps followwhen 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.
In an earlier lesson you cloned the code for the makandra cards app.
Import the staging database into your local database. There is atask 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?
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
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.
- Users can sign in
- Boards with customizable columns
- A board can have many Cards
- Users can be invited to boards for access
- Users can sign in
- Users can become "Facebook friends" with other users
- Users can write posts
- Posts are only visible for friends
- 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
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.