cobite Posts - Answer Overflow

cobite

•Created by cobite on 9/25/2023 in #help

Omit not working for me

I am trying to use Omit to return the object with the id field removed:

export type Item = InferSelectModel<typeof items>;
export type ReturnItem = Omit<Item, 'id'> & {};

export type Item = InferSelectModel<typeof items>;
export type ReturnItem = Omit<Item, 'id'> & {};

However when returned it still shows the id on the objects. I could add all the fields I want directly in the select() here but that seems like a lot of work.

export default async function getAllItems(
  limit: number
): Promise<ReturnItem[]> {
  const results = await queryDB.select().from(items).limit(limit);
  return results;
}

export default async function getAllItems(
  limit: number
): Promise<ReturnItem[]> {
  const results = await queryDB.select().from(items).limit(limit);
  return results;
}

4 replies

DTDrizzle Team

•Created by cobite on 9/24/2023 in #help

Making a column only allow letters and characters, including capitals

I am using Yup for my validation on the client side. For example the username field does this:

.matches(/^[a-zA-Z0-9]*$/, 'Username can only contain letters and numbers')

.matches(/^[a-zA-Z0-9]*$/, 'Username can only contain letters and numbers')

My user schema on the drizzle side is:

username: varchar('username', { length: 20 }).unique().notNull(),

username: varchar('username', { length: 20 }).unique().notNull(),

Just wondering is there a way to add the regex to it? Thanks

4 replies

DTDrizzle Team

•Created by cobite on 9/24/2023 in #help

Trying to generate a short id via a random string function

Hi everyone, In my schema I have:

id: uuid('id').primaryKey().defaultRandom().unique().notNull(),

id: uuid('id').primaryKey().defaultRandom().unique().notNull(),

I now want to add a shortId field, which will use something like:

shortId: unique().notNull(),

shortId: unique().notNull(),

Which would use this:

export function generateRandomString(length: number) {
  const characters =
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let result = '';

  for (let i = 0; i < length; i++) {
    const randomIndex = Math.floor(Math.random() * characters.length);
    result += characters.charAt(randomIndex);
  }

  return result;
}

export function generateRandomString(length: number) {
  const characters =
    'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  let result = '';

  for (let i = 0; i < length; i++) {
    const randomIndex = Math.floor(Math.random() * characters.length);
    result += characters.charAt(randomIndex);
  }

  return result;
}

So that I can have a shortId string generated each time, much like the normal uuid for the id field.

18 replies

DTDrizzle Team

•Created by cobite on 9/1/2023 in #help

Transforming SELECT from case to camelCase via sql with execute

I am getting some values for example

comments.created_at,

comments.created_at,

I wish to transform this with the AS keyword:

comments.created_at AS createdAt,

comments.created_at AS createdAt,

The issue is that it just returns as:

createdat

createdat

It does not follow the camelCase that was defined, it just goes lower case for some reason.

6 replies

DTDrizzle Team

•Created by cobite on 8/28/2023 in #help

How to get the count of records? Here is my normal sql, trying to translate into drizzle

.select({
      id: comments.id,
      parentId: comments.parentId,
      // TODO countReplies: get the comment count where this id is listed as the parentId of other comments
      (
             SELECT COUNT(*)
             FROM comments AS childComments
             WHERE childComments.parentId = comments.id
           ) AS countReplies
    })
    .from(comments);

.select({
      id: comments.id,
      parentId: comments.parentId,
      // TODO countReplies: get the comment count where this id is listed as the parentId of other comments
      (
             SELECT COUNT(*)
             FROM comments AS childComments
             WHERE childComments.parentId = comments.id
           ) AS countReplies
    })
    .from(comments);

100 replies

DTDrizzle Team

•Created by cobite on 8/28/2023 in #help

How to add to the database where the table has a many relationship to another table

I have a users table, which has a many relationship to to a posts table. I want to add a new user to the table, which I can do, but how do I go about adding new posts to that user? Does it add a full post object to the user? Or just an id to the new post which will be in the post table?

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom().unique().notNull(),
  username: varchar('username', { length: 512 }).unique().notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

5 replies

DTDrizzle Team

•Created by cobite on 8/26/2023 in #help

How to do this query in one step instead of two?

Is there a better way to do this than having two different queries? // gets all posts for a specific user with only a users name passed in.

export default async function getAllPosts(
  userName: string
): Promise<Post[]> {

  const userResult = await queryDB
    .select({ id: users.id })
    .from(users)
    .where(eq(users.name, userName))
    .limit(1);

  const results = await queryDB
    .select()
    .from(posts)
    .where(eq(posts.userId, userResult[0].id))

  return results;
}

3 replies

DTDrizzle Team

•Created by cobite on 8/25/2023 in #help

How to query from a many to many relationship?

Hi all, I am trying to get all the posts by a user. My schema has a many-to-many relationship set up for posts and users.

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom().unique().notNull(),
});

export const postsRelations = relations(posts, ({ many }) => ({
  users: many(users),
}));

export type Post = InferModel<typeof posts>;

And then:

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom().unique().notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export type User = InferModel<typeof users>;

Now in my file I want to

export default async function getAllUsersPosts(userId: string): Promise<Post[]> {
  const results = await queryDB.select().from(posts).where(eq(posts.users.id, userId));
  return results;
}

Error is:

Property 'users' does not exist on type 'PgTableWithColumns<{ name: "posts"; schema: undefined; columns: { id: PgColumn<{ name: "id"; tableName: "posts"; dataType: "string"; columnType: "PgUUID"; data: string; driverParam: string; notNull: true; hasDefault: true; enumValues: undefined; baseColumn: never; }

2 replies

DTDrizzle Team

•Created by cobite on 8/25/2023 in #help

optional parent child relationship on same table

Hi all I have a table called 'comments' Some comments will have a parent comment, while others will not. The error I am getting is: 'comments' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer. The code:

export const comments = pgTable('comments', {
  id: uuid('id').primaryKey().defaultRandom().unique().notNull(),
  parentId: uuid('parent_id').references(() => comments.id),

export const commentsRelations = relations(comments, ({ one }) => ({
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
  }),
}));

export type Comment = InferModel<typeof comments>;

I am using: pg-core

1 replies

Gaming

Programming