Supabase Fundamentals: Database, PostgreSQL, Projects, and Core Features

Modern applications need to store and manage a lot of information.

Suppose you create a React application. A user registers:

Email: xyz@gmail.com

Password: ********

Where should you store that information?

You could technically store it in a JavaScript variable: const users = [];

But what happens when the server restarts? The data disappears. So we need persistent storage where we can store the data. For this we are use databases.

So, now we will start with database fundamentals, understand PostgreSQL, explore what a Supabase project contains, and then see how Supabase's core features work together to help developers build applications.

What Is a Database?

A database is a system which is used for store and organize information so that application can easily save, find, update, and delete that information. The application can then retrieve this information whenever it needs it.

A database allows an application to perform common operations such as:

• Store new data
• Find existing data
• Update data
• Delete data
• Search and filter data
• Manage relationships between different pieces of data

The data stored in a database is organized in a way that makes it easier for the application to access and manage. Without a proper database, applications would have a difficult time managing large amounts of changing data.

For example, a database could contain a users table:

• A table stores a particular type of data.
• A row represents one record.
• A column represents a property of that record.

So, the database is not simply a place where data is stored. It also provides a structured way to manage and work with that data.

Why are databases important?

As an application grows, the amount of data also grows. So, manually manage that it's very difficult.

Databases help applications keep their data:

• Organized
• Searchable
• Consistent
• Secure
• Accessible

This is why databases are an important part of almost every modern application.

A simple way to understand the role of a database is:

Application
   ↓
Needs data
   ↓
Database
   ↓
Stores and manages data

What Is PostgreSQL?

PostgreSQL is an open-source Relational Database Management System (RDBMS), which allows us to create, store, organize, and manage data using a relational database.

What Is a Relational Database?

A relational Database is the type of database which store data in the tables and allows to connect two different tables through relationships.

For example we have ecommerce application.

We can store users and their orders in two different tables:

Users:

Orders:

Here, user_id connects the orders table with the users table.

So:

XYZ has mouse, headphone & ABC has key-board

This is called a relational database because different tables can be related to each other using common fields such as user_id.

PostgreSQL

So, now we can understand PostgreSQL is an open-source Relational Database Management System (RDBMS), which allows us to create, store, organize, and manage data using a relational database.

We can think like this.

PostgreSQL  —  Relational Database — Tables — Rows+Columns

Management System

PostgreSQL provides the tools and functionality needed to manage that data.

For example, we can use PostgreSQL to:

• Create tables
• Add data
• Read data
• Update data
• Delete data
• Create relationships
• Add constraints
• Search data using queries
• Manage transactions
• Create indexes

So, we can say that PostgreSQL = a system for managing relational databases.

Why Does Supabase Use PostgreSQL?

Supabase uses PostgreSQL because PostgreSQL provides a powerful functionality for storing and managing application data.

Supabase does not try to create a completely new database system. It is build a services related to PostgreSQL.

That means when we use Supabase, we still get the important features of a relational database, such as:

• Tables
• Relationships
• SQL
• Constraints
• Indexes
• Transactions

With this also Supabase provides other features that are commonly needed when building applications. Such as:

• PostgreSQL → Stores and manages application data
• Authentication → Handles user sign-up and login
• Storage → Stores files such as images and documents
• APIs → Allows applications to communicate with the database
• Realtime → Allows applications to react to relevant changes
• Edge Functions → Allows developers to run server-side logic

So instead of building all these backend features separately, developers can use them together through Supabase.

What Is a Supabase Project?

When you create a project in Supabase, you get a managed environment where some backend services are available.

A project acts as the main environment for an application. The database, authentication, storage, and other Supabase services associated with the application are managed within that project.

A simplified view is:

Supabase Project:

• PostgreSQL Database
• Authentication
• Storage
• APIs
• Realtime
• Edge Functions

PostgreSQL database stores your application's structured data.

Authentication helps manage users and login sessions.

Storage can be used for files such as images and documents.

APIs provide a way for applications to communicate with the database and other Supabase services.

Realtime allows applications to react to certain data changes.

Edge Functions allow developers to run backend logic.

These services can work together inside the same project.

Understanding the Supabase Database

The database is one of the most important parts of a Supabase project. Supabase uses PostgreSQL as its database, which means the data in a Supabase project is stored and managed using PostgreSQL.

In a relational database, data is organized into tables. A table contains rows and columns, similar to a spreadsheet.

For example, suppose we are building a task management application. We have a tasks table:

In this example, tasks is the table. id, task, and completed are the columns, and each row represents one task.

Primary Key

Every record in a table usually needs a unique identifier. So for that we are used Primary Key.

For Example:

• id=1 – Learn Supabase
• id=2 – Learn Database

Here, the id column can be used as the primary key because each task has a unique ID.

Relationships Between Tables

Applications usually have more than one type of data. For example, a task application may have users and tasks. We can connect these tables using relationships. So,

users: Id, Name, Email
   |
   | user_id
   |
tasks: Id, User_id, Task, completed

The user_id column in the tasks table can reference the id column in the users table.

These relationships are one of the important features of relational databases because they allow related information to be stored in separate tables while still being connected.

PostgreSQL uses SQL (Structured Query Language) to work with data. SQL allows us to create, read, update, and delete records in our database. for,

Create:

INSERT INTO tasks (title, completed)
VALUES ('Learn Supabase', false);

Read:

SELECT *
FROM tasks;

Update:

UPDATE tasks
SET completed = true
WHERE id = 1;

Delete:

DELETE FROM tasks
WHERE id = 1;

Working With PostgreSQL in Supabase

When we create a Supabase project, a PostgreSQL database is created as part of that project. We can manage this database through the Supabase dashboard and interact with it from our application.

Flow looks like:

React Application
   ↓
Supabase Client
   ↓
Supabase
   ↓
PostgreSQL
   ↓
tasks table

For example, we have a React application with a task list. The application needs to retrieve the tasks stored in the PostgreSQL database.

Instead of connecting the browser directly to PostgreSQL, the application can use the Supabase client to communicate with the Supabase backend.

This allows the application to work with database data without having to build the complete database communication layer from scratch.

Supabase Core Features

A Supabase project provides some features that can be used together to build an application.

Such as:

• Authentication identifies the user.
• PostgreSQL stores the user's tasks.
• APIs allow the application to work with those tasks.
• Storage can store files such as profile pictures.
• Realtime can notify the application about supported data changes.
• Edge Functions can handle server-side logic or external integrations.

1. Authentication

For example, when user logs into the application, the application needs to know that the current user. Authentication helps manage this user identity and session.

We have task application Before a user can see their tasks, they need to create an account and log in.

The basic flow is user do signUp and login then happens Authenticated Session and get Access Application.

Authentication can be used for things such as:

• User sign-up
• User login
• User logout
• Managing user sessions

2. Storage

Applications often need to store files, not just text and numbers.

For example, an application may need to store:

• Profile pictures
• Product images
• Documents
• Videos

Supabase provides Storage for managing these files.

For example, in an e-commerce application, a product may have: product name, product price and product image.

The product name and price can be stored in the PostgreSQL database, while the actual image file can be stored using Supabase Storage.

3. APIs

An application needs a way to communicate with the backend. So here used the APIs.

For example, suppose our React application wants to get tasks from the database.

React Application
   ↓
API
   ↓
Supabase / PostgreSQL
   ↓
Tasks Data
   ↓
React Application

The application can use Supabase's APIs and client libraries to work with data stored in PostgreSQL.

So APIs provide a way for the application to communicate with the backend and its data.

4. Realtime

Normally, an application may need to request data again to know whether something has changed.

But some applications need to receive updates when data changes.

For example, imagine a chat application.

User A
   ↓
Sends Message
   ↓
Database Changes
   ↓
Realtime
   ↓
User B sees the message

That is very useful for this kind of applications.

For example, if one user changes a task status, another connected user can receive the change without manually refreshing the page, when the relevant Realtime functionality is configured.

5. Edge Functions

Sometimes an application needs to run some logic on the server instead of directly in the browser.

For example, suppose your application needs to call an external service using a secret API key.

You should not put that secret key directly into frontend JavaScript.

So flow looks like:

React Application
   ↓
Edge Function
   ↓
External API
   ↓
Response
   ↓
React Application

Edge Functions can be used for things such as:

• Server-side logic
• Calling external APIs
• Processing requests
• Integrating with other services

Edge Functions allow us to run backend code without putting that logic directly in the browser.

These Features Work Together

                        Task Application
                               │
             ┌─────────────────┼─────────────────┐
             ↓                 ↓                 ↓
      Authentication       Database           Storage
             │                 │                 │
           Users             Tasks             Files
                               │
                          PostgreSQL
                               │
             ┌─────────────────┼─────────────────┐
             ↓                 ↓                 ↓
            APIs           Realtime        Edge Functions

So, instead of thinking of Supabase as only a database, it is better to think of it as a platform that provides a PostgreSQL database together with several services that help developers build applications.

Security Considerations

So far, we have seen how applications can store and work with data using PostgreSQL and Supabase.

However, storing data is not enough. We also need to make sure that the data can only be accessed or modified by the appropriate users.

For example, imagine our task application has two users. User A should not normally be able to access or modify User B's tasks.

This is where database security and access control become important.

When working with Supabase, developers should consider the following:

• Use appropriate access controls so users can only access the data they are allowed to access.
• Use Row Level Security (RLS) when row-level access rules are required.
• Never expose secret credentials such as service-level keys in frontend code.
• Validate user input before processing or storing it.
• Use appropriate database permissions to control what different users or applications can do.

Row Level Security is especially useful when different users should have access to different rows in the same table.

When Should You Use Supabase?

Now that we understand databases, PostgreSQL, Supabase projects, and the core features provided by Supabase, the next question is:

When should a developer choose Supabase for an application?

Supabase can be a useful choice when an application needs a PostgreSQL database together with commonly required backend services.

For example, a developer may choose Supabase when an application needs:

• A PostgreSQL database
• User authentication
• File storage
• APIs for working with application data
• Realtime capabilities
• Server-side functions
• A managed backend environment

For example, a simple application could use:

                 Supabase
                    │
     ┌──────────────┼──────────────┐
     ↓              ↓              ↓
 PostgreSQL   Authentication     Storage
     │
     ↓
  App Data

This can reduce the amount of backend infrastructure that developers need to build and manage themselves.

You may consider another backend architecture when:

• The application requires a highly customized backend.
• The project has specialized infrastructure requirements.
• The application depends heavily on services that are not suitable or available in the chosen Supabase architecture.
• The development team already has an existing backend infrastructure that meets its requirements.

FAQs

Is Supabase a database?

Supabase provides a PostgreSQL database as part of its platform, but Supabase itself is more than just a database. It also provides services such as authentication, storage, APIs, realtime capabilities, and Edge Functions.

Is PostgreSQL part of Supabase?

Yes. PostgreSQL is the database foundation of Supabase.

Can I use Supabase with React?

Yes. Supabase provides JavaScript libraries that can be used to connect applications such as React applications to Supabase services.

Do I need Node.js to use Supabase?

Not necessarily. Supabase provides client libraries that allow frontend applications to interact with its services. However, server-side code may still be useful depending on the application's requirements.

Is Supabase only for small applications?

No. Supabase can be used for production applications as well. However, application architecture, database design, security, performance, and scaling still need to be considered based on the application's requirements.

Key Takeaways

The easiest way to understand Supabase is to start from its database foundation.

Database
   ↓
Relational Database
   ↓
PostgreSQL
   ↓
Supabase Project
   ↓
Supabase Database
   ↓
Core Services
   ↓
Application

Conclusion

Supabase makes it easier for developers to build the backend of an application. It provides a PostgreSQL database along with useful features such as Authentication, Storage, APIs, Realtime, and Edge Functions.

In this blog, we learned the basics of databases, relational databases, PostgreSQL, and how these concepts are used in Supabase. We also looked at what a Supabase project contains and how its different features can work together.

The main thing to remember is that PostgreSQL is the database foundation of Supabase. Once we understand databases and PostgreSQL, it becomes much easier to understand how Supabase works and how we can use it to build applications.