MAutomate PostgreSQL Documentation with postgres-to-docs

postgres-to-docs is a fork of the original Klarna Incubator project postgres-to-docs that I've enhanced and maintained. Special thanks to the Klarna Incubator team for creating the initial foundation for this tool.

Supabase is an open-source alternative to Firebase, providing a powerful suite of tools built on top of PostgreSQL. It offers:

• A managed PostgreSQL database with real-time capabilities
• Built-in authentication and user management
• Row Level Security (RLS) for fine-grained access control
• Auto-generated APIs
• Edge Functions
• File storage

As I've been diving deeper into Supabase for recent projects, I've found myself increasingly appreciating PostgreSQL's power and flexibility. However, one challenge kept surfacing: keeping database documentation up-to-date as I rapidly iterated on my schema using Supabase's intuitive interface. This need led me to fork and enhance postgres-to-docs - a tool that automatically generates comprehensive Markdown documentation from PostgreSQL schemas, making it perfect for Supabase projects.

[Rest of content remains the same...] As I've been diving deeper into Supabase for recent projects, I've found myself increasingly appreciating PostgreSQL's power and flexibility. However, one challenge kept surfacing: keeping database documentation up-to-date as I rapidly iterated on my schema using Supabase's intuitive interface. This need led me to create postgres-to-docs - a tool that automatically generates comprehensive Markdown documentation from PostgreSQL schemas, making it perfect for Supabase projects.

Why I Built This

Working with Supabase has been fantastic - its PostgreSQL foundation combined with modern developer tools makes building data-driven applications a breeze. However, as my projects grew, I found myself facing several documentation challenges:

1. Rapid Schema Evolution: Supabase's interface makes it easy to modify your schema, but keeping documentation in sync became a manual chore
2. Team Communication: Other developers needed to understand the schema without always having direct Supabase access
3. Version Control: Wanted documentation that could live alongside code in Git
4. RLS Documentation: Needed to document Row Level Security policies, which are crucial in Supabase projects

The Solution

postgres-to-docs addresses these challenges by providing:

• Automated Generation: Documentation updates automatically with your schema changes
• Supabase Compatibility: Works seamlessly with Supabase's PostgreSQL databases
• RLS Policy Documentation: Captures and documents Row Level Security policies
• Markdown Output: Clean, readable documentation that integrates with your existing docs

Getting Started with Supabase

Setting up postgres-to-docs with your Supabase project is straightforward:

1. Install the package:

npm install @hankanman/postgres-to-docs

2. Create a configuration file (postgrestodocs.json) with your Supabase database credentials:

{
  "host": "db.YOUR_PROJECT_REF.supabase.co",
  "port": 5432,
  "user": "postgres",
  "password": "YOUR_DB_PASSWORD",
  "database": "postgres",
  "schema": "public",
  "output": "docs/schema.md",
  "includeTables": [],
  "excludeTables": [],
  "includeTypes": true,
  "pureMarkdown": false,
  "includeRLS": true,
  "includeToc": true
}

3. Run the tool:

postgres-to-docs

Key Features for Supabase Users

1. RLS Policy Documentation

One of the most critical aspects of Supabase projects is Row Level Security. postgres-to-docs automatically documents your RLS policies:

### users

#### Row-Level Security Policies

**Policy**: auth_users_policy
**Command**: SELECT, UPDATE, DELETE
**Roles**: authenticated
**Definition**:

```sql
auth.uid() = id
```

2. Schema Documentation

The tool captures all essential schema elements that you might create through the Supabase interface:

### profiles

| Name              | Type                     | Default    | Nullable | References |
| ----------------- | ------------------------ | ---------- | -------- | ---------- |
| id (PK)           | uuid                     | auth.uid() | False    |
| email             | text                     |            | False    |
| is_subscribed     | boolean                  | false      | False    |
| subscription_tier | user_tier                |            | True     |
| created_at        | timestamp with time zone | now()      | False    |

3. Custom Types and Enums

Supabase projects often use custom types for better data modeling. postgres-to-docs handles these beautifully:

### user_tier

- free
- pro
- enterprise

Technical Implementation

Let's look at some interesting aspects of how postgres-to-docs works with Supabase databases:

RLS Policy Extraction

We fetch RLS policies using carefully crafted queries:

const selectRLSPolicies = async () => {
  const queryString = `
    SELECT
      schemaname,
      tablename AS table_name,
      policyname AS policy_name,
      format('%I ON %I.%I TO %s', policyname, schemaname, tablename, roles) AS policy_definition,
      cmd AS command,
      roles::text,
      qual AS using,
      with_check
    FROM
      pg_policies
    WHERE
      schemaname = $1
  `
  const result = await query(queryString, [schema || 'public'])
  const decoded = rlsPolicyDecoder.guard(result.rows)
  return decoded
}

Handling Supabase System Tables

We automatically filter out Supabase's internal tables while allowing you to document your application tables:

const createTableFilter = () => {
  if (includeTables && includeTables.length > 0) {
    return `AND (${includeTables.map((pattern) => `tablename ~ '${pattern}'`).join(' OR ')})`
  }
  if (excludeTables && excludeTables.length > 0) {
    return `AND NOT (${excludeTables.map((pattern) => `tablename ~ '${pattern}'`).join(' OR ')})`
  }
  return ''
}

Real-World Usage with Supabase

In my recent projects, I've integrated postgres-to-docs into my development workflow:

1. Development Phase:

   • Make schema changes through Supabase interface
   • Run postgres-to-docs to update documentation
   • Commit both schema changes and updated docs

2. Team Communication:

   • Share generated documentation with team members
   • Include schema documentation in pull requests
   • Use as reference during code reviews

3. Project Maintenance:

   • Keep documentation in sync with database migrations
   • Document RLS policies for security reviews
   • Track schema evolution over time

Future Plans

Based on my experience with Supabase, I'm planning several enhancements:

1. Supabase CLI Integration: Direct integration with Supabase CLI for seamless workflow
2. Edge Function Documentation: Document Edge Functions and their database interactions
3. Migration Tracking: Track and document schema changes between versions
4. Security Policy Visualization: Better visualization of RLS policies and their effects
5. Realtime Configuration: Document Realtime configuration for tables

Contributing

postgres-to-docs is open source and welcomes contributions, especially from the Supabase community! You can help by:

• Sharing your Supabase use cases
• Suggesting Supabase-specific features
• Contributing code improvements
• Improving documentation

Check out the GitHub repository to get involved.

Conclusion

As I continue building with Supabase, postgres-to-docs has become an essential part of my development workflow. It helps maintain clear, accurate documentation of my database schema, security policies, and data relationships - all crucial aspects of Supabase projects.

Whether you're building a small prototype or a large-scale application with Supabase, postgres-to-docs can help you maintain clear, up-to-date documentation of your database schema. Try it out on your Supabase project and let me know how it works for you!