Simplified
Simplified

Your own Postgres database

YOU

Your own Postgres database

Time
About 15 min
Steps
6
Where
supabase.com

You'll need

  • •A computer with a browser
  • •A GitHub account or an email address

Puts into practice

  • 1.4 Column Types
  • 2.1 Keys
  • 2.2 Empty Values
  • 2.3 The Schema

You've read the schema of Simplified's users table, written out as a CREATE TABLE query. So far it's only been words on a screen. So what happens when a real Postgres database reads it?

To find out, you need a Postgres database of your own. A company called Supabase runs Postgres databases on its servers, and gives you one for free. In this project you'll set one up and hand it your schema.

You only do this once. The database you make here is the one you'll build on for the rest of the course.

The goal

Get a real Postgres database of your own, and see it enforce your schema.

When you're done

  • ✓You have a Supabase project of your own, with a real Postgres database in it.
  • ✓Its users table has the five columns of Simplified's schema, and no rows.
  • ✓You've seen Postgres refuse to make the same table twice.
1

Step 1: Make a Supabase account

Go to supabase.com and press Start your project. You can sign up with GitHub, or with your email address.

If you use email, Supabase sends you a message. Open it and confirm your address.

You should see

Your Supabase dashboard, with a New project button.

2

Step 2: Create a project

Press New project. Supabase asks which organization it belongs to. An organization is just a folder for your projects, and Supabase made one for you when you signed up, so pick that.

Fill in the form like this:

  • Project name: simplified-practice
  • Database password: press Generate a password
  • Region: the one closest to you

Before you go on, copy that password and save it somewhere safe, like a password manager. You won't need it today. You will need it in a later project, when you connect an app to this database.

Then press Create new project. Supabase now starts a Postgres database just for you, on one of its servers. That takes a minute or two.

You should see

Your project's home page, with simplified-practice at the top, once Supabase has finished setting it up.

3

Step 3: Open the SQL Editor

In the left sidebar, press SQL Editor. This is where you write SQL queries and send them to your Postgres.

Start a new query, so the editor is empty.

You should see

An empty editor, with a Run button under it.

4

Step 4: Hand Postgres your schema

Type this query into the editor, exactly as it is.

CREATE TABLE users (
  id         integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  email      text NOT NULL,
  name       varchar(50),
  xp         integer NOT NULL DEFAULT 0,
  created_at timestamp NOT NULL DEFAULT now()
);

It's worth typing and not pasting, because typing makes you read each column's name, type and rules once more.

Then press Run. Supabase may ask about RLS. If it does, pick Run and enable RLS. It's a setting this course doesn't use, so don't worry about it.

You should see

Success. No rows returned under the editor.

5

Step 5: Find your table

In the left sidebar, press Table Editor, then pick users.

Each column of your schema is there, in the same order. You'll see int4 beside two of them, which is just Postgres's short name for integer. The table has no rows yet, because CREATE TABLE makes a table, not rows.

You should see

A users table with the columns id, email, name, xp and created_at, and no rows.

6

Step 6: Run it again, and see what happens

Go back to the SQL Editor, and press Run on the same query a second time.

Why does Postgres refuse it this time?

Because the first run saved your users table on its server's disk. The name users is already taken, and Postgres won't make a second table with it.

You should see

An error that says relation "users" already exists.

Setting up is taking more than a few minutes

Wait a little longer, then reload the page. New projects sometimes take a while to start.

An error like syntax error at or near

Check the line just before the word it names. A comma is often missing between two columns, or the semicolon at the end.

I lost my database password

Nothing here needs it, but a later project does. You can set a new one in your project's database settings: press Reset database password, and save the new one this time.

Finish the project

Tick every step once you've seen what it says you should see. Then mark the project complete to unlock the next lesson.

0 of 6 steps done
Assistant

Highlight lesson text and click Ask AI, or type a question below.