DEV Community

smallStall
smallStall

Posted on • Updated on

How to migrate with Knex.js locally in Supabase

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.

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.

Supabase Studio

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
Enter fullscreen mode Exit fullscreen mode

Install pg and knex.

npm install -g knex
npm install pg
Enter fullscreen mode Exit fullscreen mode

Generate knexfile.js.

knex init
Enter fullscreen mode Exit fullscreen mode

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",
    },
  },
};

Enter fullscreen mode Exit fullscreen mode

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 
Enter fullscreen mode Exit fullscreen mode

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");
};
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

The tables created

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();
Enter fullscreen mode Exit fullscreen mode

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

supabase stop
supabase start
knex migrate:up
Enter fullscreen mode Exit fullscreen mode

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

Insert the seeds

The following command is executed to create the seed file.

knex seed:make seeds
Enter fullscreen mode Exit fullscreen mode

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" }),
  });
};
Enter fullscreen mode Exit fullscreen mode

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

knex seed:run
Enter fullscreen mode Exit fullscreen mode

The data is now in the tables.

  • auth.users table

auth.users table

  • profiles 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.

Top comments (1)

Collapse
 
rodpatulski profile image
rodpatulski

I'm new to migrations, but what benefit would working with knex.js versus using the builtin supabase cli migration tooling?