Why Postgres powers Supabase - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a new table in Supabase using SQL.

Supabase

CREATE TABLE [1] (id SERIAL PRIMARY KEY, name TEXT, active BOOLEAN DEFAULT true);

Drag options to blanks, or click blank then click option'

Astorage

Bfunctions

Cdatabase

Dusers

Attempts:

3 left

💡 Hint

Common Mistakes

Using reserved keywords as table names

Choosing unrelated names like 'storage'

The table name should be users to store user data.

2fill in blank

medium

Complete the code to insert a new user into the users table.

Supabase

INSERT INTO users (name) VALUES ([1]);

Drag options to blanks, or click blank then click option'

A'John Doe'

B"John Doe"

CJohn Doe

DJohn_Doe

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around strings

Using double quotes which are for identifiers

String values in SQL must be enclosed in single quotes.

3fill in blank

hard

Fix the error in the SQL query to select all users.

Supabase

SELECT * FROM [1];

Drag options to blanks, or click blank then click option'

Auser_table

Buser

Cusers

Dtable_users

Attempts:

3 left

💡 Hint

Common Mistakes

Using singular 'user' instead of plural 'users'

Guessing table names incorrectly

The correct table name is users, matching the table created earlier.

4fill in blank

hard

Fill both blanks to create a Supabase client and connect to the database.

Supabase

const supabase = createClient([1], [2]);

Drag options to blanks, or click blank then click option'

A'https://xyzcompany.supabase.co'

B'public-anonymous-key'

C'localhost'

D'admin-key'

Attempts:

3 left

💡 Hint

Common Mistakes

Using localhost instead of the real URL

Using admin key on client side

The first argument is the Supabase URL, and the second is the public anonymous key for client access.

5fill in blank

hard

Fill all three blanks to write a query that fetches active users from the users table.

Supabase

const { data, error } = await supabase.from([1]).select([2]).eq([3], true);

Drag options to blanks, or click blank then click option'

A'users'

B'*'

C'active'

D'id'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong table name

Selecting specific columns instead of all

Filtering by wrong column

This query selects all columns (*) from the 'users' table where the 'active' column is true.

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