Support ORMs (Prisma, Drizzle ect.)

[arvinxx]

[samwillis]

do it support prisma?

Not yet, we will be able to explore ORM support once we have #17 parameterized queries

[alexgleason]

After #17 I will create a custom adapter for this with Kysely to play around with it.

[jlarmstrongiv]

Hi @alexgleason 😄 did you end up making an example of how to use kysely with pglite?

[kzlar]

Also mentioned this on #8 but it would be awesome if pglite could support postgraphile ORM, I have a PoC that fails due to missing tables that are needed for introspection that I can share if there's interest.

[intellix]

would like to add a vote for sequelize. Not sure if there's anything ORM specific or if it would basically be an engine swap

[amerryma]

Also mentioned this on #8 but it would be awesome if pglite could support postgraphile ORM, I have a PoC that fails due to missing tables that are needed for introspection that I can share if there's interest.

Curious how are you using postgraphile with pglite?

[sledorze]

After #17 I will create a custom adapter for this with Kysely to play around with it.

out of curiosity, have you started on it ? (no pressure, this is OSS :) )

[kzlar]

Also mentioned this on #8 but it would be awesome if pglite could support postgraphile ORM, I have a PoC that fails due to missing tables that are needed for introspection that I can share if there's interest.

Curious how are you using postgraphile with pglite?

I posted my PoC over on postgraphile's discord:
https://discord.com/channels/489127045289476126/551374822521831424/1223313956668571758
The idea is to eventually have complete e2e testing with a single process - so no need to set up a postgres server that you need to set up and tear down for every test. Still a long way to go though :)

[lastmjs]

Just an FYI I would like to see support for the following:

• Kysely
• Drizzle
• TypeORM
• Prisma

[lastmjs]

What special work needs to be done to support these? I was hoping the API/interface was pretty basic and generic

[sledorze]

Context: we wanted to use PGLite to speed up our unit tests with an in memory version.

As a feedback to save time for people trying the same :

• We had a problem with the single process nature (no isolation between Txs) - I understand why, docs are crystal clear, it's by design.
• We had a problem with startup time and RAM consumption - because of test isolation as the runner creates as much in memory instances as files make necessary - this is logical and also by design

This is not a criticism against PGLite; those are the problems all other solutions based on the same usage of bringing 'native' PG in the runtime will have.

Beside that: Great library, thanks for all efforts !

[samwillis]

@lastmjs Drizzle now supports PGlite as of a couple week ago. I should have updated here!

For the others, PGlite should have everything needed to build adapters/plugins for them. I'm not sure we are going to do this ourselves due to a long list of core PGlite stuff to do first. I'm very happy to do any testing or bug fixes to help out with building extensions.

For the Drizzle support they did the majority of the work and then I did a pass over it once they had tests running to fix any failures (at the time mostly our side). I am more than happy to do the same again!

[samwillis]

@sledorze we have a few ideas around how to improve memory usage and startup time for unit/integration testing.

What I would love to do is create a method to fork an in memory PGlite with copy-on-write of the VFS (and maybe the heap+stack, but I suspect that's hard). You could then create the database and load any data once, but then create a clean fork for each test. This should be super quick with relatively low memory usage per fork. It's not on the roadmap currently, but I hope we can look at it at some point.

[xeoshow]

Hi @lastmjs ,

Is it possible to support Parse Server? Since it supports the stardard pgsql. Thanks very much.
https://github.com/parse-community/parse-server

[nicksrandall]

@alexgleason did you ever to get writing an adapter for Kysley?

[alexgleason]

@alexgleason did you ever to get writing an adapter for Kysley?

I could never get it working in Deno, so I gave up. It wouldn't be hard to do, though. Just fork the official postgres dialect: https://github.com/kysely-org/kysely/tree/master/src/dialect/postgres and modify executeQuery

[sukeshpabolu]

Eagerly waiting for this support. Make it happen.

[luixo]

I spinned up a simple Kysely adapter, the query values parsing is stolen from pg-mem.
It's a very rough version, probably doesn't work properly!

[alexgleason]

Here is a PGlite Kysely dialect based on the official Postgres dialect (with some pieces adapted from SQLite): https://jsr.io/@soapbox/kysely-pglite

Source code: https://gitlab.com/soapbox-pub/kysely-pglite

[samwillis]

@alexgleason wow awesome!

Will you be publishing it to NPM too? We can add it to the ORM page on the docs (will have to rename the page ORMs and Query builders 😁 )

[alexgleason]

JSR packages work with npm/Node too, though it's a bit hacky:

npx jsr add @soapbox/kysely-pglite

Once you realize how much better things can be, it's hard to go back. 😃

[electrovir]

FYI Prisma has a ticket for PGlite, where they appear to be making progress: prisma/prisma#23752

[Bewinxed]

I'm using drizzle with webworkers just fine :) it will type complain but it works! I use the following to generate tables:

import { PGliteWorker } from '@electric-sql/pglite/worker'
import { getTableColumns, getTableName } from 'drizzle-orm'
import type { AnyPgColumn, PgIndexMethod, PgTable } from 'drizzle-orm/pg-core'
import { drizzle } from 'drizzle-orm/pglite'

export function generateCreateTableQuery<T extends PgTable>(
  table: T,
  indexes: {
    key: keyof T
    algorithm?: PgIndexMethod
  }[] = [],
) {
  const tableName = getTableName(table)
  const columns = getTableColumns(table)

  const columnDefinitions = Object.entries(columns).map(([columnName, column]) => {
    const pgColumn = column as AnyPgColumn
    let definition = `"${columnName}" `

    // Map Drizzle types to PostgreSQL types
    switch (pgColumn.dataType.toString().toLowerCase()) {
      case 'string':
        definition += 'TEXT'
        break
      case 'uuid':
        definition += 'UUID'
        break
      case 'date':
        definition += 'TIMESTAMP'
        break
      case 'number':
        definition += 'NUMERIC'
        break
      case 'json':
        definition += 'JSONB'
        break
      case 'array':
        definition += 'TEXT[]'
        break
      default:
        definition += pgColumn.dataType.toString().toUpperCase()
    }

    if (pgColumn.primary) {
      definition += ' PRIMARY KEY'
    }
    if (pgColumn.notNull) {
      definition += ' NOT NULL'
    }
    if (pgColumn.default !== undefined) {
      if (typeof pgColumn.default === 'function') {
        if (pgColumn.default.toString().includes('now()')) {
          definition += ' DEFAULT CURRENT_TIMESTAMP'
        } else if (pgColumn.default.toString().includes('random()')) {
          definition += ' DEFAULT gen_random_uuid()'
        }
      } else if (typeof pgColumn.default === 'number') {
        definition += ` DEFAULT ${pgColumn.default}`
      } else if (typeof pgColumn.default === 'string') {
        definition += ` DEFAULT '${pgColumn.default}'`
      }
    }

    return definition
  })

  const indexQuery = indexes
    .map(({ algorithm, key }) => {
      let indexDef = `CREATE INDEX IF NOT EXISTS "${tableName}_${String(key)}_idx" ON "${tableName}"`
      if (!algorithm) {
        indexDef += `(${String(key)})`
      } else if (
        algorithm === 'gin' &&
        (columns[key as string] as AnyPgColumn).dataType.toString().toLowerCase() === 'string'
      ) {
        // Use a more generic approach that doesn't rely on a specific text search configuration
        indexDef += ` USING gin (${String(key)} gin_trgm_ops)`
      } else {
        indexDef += ` USING ${algorithm} (${String(key)})`
      }
      return `${indexDef};`
    })
    .join('\n')

  // Add extension creation for trigram search
  const extensionQuery = ''
  ;('CREATE EXTENSION IF NOT EXISTS pg_trgm;')

  const createTableQuery = `
  ${extensionQuery}
  
  CREATE TABLE IF NOT EXISTS "${tableName}" (
    ${columnDefinitions.join(',\n  ')}
  );
  
  ${indexQuery}
    `

  return createTableQuery
}

YMMV