titongo Posts - Answer Overflow

titongo

Explore posts from servers

•Created by titongo on 8/3/2024 in #help

limiting a join when filtering by an extra field.

I have a query which finds properties based on how far away they are from a certain point and then joins some other tables.

 const sq = db
    .select({
      ...getTableColumns(placeDetailsTable),
      distance: sql<number>`.....`.as("distance"),
    })
    .from(placeDetailsTable)
    .as("subquery");

  const query = await db
    .select()
    .from(sq)
    .where(
      and(lt(sq.distance, maxDistanceInKm), eq(postingsTable.isActive, true)),
    )
    .orderBy(sq.distance)
    .innerJoin(propertiesTable, eq(sq.id, propertiesTable.placeId))
    .innerJoin(
      postingsTable,
      eq(propertiesTable.publicFacingId, postingsTable.propertyPublicId),
    );

 const sq = db
    .select({
      ...getTableColumns(placeDetailsTable),
      distance: sql<number>`.....`.as("distance"),
    })
    .from(placeDetailsTable)
    .as("subquery");

  const query = await db
    .select()
    .from(sq)
    .where(
      and(lt(sq.distance, maxDistanceInKm), eq(postingsTable.isActive, true)),
    )
    .orderBy(sq.distance)
    .innerJoin(propertiesTable, eq(sq.id, propertiesTable.placeId))
    .innerJoin(
      postingsTable,
      eq(propertiesTable.publicFacingId, postingsTable.propertyPublicId),
    );

I would also like to join a propertyImages table but I only want to bring the first 5 of them. I know that this is beautifully easy using relational queries because I can do

{ with: { images: { limit: 5 } } }

{ with: { images: { limit: 5 } } }

However, I am not sure if this filtering based on a calculated field is possible. extras is there but then I can't include that in the where clause of the relational query. Maybe I should not join the images table and do a subquery there?

2 replies

DTDrizzle Team

•Created by titongo on 6/17/2024 in #help

How should I do custom migrations?

I am trying to do migrations in sqlite and sometimes drizzle will leave a comment in the migration file saying it cannot add constraints to existing columns, I understand it is necessary but it'd be appreciated if there was better documentation on how to achieve these things without leaving drizzle too much. I feel like the whole migration process from drizzle-kit is not that clear and its quite hard to understand what are its limitations. For instance, I had a 0005_etc migration file written by me (I guess this is necessary to keep it version-controllable) and it used BEGIN TRANSACTION; Now, trying to run my 0006_etc file I am getting SQL_MANY_STATEMENTS and if I add statement-breakpoints I get SQLite error: cannot start a transaction within a transaction I am unsure about whether it has to do with this file or the previous one. Some of the questions that I am now getting: - Does running drizzle-kit migrate run all files? how does it decide which to run? - Why can't it point me to the file/statement that is erroring? - how are --> statement-breakpoints used? - Can I turn on/off foreign keys during migrations?

1 replies

DTDrizzle Team

•Created by titongo on 6/15/2024 in #help

nesting db calls in transactions without using the callback argument

What is the point of the trx argument when calling db.transaction()? when using raw sql, a transaction looks something like

BEGIN TRANSACTION
INSERT into whatever values something returning id;
INSERT into otherWhatever values somertinhElse;
COMMIT;

BEGIN TRANSACTION
INSERT into whatever values something returning id;
INSERT into otherWhatever values somertinhElse;
COMMIT;

so the queries don't really have to "know" that they are inside a transaction, so my question is, is this possible?

  const insertedPropId = await db.transaction(async (trx) => {
    // insertPlaceDetails calls the db DIRECTLY, instead of something like
    // trx.insert()
    const insertedPlaceData = await insertPlaceDetails([
      location.latitude,
      location.longitude,
    ]);
    const [insertedProperty] = await db
      .insert(propertiesTable)
      .values(propertyData)
      .returning({ id: propertiesTable.id });
    return insertedProperty.id;
  });
  return insertedPropId;
};

  const insertedPropId = await db.transaction(async (trx) => {
    // insertPlaceDetails calls the db DIRECTLY, instead of something like
    // trx.insert()
    const insertedPlaceData = await insertPlaceDetails([
      location.latitude,
      location.longitude,
    ]);
    const [insertedProperty] = await db
      .insert(propertiesTable)
      .values(propertyData)
      .returning({ id: propertiesTable.id });
    return insertedProperty.id;
  });
  return insertedPropId;
};

if this is not possible: is there any way of nesting inserts without passing the trx argument around? I'd rather keep those things outside functions like insertPlaceDetails, which would now need to "know" about its context. Thanks!

6 replies

DTDrizzle Team

•Created by titongo on 5/23/2024 in #help

How to use libsql features in migrations

Hey there, first time using sqlite here, AFAIK, you can't modify a sqlite column to, for instance, change its ON DELETE action from nothing to cascade. However, libsql mentions this in its docs https://github.com/tursodatabase/libsql/blob/main/libsql-sqlite3/doc/libsql_extensions.md#altering-columns which makes me think there is actually a way to do this without using a mirror table. I understand that running 'drizzle-kit generate' with schema changes like this one will output a warning saying you should write the migration yourself. What I don't understand is where should I do this. Should I edit my 0001_etc.sql file and write my code there? If i do that, and write something like libsql> ALTER TABLE emails ALTER COLUMN user_id TO user_id INT then I get an error because libsql> is not recognized, but then what is the point of writing driver: "turso" in my config file? isn't the turso driver what tells drizzle it can use libSQL features?

1 replies

DTDrizzle Team

•Created by titongo on 12/14/2023 in #help

Extremely high usage due to weird ordering used by drizzle

After facing a ton of read rows in planetscale, I contacted the support team, the issue seemed to be in a weird piece of the query that drizzle was performing Basically, this query:

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: desc(postsTable.createdAt),
        limit: 16,
      },
    },
  });

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: desc(postsTable.createdAt),
        limit: 16,
      },
    },
  });

translates to the following sql query:

11 replies

DTDrizzle Team

•Created by titongo on 12/8/2023 in #help

+21.000 row reads in query, how can I improve performance?

My planetscale free tier has been absolutely demolished, and the most expensive query has a rows read / returned ratio of 14,196. according to their docs, this ratio is: The result of dividing total rows read by rows returned in a query. A high number can indicate that your database is reading unnecessary rows, and they query may be improved by adding an index. the query that is causing this is the following:

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },
    },
  });

db.query.debatesTable.findFirst({
    where: (debate, { eq }) => eq(debate.name, debateName),
    with: {
      league: true,
      team: true,
      posts: {
        with: {
          author: {
            columns: {
              name: true,
            },
            with: { team: { columns: { logoUrl: true } } },
          },
        },
        orderBy: (a, { desc }) => desc(a.createdAt),
        limit: 16,
      },
    },
  });

103 replies

DTDrizzle Team

•Created by titongo on 12/5/2023 in #help

Help with deciding when to use indexes.

I have the following schema:

// missing imports from drizzle to avoid discord character limit
import { teamsTable } from "./clubSchemas";
import crypto from "node:crypto";

const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const usersTable = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  name: text("name").notNull(),
  teamId: text("team_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  lastEdited: timestamp("last_edited").defaultNow().notNull(),
});

export const usersRelations = relations(usersTable, ({ many, one }) => ({
  posts: many(postsTable),
  team: one(teamsTable, {
    fields: [usersTable.teamId],
    references: [teamsTable.id],
  }),
}));

export const postsTable = mysqlTable("posts", {
  id: v4ID("id").primaryKey(),
  authorId: text("author_id").notNull(),
  content: text("content").notNull(),
  debateId: text("debate_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  likeCount: mediumint("like_count").default(0).notNull(),
  dislikeCount: mediumint("dislike_count").default(0).notNull(),
});

export const postsRelations = relations(postsTable, ({ one, many }) => ({
  author: one(usersTable, {
    fields: [postsTable.authorId],
    references: [usersTable.id],
  }),
  debate: one(debatesTable, {
    fields: [postsTable.debateId],
    references: [debatesTable.id],
  }),
}));

export const debatesTable = mysqlTable("debates", {
  id: v4ID("id").notNull(),
  name: varchar("name", { length: 255 }).primaryKey(),
  teamId: text("team_id"),
  debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
});

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));

// missing imports from drizzle to avoid discord character limit
import { teamsTable } from "./clubSchemas";
import crypto from "node:crypto";

const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const usersTable = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  name: text("name").notNull(),
  teamId: text("team_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  lastEdited: timestamp("last_edited").defaultNow().notNull(),
});

export const usersRelations = relations(usersTable, ({ many, one }) => ({
  posts: many(postsTable),
  team: one(teamsTable, {
    fields: [usersTable.teamId],
    references: [teamsTable.id],
  }),
}));

export const postsTable = mysqlTable("posts", {
  id: v4ID("id").primaryKey(),
  authorId: text("author_id").notNull(),
  content: text("content").notNull(),
  debateId: text("debate_id").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  likeCount: mediumint("like_count").default(0).notNull(),
  dislikeCount: mediumint("dislike_count").default(0).notNull(),
});

export const postsRelations = relations(postsTable, ({ one, many }) => ({
  author: one(usersTable, {
    fields: [postsTable.authorId],
    references: [usersTable.id],
  }),
  debate: one(debatesTable, {
    fields: [postsTable.debateId],
    references: [debatesTable.id],
  }),
}));

export const debatesTable = mysqlTable("debates", {
  id: v4ID("id").notNull(),
  name: varchar("name", { length: 255 }).primaryKey(),
  teamId: text("team_id"),
  debateType: mysqlEnum("debate_type", ["league", "team"]).notNull(),
});

export const debatesRelations = relations(debatesTable, ({ one, many }) => ({
  posts: many(
  team: one(teamsTable, {
    fields: [debatesTable.teamId],
    references: [teamsTable.id],
  }),
}));

34 replies

DTDrizzle Team

•Created by titongo on 11/9/2023 in #help

need help abstracting a function

This may be an easy answer but how can I implement the following type?

export const getUserWithEmail = (email: string, withConfig: any) => {
  return db.query.usersTable.findFirst({
    where: (dbUser, { eq }) => eq(dbUser.email, email), 
    with: withConfig
  });
};

export const getUserWithEmail = (email: string, withConfig: any) => {
  return db.query.usersTable.findFirst({
    where: (dbUser, { eq }) => eq(dbUser.email, email), 
    with: withConfig
  });
};

is this abstractions stupid? I would like withConfig to be correctly typed. Does it make sense or should I just repeat the function and be free?

12 replies

DTDrizzle Team

•Created by titongo on 10/1/2023 in #help

rpc error: code = InvalidArgument desc = Invalid default value

When I do drizzle-kit push:mysql I get the following error:

Error: target: (dbname).-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: 12s8gn7khn0atcm380g0): Sql: "alter table posts modify column id varchar(36) not null", BindVars: {REDACTED}
    at PromiseConnection.query (C:\Users\...)
    at Command.<anonymous> (C:\Users\...)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)

Error: target: (dbname).-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: 12s8gn7khn0atcm380g0): Sql: "alter table posts modify column id varchar(36) not null", BindVars: {REDACTED}
    at PromiseConnection.query (C:\Users\...)
    at Command.<anonymous> (C:\Users\...)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)

{
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `posts` MODIFY COLUMN `id` varchar(36) NOT NULL;',
  sqlState: '42000',
  sqlMessage: `target: (dbname).-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: 12s8gn7khn0atcm380g0): Sql: "alter table posts modify column id varchar(36) not null", BindVars: {REDACTED}`
}

{
  code: 'ER_INVALID_DEFAULT',
  errno: 1067,
  sql: 'ALTER TABLE `posts` MODIFY COLUMN `id` varchar(36) NOT NULL;',
  sqlState: '42000',
  sqlMessage: `target: (dbname).-.primary: vttablet: rpc error: code = InvalidArgument desc = Invalid default value for 'created_at' (errno 1067) (sqlstate 42000) (CallerID: 12s8gn7khn0atcm380g0): Sql: "alter table posts modify column id varchar(36) not null", BindVars: {REDACTED}`
}

relevant code:

//app/services/schemas/schema.ts
const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const users = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  createdAt: timestamp("created_at", { fsp: 3, mode: "string" }).default(
    sql`current_timestamp(3)`
  ),
//drizzle.config.ts
export default {
  schema: "./app/services/schemas/*",
  out: "./drizzle",
  driver: "mysql2",
  dbCredentials: {
  // the dbURL is from planetscale
    connectionString: config.dbURL,
  },
}

//app/services/schemas/schema.ts
const v4ID = (name: string) =>
  varchar(name, { length: 36 }).$defaultFn(() => crypto.randomUUID());

export const users = mysqlTable("users", {
  id: v4ID("id").primaryKey(),
  createdAt: timestamp("created_at", { fsp: 3, mode: "string" }).default(
    sql`current_timestamp(3)`
  ),
//drizzle.config.ts
export default {
  schema: "./app/services/schemas/*",
  out: "./drizzle",
  driver: "mysql2",
  dbCredentials: {
  // the dbURL is from planetscale
    connectionString: config.dbURL,
  },
}

I don't understand the problem at all, the error seems to indicated it has to do with the id but the error message talks about created_at.

9 replies

DTDrizzle Team

•Created by titongo on 9/30/2023 in #help

about not null

I am fairly new to db design so I don't really understand: should I stick .notNull() everywhere if that's mostly my case? I just find it weird that the default for every value is type | null

3 replies

Gaming

Programming