To migrate with local Supabase

Supabase provides a local development environment that can be used to facilitate development.
The following are possible ways to migrate with Supabase.

• SQL and PL/pgSQL
• Prisma(ORM)
• Knex.js(SQL Query Builder)

Each method has its advantages and disadvantages, and in the case of the raw SQL pattern, it is better in some cases to know a little PL/pgSQL to put the seed in. For Prisma, you may need to learn the syntax and look into GitHub discussion. Knex.js also requires learning the syntax. For those unfamiliar with the Prisma front-end, I think Knex.js is the easiest to understand at this point, so I will introduce this method.

Install Supabase locally

The documents are available.https://supabase.com/docs/guides/cli/local-development
Please note that this is quite a lot of traffic to install.
Hopefully when you access http://localhost:54323/ you will see a screen like this.

The Migration Procedure

As a prerequisite, we will describe the procedure in case we want to add an authentication feature. If not, modify the procedure as necessary. Go to the supabase folder you created in the previous section and generate package.json.

npm init -y

Install pg and knex.

npm install -g knex
npm install pg

Generate knexfile.js.

knex init

Once the knexfile is generated, make the following changes to the file.

// Update with your config settings.

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {
  development: {
    client: "pg",
    connection: "postgresql://postgres:postgres@localhost:54322/postgres",
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      directory: __dirname + "/migrations",
    },
    seeds: {
      directory: __dirname + "/seeds/development",
    },
  },
};

The postgresql://postgres:postgres@localhost:54322/postgres part is the local postgres URL.
Next, generate the file for migration.

npx knex migrate:make migration_name 

Open the files in the generated migration folder and edit it as follows.

exports.up = function (knex) {
  return knex.schema.createTable("profiles", (table) => {
    table
      .uuid("id")
      .references("id")
      .inTable("auth.users")
      .primary()
      .onDelete("CASCADE");
    table.string("name", 18).notNullable;
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */

exports.down = function (knex) {
  return knex.schema.dropTable("profiles");
};

In this file, auth.users, a table containing sensitive user information, is inaccessible to general users for security reasons. Inaccessibility means that information such as your name, handle, icon, etc. cannot be retrieved (some information such as email address can be retrieved from auth.getUser). Instead, the profiles table created in this file is used to allow general users to access user information. ↓For more details, click here.

https://supabase.com/docs/guides/auth/managing-user-data

Execute the following command to perform migration up.

knex migrate:up

Accessing http://localhost:54323/ will create the profiles table as follows.

Generate the trigger

To link the auth.users and profiles tables, a trigger should be registered in the database so that when data is entered into the auth.users table (i.e., a new user is registered), data is entered into the profiles table.
Add the following code to seed.sql.

-- inserts a row into public.profiles
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, name)
  values (new.id, new.raw_user_meta_data ->>'name');
  return new;
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Once the database is initialized, run the following command to start over: supabase stop.

supabase stop
supabase start
knex migrate:up

The seed.sql is run at supabase start. If all went well, the on_auth_user_created trigger should be generated as follows.

Insert the seeds

The following command is executed to create the seed file.

knex seed:make seeds

Edit the seed file as follows.

const crypto = require("crypto");
const uuid1 = crypto.randomUUID();

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.seed = async function (knex) {
  // Deletes ALL existing entries
  await knex("auth.users").del();
  await knex("auth.users").insert({
    id: uuid1,
    raw_user_meta_data: JSON.stringify({ name: "hoge" }),
  });
};

We generate a uuid and seed the auth.users and profiles tables.
Execute the following command.

knex seed:run

The data is now in the tables.

• auth.users table

• profiles table

If we add tables, we will be able to continue the frontend development with Supabase.
Knex.js can do less than Prisma, and type generation from models is not possible. However, the Supabase CLI covers type generation from tables, so I think it would be a good match with no role conflicts. I think it is a good match. For client-side CRUD, @supabase/supabase-js is used and Knex is not used. This is to avoid SQL injection client-side SQL is controlled.