The Prince Academy株式会社 | Defining Data Types and Constraints | Setting Up Your Supabase Project and Database | Supabase Made Easy: Your First Steps to Building with Open Source Firebase Alternative

Now that we have our Supabase project up and running, it's time to define the structure of our data. Think of this as building the blueprint for your application's information. Supabase uses PostgreSQL under the hood, so we'll be working with SQL data types and constraints.

The Supabase SQL Editor provides a user-friendly interface to manage your database schema. We'll primarily be creating tables and then defining the columns within those tables, specifying their data types and any important rules or constraints they must follow.

Let's start by creating a simple table to store information about our users. We'll name this table users. When defining tables, it's good practice to include a unique identifier, often called a primary key.

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

In this statement:

CREATE TABLE users: This is the command to create a new table named users.
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(): This defines a column named id. uuid is a data type that generates a universally unique identifier. PRIMARY KEY ensures that each id value is unique and cannot be null, serving as the main identifier for each row. DEFAULT uuid_generate_v4() automatically generates a new UUID whenever a new row is inserted without an explicit id.

Next, let's add some more common fields to our users table, such as an email address and a timestamp for when the user was created. We'll also enforce that the email must be unique.

ALTER TABLE users
ADD COLUMN email text UNIQUE NOT NULL,
ADD COLUMN created_at timestamp with time zone DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP);

Here's what these additions mean:

ALTER TABLE users ADD COLUMN email text UNIQUE NOT NULL: We're adding a column named email. text is a flexible data type for strings. UNIQUE means no two users can have the same email. NOT NULL ensures that every user record must have an email address.
ADD COLUMN created_at timestamp with time zone DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP): This adds a created_at column to record when the user was created. timestamp with time zone stores date and time information along with the timezone. DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP) automatically sets the creation time to the current UTC time if not specified.

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

In this statement:

CREATE TABLE users: This is the command to create a new table named users.
id uuid PRIMARY KEY DEFAULT uuid_generate_v4(): This defines a column named id. uuid is a data type that generates a universally unique identifier. PRIMARY KEY ensures that each id value is unique and cannot be null, serving as the main identifier for each row. DEFAULT uuid_generate_v4() automatically generates a new UUID whenever a new row is inserted without an explicit id.

Next, let's add some more common fields to our users table, such as an email address and a timestamp for when the user was created. We'll also enforce that the email must be unique.

ALTER TABLE users
ADD COLUMN email text UNIQUE NOT NULL,
ADD COLUMN created_at timestamp with time zone DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP);

Here's what these additions mean:

ALTER TABLE users ADD COLUMN email text UNIQUE NOT NULL: We're adding a column named email. text is a flexible data type for strings. UNIQUE means no two users can have the same email. NOT NULL ensures that every user record must have an email address.
ADD COLUMN created_at timestamp with time zone DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP): This adds a created_at column to record when the user was created. timestamp with time zone stores date and time information along with the timezone. DEFAULT timezone('utc'::text, CURRENT_TIMESTAMP) automatically sets the creation time to the current UTC time if not specified.