cssetian Posts - Answer Overflow

cssetian

Explore posts from servers

•Created by cssetian on 3/13/2024 in #help

Timestamp select/parsing precision issue with Postgres

Hi, I have a postgres timestamp column on my database, using drizzle-orm v0.30.1. When I write dates to the column, I see in my database that they are being written with a precision of 6 digits of milisecond precision, e.g. 2024-03-03 08:25:18.380572+00. However, when I select that column into my data, it seems to only ever parse the value with 3 miliseconds of precision, e.g. 2024-03-03T08:25:18.380Z. I don't have any additional parameters specified on the column, it's simply defined as createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow() I see in the docs the addition of the 'precision' parameter to the timestamp, but am confused about why I am getting different behavior in writes vs reads if I'm just using the defaults in the column for both reads and writes (perhaps related to the default value defined in postgres?) I couldn't find any information about default precision in the docs here, and am wondering whether there is some mismatch in default precision values between writing and reading, e.g. if the drizzle default for reading is 3, but the postgres default for writing is 5? Is the only way to force these to be the same to define a precision value on all my columns? https://orm.drizzle.team/docs/column-types/pg#timestamp The reason this becomes troublesome is that I am trying to use a datetime as a cursor value in pagination, but due to the loss of precision I am getting incorrect paging results by truncating the precision of the cursor when I pass it back into my query to fetch the 'next page'. Thank you!

3 replies

DTDrizzle Team

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

Trouble using findMany with joined relation and where clause on relation

Hello, I'm trying to construct a query using the findMany function of drizzle, and perform a simple join to a second table with a foreign key relation and include a 'where' clause that keys on one of the columns of the joined table. My current query is constructed as follows:

  const response = await db.query.tableOne.findMany({
    where: and(gt(tableOne.createdAt, createdAfter), eq(tableOne.createdBy, userId)),
    with: {
      tableTwoEntity: {
        where: and(eq(tableTwo.organizationId, organizationId)),
      }
    },
    orderBy: [desc(tableOne.createdAt)],
    limit,
  })

  const response = await db.query.tableOne.findMany({
    where: and(gt(tableOne.createdAt, createdAfter), eq(tableOne.createdBy, userId)),
    with: {
      tableTwoEntity: {
        where: and(eq(tableTwo.organizationId, organizationId)),
      }
    },
    orderBy: [desc(tableOne.createdAt)],
    limit,
  })

I am querying against tableOne, which I have a relation to tableTwo defined for in my schema definition, with tableTwoEntity being the named entity of the relation. I would like to include a number of filters on tableOne, but additionally filter by tableTwo on one of the columns. For some reason Drizzle gives me a typescript error stating that the Object literal may only specify known properties, and 'where' does not exist in type .... I believe this is the best practice way to do things, but would appreciate any guidance if I'm not following the correct best practice for doing this type of combined where clause in a findMany query. Thank you

8 replies

DTDrizzle Team

•Created by cssetian on 11/12/2023 in #help

How to implement a 'check' constraint

I'd like to implement a 'check' constraint that implements these psql table constraints below. What is the best way to add this constraint in native SQL alongside my drizzle table definitions if it is not yet supported as a native Drizzle piece of functionality? -- User Table CREATE TABLE "User" ( UserID UUID DEFAULT uuid_generate_v4() PRIMARY KEY, UserName VARCHAR(255) NOT NULL, Email VARCHAR(255) NOT NULL -- Other user attributes ); -- Organization Table CREATE TABLE Organization ( OrgID UUID DEFAULT uuid_generate_v4() PRIMARY KEY, OrgName VARCHAR(255) NOT NULL -- Other organization attributes ); -- Application Table CREATE TABLE Application ( AppID UUID DEFAULT uuid_generate_v4() PRIMARY KEY, Name VARCHAR(255) NOT NULL, OwnerType VARCHAR(20) CHECK (OwnerType IN ('User', 'Organization')) NOT NULL, OwnerID UUID NOT NULL, Visibility VARCHAR(20) CHECK (Visibility IN ('Individual', 'Team', 'Organization')) NOT NULL, CONSTRAINT AtLeastOneOwner CHECK (OwnerType = 'User' AND OwnerID IN (SELECT UserID FROM "User") OR OwnerType = 'Organization' AND OwnerID IN (SELECT OrgID FROM Organization)) -- Other application attributes ); -- OwnerReferences Table CREATE TABLE OwnerReferences ( ID UUID DEFAULT uuid_generate_v4() PRIMARY KEY, Type VARCHAR(20) CHECK (Type IN ('User', 'Organization')) NOT NULL );

2 replies

Gaming

Programming