predaytor
predaytor
Explore posts from servers
CDCloudflare DevelopersSSolidJSHHonoDTDrizzle Team
PostsComments
DTDrizzle Team
Created by predaytor on 6/6/2024 in #help
Represent a table that contains an variants with strict keys (aka `Record` mapped type)?
I have a table that represents words (it's an English learning platform) where each word should have two examples (eg US and UK). How to display such a connection? This is not a repeater field where we can create many records attached to a word, but rather an already defined dataset with (in our case) two keys. 
export const word = sqliteTable('word', {
    id: integer('id').notNull().primaryKey(),
    name: text('name', { mode: 'text', length: 255 }).notNull().unique(),

    // ?
    // examples: text('examples', { mode: 'json' }).$type<Record<'us' | 'uk', { value: string }>>().notNull(),
    // ?
    // examples: text('examples', { mode: 'json' }).$type<Array<{ value: string }>>().notNull(),
});

// ?
export const wordExample = sqliteTable('word_example', {
    id: integer('id').notNull().primaryKey(),
    wordId: integer('word_id').references(() => word.id, { onDelete: 'cascade' }).unique(),
    type: text('type', { enum: ['us', 'uk'] }).notNull().unique(),
    value: text('value', { mode: 'text' }).notNull(),
});
export const word = sqliteTable('word', {
    id: integer('id').notNull().primaryKey(),
    name: text('name', { mode: 'text', length: 255 }).notNull().unique(),

    // ?
    // examples: text('examples', { mode: 'json' }).$type<Record<'us' | 'uk', { value: string }>>().notNull(),
    // ?
    // examples: text('examples', { mode: 'json' }).$type<Array<{ value: string }>>().notNull(),
});

// ?
export const wordExample = sqliteTable('word_example', {
    id: integer('id').notNull().primaryKey(),
    wordId: integer('word_id').references(() => word.id, { onDelete: 'cascade' }).unique(),
    type: text('type', { enum: ['us', 'uk'] }).notNull().unique(),
    value: text('value', { mode: 'text' }).notNull(),
});
2 replies
DTDrizzle Team
Created by predaytor on 5/30/2024 in #help
Error handling
Should execute be used to correctly throw errors, or is it the same in this case? 
const result = await context.db
    .delete(user)
    .where(and(eq(user.role, 'student'), eq(user.id, userId)))
    .execute();

if (!result.success && result.error) throw new Error(result.error);
const result = await context.db
    .delete(user)
    .where(and(eq(user.role, 'student'), eq(user.id, userId)))
    .execute();

if (!result.success && result.error) throw new Error(result.error);
await context.db
    .delete(user)
    .where(and(eq(user.role, 'student'), eq(user.id, userId)));
await context.db
    .delete(user)
    .where(and(eq(user.role, 'student'), eq(user.id, userId)));
2 replies
DTDrizzle Team
Created by predaytor on 5/30/2024 in #help
The result after join (table alias)
How to configure alias here? The result of the join return the original table name (library_level), but I expect libraryLevel, which is defined at the application level. 
export const libraryLevel = sqliteTable('library_level', {
    id: integer('id').notNull().primaryKey(),
    name: text('name', { mode: 'text', length: 255 }).notNull().unique(),
});

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    email: text('email', { mode: 'text', length: 320 }).notNull().unique(),
    libraryLevelId: integer('library_level_id').references(() => libraryLevel.id),
});

///

const users = await context.db
    .select()
    .from(user)
    .leftJoin(libraryLevel, eq(user.libraryLevelId, libraryLevel.id))
    .all();

users[0]?.library_level

// instead of 

users[0]?.libraryError
export const libraryLevel = sqliteTable('library_level', {
    id: integer('id').notNull().primaryKey(),
    name: text('name', { mode: 'text', length: 255 }).notNull().unique(),
});

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    email: text('email', { mode: 'text', length: 320 }).notNull().unique(),
    libraryLevelId: integer('library_level_id').references(() => libraryLevel.id),
});

///

const users = await context.db
    .select()
    .from(user)
    .leftJoin(libraryLevel, eq(user.libraryLevelId, libraryLevel.id))
    .all();

users[0]?.library_level

// instead of 

users[0]?.libraryError
2 replies
DTDrizzle Team
Created by predaytor on 5/18/2024 in #help
Write SQL query in Drizzle?
Hey! How to write a similar query in drizzle? Basically, I need a way to count the total number of pages in a single query (should I?) for offset pagination? 
const nodesCount = await context.db.select({ count: count() }).from(user).where(eq(user.role, 'student'));

const users = await context.db.query.user.findMany({
    limit: pageSize,
    offset: pageOffset,
    where: user => eq(user.role, 'student'),
    orderBy: (user, { asc }) => [asc(user.fullName), asc(user.id)],
});
const nodesCount = await context.db.select({ count: count() }).from(user).where(eq(user.role, 'student'));

const users = await context.db.query.user.findMany({
    limit: pageSize,
    offset: pageOffset,
    where: user => eq(user.role, 'student'),
    orderBy: (user, { asc }) => [asc(user.fullName), asc(user.id)],
});
vs. what I found on stackoveflow: 
with cte as(
  select count(*) total from table 
)
select *, (select total from cte) total
from table limit 0, 100
with cte as(
  select count(*) total from table 
)
select *, (select total from cte) total
from table limit 0, 100
6 replies