Databases · Lesson 3.1
Rules before the first row
Simplified's users table now has a type for every column, and a generated primary key.
It has NOT NULL on email, xp and created at, and a DEFAULT on xp and created at.
And Postgres checks every new row against all of them.
But how does Postgres know these rules before the first learner ever signs up?
Because Simplified's developers wrote them down for it, before the table held a single row.
That written description of a table is called its schema.
A schema lists a table's columns, their types, and their rules.
It holds no rows: just the shape every row must fit.
So what happens when learners start signing up?
Say Zoe signs up with an email and a name.
Her row leaves out xp and created at, so their defaults fill them in, and the row fits.
Then Max signs up with a name, but no email.
The schema says email is NOT NULL, so Postgres refuses his row.
Then say a row arrives for Max with his email, but with 'lots' in xp.
The schema says xp is an integer, so Postgres refuses that row too.
So the schema is decided before the first row exists.
And every row added after that must fit it.
Say Instagram's developers write the schema for a table of saved posts. It lists a post id column, an integer with NOT NULL, and a saved at column, a timestamp with NOT NULL. Then a saved post arrives with no post id.
Does that row fit the schema?
Explanation
First, the schema says post id is NOT NULL, so every row must hold one.
Then, this row has no post id, so it would hold NULL there.
Therefore, it doesn't fit, so Postgres refuses it.
Say Spotify's developers write the schema for a songs table. It lists four columns: id, title, artist and length, each with its type. No songs have been added yet.
How many columns does the schema list?
Databases · Lesson 3.2
The language Postgres reads
Simplified's developers wrote the users schema before the first learner signed up.
To hand it to Postgres, they send it as a query, like any instruction to a database.
But what language does Postgres read a query in?
Well, think of the code you've written for the browser.
Your browser runs JavaScript, like a fetch() call.
It won't run a line of plain English, or any other language.
A relational database works the same way.
It reads queries written in one language, called SQL.
SQL is the language for asking a relational database for data, and telling it what to do.
So Postgres reads SQL, the way your browser reads JavaScript.
So SQL is the language Postgres reads.
Every query to it, a question or an instruction, is written in SQL.
Simplified's server needs Ben's email, so it sends Postgres a query asking for it.
What language is that query written in?
Explanation
First, Postgres is a relational database, and it reads queries in one language.
Then, that language is SQL, for questions and instructions alike.
Therefore, the query is written in SQL.
Simplified's developers send Postgres a query that makes a new table.
What language is that query written in?
Simplified's server needs a learner's XP from Postgres.
Which of these can Postgres read?
Say Simplified's server code is written in JavaScript.
When the server asks Postgres for a learner's XP, what language is that query written in?
In one second, Instagram's server sends its relational database 3 questions, like asking for a profile's photos, and 2 instructions, like storing a new like.
How many of those 5 queries are written in SQL?
Your browser runs code written in JavaScript.
What plays that same part for Postgres?
Say Spotify's server sends its relational database a query telling it to store a new playlist.
Which is true?
Databases · Lesson 3.3
Writing your first schema
Every query to Postgres is written in SQL, and that includes a schema.
Simplified's developers wrote the users schema before the first learner signed up.
So which SQL query hands that schema to Postgres?
It's called CREATE TABLE.
CREATE TABLE makes a new table, with the columns, types and rules you list.
So what does the users table's CREATE TABLE say?
The query names the table, users, then gives each column a line.
ID is an integer and the primary key, and GENERATED ALWAYS AS IDENTITY makes Postgres generate it.
email is text, with NOT NULL.
name is varchar 50, with no NOT NULL, so it can be NULL.
xp is an integer, with NOT NULL and a DEFAULT of 0.
Created at is a timestamp, with NOT NULL and a DEFAULT of now().
A semicolon ends the query, and Postgres makes the table, with no rows yet.
Now how might Spotify write the schema for its songs?
Say its developers write CREATE TABLE songs.
ID is an integer and the primary key, generated by Postgres.
title is text, with NOT NULL, because every song needs a title.
length is an integer, the song's length in seconds, with no rule.
Postgres makes the songs table, ready for its first song.
So a schema is written in SQL, with a CREATE TABLE query.
And each column's line gives its name, its type, then its rules.
A line in Simplified's CREATE TABLE users says xp integer NOT NULL DEFAULT 0.
What does that line tell Postgres about xp?
Explanation
First, integer says xp holds whole numbers.
Then, NOT NULL says every row must hold a value in xp.
Then, DEFAULT 0 says a new row that leaves xp out gets 0.
Therefore, xp holds whole numbers, is never NULL, and starts at 0.
A line in a CREATE TABLE query reads: title text NOT NULL.
Which part of that line is the column's rule?
Say YouTube's developers run a CREATE TABLE query for a new comments table.
What does Postgres do?
Say a developer writes this query: CREATE TABLE playlists (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name varchar(100) NOT NULL, description text, song_count integer NOT NULL DEFAULT 0, created_at timestamp NOT NULL DEFAULT now());
How many columns will the playlists table have?
Say a developer runs: CREATE TABLE tasks (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, title text NOT NULL, notes text, due timestamp, done boolean NOT NULL DEFAULT false);
How many of its columns can never hold NULL?
A line in a CREATE TABLE query says: bio varchar(150).
What does that line tell Postgres about bio?
Say Netflix's developers write CREATE TABLE profiles, with the lines name text NOT NULL and language text. Later, a new profile arrives with a language but no name.
What does Postgres do?