Why Postgres powers Supabase - See It in Action

Why Postgres powers Supabase

📖 Scenario: You are learning how Supabase uses Postgres as its database engine. Supabase is a platform that helps developers build apps quickly by providing a ready-to-use backend powered by Postgres.Understanding how Postgres works inside Supabase will help you see why it is a strong choice for cloud databases.

🎯 Goal: Build a simple Supabase project setup that connects to a Postgres database and creates a table to store user profiles.This will show how Postgres powers the data storage in Supabase.

📋 What You'll Learn

Create a Supabase client connected to a Postgres database

Define a configuration variable for the database schema

Write a query to create a table called profiles with columns id, username, and email

Add the final Supabase client call to execute the table creation

💡 Why This Matters

🌍 Real World

Supabase uses Postgres to store and manage app data reliably and scalably in the cloud.

💼 Career

Understanding how to connect and configure Postgres in Supabase is essential for backend cloud developers and database administrators.

1

Create Supabase client connection

Create a variable called supabaseUrl with the value "https://xyzcompany.supabase.co" and a variable called supabaseKey with the value "public-anonymous-key". Then create a Supabase client called supabase using createClient(supabaseUrl, supabaseKey).

Supabase

# Your code here

Hint

Use createClient from @supabase/supabase-js with the URL and key.

2

Define database schema configuration

Add a constant called schema and set it to the string "public" to specify the Postgres schema to use.

Supabase

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = "https://xyzcompany.supabase.co"
const supabaseKey = "public-anonymous-key"
const supabase = createClient(supabaseUrl, supabaseKey)

// Your code here

Hint

Use a constant named schema with the value "public".

3

Write SQL query to create profiles table

Create a constant called createTableQuery and assign it a string with the SQL command to create a table named profiles with columns: id as uuid primary key, username as text not null, and email as text unique.

Supabase

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = "https://xyzcompany.supabase.co"
const supabaseKey = "public-anonymous-key"
const supabase = createClient(supabaseUrl, supabaseKey)

const schema = "public"

// Your code here

Hint

Write the SQL command as a template string assigned to createTableQuery.

4

Execute the table creation query

Use await supabase.rpc with the function name sql and pass an object with query: createTableQuery to run the SQL command. Assign the result to a constant called result.

Supabase

import { createClient } from '@supabase/supabase-js'

const supabaseUrl = "https://xyzcompany.supabase.co"
const supabaseKey = "public-anonymous-key"
const supabase = createClient(supabaseUrl, supabaseKey)

const schema = "public"

const createTableQuery = `CREATE TABLE IF NOT EXISTS profiles (
  id uuid PRIMARY KEY,
  username text NOT NULL,
  email text UNIQUE
);`

// Your code here

Hint

Use await supabase.rpc('sql', { query: createTableQuery }) to run the SQL.

Practice

1. Why does Supabase use Postgres as its database?

easy

A. Because Postgres is a cloud provider

B. Because Postgres is the only database available

C. Because Postgres is a frontend framework

D. Because Postgres is reliable and supports complex data

Solution

1. Step 1: Understand Supabase's database choice

   Supabase uses Postgres because it is a reliable and powerful database system.

2. Step 2: Recognize Postgres features

   Postgres supports complex data types and scales well, making it suitable for many app sizes.

3. Final Answer:

   Because Postgres is reliable and supports complex data -> Option D

4. Quick Check:

   Postgres reliability = reason for Supabase choice [OK]

Hint: Remember Supabase builds on a strong, reliable database [OK]

Common Mistakes:

• Thinking Postgres is a frontend tool
• Assuming Postgres is the only database option
• Confusing Postgres with cloud providers

2. Which of the following is the correct way to connect to a Postgres database in Supabase?

easy

A. supabase.connect('postgres://user:pass@host/db')

B. supabase.connect('http://user:pass@host/db')

C. supabase.connect('ftp://user:pass@host/db')

D. supabase.connect('file://user:pass@host/db')

Solution

1. Step 1: Identify the correct protocol for Postgres connection

   Postgres uses the 'postgres://' protocol in connection strings.

2. Step 2: Match the protocol with Supabase connection syntax

   Supabase connects to Postgres using 'supabase.connect' with a 'postgres://' URL.

3. Final Answer:

   supabase.connect('postgres://user:pass@host/db') -> Option A

4. Quick Check:

   Postgres connection string starts with 'postgres://' [OK]

Hint: Look for 'postgres://' in connection strings [OK]

Common Mistakes:

• Using HTTP or FTP protocols for database connection
• Confusing file paths with URLs
• Missing the correct protocol prefix

3. Given this SQL query run on Supabase's Postgres database:
SELECT COUNT(*) FROM users WHERE active = true;
What does this query return?

medium

A. The total number of users in the database

B. A list of all user names

C. The number of active users

D. The number of inactive users

Solution

1. Step 1: Analyze the SQL query condition

   The query counts rows in 'users' where 'active' is true, filtering only active users.

2. Step 2: Understand the COUNT(*) function

   COUNT(*) returns the number of rows matching the condition, here active users only.

3. Final Answer:

   The number of active users -> Option C

4. Quick Check:

   COUNT with WHERE active=true counts active users [OK]

Hint: COUNT(*) with WHERE filters counts matching rows [OK]

Common Mistakes:

• Thinking it counts all users regardless of status
• Confusing COUNT(*) with SELECT *
• Assuming it returns user details instead of a number

4. You wrote this SQL query in Supabase but it returns an error:
SELECT * FROM users WHERE active = 'true';
What is the likely cause?

medium

A. Using quotes around true causes a type mismatch error

B. Missing a semicolon at the end

C. Table 'users' does not exist

D. Using SELECT * is not allowed in Supabase

Solution

1. Step 1: Check the data type of 'active' column

   'active' is a boolean, so it expects true/false without quotes.

2. Step 2: Understand why quotes cause error

   Quotes make 'true' a string, causing a type mismatch error in Postgres.

3. Final Answer:

   Using quotes around true causes a type mismatch error -> Option A

4. Quick Check:

   Boolean values must not be quoted in SQL [OK]

Hint: Boolean values in SQL are unquoted true/false [OK]

Common Mistakes:

• Assuming quotes are needed for booleans
• Thinking missing semicolon causes error
• Believing SELECT * is disallowed

5. Supabase uses Postgres to handle real-time updates. Which Postgres feature allows Supabase to efficiently notify clients about data changes?

hard

A. Postgres full table scans

B. Postgres LISTEN/NOTIFY system

C. Postgres static backups

D. Postgres manual indexing

Solution

1. Step 1: Identify Postgres feature for notifications

   Postgres provides LISTEN/NOTIFY to send messages about data changes in real-time.

2. Step 2: Understand Supabase real-time updates

   Supabase uses LISTEN/NOTIFY to push updates to clients efficiently without polling.

3. Final Answer:

   Postgres LISTEN/NOTIFY system -> Option B

4. Quick Check:

   Real-time updates use LISTEN/NOTIFY in Postgres [OK]

Hint: Real-time in Postgres uses LISTEN/NOTIFY [OK]

Common Mistakes:

• Confusing backups with real-time features
• Thinking full table scans notify clients
• Assuming manual indexing triggers updates