Supabase Dialect

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'supabase',
  host: 'aws-0-us-east-1.pooler.supabase.com',
  port: 6543, // the transaction pooler
  database: 'postgres',
  username: 'postgres.abcdefghijklmnop',
  password: process.env.SUPABASE_DB_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'supabase',
  url: 'supabase://postgres.abcdefghijklmnop:secret@aws-0-us-east-1.pooler.supabase.com:6543/postgres',
});

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
  meta: { type: DataTypes.JSONB() },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

import { Op } from 'ts-prorm-orm';

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

new Prorm({
  dialect: 'supabase',
  host: 'aws-0-us-east-1.pooler.supabase.com',
  port: 6543,
  username: 'postgres.abcdefghijklmnop',
  password: process.env.SUPABASE_DB_PASSWORD,
  database: 'postgres',
  dialectOptions: { sslRootCert: fs.readFileSync('prod-ca-2021.crt', 'utf8') },
});

[prorm supabase] Connection is encrypted but the server certificate is not verified
(ssl.rejectUnauthorized is false), matching Supabase's sslmode=require guidance. To
verify the server, download the project's server CA certificate from the dashboard
and pass it as `sslRootCert`. To require verification instead of falling back, set
tls: 'verify'.

new SupabaseDialect({
  host: 'db.abcdefghijklmnop.supabase.co',
  tls: 'verify', // throws unless you also pass `sslRootCert` or `ssl`
});

// Exported from src/dialects/supabase, not from the package root.
import { supabaseAuth, SUPABASE_ROLES } from 'ts-prorm-orm/dialects/supabase';

const dialect = db.getDialectInstance() as SupabaseDialect;
await dialect.enableRLS('profiles');
await dialect.createOwnerPolicy({ table: 'profiles', command: 'UPDATE' });
// CREATE POLICY "profiles_update_own" ON "public"."profiles" FOR UPDATE TO authenticated
//   USING ("user_id" = (select auth.uid())) WITH CHECK ("user_id" = (select auth.uid()))

await dialect.createPolicy({
  name: 'Same org',
  tableName: 'projects',
  roles: [SUPABASE_ROLES.authenticated],
  using: { org_id: supabaseAuth.jwt('app_metadata', 'org_id', { asText: true }) },
});
// ... USING ("org_id" = (select auth.jwt() -> 'app_metadata' ->> 'org_id'))