Bun.sql tracking issue (Postgres client)

[Jarred-Sumner]

Bun.sql is Bun's builtin postgres client, currently only available on canary builds of Bun.

import { sql } from 'bun';

const [{x}] = await sql`select 1 as x`;
console.log({x});

sqlite and other database protocols will be added sometime after it ships.

Minimum necessary to unflag for Bun v1.2:

• Automatic prepared statements
• Pass primitive types back and forth
• Pass objects back and forth
• Support sql(array) in INSERT INTO queries
• Query pipelining
• SCRAM-SHA-256 authentication
• MD5 authentication
• TLS support
• sslmode=require
• Connection timeouts
• Make sure it works on RDS
• Make sure it works on Neon
• Make sure it works on Supabase

Feature complete:

• Support COPY protocol
• Round-robin database connections, automatic graceful connection closing after timeout
• Support async iterators
• Support Point & other geo-related types

Nice to have:

• --sql-preconnect CLI flag to start connecting to DB before code loads
• sql.begin(callback) API for transactions
• sql.array(array) for various operations involving arrays
• Query instrumentation / logging

[aquapi]

Can this be an import from bun:postgres to be more clear?

import { sql } from 'bun:postgres';

const [{ x }] = await sql`select 1 as x`;
console.log({ x });

[truongan07]

Use .iterate() to run a query and incrementally return results. This is useful for large result sets that you want to process one row at a time without loading all the results into memory.

Support this method in Postgres client, please.

[versecafe]

What's the reason for a import { sql } from ... instead of matching the SQLite setup and just swapping it to bun:postgres

[rafaell-lycan]

I'd like to see some features such as flexible prepared statements and Pool as both exist on libs such as Postgres.js and Node-PG.

This is personal but the prepared statements on pg lib feel quite easy to use, but no hard feelings or anything really:

sql(
  'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)',
  ['1', 'example.com']
)

/**
 * Alternatively you could also accept an object as a param
 * which could have more metadata to improve tracing later on:
 */

sql({
  name: 'add-photo',
  text: 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)',
  values: ['1', 'example.com'],
})

It can be a future enhancement tho.

[xorraxraxret]

https://github.com/oven-sh/bun/tree/jarred/fix-postgres-duplicate-columns don't forget this.

[mithleshjs]

I'd like to see some features such as flexible prepared statements and Pool as both exist on libs such as Postgres.js and Node-PG.

This is personal but the prepared statements on pg lib feel quite easy to use, but no hard feelings or anything really:

sql(
'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)',
['1', 'example.com']
)

/**

• Alternatively you could also accept an object as a param
• which could have more metadata to improve tracing later on:
  */

sql({
name: 'add-photo',
text: 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)',
values: ['1', 'example.com'],
})

It can be a future enhancement tho.

@rafaell-lycan This is a must feature in my opinion if we were to migrate from existing pg libraries.

[Jarred-Sumner]

@mithleshjs it defaults to using prepared statements for all queries (similar to postgres.js)

[mithleshjs]

import { bindParams, findMany, type FindManyParams } from "@knaadh/pg-raw";
import { Client } from "pg";

const pgClient = new Client({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT ? Number.parseInt(process.env.DB_PORT, 10) : 5432,
  database: process.env.DB_DATABASE,
});

const params: FindManyParams = {
	table: "users",
	query: {
            select: { 
              id: true, 
              name: true 
             },
	     where: {
		name: "@name",
	     },
	     limit: 10,
	},
};
const rawQuery = findMany(params);
// Output: SELECT "id", "name" FROM "users" WHERE "name" = '@name' LIMIT 10
const { text, values } = bindParams(rawQuery, { name: "Alan Wake" });
/*
{
  text: "SELECT \"id\", \"name\" FROM \"users\" WHERE \"name\" = $1 LIMIT 10",
  values: [ "Alan Wake" ],
}
*/

const result = await pgClient.query(text, values);
console.log(result.rows[0]);

@Jarred-Sumner @rafaell-lycan We built our raw query builder and the generated query string works flawlessly with node-postgres as show in the code above and every other Postgres client and tools. But it doesn't work with Postgres.js despite the fact the generated query is just a raw query string. The reason being the Tagged Template Strings recognizes the generated query string as parameter, similar is the case with Bun Postgres client. I am of the opinion that approach used by node-postgres is much better and less opinionated and flexible for every use case. Wouldn't it be better to left it to the users if they want to use binding or not? Maybe a separate function for that?

[nicksrandall]

Supporting sslmode=verify (e.g., verify-ca or verify-full) in addition to sslmode=require is crucial for robust security because it not only encrypts the connection but also validates the server’s certificate. This prevents man-in-the-middle attacks by ensuring the certificate is signed by a trusted authority and, in the case of verify-full, that the server’s hostname matches the certificate.

Sorry, I was misinformed. Ignore this comment.

[Jarred-Sumner]

@nicksrandall did you try it

[nicksrandall]

@Jarred-Sumner Yes, it looks like I was a bit premature in commenting on this issue. Weirdly, I had sslmode=verify in my connection string (note that it was missing -ca or -full) and postgres.js seemed to handle that just fine. Bun on the other hand threw an error saying it was an unexpected value. This combined with the fact that the note above only mentioned sslmode=require lead me to assume that the feature wasn't supported.

When I change the connection string to sslmode=verify-full, everything seems to work.

I apologize for creating unnecessary noise.

[TomasHubelbauer]

Edit: I figured this out, see the edits below.

Hi, I have two questions about using Postgres in Bun, they are specifically in context of connecting to a Supabase Postgres database.

1. When using an explicit SQL constructor, is its return value the sql function to use to make queries? The docs at https://bun.sh/docs/api/sql are a bit confusing on this… In cases where the SQL connection information is implicitly taken from the environment variables, the sql function is just imported and immediately used:

   const rows = await sql`SELECT * FROM users`.values();
   console.log(rows);

   OTOH the parts that show how to pass a connection string or a connection configuration object never show how to make queries next and the SQL constructor return value is usually called db in these, but I believe the return type of the constructor is actually that template function, because in the connection strings section it hints at that: https://bun.sh/docs/api/sql#using-with-connection-strings:

   const sql = new SQL("postgres://user:password@localhost/mydb?sslmode=prefer");

   With this setup, do I import just SQL and not sql and use the return value of new SQL the same way I would use the sql function if I imported it and it was configured with environment variables instead?

2. Is there a way to pass a CRT certificate to the connection configuration? Supabase requires being able to do this: https://supabase.com/docs/guides/database/psql. I have access to the CRT file, but no way to pass it to. The connection configuration object has a tls field, but it is a boolean flag. There is also ssl in the docs, but it seems to be missing in bun-types and does not appear to be able to accept an object with the certificate information either.

   Without being able to pass the certificate, just using a connection string with the user name and DB password, I get this: ERR_POSTGRES_CONNECTION_CLOSED. I believe the cause is not passing the certificate.

Thanks

Edit: looking at the postgres.js quick start guide, it looks as though I should be able to just use a connection string:
https://supabase.com/docs/guides/database/postgres-js

I tried this:

import { SQL } from "bun";

// Note that this URL looks like this: `postgresql://postgres:${password}@db.${projectId}.supabase.co:5432/postgres`
import supabaseUrl from "../../secrets/supabaseUrl";

const sql = new SQL(supabaseUrl);

const users = await sql`select * from users`;
console.log(users);

But this still gives me this error:

PostgresError: Connection closed
 code: "ERR_POSTGRES_CONNECTION_CLOSED"

Edit:

How to get Supabase connection to work with Bun's Postgres support:

1. Use the Session Pooler connection string from the Connect button dialog in Supabase (allows using IPv4 over IPv6)
2. Append ?sslmode=require at the end of the connection string (your DB likely requires SSL, mine did and I did not wish to change that, this opts the connection into a secure one)
3. Add your own IPv4 address to the white-list in the database configuration page under the Network Restrictions section (my DB was set to reject all direct connections which I also wished to keep so I just added this exception for my own IP)
4. Now everything should work:

import { sql } from "bun";

const version = await sql`SELECT version()`;
console.log(version);

Call with:

POSTGRES_URL="postgresql://postgres.${projectId}:${dbPassword}@${geo}.pooler.supabase.com:5432/postgres?sslmode=require" bun .

Gary Austin over at the Supabase Discord helped me figure this out.

[ShlokDesai33]

@Jarred-Sumner I don't understand why the postgres driver isn't split up into it's own import like you guys did with bun:sqlite. If the goal is introduce more database drivers, won't you eventually be unable to support database specific features? How is it possible to abstract all database drivers into a single utility without losing a 100 percent feature parity with libraries that are designed to connect to only a single database? What am I missing?

[nicksrandall]

Automatic prepared statements should only happen if the user passes at least one variable. Otherwise, a "simple" query should be used that allows multiple statements. (This is how most postgres clients work).

See: #17029