Supabase Tutorial: Getting Started with the SQL Editor and PostgreSQL
If you're looking for a simple way to build and manage a backend, Supabase is a great place to start. Supabase is an open-source backend platform built around PostgreSQL that provides developers with a database and a collection of tools for building modern web and mobile applications.
The platform includes features such as a PostgreSQL database, authentication, file storage, APIs, and real-time capabilities, all managed from a single platform. Its developer-friendly dashboard also makes it easy to manage your project and interact with your database.
In this tutorial, we'll be working with the latest version of Supabase and its current dashboard and tools. We'll start with the basics and gradually explore how the platform works, so the examples and steps in this series reflect the current Supabase experience.
Our first focus will be the Supabase SQL Editor. The SQL Editor allows you to write and run SQL queries directly against your PostgreSQL database. You can use it to create tables, insert and retrieve data, modify records, and manage your database structure.
Whether you're new to Supabase or already familiar with PostgreSQL, this guide will give you a solid foundation for working with Supabase, PostgreSQL, and the SQL Editor.
What Is Supabase?
Supabase is an open-source backend platform that makes it easier to build modern web and mobile applications. At its core, Supabase provides a fully managed PostgreSQL database, along with a set of tools and services that help developers build applications without having to set up and maintain the entire backend themselves. If you've worked with databases before, PostgreSQL is an important part of understanding Supabase. Supabase doesn't introduce a new database system—it uses PostgreSQL as its database engine. This means you can use standard SQL and PostgreSQL features while taking advantage of the additional tools provided by Supabase. A Supabase project comes with several features that are commonly needed when building an application:
PostgreSQL Database — Store and manage your application's data using PostgreSQL.
Authentication — Add user sign-up, login, and authentication methods to your application.
Storage — Store files such as images, videos, and documents.
APIs — Access your database through automatically generated APIs.
Realtime — Listen for changes in your database and build applications that can respond to data changes in real time.
Edge Functions — Run server-side code close to your users.
All of these features are managed through the Supabase dashboard, giving you a central place to create and configure your project.
Why Use Supabase?
One of the main advantages of Supabase is that it lets you work with a familiar relational database while providing many of the tools needed to build an application around it. For example, instead of setting up a PostgreSQL server, configuring authentication separately, creating an API layer, and finding another service for file storage, you can manage these parts of your application through a single Supabase project. Supabase is also particularly useful for developers who already know—or want to learn—SQL and PostgreSQL. Because your data is stored in PostgreSQL, the SQL you learn while working with Supabase is based on a widely used relational database system. In the next section, we'll create a Supabase project and take a look at the dashboard. From there, we'll focus on the SQL Editor and start working directly with our PostgreSQL database.
How to Create a Supabase Project
Before using the Supabase SQL Editor or working with a PostgreSQL database, you need to create a Supabase project. A Supabase project includes your PostgreSQL database and provides access to features such as authentication, storage, APIs, and other backend services. In this section, we'll walk through how to create a Supabase project step by step.
Create a Supabase Account
If you don't have a Supabase account yet, you can create one.
The Supabase dashboard is where you'll create and manage your projects, databases, authentication settings, storage, and other Supabase services.
For this tutorial, we'll use the dashboard to create a new project and then explore its PostgreSQL database and SQL Editor.
Create a New Supabase Project
From the Supabase Dashboard, select New project to create your first project.
Supabase will ask you to select an organization. An organization is required to create a project, but if you don't have one yet, you can create one during this process.
For this guide, we'll use Supabase's Free tier, so you don't need to upgrade to a paid plan or enter any payment details just to get started.
You'll then need to provide some basic information about your project, including:
• Project name — The name you'll use to identify your project.
• Database password — A password for your PostgreSQL database. Keep this password secure because it may be required when connecting to the database directly.
• Region — The geographical location where your database will be hosted.
When choosing a database region, it's generally a good idea to select a location that is relatively close to the majority of your users. This can help reduce network latency when your application communicates with the database. After entering the required information, select Create new project. Supabase will then provision your project and set up the PostgreSQL database. Depending on the current Supabase platform and project configuration, this process may take a short amount of time.
Once your project is ready, open it from the Supabase Dashboard.
You'll see the project interface with access to the different services available in Supabase. The exact layout and available options may change as Supabase evolves, but you'll generally find tools for managing your database, authentication, storage, APIs, and other project settings.
For this tutorial, we're mainly interested in the database and, more specifically, the SQL Editor.
The Supabase SQL Editor
The SQL Editor is one of the main tools you'll use when working with a Supabase database. It allows you to write and run SQL queries directly against your project's PostgreSQL database. The SQL Editor is one of the main tools you'll use when working with a Supabase database. It allows you to write and run SQL queries directly against your project's PostgreSQL database. For this project, we'll use the SQL Editor to set up our database and define the rules that control how data can be accessed and modified. Instead of relying entirely on Supabase's graphical interface, we'll write the SQL ourselves so that the complete database setup can be easily understood and reproduced. We'll start by creating the tables our application needs and defining their relationships. We'll then add the necessary constraints and configure Row Level Security (RLS) to control access to the data. Our RLS policies will define what users are allowed to do with the data. Depending on the table and the user's role, the policies will allow users to read, create, update, and delete records. These rules are enforced by PostgreSQL, which means they remain in place even when the database is accessed from a different part of the application. This is particularly important for the next article in the series. Once the database and its security rules are in place, we'll connect it to a React Native application built with Expo and perform these operations from the app.
What We'll Build
In the SQL Editor, we'll set up the database step by step:
• Create the tables required by the application
• Define primary keys and relationships
• Add the necessary constraints
• Enable Row Level Security
• Create policies for reading records
• Create policies for creating records
• Create policies for updating records
• Create policies for deleting records
• Add initial data where necessary
• Test the policies and database operations
By the end of this article, we'll have a PostgreSQL database with access rules that are ready to be used by our React Native application.
In the next article, we'll use React Native and Expo to connect to Supabase and work with the database from the application.
Create the Database and RLS Policies
The Row Level Security (RLS) policies define how users can interact with the data in our database. In this project, authenticated users are allowed to read, create, update, and delete records according to the rules defined for each table. These policies are enforced at the database level, ensuring that data access is controlled by PostgreSQL regardless of whether the request comes from our React Native application or another client. In the next article, we'll connect the application to Supabase and see how these rules affect the way we work with the database from React Native and Expo.
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;
Creating an RLS Policy
Once Row Level Security is enabled, we can create policies that define what users are allowed to do with the data.
For example, the following policy allows authenticated users to view only their own notes:
CREATE POLICY "Users can view their own notes"
ON public.notes
FOR SELECT
TO authenticated
USING (auth.uid() = user_id);
Let's break it down:
CREATE POLICY "Users can view their own notes" — Creates a policy and gives it a descriptive name.
ON public.notes — Specifies that the policy applies to the notes table.
FOR SELECT — Defines the database operation covered by the policy. In this case, it applies when a user reads data.
TO authenticated — Specifies that the policy applies to authenticated users.
USING (auth.uid() = user_id) — Defines which rows the user is allowed to access. auth.uid() returns the ID of the currently authenticated user, and user_id is the ID stored with each note. The user can therefore access only rows where these two IDs match.
In simple terms, this policy means: an authenticated user can read a note only when that note belongs to them.
The same approach can be used to create policies for other database operations, such as INSERT, UPDATE, and DELETE.
Grant Permissions
In addition to RLS policies, we also need to grant the appropriate database permissions to the role that will access the table. For example:
GRANT SELECT, INSERT, DELETE
ON public.notes
TO authenticated;
Let's break it down:
• GRANT — Gives specific database permissions to a role.
• SELECT — Allows the role to read rows from the table.
• INSERT — Allows the role to create new rows.
• DELETE — Allows the role to delete rows.
• ON public.notes — Specifies that these permissions apply to the notes table.
• TO authenticated — Gives these permissions to Supabase's authenticated role.
It's important to understand that a GRANT does not determine which rows a user can access. It only determines which database operations the role is allowed to attempt.
The RLS policies provide the additional row-level restrictions. For example, GRANT SELECT allows an authenticated user to read from the notes table, while an RLS policy can restrict that user to reading only their own notes.
In simple terms:
GRANT answers: “What can this role do?”
RLS policies answer: “Which rows can this role do it to?”
Note: Starting October 30, Supabase will stop automatically granting Data API access to new tables in the public schema for existing projects. This means you'll need to explicitly grant the required permissions to the appropriate roles when creating new tables. In this tutorial, we'll include the necessary GRANT statements along with our RLS policies to make sure the tables can be accessed through the Data API.
The rules we've configured in this article apply to the database tables used by our application. Supabase Storage has its own access-control policies, which are separate from database table policies. We'll cover Storage policies and file access in a later article.
Supabase and PostgreSQL
If you're new to Supabase, one important thing to understand is the relationship between Supabase and PostgreSQL. Supabase uses PostgreSQL as the database behind every Supabase project. This means that when you create tables, define relationships, write SQL queries, or configure database permissions in Supabase, you're working with a real PostgreSQL database. Supabase adds a collection of tools and services around PostgreSQL, making it easier to build an application without having to manage the database infrastructure yourself. However, the underlying database is still PostgreSQL, so standard SQL and PostgreSQL features remain available. This is also why learning SQL while working with Supabase is useful beyond Supabase itself. The concepts you learn—such as tables, columns, relationships, constraints, indexes, and queries—are PostgreSQL concepts that can be applied to other PostgreSQL projects as well. For this series, we'll use the Supabase SQL Editor to work directly with our PostgreSQL database. The SQL we write will create the database structure and security rules that our React Native application will use later.
Common SQL Editor Mistakes
When you're getting started with the Supabase SQL Editor, it's easy to run into a few common problems. Most of them are straightforward to fix once you understand what the error is telling you.
Running a Query in the Wrong Project
If you work with multiple Supabase projects, make sure you're using the correct project before running a query. A SQL query runs against the database belonging to the project you're currently working in.
Running the Same SQL More Than Once
Some SQL statements can cause an error if you run them again. For example, trying to create a table that already exists can result in an error. If you're testing a script repeatedly, you may need to check whether the object already exists or adjust the SQL accordingly.
Missing Database Permissions
If a table doesn't have the required database permissions, requests through the Supabase Data API can fail even when RLS policies have been configured correctly. This is why our setup includes both the appropriate GRANT statements and RLS policies.
RLS Policies Not Matching Your Query
Having RLS enabled doesn't automatically mean that every query will work. Your policies determine which rows a user can access or modify. For example, a policy using:
USING (auth.uid() = user_id)
will only allow a user to access rows where the user_id matches the ID of the currently authenticated user. If a query doesn't return the data you expect, checking the relevant RLS policy is a good place to start.
Checking the Error Message
When a query fails, don't immediately assume that something is wrong with Supabase. Read the error message first. PostgreSQL often provides useful information about what went wrong, such as a missing permission, an invalid column, or a syntax error. Understanding these messages will make working with the SQL Editor much easier as your database becomes more complex.
Conclusion
In this article, we took our first steps with Supabase and explored how it works with PostgreSQL. We created a Supabase project, looked at the dashboard, and used the SQL Editor to set up our database.
We also introduced database permissions and Row Level Security (RLS) and defined the rules that control how users can read, create, update, and delete data. These rules are an important part of keeping our application's data secure and will be used when our application communicates with Supabase.
But setting up the database is only half of the story. In the next article, we'll take these rules and put them into practice by connecting our database to a React Native application built with Expo. We'll create the RLS policies for our project and then use the application to read, create, update, and delete data through Supabase. This will give us a practical look at how Supabase, PostgreSQL, RLS, and React Native work together in a real application.
This is the first article in the Supabase series and serves as an introduction to Supabase and the concepts you need to get started.
In the following articles, we'll move from the basics to practical examples, including how to work with Supabase Database and Storage in an Expo application. More topics will be introduced throughout the series as we explore different ways to use Supabase with Expo.
If you're new to Supabase, I recommend following the series from the beginning, as each article will build on what we've covered previously.