Closed DmitryScaletta closed 5 months ago
I forgot to mention that it's also possible to get session and user in one query with Drizzle Queries, but it requires one additional step - defining session relations:
const userTable = sqliteTable('user', {
id: text('id').notNull().primaryKey(),
username: text('username').notNull(),
});
const sessionTable = sqliteTable('session', {
id: text('id').notNull().primaryKey(),
userId: text('user_id')
.notNull()
.references(() => userTable.id),
expiresAt: integer('expires_at').notNull(),
});
const sessionTableRelations = relations(sessionTable, ({ one }) => ({
user: one(userTable, {
fields: [sessionTable.userId],
references: [userTable.id],
}),
}));
const sqliteDb = sqlite(':memory:');
const db = drizzle(sqliteDb, {
schema: { userTable, sessionTable, sessionTableRelations },
});
await db.insert(userTable).values({ id: '1', username: 'John' });
await db.insert(sessionTable).values({ id: '1', userId: '1', expiresAt: 0 });
const result = await db.query.sessionTable.findFirst({
with: {
user: true,
},
where: eq(sessionTable.id, '1'),
});
{
id: '1',
userId: '1',
expiresAt: 0,
user: { id: '1', username: 'John' }
}
Thanks!
DrizzleORM has Partial Select API so it's very easy to get session and user in one database query.
Simple example how it works
```ts import { eq, sql } from 'drizzle-orm'; import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'; import { drizzle } from 'drizzle-orm/better-sqlite3'; import sqlite from 'better-sqlite3'; const userTable = sqliteTable('user', { id: text('id').notNull().primaryKey(), username: text('username').notNull(), }); const sessionTable = sqliteTable('session', { id: text('id').notNull().primaryKey(), userId: text('user_id') .notNull() .references(() => userTable.id), expiresAt: integer('expires_at').notNull(), }); const sqliteDb = sqlite(':memory:'); const db = drizzle(sqliteDb, { schema: { userTable, sessionTable } }); db.run(sql`CREATE TABLE user ( id TEXT NOT NULL PRIMARY KEY, username TEXT NOT NULL )`); db.run(sql`CREATE TABLE session ( id TEXT NOT NULL PRIMARY KEY, expires_at INTEGER NOT NULL, user_id TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) )`); await db.insert(userTable).values({ id: '1', username: 'John' }); await db.insert(sessionTable).values({ id: '1', userId: '1', expiresAt: 0 }); const result = await db .select({ user: userTable, session: sessionTable, }) .from(sessionTable) .innerJoin(userTable, eq(sessionTable.userId, userTable.id)) .where(eq(sessionTable.id, '1')); console.log(result); /* [ { user: { id: '1', username: 'John' }, session: { id: '1', userId: '1', expiresAt: 0 } } ] */ ```