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

user_14592298 0
Viewed 979

Still learning web dev fundamentals so be patient with me :)

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? Thank you in advance for the responses or any insight.

sveltekit svelte postgresql
1 Answers

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.

user_132147680
Related