Reusing types from Supabase

[gilbert]

Just in case anyone needs it, here's how to reuse your generated Supabase types for Kysely:

import { Database } from './types/supabase'

type Tables = Database['public']['Tables']

type DB = {
  [TableName in keyof Tables]: {
    [ColumnName in keyof Tables[TableName]['Insert']]-?:
      undefined extends Tables[TableName]['Insert'][ColumnName]
      ? Generated<NoUndefined<Tables[TableName]['Insert'][ColumnName]>>
      : Tables[TableName]['Insert'][ColumnName]
  }
}

type NoUndefined<T> = T extends undefined ? never : T

const db = new Kysely<DB>(...)

This transforms e.g. the following Supabase type:

interface Database {
  public: {
    Tables: {
      projects: {
        Row: {
          created_at: string
          id: number
          name: string
        }
        Insert: {
          created_at?: string
          id?: number
          name: string
        }
        Update: {
          created_at?: string
          id?: number
          name?: string
        }
      }
    }
  }
}

into the following for Kysely:

type DB = {
  projects: {
    created_at: Generated<string>
    id: Generated<number>
    name: string
  }
}

Not sure if it's perfect but it's a great starting point!

[jtlapp]

This is nice! But I haven't decided whether to keep generating the schema via Kysely.

What dialect adapter are you using? Have you written a driver? The driver looks like it might be complex enough that it ought to be its own repo, if not included with Kysely itself.

[igalklebanov]

This is nice! But I haven't decided whether to keep generating the schema via Kysely.

What dialect adapter are you using? Have you written a driver? The driver looks like it might be complex enough that it ought to be its own repo, if not included with Kysely itself.

AFAIK you can use pg or postgres.js. The supabase client doesn't seem to map to Kysely's internals at all.

[jtlapp]

AFAIK you can use pg or postgres.js. The supabase client doesn't seem to map to Kysely's internals at all.

I have Kysely working with Supabase using pg's ClientConfig.connectionString, but it's pooling connections. Serverless solutions need to use Supabase's pgBouncer for connection pooling.

So I think all we need is a non-pooling Postgres driver for Kysely, one that uses pg's Client instead of Pool. Kysely's PostgresDialectConfig only accepts a pool property and not a client property. I just need support for Client too.

[jtlapp]

I guess I could just be using a max pool size of 1, but if that's all I need to do, I wonder why pg bothered to provide a single-connection Client too.

[jtlapp]

I posted the question on Stackoverflow and have so far received two comments:

• "It's important to use a client only when you need to control that you get the same single client every time (and, probably, that nothing else is using that one client)"
• "Using a Pool with a maximum of 1 connection vs a single client vs a new client for every request only should make a difference regarding the number of idle open connections to the database and the (average) time it takes for a request handler to acquire the connected client. It also depends on how your "serverless" environment handles concurrent requests and how long it keeps your application open to receive the next request vs starting it from scratch."

[igalklebanov]

how long it keeps your application open to receive the next request

A single lambda function instance, if keeps receiving requests, can stay alive for 2-3 hours.
https://xebia.com/blog/til-that-aws-lambda-terminates-instances-preemptively/

[jtlapp]

A single lambda function instance, if keeps receiving requests, can stay alive for 2-3 hours.

I'm not sure what to do with this. I don't think Lambda or Vercel will throttle when all connections in the pool are in use. I think each HTTP request has to open and close its connection.

Here's something else. At present, to use Kysely with Supabase, we have to use the postgres protocol. But that protocol is not secure by default. In fact, Supabase has this to say:

"Postgres SSL Enforcement is currently in beta and is slowly being made available to all projects. Contact support if you'd like to request early access."

This has me wondering if Kysely (or any ORM) should even be used with Supabase at this time.

[jtlapp]

Okay, I've learned that multi-query transactions have to use Supabase's direct postgres port, which does not pool DB connections, while non-transactional queries are free to use Supabase's pgBouncer port, which pools connections.

So we do need a special Supabase adapter. I might as well code it to use pg's single-connection Client, allowing me to sidestep the client-side pooling issue. I wasn't fond of having each request wasting time and memory creating and destroying pools, anyway.

UPDATE: I'll write this tomorrow. I'm seeing people have trouble with local development when they assume only a single connection, so I'll support connection pooling as well, using Client only when max connections is 1. You can use Supabase outside serverless, after all.

[jtlapp]

I'm backtracking. Supabase provides a "transaction" mode on its pgBouncer port, which preserves a pg connection for the duration of a transaction, but not across multiple transactions. A Supabase client therefore is not necessary.

However, I've looked through the node-postgres code for pooling, and it's quite a bit on top of also creating individual client connections. The thought of doing this unnecessary work on each HTTP request is driving me crazy, so I'm inclined to create a serverless postgres dialect anyway. I'll leave it to the app to decide whether to use the native pooling dialect or this serverless dialect for rigid single-connection access.

Also, I noticed that Supabase automatically gives me an SSL cert for postgres connections, so the doc I read must be out of date, as it seems it's not necessary to contact support to enable this feature.

[o-az]

Just in case anyone needs it, here's how to reuse your generated Supabase types for Kysely:

import { Database } from './types/supabase'

type Tables = Database['public']['Tables']

type DB = {
  [TableName in keyof Tables]: {
    [ColumnName in keyof Tables[TableName]['Insert']]-?:
      undefined extends Tables[TableName]['Insert'][ColumnName]
      ? Generated<NoUndefined<Tables[TableName]['Insert'][ColumnName]>>
      : Tables[TableName]['Insert'][ColumnName]
  }
}

type NoUndefined<T> = T extends undefined ? never : T

const db = new Kysely<DB>(...)

This transforms e.g. the following Supabase type:

interface Database {
  public: {
    Tables: {
      projects: {
        Row: {
          created_at: string
          id: number
          name: string
        }
        Insert: {
          created_at?: string
          id?: number
          name: string
        }
        Update: {
          created_at?: string
          id?: number
          name?: string
        }
      }
    }
  }
}

into the following for Kysely:

type DB = {
  projects: {
    created_at: Generated<string>
    id: Generated<number>
    name: string
  }
}

Not sure if it's perfect but it's a great starting point!

@gilbert where did the Generated type utility come from in your code?

[igalklebanov]

@o-az Kysely.