Inserting and querying data in Supabase - Mini Project: Build & Apply

Inserting and querying data

📖 Scenario: You are building a simple cloud database for a small bookstore. You want to add books to the database and then find books by their author.

🎯 Goal: Create a table with book data, add a configuration variable for filtering, write a query to find books by a specific author, and complete the setup to return the query results.

📋 What You'll Learn

Create a table called books with columns id, title, and author

Insert three specific books into the books table

Create a variable called author_name to filter books by author

Write a query to select books where author matches author_name

Complete the query to return the filtered books

💡 Why This Matters

🌍 Real World

Managing and querying data in a cloud database is essential for many applications like online stores, blogs, and inventory systems.

💼 Career

Knowing how to insert and query data in cloud databases like Supabase is a key skill for backend developers and cloud engineers.

1

Create the books table and insert data

Write SQL commands to create a table called books with columns id (integer, primary key), title (text), and author (text). Then insert these three books exactly: (1, 'The Hobbit', 'J.R.R. Tolkien'), (2, '1984', 'George Orwell'), and (3, 'To Kill a Mockingbird', 'Harper Lee').

Supabase

-- Your SQL code here to create table and insert data

Need a hint?

Use CREATE TABLE to make the table, then INSERT INTO to add each book.

2

Add a variable to filter by author

Create a variable called author_name and set it to the string 'George Orwell'. This will be used to find books by this author.

Supabase

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');

-- Create a variable called author_name and set it to 'George Orwell'
-- Your code here

Need a hint?

Assign the string 'George Orwell' to the variable author_name.

3

Write a query to select books by author

Write a SQL query to select all columns from the books table where the author column matches the variable author_name. Use a placeholder or parameter for author_name in the query.

Supabase

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');
author_name = 'George Orwell'

-- Write a SQL query to select all books where author equals author_name
-- Your code here

Need a hint?

Use a parameter placeholder like :author_name in the SQL query string.

4

Complete the query execution to get results

Write code to execute the query using the Supabase client, passing author_name as a parameter, and assign the result to a variable called result. Use the method supabase.rpc or supabase.from('books').select().eq() as appropriate.

Supabase

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES (1, 'The Hobbit', 'J.R.R. Tolkien');
INSERT INTO books (id, title, author) VALUES (2, '1984', 'George Orwell');
INSERT INTO books (id, title, author) VALUES (3, 'To Kill a Mockingbird', 'Harper Lee');
author_name = 'George Orwell'
query = "SELECT * FROM books WHERE author = :author_name"

-- Execute the query using supabase client and assign to result
-- Your code here

Need a hint?

Use supabase.from('books').select('*').eq('author', author_name) to get the filtered books.