PostgresError: password authentication failed for user.

CyberCipher · 2024-09-30T23:50:26.725Z

I am getting error while trying to insert a new user to the DB.

C

CyberCipherOP•9/30/24, 11:53 PM

Here is my

scheme.ts

scheme.ts

scheme.ts

scheme.ts file,

import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});

import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});

import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});

import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});

C

CyberCipherOP•9/30/24, 11:53 PM

here is

db.ts

db.ts

,

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

here is

migrate.ts

migrate.ts

migrate.ts

migrate.ts,

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()

here is my

drizzle.config.ts

drizzle.config.ts

,

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});

I have a

.env

.env

file in which I have this,

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

I run

postgresql

postgresql

postgresql

postgresql in a docker container, here is the

docker-compose

docker-compose

docker-compose

docker-compose,

volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw

volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw

volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw

volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw

C

CyberCipherOP•9/30/24, 11:55 PM

now I wrote a simple query for inserting a new user,

import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}

import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}

import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}

import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}

and I have this,

import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        email: "foo1@bar.com",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;

import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        email: "foo1@bar.com",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;

import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        email: "foo1@bar.com",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;

import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        email: "foo1@bar.com",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;

C

CyberCipherOP•9/30/24, 11:56 PM

when I run my project, it starts fine but as soon as I try to go the URL the server crashes and I get this error,

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

C

CyberCipherOP•9/30/24, 11:57 PM

Am I doing something wrong? Not sure why its trying to authenticate using my

system

system

system

system user..

C

CyberCipherOP•9/30/24, 11:59 PM

I can do

psql postgres://drizzle:drizzle@localhost/drizzle

psql postgres://drizzle:drizzle@localhost/drizzle

psql postgres://drizzle:drizzle@localhost/drizzle

psql postgres://drizzle:drizzle@localhost/drizzle or

docker exec -it drizzle-db psql -U drizzle -d drizzle

docker exec -it drizzle-db psql -U drizzle -d drizzle

docker exec -it drizzle-db psql -U drizzle -d drizzle

docker exec -it drizzle-db psql -U drizzle -d drizzle both works, I get dropped in the

psql

psql

psql

psql shell.

C

CyberCipherOP•10/1/24, 6:39 AM

drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=#

drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=#

drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=#

drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=#

C

CyberCipherOP•10/1/24, 6:40 AM

migration ran successfully, so I'm guessing the problem is not with connection to DB?

K

Kuba•10/1/24, 4:27 PM

Are you sure

process.env.DB_URL

process.env.DB_URL

process.env.DB_URL

process.env.DB_URL is loaded correctly?

K

Kuba•10/1/24, 4:28 PM

Add a console log to

drizzle.config.ts

drizzle.config.ts

and see if it prints the desired value

K

Kuba•10/1/24, 4:30 PM

If not, read on how env vars are handled by Vite:
https://vitejs.dev/guide/env-and-mode.html

vitejs

Env Variables and Modes

Next Generation Frontend Tooling

KKuba Are you sure `process.env.DB_URL` is loaded correctly?

C

CyberCipherOP•10/1/24, 5:22 PM

If it wouldn't, how would the migration happen?

C

CyberCipherOP•10/1/24, 5:22 PM

But I'll double check.

C

CyberCipherOP•10/1/24, 5:25 PM

Oh! wait.. I just noticed.. its not

DB_URL

DB_URL

DB_URL

DB_URL its

DATABASE_URL

DATABASE_URL

, I have no idea how it got changed..

C

CyberCipherOP•10/1/24, 5:26 PM

in that case, I guess the migration one doesn't gets the URL from

drizzle.config.ts

drizzle.config.ts

?

K

Kuba•10/1/24, 5:28 PM

The migration runs with Node.js, where

process.env

process.env

is available. Vite has a bit of it's own logic when handling environment variables

CCyberCipher in that case, I guess the migration one doesn't gets the URL from `drizzle.confi...

K

Kuba•10/1/24, 5:30 PM

Well, it doesn't seem to as you've defined the client explicitly with:

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

C

CyberCipherOP•10/1/24, 5:31 PM

drizzle.config.ts

drizzle.config.ts

,

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);

  npm run db:generate

> sonora@0.0.1 db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql 🚀

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> sonora@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

  npm run db:generate

> sonora@0.0.1 db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql 🚀

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> sonora@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

  npm run db:generate

> sonora@0.0.1 db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql 🚀

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> sonora@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

  npm run db:generate

> sonora@0.0.1 db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql 🚀

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> sonora@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts

C

CyberCipherOP•10/1/24, 5:31 PM

but once again it crashes,

  npm run dev       

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev       

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev       

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

  npm run dev       

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0

KKuba Well, it doesn't seem to as you've defined the client explicitly with: ```ts con...

C

CyberCipherOP•10/1/24, 5:32 PM

I see.

C

CyberCipherOP•10/1/24, 5:32 PM

Can this

migrate.ts

migrate.ts

migrate.ts

migrate.ts file inherit this from

drizzle.config.ts

drizzle.config.ts

?

K

Kuba•10/1/24, 5:37 PM

I don't know to be honest. rn I tried to import the result of

defineConfig

defineConfig

defineConfig

defineConfig but seems that

dbCredentials

dbCredentials

dbCredentials

dbCredentials are not exposed

import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved

import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved

import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved

import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved

I didn't run it though

K

Kuba•10/1/24, 5:38 PM

I have it defined separately also, and I don't think these will change anytime in the project lifespan tbh

K

Kuba•10/1/24, 5:38 PM

Sometimes it's good to settle on "good enough"

K

Kuba•10/1/24, 5:39 PM

Back to the issue, how do you load environment variables?

K

Kuba•10/1/24, 5:39 PM

Do you have an env file with this var defined?

K

Kuba•10/1/24, 5:40 PM

Try prefixing it with

VITE_

VITE_

VITE_

VITE_ and see if it helps when running with Vite

KKuba Do you have an env file with this var defined?

C

CyberCipherOP•10/1/24, 5:43 PM

Yes, a

.env

.env

file and I use the

dotenv

dotenv

dotenv

dotenv package I think.

        "dotenv": "^16.4.5",

        "dotenv": "^16.4.5",

        "dotenv": "^16.4.5",

        "dotenv": "^16.4.5",

KKuba Try prefixing it with `VITE_` and see if it helps when running with Vite

C

CyberCipherOP•10/1/24, 5:43 PM

in which file? everywhere?

K

Kuba•10/1/24, 5:46 PM

Nope, just in

.env

.env

file where you define the

DATABASE_URL

DATABASE_URL

and change in the

db.ts

db.ts

where you use it, and change it to:

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL

KKuba Nope, just in `.env` file where you define the `DATABASE_URL` and change in the ...

C

CyberCipherOP•10/1/24, 5:48 PM

you mean rename the

DATABASE_URL

DATABASE_URL

to

VITE_DATABASE_URL

VITE_DATABASE_URL

VITE_DATABASE_URL

VITE_DATABASE_URL in

.env

.env

file and use

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL

import.meta.env.VITE_DATABASE_URL in

db.ts

db.ts

instead?

C

CyberCipherOP•10/1/24, 5:49 PM

I would also have to change the same in

drizzle.config.ts

drizzle.config.ts

as well I guess? otherwise how would it pick it up?

C

CyberCipherOP•10/1/24, 5:49 PM

so I have now these 2 fields in

.env

.env

file,

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle

CCyberCipher you mean rename the `DATABASE_URL` to `VITE_DATABASE_URL` in `.env` file and use...

K

Kuba•10/1/24, 5:53 PM

yes

K

Kuba•10/1/24, 5:54 PM

For now, let's just test out if it works for Vite

C

CyberCipherOP•10/1/24, 6:00 PM

interesting, in

db.ts

db.ts

I did,

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });

and it looks like its not crashing anymore..

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]

  npm run dev

> sonora@0.0.1 dev
> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "foo1@bar.com", "foo1bar1", "asdads"]

K

Kuba•10/1/24, 6:01 PM

Soo as I suspected, I recommend giving thorough read to the linked docs

C

CyberCipherOP•10/1/24, 6:01 PM

and I do infact have a new user,

drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | foo1@bar.com |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)

drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | foo1@bar.com |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)

drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | foo1@bar.com |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)

drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | foo1@bar.com |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)

K

Kuba•10/1/24, 6:02 PM

Just be aware that there's a security implication, as if you bundle/build your application, all variables exposed with

VITE_

VITE_

VITE_

VITE_ stay in client-side code

K

Kuba•10/1/24, 6:02 PM

Which means that every browser can inspect the connection string

C

CyberCipherOP•10/1/24, 6:03 PM

How can I handle this better?

K

Kuba•10/1/24, 6:03 PM

Build an API for that

K

Kuba•10/1/24, 6:03 PM

Do you use some frontend framework?

C

CyberCipherOP•10/1/24, 6:03 PM

I am using sveltekit.

K

Kuba•10/1/24, 6:04 PM

Hmm, I'm not that familiar with sveltekit, but I assume that there's a way to run this code server-side

C

CyberCipherOP•10/1/24, 6:04 PM

Ah! I see what you mean.

C

CyberCipherOP•10/1/24, 6:05 PM

I'll ask in svelte discord channel for this.

K

Kuba•10/1/24, 6:05 PM

essentialy you don't won't to expose any confidential secret to be exposed to the client

K

Kuba•10/1/24, 6:07 PM

So the drizzle client should be initialized on the server-side, and then the

VITE_

VITE_

VITE_

VITE_ prefix can be removed then

KKuba So the drizzle client should be initialized on the server-side, and then the `VI...

C

CyberCipherOP•10/1/24, 6:09 PM

I see. Perhaps I should move the drizzle client config to svelte's "backend" thing..