Background

In a previous article, I had my first experience with Flutter, Supabase, and PowerSync, and I found this setup quite promising:

Running a Sample of an Offline-First Todo List with Flutter, Supabase, and PowerSync

However, there was one particular aspect that concerned me. As I embarked on local development, I wanted to create a configuration where Supabase, launched on localhost, could be utilized with PowerSync also running on localhost. At the current stage, it appeared that PowerSync wasn't capable of running on localhost as I had envisioned.

As per Supabase's documentation, they strongly recommend starting development in a local environment, as seen here:

https://supabase.com/docs/guides/cli/local-development

Given the significance of this recommendation, I decided to initially forgo the Offline-First approach and focus on development using Supabase alone, while waiting for a suitable solution to emerge. After all, PowerSync is still in its Beta stage. Nevertheless, Offline-First is crucial for a seamless user experience, and if it becomes feasible to operate with Supabase alone in a local environment and then incorporate PowerSync in staging environments and beyond, it's a choice I'm considering for practical operation.

Tasks in This Article

First and foremost, since local development with Supabase is essential for this article, we'll start by setting up Supabase in a local environment while referring to the following documentation to become familiar with the process: https://supabase.com/docs/guides/cli/local-development

CLI Installation

We will install the CLI by following the instructions provided here:

https://supabase.com/docs/guides/cli/getting-started?platform=macos

$ brew install supabase/tap/supabase

By the way, for upgrading, you can use the following command:

$ brew upgrade supabase

After the upgrade, you'll need to restart the containers using the following commands:

$ supabase stop --no-backup
$ supabase start

Logging into Supabase

You can log in using the following command:

$ supabase login
You can generate an access token from https://supabase.com/dashboard/account/tokens
Enter your access token:
Finished supabase login

You can generate an access token from the URL: https://supabase.com/dashboard/account/tokens. Click on the "Generate new Token" button, enter a suitable name, and the token will be issued. Once you copy and paste this token into the terminal, the login process is successfully completed.

Creating a Project Folder

$ cd workspace
$ mkdir subapase-sample
$ cd supabase-sample
$ mkdir supabase-local-dev
$ cd supabase-local-dev
$ git init

Installing Docker

While performing these tasks, I realized that Docker had not yet been installed on my Mac. You can install it from the following link:

https://matsuand.github.io/docs.docker.jp.onthefly/desktop/mac/install/

Once Docker is up and running, follow the on-screen prompts to complete the installation.

Starting Supabase

Initialize and start Supabase using the following commands:

$ supabase init
$ supabase start

You can access the dashboard by visiting http://localhost:54323.

Installing the psql Command

$ brew doctor
$ brew update
$ brew install libpq
$ echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc
$ source ~/.zshrc
$ psql --version
psql (PostgreSQL) 16.0
$ psql 'postgresql://postgres:postgres@localhost:54322/postgres'

psql (16.0, server 15.1 (Ubuntu 15.1-1.pgdg20.04+1))
Type "help" for help.

postgres=>

DB migration

Create a new migration file with the following command:

$ supabase migration new create_employees_table

This command will generate a file named "timestamp_create_employees_table.sql" inside the migrations directory. Copy and paste the following content into this file:

create table
employees (
id bigint primary key generated always as identity,
name text,
email text,
created_at timestamptz default now()
);

Next, apply the migration using the following command:

$ supabase db reset

Now, execute an alter table operation:

$ supabase migration new add_department_to_employees_table

alter table
if exists public.employees add department text default 'Hooli';

$ supabase db reset

Inserting Sample Data

Copy and paste the following content into the supabase/seed.sql file to apply it:

insert into
public.employees (name)
values
('Erlich Bachman'),
('Richard Hendricks'),
('Monica Hall');

$ supabase db reset

Checking Schema and Data with psql Command

This is an extra step not covered in the official documentation, but I wanted to confirm it personally.

After executing this command, you will enter the psql environment, and you can check the schema and data as follows:

$ psql 'postgresql://postgres:postgres@localhost:54322/postgres'
psql (16.0, server 15.1 (Ubuntu 15.1-1.pgdg20.04+1))
Type "help" for help.

postgres=> \d

   Schema   |          Name           |   Type   |     Owner
------------+-------------------------+----------+----------------
 extensions | pg_stat_statements      | view     | supabase_admin
 extensions | pg_stat_statements_info | view     | supabase_admin
 public     | employees               | table    | postgres
 public     | employees_id_seq        | sequence | postgres
(4 rows)

postgres=> \d employees
                                  Table "public.employees"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 name       | text                     |           |          |
 email      | text                     |           |          |
 created_at | timestamp with time zone |           |          | now()
 department | text                     |           |          | 'Hooli'::text
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)

postgres=> select * from public.employees;
 id |       name        | email |          created_at           | department
----+-------------------+-------+-------------------------------+------------
  1 | Erlich Bachman    |       | 2023-10-25 23:34:05.292402+00 | Hooli
  2 | Richard Hendricks |       | 2023-10-25 23:34:05.292402+00 | Hooli
  3 | Monica Hall       |       | 2023-10-25 23:34:05.292402+00 | Hooli
(3 rows)

As expected, the schema was created, and the data was inserted.

You can google psql commands cheat sheet such as:

https://postgrescheatsheet.com/#/tables

Deployment

Create a new project from the Supabase dashboard:

https://supabase.com/dashboard/projects

Use the project-id generated for the newly created project:

$ supabase link --project-ref <project-id>
Enter your database password (or leave blank to skip): 
Finished supabase link.

You will be prompted to enter your database password or leave it blank to skip. After that, the link is established.

This will apply the migrations to the remote and update the schema

$ supabase db push
Applying migration 20231025232236_create_employees_table.sql...
Applying migration 20231025232523_add_department_to_employees_table.sql...
Finished supabase db push.

RLS Policy

Learning how to set up Row Level Security Policy is the next step, and once you're familiar with it, you'll be ready to start using it. You can refer to this YouTube video for clear instructions: https://supabase.com/docs/guides/auth/row-level-security

That concludes this article. In the next one, I plan to learn about connecting a Flutter application to the Supabase database by following this tutorial, and I'll incorporate it into my personal development project.