porsager / postgres

Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare
The Unlicense
7.54k stars 273 forks source link

Dynamic Custom Type support #959

Open Louis-Tian opened 1 month ago

Louis-Tian commented 1 month ago

This PR tries to solve two related issues in relation to custom types.

  1. Allow custom type to be added using type name instead of oid.
  2. Allow custom type to be added after initialisation.

Currently, to add a custom type one must know the oid of the type beforehand on initialisation. This introduces a chicken and egg problem. In order to get the oid one must have a working instance to query the pg_type table to begin without. In addition, if a type is created on database during a session, there is no way to add a matching type on the client side without recreate a new Sql instance.

To solve this problem, this PR adds an addType function. For example

 const sql = postgres(options)
  await sql.addType('point', {
    serializer: (v) => `(${v.x},${v.y})`,
    parser: (v) => {
      const [x, y] = v.slice(1, -1).split(',')
      return { x: parseFloat(x), y: parseFloat(y) }
    }
  })
  await sql`create table test (p point)`
  const data = { x: 1, y: 2 }
  await sql`insert into test values(${sql.typed.point(data)})`

  const [{ p }] = await sql`select p from test`
  const equal = p.x === data.x && p.y === data.y

The idea of this PR is simple. we delay the registration of custom type after sql initialise. then use that connection to query the pg_type to get the oid and use it to add serialiser and parser dynamically.

One problem I am not sure how to solve is how merge the typescript definition of this new custom type into the existing TTypes. Just throw it out first to see what everyone think.