CyberCipher
CyberCipher
PostsComments
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
I see. I don't like this, it feels like a hack, I don't think this should be in a release but rather in a testing or dev branch. I think I'm gonna stick to db.select().
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
Yes, I pass both.
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
this makes more sense when reading the query as well.
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
unless there is some benefit performance wise or something.. I think I'll stick with this.
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
Is db.query worth it though? I always used db.select but was trying out this recently, at first it seemed simple, but I think I'll stick with db.select because complex queries like, 
    const libraries = await db
        .select({
            library: Library,
            artist: Artist,
            album: Album
        })
        .from(Library)
        .leftJoin(LibraryArtist, eq(Library.id, LibraryArtist.library_id))
        .leftJoin(Artist, eq(LibraryArtist.artist_id, Artist.id))
        .leftJoin(LibraryAlbum, eq(Library.id, LibraryAlbum.library_id))
        .leftJoin(Album, eq(LibraryAlbum.album_id, Album.id))
        .leftJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id))
    const libraries = await db
        .select({
            library: Library,
            artist: Artist,
            album: Album
        })
        .from(Library)
        .leftJoin(LibraryArtist, eq(Library.id, LibraryArtist.library_id))
        .leftJoin(Artist, eq(LibraryArtist.artist_id, Artist.id))
        .leftJoin(LibraryAlbum, eq(Library.id, LibraryAlbum.library_id))
        .leftJoin(Album, eq(LibraryAlbum.album_id, Album.id))
        .leftJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id))
I don't know how you would do that with db.query thing.
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
I didn't see any example in the post.
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
    const libraries = await db.query.Library.findMany({
        with: {
            users: {
                where: eq(LibraryUser.user_id, user_id)
            },
            paths: true
        },
    });
    const libraries = await db.query.Library.findMany({
        with: {
            users: {
                where: eq(LibraryUser.user_id, user_id)
            },
            paths: true
        },
    });
this is not correct either..
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
hmm?
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
This works, I would like to db.query though if possible. 
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db
        .select()
        .from(Library)
        .innerJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id));

    return libraries;
}
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db
        .select()
        .from(Library)
        .innerJoin(LibraryUser, eq(Library.id, LibraryUser.library_id))
        .where(eq(LibraryUser.user_id, user_id));

    return libraries;
}
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
can db.query do this kind of query? do I have to use db.select() and leftJoin or something?
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
I wanna get the all libraries for a user.. 
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db.query.Library.findMany({
        with: {
            users: true,
            paths: true
        },
        where: eq(LibraryUser.user_id, user_id)
    });

    return libraries;
}
export async function get_all_libraries_for_user(user_id: string) {
    const libraries = await db.query.Library.findMany({
        with: {
            users: true,
            paths: true
        },
        where: eq(LibraryUser.user_id, user_id)
    });

    return libraries;
}
this doesn't work, 
PostgresError: column Library.user_id does not exist
    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)
PostgresError: column Library.user_id does not exist
    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)
20 replies
DTDrizzle Team
Created by CyberCipher on 11/22/2024 in #help
How to query join table with db.query syntax?
import { relations } from "drizzle-orm";
import { text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryUser } from "./library-user";
import { Playlist } from "./playlist";
import { schema } from "./schema";
import { UserTrackLikes } from "./user-track-likes";
import { UserTrackPlays } from "./user-track-plays";

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username", { length: 255 }).notNull().unique(),
    email: varchar("email", { length: 255 }).notNull(),
    token: varchar("token", { length: 255 }).unique(),
    hashed_password: varchar("hashed_password", { length: 255 }).unique().notNull(),
    avatar_url: varchar("avatar_url", { length: 255 }),
    role: text("role").array().default(["User"]).notNull(),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        user_index: uniqueIndex("user_index").on(table.email)
    }
});

export const UserRelation = relations(User, ({ many }) => {
    return {
        libraries: many(LibraryUser),
        playlists: many(Playlist),
        track_likes: many(UserTrackLikes),
        track_plays: many(UserTrackPlays),
    }
});
import { relations } from "drizzle-orm";
import { text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";
import { LibraryUser } from "./library-user";
import { Playlist } from "./playlist";
import { schema } from "./schema";
import { UserTrackLikes } from "./user-track-likes";
import { UserTrackPlays } from "./user-track-plays";

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username", { length: 255 }).notNull().unique(),
    email: varchar("email", { length: 255 }).notNull(),
    token: varchar("token", { length: 255 }).unique(),
    hashed_password: varchar("hashed_password", { length: 255 }).unique().notNull(),
    avatar_url: varchar("avatar_url", { length: 255 }),
    role: text("role").array().default(["User"]).notNull(),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        user_index: uniqueIndex("user_index").on(table.email)
    }
});

export const UserRelation = relations(User, ({ many }) => {
    return {
        libraries: many(LibraryUser),
        playlists: many(Playlist),
        track_likes: many(UserTrackLikes),
        track_plays: many(UserTrackPlays),
    }
});
20 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
Thanks! @Kuba
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
I'm also learning sveltekit BTW. I chose this framework as I wanted to learn it.
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
I see. Perhaps I should move the drizzle client config to svelte's "backend" thing..
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
I'll ask in svelte discord channel for this.
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
Ah! I see what you mean.
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
I am using sveltekit.
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
How can I handle this better?
57 replies
DTDrizzle Team
Created by CyberCipher on 9/30/2024 in #help
PostgresError: password authentication failed for user.
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     | [email protected] |       | 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     | [email protected] |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)
57 replies