In this article, we'll create a PostgresSQL database schema with two tables, "customers" and "orders", and establish a foreign key relationship between them. Then, we'll use the Supabase client api to query the orders table and join it with the customers table to get customer details.
Supabase is an open-source platform that provides a set of tools to build and deploy modern web and mobile applications. It includes a PostgreSQL database, an authentication system, and a serverless function hosting service.
Prerequisites
Before we begin, make sure you have the following:
- A Supabase account. If you don't have one already, you can sign up for free at supabase.io.
- Node.js and npm installed on your machine.
- A basic understanding of SQL and JavaScript.
Step 1: Create a new project
Log in to your Supabase account and create a new project. Give it a name and choose a region that's closest to you. Once your project is created, you'll be taken to the dashboard.
Step 2: Create the customers table
To create the customers table, open the SQL editor from the dashboard and enter the following SQL command:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);
This will create a new table called "customers" with three columns: "id", "name", and "email". The "id" column is a serial type, which means it will automatically generate a unique value for each new row.
Step 3: Create the orders table
Next, create the orders table by entering the following SQL command in the SQL editor:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
product VARCHAR(255),
price DECIMAL(10,2)
);
This will create a new table called "orders" with four columns: "id", "customer_id", "product", and "price". The "customer_id" column is a foreign key that references the "id" column in the "customers" table. This establishes a relationship between the two tables, where each order belongs to a single customer.
Step 4: Insert dummy data
To test our schema, let's insert some dummy data into the customers and orders tables. Enter the following SQL commands in the SQL editor:
INSERT INTO customers (name, email) VALUES
('John Doe', 'johndoe@example.com'),
('Jane Smith', 'janesmith@example.com'),
('Bob Johnson', 'bobjohnson@example.com');
This will insert three rows into the customers table with the given names and email addresses. Then, enter the following SQL commands to insert some orders:
INSERT INTO orders (customer_id, product, price) VALUES
(1, 'Widget', 10.99),
(1, 'Gizmo', 7.99),
(2, 'Doodad', 5.99),
(3, 'Thingamajig', 19.99),
(3, 'Whatchamacallit', 12.99),
(3, 'Doohickey', 8.99);
This will insert six rows into the orders table, with each order belonging to a specific customer.
Step 5: Query the orders table with foreign key relations (continued)
Create a new Node.js project in your preferred code editor and navigate to the project directory in the terminal. Then, initialize the project with the following command:
npm init -y
This will create a package.json file with default settings. Next, install the @supabase/supabase-js package with the following command:
npm install @supabase/supabase-js
This package provides a client library for interacting with the Supabase database and authentication service.
Now, create a new file called index.js and paste the following code:
const { createClient } = require("@supabase/supabase-js");
const supabaseUrl = "your supabase url";
const supabaseAnonKey = "your anon key";
const supabase = createClient(supabaseUrl, supabaseAnonKey);
async function getOrders() {
const { data: orders, error } = await supabase
.from("orders")
.select("id, product, price, customer_id(id, name, email)");
if (error) {
console.error(error);
return;
}
console.log(orders);
}
getOrders();
Replace your supabase url and your anon key with your actual Supabase URL and anonymous key, respectively. You can find your Supabase URL and anonymous key on the project dashboard under the "Settings" -> "API" tab.
This code uses the Supabase client library to connect to your database and retrieve data from the "orders" table. It selects the "id", "product", "price", and "customer_id" columns, as well as the "id", "name", and "email" columns from the "customers" table using the customer_id(id, name, email) syntax.
Run the code.
node index.js
This should output an array of objects representing the orders in the "orders" table, with customer details included.
Output:
[
{
id: 1,
product: 'Widget',
price: 10.99,
customer_id: { id: 1, name: 'John Doe', email: 'johndoe@example.com' }
},
{
id: 2,
product: 'Gizmo',
price: 7.99,
customer_id: { id: 1, name: 'John Doe', email: 'johndoe@example.com' }
},
{
id: 3,
product: 'Doodad',
price: 5.99,
customer_id: { id: 2, name: 'Jane Smith', email: 'janesmith@example.com' }
},
{
id: 4,
product: 'Thingamajig',
price: 19.99,
customer_id: { id: 3, name: 'Bob Johnson', email: 'bobjohnson@example.com' }
},
{
id: 5,
product: 'Whatchamacallit',
price: 12.99,
customer_id: { id: 3, name: 'Bob Johnson', email: 'bobjohnson@example.com' }
},
{
id: 6,
product: 'Doohickey',
price: 8.99,
customer_id: { id: 3, name: 'Bob Johnson', email: 'bobjohnson@example.com' }
}
]
To wrap up: Here we have created a database schema with two tables and established a foreign key relationship between them using Supabase. You have also learned how to use the Supabase client library to retrieve data from the database and join related tables.
Some further notes on foreign Keys.
Foreign keys in SQL provide several advantages:
Referential integrity: By using foreign keys, you can ensure that the data in your tables maintains referential integrity. This means that data in the child table (e.g., orders) cannot reference data in the parent table (e.g., customers) that does not exist. This helps prevent data inconsistencies and errors in your database.
Data consistency: Foreign keys help ensure that data is consistent across related tables. For example, if you have a foreign key customer_id on an order table, you can be sure that the customer_id in the order table corresponds to a valid customer_id in the customer table. This helps ensure that your data is accurate and reliable.
Query efficiency: By using foreign keys, you can join related tables together more efficiently. In your example, if you want to select the specified customer for an order, you can join the order and customer tables using the customer_id foreign key. This can be much more efficient than querying the customer table separately for each order.
Data modeling: Using foreign keys can help you design your database schema more effectively. By modeling relationships between tables using foreign keys, you can create a more flexible and scalable data model that is easier to understand and maintain over time.
Overall, foreign keys are an important feature of SQL that help ensure data consistency, maintain referential integrity, and improve query efficiency. By using foreign keys, you can create more reliable and scalable databases that are easier to work with and maintain over time.