How to connect my PostgreSQL database to Sveltekit and run queries?

Question

I'm trying to connect my postgres database to my web app. Per this post, I have my hooks.js connection set up like so:

export const handle = async ({event, resolve}) => {
  event.locals = {
    user: 'me',
    host: 'localhost',
    database: 'test',
    password: 'password',
    port: 1234,
  }
  const response = await resolve(event)
  return response;
}

And am now trying to set up my endpoint. How can I access my db in an endpoint and set up queries?

Selfeffacing

https://mcmap.net/q/1480031/-how-do-i-integrate-mysql-with-sveltekit-nodejs – Herringbone

Answer 1

I think you are missing a piece to connecting to the database. With the code snippet you provided above, it looks like you are trying to pass the database connection information through in the locals variable, but what you need to be passing is the database connection itself.

To start then, you would need a Postgres client. You can use any one you want, I'll show you what a connection might look like with the postgresjs package found here

hooks.js

import postgres from 'postgres';

export const handle = async ({event, resolve}) => {
  const sql = postgres('postgres://username:password@host:port/database');

  event.locals = {
    sql: sql
  };
  const response = await resolve(event);
  return response;
};

You will have to fill in the database information in the connection string like you have above, but now your endpoints will have access to the locals object which will contain the sql connection.

Next:

yourendpoint.js

export async function get({ locals }) {
  // destructure to get the sql connection
  const { sql } = locals;

  // Do work with the connection
  // different connection libraries will do this in different ways
  // postgres.js uses tagged template literals,
  // and you pass in sql statements
  const result = await sql`SELECT name, age FROM users`;

  // Now you have a result object that contains the query response
  // Do what you need to do from here

  ... code to work with result ... 
};

The rest of what you need for sending the response and so forth is documented in the Sveltekit docs. The key takeaway here though is that you are setting up the connection in the hooks.js file and then passing it along with the request to the endpoint handler. This centralizes your setup logic in one place.

Also if you aren't familiar with postgres libraries for Javascript, try a few out. postgres.js is designed to be simple and straightforward, as is pg. You can also step up to libraries that do more for you like sequelize or prisma

I am personally a huge fan of Prisma, but I encourage you to experiment and find what fits best for you.

Hypochromia

Answer 2

I'd recommend against putting your database credentials in event.locals, since it's meant for request-specific information and your creds are independent of each individual request. From the SvelteKit docs:

There are two hooks files, both optional:

src/hooks.server.ts — your app's server hooks

src/hooks.client.ts — your app's client hooks

Code in these modules will run when the application starts up, making them useful for initializing database clients and so on.

The way I set up the connection, modified from here, is as follows:

// lib/db.ts
import { Pool } from "pg";

/**
 * Create a new connection pool to the database.
 */
const pool = new Pool({
  database: import.meta.env.POSTGRES_DB || "postgres",
  user: import.meta.env.POSTGRES_USERNAME || "postgres",
  host: import.meta.env.POSTGRES_HOST || "localhost",
  port: Number(import.meta.env.POSTGRES_PORT || 5432),
});

/**
 * Connect to the PostgreSQL database.
 * @returns {Promise<import("pg").Client>} A new client from the connection pool.
 */
export const connectToDB = async () => await pool.connect();

Then you can import it in your hooks.server.ts file to make the database connection available in the event:

import { connectToDB } from "$lib/db";
import type { Handle } from "@sveltejs/kit";

export const handle = (async ({ event, resolve }) => {
  const dbconn = await connectToDB();
  event.locals = { dbconn };

  const response = await resolve(event);
  dbconn.release();

  return response;
}) satisfies Handle;

Weichsel

Thanks! hooks.server.ts - something that sits on the server and runs once - was exactly what I was looking for. I added a quote from the Svelte docs which confirms this is the best place. – Rosaline