Inserting and querying data in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Query Result Count After Insert

You insert a new row into a Supabase table named users with columns id and name. After insertion, you run a query to count all rows in users. What will be the count if the table initially had 3 rows?

Supabase

await supabase.from('users').insert([{id: 4, name: 'Anna'}]);
const { data, error } = await supabase.from('users').select('*');
const count = data.length;

A 4

B 3

C 1

D 0

💡 Hint

Think about what happens to the number of rows after adding one.

Inserting one new row increases the total count by one. Starting from 3 rows, after insertion, the count is 4.

❓ Configuration

intermediate

Correct Insert Syntax in Supabase

Which option correctly inserts a row with id 5 and name 'John' into the users table using Supabase client?

A await supabase.from('users').insert([{id: 5, name: 'John'}]);

B await supabase.insert('users', {id: 5, name: 'John'});

C await supabase.from('users').insert({id: 5, name: 'John'});

D ;)]}'nhoJ' :eman ,5 :di{[(tresni.)'sresu'(morf.esabapus tiawa

💡 Hint

Remember that insert expects an array of objects.

Supabase's insert method requires an array of objects even for a single row. Option A uses the correct syntax.

❓ Architecture

advanced

Best Practice for Querying Large Tables

You have a large orders table with millions of rows in Supabase. You want to fetch only the latest 10 orders for a user with user_id 42. Which query is best to minimize data transfer and improve performance?

A await supabase.from('orders').select('*').eq('user_id', 42);

B await supabase.from('orders').select('*').eq('user_id', 42).limit(10).order('created_at', {ascending: false});

C await supabase.from('orders').select('*').order('created_at', {ascending: false}).limit(10);

D await supabase.from('orders').select('*').limit(10).eq('user_id', 42).order('created_at', {ascending: false});

💡 Hint

Filter first, then order and limit to reduce data scanned.

Option B filters by user_id first, then orders by date descending, and limits to 10 rows, minimizing data scanned and transferred.

❓ security

advanced

Preventing Unauthorized Data Access

You want to ensure users can only query their own data in the profiles table. Which Supabase feature helps enforce this at the database level?

A Using API keys with full access

B Encrypting data in the client

C Client-side filtering after query

D Row Level Security (RLS) policies

💡 Hint

Think about database-level access control.

Row Level Security (RLS) policies allow the database to restrict which rows a user can access, enforcing security at the source.

✅ Best Practice

expert

Handling Insert Conflicts Gracefully

You want to insert a row into the products table but avoid errors if a row with the same id already exists. Which option correctly uses Supabase to update the existing row instead of failing?

A await supabase.from('products').update({name: 'Widget'}).eq('id', 10);

B await supabase.from('products').insert([{id: 10, name: 'Widget'}]).onConflict('id').ignore();

C await supabase.from('products').insert([{id: 10, name: 'Widget'}]).onConflict('id').merge();

D await supabase.from('products').insert([{id: 10, name: 'Widget'}]);

💡 Hint

Look for the option that updates on conflict.

Option C uses onConflict('id').merge() to update the existing row if id exists, avoiding insert errors.

Practice

1. What does the insert() method do in Supabase?

easy

A. Adds new rows of data to a table

B. Deletes rows from a table

C. Updates existing rows in a table

D. Retrieves data from a table

Solution

1. Step 1: Understand the purpose of insert()

   The insert() method is used to add new data entries (rows) into a database table.

2. Step 2: Differentiate from other methods

   Methods like delete() remove data, update() changes existing data, and select() retrieves data. Only insert() adds new rows.

3. Final Answer:

   Adds new rows of data to a table -> Option A

4. Quick Check:

   insert() = Add rows [OK]

Hint: Insert means add new data rows [OK]

Common Mistakes:

• Confusing insert() with update()
• Thinking insert() deletes data
• Mixing insert() with select()

2. Which of the following is the correct syntax to insert a new user with name 'Alice' into the 'users' table using Supabase client?

easy

A. supabase.insert('users').from({name: 'Alice'})

B. supabase.users.insert('Alice')

C. supabase.table('users').add({name: 'Alice'})

D. supabase.from('users').insert({name: 'Alice'})

Solution

1. Step 1: Recall Supabase insert syntax

   The correct syntax uses supabase.from('table').insert(data) to add data.

2. Step 2: Match syntax with options

   supabase.from('users').insert({name: 'Alice'}) matches the correct pattern: supabase.from('users').insert({name: 'Alice'}). Others misuse method order or names.

3. Final Answer:

   supabase.from('users').insert({name: 'Alice'}) -> Option D

4. Quick Check:

   from('table').insert(data) = correct syntax [OK]

Hint: Use from('table').insert({data}) to add rows [OK]

Common Mistakes:

• Swapping method order like insert().from()
• Using non-existent methods like add()
• Passing data as string instead of object

3. Given this code snippet, what will be the output?

const { data, error } = await supabase
  .from('products')
  .select('*')
  .eq('category', 'books');
console.log(data);

medium

A. An empty array always

B. An error message because of wrong syntax

C. An array of products where category is 'books'

D. All products regardless of category

Solution

1. Step 1: Understand the query filters

   The select('*') fetches all columns, and .eq('category', 'books') filters rows where category equals 'books'.

2. Step 2: Predict the output of console.log(data)

   It will print an array of product objects matching the category 'books'. If no error, data contains matching rows.

3. Final Answer:

   An array of products where category is 'books' -> Option C

4. Quick Check:

   select('*').eq() filters rows = filtered array [OK]

Hint: eq() filters rows by column value [OK]

Common Mistakes:

• Thinking select('*') returns all rows without filter
• Assuming syntax error due to chaining
• Expecting empty array without data

4. What is wrong with this Supabase insert code?

const { data, error } = await supabase
  .from('orders')
  .insert(['item', 'quantity']);

medium

A. insert() expects an array of objects, not an array of strings

B. from() should be called after insert()

C. insert() cannot be awaited

D. Missing .select() after insert()

Solution

1. Step 1: Check insert() argument type

   The insert() method requires an array of objects representing rows, e.g., [{item: 'apple', quantity: 2}]. Passing an array of strings is invalid.

2. Step 2: Verify method order and usage

   Calling from() before insert() is correct. Awaiting insert() is valid. .select() after insert() is optional for returning data.

3. Final Answer:

   insert() expects an array of objects, not an array of strings -> Option A

4. Quick Check:

   insert() needs array of objects [OK]

Hint: Insert data as objects inside array [OK]

Common Mistakes:

• Passing array of strings instead of objects
• Calling from() after insert()
• Thinking insert() can't be awaited

5. You want to insert multiple new users and then immediately fetch all users with age over 20. Which code correctly does this in Supabase?

hard

A. const { data } = await supabase.from('users').select('*').gt('age', 20);
await supabase.from('users').insert([{name: 'Bob', age: 25}, {name: 'Eve', age: 22}]);

B. await supabase.from('users').insert([{name: 'Bob', age: 25}, {name: 'Eve', age: 22}]);
const { data } = await supabase.from('users').select('*').gt('age', 20);

C. await supabase.from('users').insert({name: 'Bob', age: 25, name: 'Eve', age: 22});
const { data } = await supabase.from('users').select('*').gt('age', 20);

D. await supabase.from('users').insert([{name: 'Bob', age: 25}, {name: 'Eve', age: 22}]).select('*').gt('age', 20);

Solution

1. Step 1: Insert multiple users correctly

   Use insert() with an array of objects: [{name: 'Bob', age: 25}, {name: 'Eve', age: 22}]. A single malformed object is invalid.

2. Step 2: Query users with age > 20 after insertion

   Insert first, then use a separate query: supabase.from('users').select('*').gt('age', 20). Querying before insert misses new data; chaining select().gt() after insert() is invalid.

3. Final Answer:

   Insert first, then query with .gt('age', 20) -> Option B

4. Quick Check:

   Insert then query = correct order [OK]

Hint: Insert first, then query filtered data [OK]

Common Mistakes:

• Querying before inserting new data
• Passing single object instead of array for multiple inserts
• Chaining select() after insert() incorrectly