srtd 🪄 Supabase Repeatable Template Definitions

Live-reloading SQL templates for Supabase projects. DX supercharged! 🚀

srtd enhances the Supabase DX by adding live-reloading SQL from templates into local db.

Templates act as single-source-of-truth of your database objects, that build to regular SQL migrations; This makes for sane code reviews and functional change history, (e.g git blame works as expected).

📖 Blog: Introducing srtd: Live-Reloading SQL Templates for Supabase

Why This Exists 🤔

While building Timely's next-generation Memory Engine on Supabase, we found ourselves facing two major annoyances:

1. Code reviews were painful - function changes showed up as complete rewrites, git blame was useless
2. Designing and iterating on database changes locally was full of friction, no matter which workflow we tried

I spent nearly two years looking for something pre-existing, to no avail. Sufficiently fed up, I paired with Claude to eliminate these annoyances.

Say hello to srtd.

Key Features ✨

• Live Reload: Changes to your SQL templates instantly update your local database
• Templates as source of truth: Templates are the source of (non-mutable) database objects
• Just SQL: Templates as just SQL, and build to standard Supabase migrations when you're ready to ship
• Sane code reviews: Templates evolve like regular code, with diffs in PR's working git blame.
• Developer Friendly: Interactive CLI with visual feedback for all operations.

Built specifically for projects using the standard Supabase stack (but probably works alright for other Postgres-based projects, too).

Quick Start 🚀

Requirements

• Node.js v20.18.1 or higher
• Supabase project initialized (in /supabase).

Installation

# Global installation
npm install -g @t1mmen/srtd

# Project installation
npm install --save-dev @t1mmen/srtd

# Or run directly
npx @t1mmen/srtd

Setup

cd your-supabase-project
npx @t1mmen/srtd init # Creates srtd.config.json, not required

Create Your First Template

Create supabase/migrations-templates/my_function.sql:

DROP FUNCTION IF EXISTS public.my_function; -- Makes it easier to change args later
CREATE FUNCTION my_function()
RETURNS void AS $$
BEGIN
  -- Your function logic here
END;
$$ LANGUAGE plpgsql;

Development Workflow

1. Start watch mode:

npx @t1mmen/srtd watch  # Changes auto-apply to local database

2. When ready to deploy:

npx @t1mmen/srtd build     # Creates timestamped migration file
supabase migration up      # Apply using Supabase CLI

Tip

To reduce noise in PR's, consider adding migration-templates/*srtd*.sql linguist-generated=true to your .gitattributes file. (unless you manually edit the generated files)

The Power of Templates 💪

Without templates, the smallest change to a function would show up as a complete rewrite in your version control system. With templates, the diff is clear and concise.

Perfect For 🎯

✅ Database functions:

  -- Event notifications
  DROP FUNCTION IF EXISTS notify_changes;
  CREATE FUNCTION notify_changes()
  RETURNS trigger AS $$
  BEGIN
    PERFORM pg_notify(
      'changes',
      json_build_object('table', TG_TABLE_NAME, 'id', NEW.id)::text
    );
+   RAISE NOTICE 'Notified changes for %', TG_TABLE_NAME; -- Debug logging
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

✅ Row-Level Security (RLS):

  -- Replace/update policies safely
  DROP POLICY IF EXISTS "workspace_access" ON resources;
  CREATE POLICY "workspace_access" ON resources
    USING (workspace_id IN (
      SELECT id FROM workspaces
      WHERE organization_id = auth.organization_id()
+       AND auth.user_role() NOT IN ('pending')
    ));

✅ Views for data abstraction:

  CREATE OR REPLACE VIEW active_subscriptions AS
  SELECT
    s.*,
    p.name as plan_name,
    p.features
  FROM subscriptions s
  JOIN plans p ON p.id = s.plan_id
-  WHERE s.status = 'active';
+  WHERE s.status = 'active'
+    AND s.expires_at > CURRENT_TIMESTAMP;

✅ Roles and Permissions:

  -- Revoke all first for clean state
  REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;

  -- Grant specific access
  GRANT USAGE ON SCHEMA public TO authenticated;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO authenticated;
+ GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admin;

✅ Safe Type Extensions:

 DO $$
 BEGIN
   -- Add new enum values idempotently
   IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'notification_type') THEN
     CREATE TYPE notification_type AS ENUM ('email', 'sms');
   END IF;

   -- Extend existing enum safely
   ALTER TYPE notification_type ADD VALUE IF NOT EXISTS 'push';
   ALTER TYPE notification_type ADD VALUE IF NOT EXISTS 'pusher';
   ALTER TYPE notification_type ADD VALUE IF NOT EXISTS 'webhook';
+  ALTER TYPE notification_type ADD VALUE IF NOT EXISTS 'whatsapp';
 END $$;

✅ Triggers

 DROP TRIGGER IF EXISTS on_new_user ON auth.users;
 DROP FUNCTION IF EXISTS public.setup_new_user;

 CREATE FUNCTION public.setup_new_user() RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER
 SET search_path = public AS $$
 BEGIN
   -- Existing logic for new users

+  -- Your new changes go here..
 END;
 $$;

 CREATE TRIGGER on_new_user AFTER INSERT ON auth.users FOR EACH ROW EXECUTE PROCEDURE public.setup_new_user ();

Tip

You don't need to specifying parameters in drop functions. E.g DROP FUNCTION IF EXISTS public.my_function;. This ensures you don't end up with multiple functions with the same name, but different parameters.

Not Recommended For:

• ❌ Table structures
• ❌ Indexes
• ❌ Data modifications
• ❌ Non-idempotent operations

Use regular Supabase migrations for these cases.

Commands 🎮

Interactive Mode

Running npx @t1mmen/srtd without arguments opens an interactive menu. All commands can also be run directly:

• 👀 srtd watch - Watch and auto-apply changes
• 🏗️ srtd build [--force] - Generate migrations from templates
• ▶️ srtd apply [--force] - Apply templates directly to local database
• ✍️ srtd register [file.sql...] - Mark templates as already built
• 🚀 srtd promote - [file.sql ...] - Promote WIP template to buildable templates
• 🧹 srtd clean - Remove all logs and reset config

Important

watch and apply commands modify your local database directly and don't clean up after themselves. Use with caution!

Configuration 📝

srtd.config.json can be created with init command. It is not necessary, if the defaults suit your needs.

{
  // Prevents building templates with this extension
  "wipIndicator": ".wip",

  // Migration file naming: 20211001000000_srtd-my_function.sql
  "migrationPrefix": "srtd",

  // Template discovery
  "filter": "**/*.sql",

  // Migration file comments
  "banner": "You very likely **DO NOT** want to manually edit this generated file.",
  "footer": "",

  // Wrap migrations in transaction
  "wrapInTransaction": true,

  // File paths
  "templateDir": "supabase/migrations-templates",
  "migrationDir": "supabase/migrations",
  "buildLog": "supabase/migrations-templates/.buildlog.json",
  "localBuildLog": "supabase/migrations-templates/.buildlog.local.json",

  // Database connection
  "pgConnection": "postgresql://postgres:postgres@localhost:54322/postgres"
}

Other Features 🔧

Work in Progress Templates

Add .wip.sql extension to prevent migration generation:

my_function.wip.sql  # Only applied locally, never built

Make a WIP template buildable as migration by renaming it, or using the promote command:

npx @t1mmen/srtd promote my_function.wip.sql

Register Existing Objects

Registering a template is useful when you're creating templates for what is already in your database. This avoids generating migrations on build (until they're changed)

# Register specific template
npx @t1mmen/srtd register my_function.sql another_fn.sql

# Interactive multi-select UI
npx @t1mmen/srtd register

This can be useful when setting up srtd for an existing project, where you may have hundreds of existing functions, views, etc that you want as templates, but don't want to generate migrations until changed later.

Template State Management

The state of templates are stored to..

• .buildlog.json - Migration build state (commit this)
• .buildlog.local.json - Local database state (add to .gitignore)

This helps srtd identify when templates are changed, to only build (as migrations) or apply the necessary changes (directly to local db).

Development 🛠️

Local Setup

# Clone and install
git clone https://github.com/stokke/srtd.git
cd srtd
npm install

# Development
npm run dev     # Watch mode
npm test        # Run tests
npm start       # Run CLI
npm start:link  # Build, npm link, and run CLI

# Quality Checks
npm run typecheck       # Type checking
npm run lint            # Lint and fix
npm run format          # Format code
npm run test:coverage   # Test coverage

Contributing 🤝

While feature-complete for our needs, we welcome:

• 🐛 Bug fixes and reliability improvements
• 📚 Documentation improvements
• ✅ Test coverage enhancements
• ⚡️ Performance optimizations

Contribution Process

1. Create a changeset (npm run changeset)
2. Ensure tests pass (npm test)
3. Follow existing code style
4. Update documentation

Note: New features are evaluated based on alignment with project scope.

Built With 🛠️

Terminal UI

• Ink - React for CLI interfaces
• Pastel - Next-like framework for Ink
• Figures - Unicode symbols
• Chokidar - File watcher
• update-notifier - Version checks
• Zod - Schema validation
• Conf - Config management
• vhs - Video recording

License

MIT License - see LICENSE file.

---

Made with 🪄 by Timm Stokke & Claude Sonnet