Kysely•2y ago

Unable to connect to DB

Thank God there is a Discord server xD I been trying to make this work for like 2 hours and I can't get it to work

import { Pool } from 'pg';
import {
    Kysely,
    PostgresDialect,
    Generated,
    ColumnType,
    Selectable,
    Insertable,
    Updateable
} from 'kysely';

interface UserTable {
    id: Generated<number>;
    email: string;
    firstName: string | null;
    lastName: string | null;
}

interface Database {
    User: UserTable;
}

const db = new Kysely<Database>({
    dialect: new PostgresDialect({
        pool: new Pool({
            user: 'postgres',
            password: 'secretPassword',
            host: 'localhost',
            port: 5432,
            database: 'Barbercate'
        })
    })
});

console.log(db);

export async function getUser() {
    console.log('inside kysely.ts');
    const person = await db.selectFrom('User').select('firstName').executeTakeFirst();
    console.log('person:', person);
    return person;
}

export async function getAllTables(): Promise<string[]> {
    const tables = await db.select('*').from('pg_catalog.pg_tables').execute();
    return tables;
}

import { Pool } from 'pg';
import {
    Kysely,
    PostgresDialect,
    Generated,
    ColumnType,
    Selectable,
    Insertable,
    Updateable
} from 'kysely';

interface UserTable {
    id: Generated<number>;
    email: string;
    firstName: string | null;
    lastName: string | null;
}

interface Database {
    User: UserTable;
}

const db = new Kysely<Database>({
    dialect: new PostgresDialect({
        pool: new Pool({
            user: 'postgres',
            password: 'secretPassword',
            host: 'localhost',
            port: 5432,
            database: 'Barbercate'
        })
    })
});

console.log(db);

export async function getUser() {
    console.log('inside kysely.ts');
    const person = await db.selectFrom('User').select('firstName').executeTakeFirst();
    console.log('person:', person);
    return person;
}

export async function getAllTables(): Promise<string[]> {
    const tables = await db.select('*').from('pg_catalog.pg_tables').execute();
    return tables;
}

Solution:

There are a few approaches: https://kysely.dev/docs/recipes/schemas 1. WithSchemaPlugin to apply schema globally to all queries. 2. withSchema method to apply to specific queries on the fly....

Working with schemas | Kysely

First of all, when we talk about schemas in this document, we mean custom

Jump to solution

22 Replies

Igal•2y ago

Hey 👋 What errors are you getting?

NazCodelandOP•2y ago

my db variable returns an empty object so all the functions I run on it don't work cuz it's an empty object that doesn't contain those functions for example, selectFrom

Igal•2y ago

its a class instance, without toString, toJSON, etc. implementation and no regard for enumarable keys. It doesn't mean the functions don't exist on it when you print it 🙂

NazCodelandOP•2y ago

ok my knowledge isn't too vast so excuse me if its an easy fix

Igal•2y ago

All good, we all learn each day

NazCodelandOP•2y ago

Igal•2y ago

export async function getAllTables(): Promise<string[]> {
    const tables = await db.select('*').from('pg_catalog.pg_tables').execute();
    return tables;
}

export async function getAllTables(): Promise<string[]> {
    const tables = await db.select('*').from('pg_catalog.pg_tables').execute();
    return tables;
}

this shouldn't work. it's not kysely code. Also Kysely has an introspection module that returns all tables.

NazCodelandOP•2y ago

the reason I had this code was because I keep getting the error that

Igal•2y ago

https://kysely-org.github.io/kysely/classes/Kysely.html#introspection this exists on db and has functionality to get all tables metadata

Kysely | kysely

Documentation for kysely

NazCodelandOP•2y ago

I keep getting this error

Uncaught (in promise) TRPCClientError: getIser is not defined
    at TRPCClientError.from (transformResult-9a244fe7.mjs:4:20)
    at httpBatchLink-e66c0674.mjs:187:60
from @ transformResult-9a244fe7.mjs:4
(anonymous) @ httpBatchLink-e66c0674.mjs:187
await in (anonymous) (async)
(anonymous) @ index.mjs:470
Show 3 more frames

Uncaught (in promise) TRPCClientError: getIser is not defined
    at TRPCClientError.from (transformResult-9a244fe7.mjs:4:20)
    at httpBatchLink-e66c0674.mjs:187:60
from @ transformResult-9a244fe7.mjs:4
(anonymous) @ httpBatchLink-e66c0674.mjs:187
await in (anonymous) (async)
(anonymous) @ index.mjs:470
Show 3 more frames

Igal•2y ago

this is a TRPC error

NazCodelandOP•2y ago

oops one second I noticed a small mistake this is my TRPC endpoint code

relation "User" does not exist

relation "User" does not exist

I am making the api call using TRPC but it's this function that isn't running

Igal•2y ago

relation User does not exist means that table is not in your database

NazCodelandOP•2y ago

right, but I do have it in my db

NazCodelandOP•2y ago

NazCodelandOP•2y ago

and that is why I was trying to retrieve all my tables to understand what Kysely sees but I will take a look at the link you sent, thank you for the help, hope you don't mind if I come back with further questions

Igal•2y ago

Its all good, happy to answer look at your Pool config, verify its the right database instance. verify User table is in the default postgres schema, if not you need to add schema prefix when refering to it or use .withSchema

NazCodelandOP•2y ago

NazCodelandOP•2y ago

its in the default Postgres Schema I think I need to do .withSchema and usue "barber" does .withSchema() go on the Pool instance?

NazCodelandOP•2y ago

https://kysely-org.github.io/kysely/classes/WithSchemaPlugin.html found this gonna take a loook

WithSchemaPlugin | kysely

Documentation for kysely

NazCodelandOP•2y ago

export async function getUser() {
    console.log('inside kysely.ts');
    const person = await db
        .withSchema('barber') // Specify the schema here
        .selectFrom('User')
        .select('firstName')
        .executeTakeFirst();
    console.log('person:', person);
    return person;
}

export async function getUser() {
    console.log('inside kysely.ts');
    const person = await db
        .withSchema('barber') // Specify the schema here
        .selectFrom('User')
        .select('firstName')
        .executeTakeFirst();
    console.log('person:', person);
    return person;
}

thank you very much @Igal if not you need to add schema prefix where would I add this in here database:'Barbercate\schema=barbe' rather have it default to that schema instead of using .withSchema() within my functions beautiful, thank you again!

Solution

Igal•2y ago

There are a few approaches: https://kysely.dev/docs/recipes/schemas 1. WithSchemaPlugin to apply schema globally to all queries. 2. withSchema method to apply to specific queries on the fly. 3. naming the tables schema.table in Database interface.

Working with schemas | Kysely

First of all, when we talk about schemas in this document, we mean custom

Gaming

Programming

Unable to connect to DB