mr_pablo Posts - Answer Overflow

mr_pablo

•Created by mr_pablo on 9/19/2024 in #help

Multiple databases

I am planning a new feature at work to make use of multiple databases on the same RDS cluster. Each DB will have a separate schema. Can drizzle handle querying multiple databases? Does anyone have a good example of using Drizzle with the MySql driver configured to query across multiple DBs?

1 replies

DTDrizzle Team

•Created by mr_pablo on 11/29/2023 in #help

Creating zod insert schema from Query with relations

I am using a Query FindFirst, with multiple relations enabled. I can infer the schema from this using Awaited<ReturnType<typeof myQuery['execute']>> but this includes the ID field in the relational objects. How can I create a type that matches the query schema, but minus the IDs in the relations? Bonus question, how can I then use Zod to add this type into a zod schema object?

1 replies

DTDrizzle Team

•Created by mr_pablo on 9/21/2023 in #help

MySQL UUID as PK

I am unable to get a PK setup which has a default UUID value. I have

logID: varchar('logID', { length: 48 }).default(sql`'UUID()'`).primaryKey(),

logID: varchar('logID', { length: 48 }).default(sql`'UUID()'`).primaryKey(),

in my table schema But it creates a migration query that literally adds the literal string 'UUID()' in the field

CREATE TABLE `feedLogs` (
    `logID` varchar(48) NOT NULL DEFAULT 'UUID()',
    `feedType` varchar(10),
    CONSTRAINT `feedLogs_logID` PRIMARY KEY(`logID`)
);

CREATE TABLE `feedLogs` (
    `logID` varchar(48) NOT NULL DEFAULT 'UUID()',
    `feedType` varchar(10),
    CONSTRAINT `feedLogs_logID` PRIMARY KEY(`logID`)
);

but running this in MySQL directly, the table is created with a PK that does default to an actual UUID string value

7 replies

DTDrizzle Team

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

Cannot drop primary key

I needed to change a table from a single field PK to a composite PK, using the following syntax

, (table) => {
  return {
    pk: primaryKey(table.playerID, table.teamID, table.competitionID, table.seasonID),
  };
});

, (table) => {
  return {
    pk: primaryKey(table.playerID, table.teamID, table.competitionID, table.seasonID),
  };
});

I ran the drizzle kit generate, and it spat out

ALTER TABLE `teamStats` DROP PRIMARY KEY;
ALTER TABLE `teamStats` ADD PRIMARY KEY(`competitionID`,`seasonID`,`teamID`);

ALTER TABLE `teamStats` DROP PRIMARY KEY;
ALTER TABLE `teamStats` ADD PRIMARY KEY(`competitionID`,`seasonID`,`teamID`);

but this doesn't work. I tried runnign it manually, with the addition of SET foreign_key_checks = 0; at the start, but no luck. A quick google suggests the FK needs removing, but drizzle kit isn't adding that in. Using drizzle kit 0.19.12

19 replies

DTDrizzle Team

•Created by mr_pablo on 8/24/2023 in #help

Error when inferring type from db.query

When I try to use type foo = ReturnType<typeof myQuery> I get the following error Type 'MySql2PreparedQuery<PreparedQueryConfig & { execute: { [x: string]: any; matchPlayers: { [x: string]: any; playerName: { [x: string]: any; } | { [x: string]: any; }[]; } | { [x: string]: any; playerName: { ...; } | { ...; }[]; }[]; matchTeams: { ...; } | { ...; }[]; homeTeam: { ...; } | { ...; }[]; awayTeam: { ...; ...' does not satisfy the constraint '(...args: any) => any'. Type 'MySql2PreparedQuery<PreparedQueryConfig & { execute: { [x: string]: any; matchPlayers: { [x: string]: any; playerName: { [x: string]: any; } | { [x: string]: any; }[]; } | { [x: string]: any; playerName: { ...; } | { ...; }[]; }[]; matchTeams: { ...; } | { ...; }[]; homeTeam: { ...; } | { ...; }[]; awayTeam: { ...; ...' provides no match for the signature '(...args: any): any' The query is a prepared statement outside of my class, and I have the type just underneath so it can be used globally by functions inside my class.

29 replies

DTDrizzle Team

•Created by mr_pablo on 8/8/2023 in #help

With condition

I am using db.query to fetch some data with a lot of related data. One of the with statements is only applicable if a column value from another joined table is a certain value. Does with support a where that uses another joined tables data? If so, what does the syntax look like?

1 replies

DTDrizzle Team

•Created by mr_pablo on 7/14/2023 in #help

orderBy related table column

Given a relational query such as

const matchResult = await db.query.matches.findMany({
        where: and(
          between(matches.time, from, to),
        ),
        orderBy: [
          asc(matches.time),
        ],
        with: {
          team1: true, // one to one with a "team" entity
          team2: true, // one to one with a "team" entity
        },
      });

const matchResult = await db.query.matches.findMany({
        where: and(
          between(matches.time, from, to),
        ),
        orderBy: [
          asc(matches.time),
        ],
        with: {
          team1: true, // one to one with a "team" entity
          team2: true, // one to one with a "team" entity
        },
      });

how can I additionally order by a column from the team1 relation? (bear in mind that a limit and offset may be used, so I don't want to have to fetch all results then sort them after, as it defeats the point of using mysql for this task)

9 replies

DTDrizzle Team

•Created by mr_pablo on 7/7/2023 in #help

transaction not running if previous transactions fail

I have ultiple transaction in my code, inside a single try/catch. If a transaction fails, any transactions after it are never executed, despite them not being nested/connected in anyway.

7 replies

DTDrizzle Team

•Created by mr_pablo on 5/26/2023 in #help

Timestamp mode differences?

What is the difference between the timestamp "mode" of "string'"and "date"?

14 replies

DTDrizzle Team

•Created by mr_pablo on 5/24/2023 in #help

drop tables

Is there a way to do a migration to drop tables? Other ORMs like Sequelize and Prisma have a concept of up & down migrations, but Drizzle doesn't. Is there a Drizzle way to do a "down" migration?

23 replies

DTDrizzle Team

•Created by mr_pablo on 5/24/2023 in #help

db.query error with relation

I have created a schema.ts, with two tables, with a one-to-one relationship. I have also create the "relation". In my index file, I get an error with the following code

const singleMatch = await db.query.matches.findMany({
        where: eq(matches.matchID, 'g123245'),
        with: {
          matchDetails: true,
        },
      });

const singleMatch = await db.query.matches.findMany({
        where: eq(matches.matchID, 'g123245'),
        with: {
          matchDetails: true,
        },
      });

saying "Property 'matches' does not exist on type '{}'"

78 replies

Gaming

Programming