Rorsch Posts - Answer Overflow

Rorsch

•Created by Rorsch on 7/31/2024 in #help

findMany with custom field

Currently, I have a code to fetch order details by aggregating several tables (Order, Customer, and Order Line Items tables). I've read the documentation about .findMany() and this is what I'm able to do

    const data = await db.query.orders.findMany({
      with: {
        customer: {
          columns: {
            fullName: true,
            phoneNumber: true,
          },
        },
        orderLineItems: {
          columns: {
            serviceId: true,
            qty: true,
          },
        },
      },
      columns: {
        id: true,
        status: true,
        totalPrice: true,
        createdAt: true,
      },
      where: eq(id, orders.id),
    });

    const data = await db.query.orders.findMany({
      with: {
        customer: {
          columns: {
            fullName: true,
            phoneNumber: true,
          },
        },
        orderLineItems: {
          columns: {
            serviceId: true,
            qty: true,
          },
        },
      },
      columns: {
        id: true,
        status: true,
        totalPrice: true,
        createdAt: true,
      },
      where: eq(id, orders.id),
    });

The code currently can return something like below

{
  id: 'ord_69N98NLF5Z5ZO16ST38PD',
  status: 'COMPLETED',
  totalPrice: 65000,
  createdAt: 2024-07-31T11:32:00.608Z,
  customer: { fullName: 'abbo', phoneNumber: '+123456780000' },
  orderLineItems: [
    { serviceId: 'svc_QC0OO1Z7ZPEGYP6P84EWI', qty: 1 },
    { serviceId: 'svc_ML00B6kXmt9f07oyYD71q', qty: 2 }
  ]
}

{
  id: 'ord_69N98NLF5Z5ZO16ST38PD',
  status: 'COMPLETED',
  totalPrice: 65000,
  createdAt: 2024-07-31T11:32:00.608Z,
  customer: { fullName: 'abbo', phoneNumber: '+123456780000' },
  orderLineItems: [
    { serviceId: 'svc_QC0OO1Z7ZPEGYP6P84EWI', qty: 1 },
    { serviceId: 'svc_ML00B6kXmt9f07oyYD71q', qty: 2 }
  ]
}

I want to fetch name from Service Table, and replace serviceId with it where serviceId = service.id. How can I achieve that? I've read https://orm.drizzle.team/docs/rqb#include-custom-fields but I still can't get it to work.

5 replies

DTDrizzle Team

•Created by Rorsch on 7/23/2024 in #help

Fetch data based on client's timezone.

Currently, I have a postgres db, where my timestamp data is being saved as a timestamp with timezone type, with +0 timezone. I've been fetching my data with the code below

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

    const statement = sql`
      WITH date_series AS (
        SELECT generate_series(
          NOW() - INTERVAL '${sql.raw((days - 1).toString())} DAYS', 
          NOW(), 
          '1 day'::interval
        )::date AS date
      )
      SELECT 
        ds.date,
        COALESCE(SUM(${orders.totalPrice}), 0) AS revenue
      FROM date_series ds
      LEFT JOIN ${orders} ON date_trunc('day', ${orders.createdAt}) = ds.date
      GROUP BY ds.date
      ORDER BY ds.date ASC;
    `;

The problem I have, is that the system will group the data based on the DB server's timezone (in this case +0). How can i update the code so that the data returned will always follow the client's timezone? For example, If the client is in UTC +9, then it should group the date after offsetting the timestamps by 9 hours.

3 replies

DTDrizzle Team

•Created by Rorsch on 7/15/2024 in #help

How to Normalize Database using Drizzle?

I previously have made my Order Table to only support one service/line item back then (based on the business logic back then). Now, I need to normalize my database, so that it supports multiple services/line items in a single order. I have tried to write the migration file but it's not running.

13 replies

DTDrizzle Team

•Created by Rorsch on 7/3/2024 in #help

Altering existing table with Timestamp column to Timestamp with Timezone in Postgres using Drizzle

I have an existing table with columns such as created_at with data type of timestamp. I want to update the column, so that every single timestamp in my database will be timestamp with timezone. I tried modifying db schema from JS by changing

  createdAt: timestamp("created_at")
    .defaultNow()
    .notNull(),

  createdAt: timestamp("created_at")
    .defaultNow()
    .notNull(),

  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

When trying to push the changes, drizzle-kit warned me that my data will be lost.

...

ALTER TABLE "accounting_transaction" ALTER COLUMN "created_at" SET DATA TYPE timestamp with time zone;
truncate table "accounting_transaction" cascade;

...

 Warning  Found data-loss statements:
· You're about to change created_at column type from timestamp to timestamp with time zone with 42 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
[x] All changes were aborted

...

ALTER TABLE "accounting_transaction" ALTER COLUMN "created_at" SET DATA TYPE timestamp with time zone;
truncate table "accounting_transaction" cascade;

...

 Warning  Found data-loss statements:
· You're about to change created_at column type from timestamp to timestamp with time zone with 42 items

THIS ACTION WILL CAUSE DATA LOSS AND CANNOT BE REVERTED

Do you still want to push changes?
[x] All changes were aborted

What strategy that I can use to achieve this?

1 replies

DTDrizzle Team

•Created by Rorsch on 4/16/2024 in #help

Selecting from 2 subqueries without using magic `sql()`

Is it possible to select 2 subqueries together using drizzle-orm? in my case, i want to return current month revenue and previous month revenue.

   const currentMonthRevenue = db
      .select({ currentMonthRevenue: sum(accountingTransactions.amount) })
      .from(accountingTransactions)
      .innerJoin(
        chartOfAccounts,
        eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
      )
      .where(
        and(
          eq(chartOfAccounts.type, "REVENUE"),
          eq(
            sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
            sql`DATE_TRUNC('month', CURRENT_DATE)`
          )
        )
      );

    const lastMonthRevenue = db
      .select({ currentMonthRevenue: sum(accountingTransactions.amount) })
      .from(accountingTransactions)
      .innerJoin(
        chartOfAccounts,
        eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
      )
      .where(
        and(
          eq(chartOfAccounts.type, "REVENUE"),
          eq(
            sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
            sql`DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')`
          )
        )
      );

   const currentMonthRevenue = db
      .select({ currentMonthRevenue: sum(accountingTransactions.amount) })
      .from(accountingTransactions)
      .innerJoin(
        chartOfAccounts,
        eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
      )
      .where(
        and(
          eq(chartOfAccounts.type, "REVENUE"),
          eq(
            sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
            sql`DATE_TRUNC('month', CURRENT_DATE)`
          )
        )
      );

    const lastMonthRevenue = db
      .select({ currentMonthRevenue: sum(accountingTransactions.amount) })
      .from(accountingTransactions)
      .innerJoin(
        chartOfAccounts,
        eq(accountingTransactions.creditAccountId, chartOfAccounts.id)
      )
      .where(
        and(
          eq(chartOfAccounts.type, "REVENUE"),
          eq(
            sql`DATE_TRUNC('month', ${accountingTransactions}.date)`,
            sql`DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')`
          )
        )
      );

below is a working code

    const data = await db.execute(
      sql`SELECT current_month_revenue, last_month_revenue FROM ${currentMonthRevenue}, ${lastMonthRevenue}`
    );

    const data = await db.execute(
      sql`SELECT current_month_revenue, last_month_revenue FROM ${currentMonthRevenue}, ${lastMonthRevenue}`
    );

I'm just wondering if i can do it without using the magic sql

4 replies

DTDrizzle Team

•Created by Rorsch on 12/17/2023 in #help

net::ERR_NAME_NOT_RESOLVED showing multiple times on the console

Hello, I'm trying to do simple database access using drizzle orm. The data got fetched, but my console has been showing a lot of the error index.js: POST https://undefined/psdb.v1alpha1.Database/Execute net::ERR_NAME_NOT_RESOLVED This is the code that I use to fetch my data.

export default async function getCustomers() {
  try {
    const data = await db.select().from(customers);
    return data;
  } catch (error) {
    console.error("Database Error:", error);
    throw new Error("Failed to get customers data.");
  }
}

export default async function getCustomers() {
  try {
    const data = await db.select().from(customers);
    return data;
  } catch (error) {
    console.error("Database Error:", error);
    throw new Error("Failed to get customers data.");
  }
}

this is the db setup

import * as schema from "@/app/db/schema";
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { connect } from "@planetscale/database";

const connection = connect({
  url: process.env["DATABASE_URL"],
});

export const db = drizzle(connection, { schema });

import * as schema from "@/app/db/schema";
import { drizzle } from "drizzle-orm/planetscale-serverless";
import { connect } from "@planetscale/database";

const connection = connect({
  url: process.env["DATABASE_URL"],
});

export const db = drizzle(connection, { schema });

and this is the drizzle.config.ts

import { defineConfig } from "drizzle-kit";
export default defineConfig({
  schema: "./app/db/schema.ts",
  driver: "mysql2",
  out: "./app/drizzle/",
  dbCredentials: {
    uri: process.env["DATABASE_URL"] || "",
  },
  verbose: true,
  strict: true,
});

import { defineConfig } from "drizzle-kit";
export default defineConfig({
  schema: "./app/db/schema.ts",
  driver: "mysql2",
  out: "./app/drizzle/",
  dbCredentials: {
    uri: process.env["DATABASE_URL"] || "",
  },
  verbose: true,
  strict: true,
});

32 replies

DTDrizzle Team

•Created by Rorsch on 12/16/2023 in #help

Error in SQL syntax when pushing schema

Hello, I'm trying to push my database schema to planetscale using drizzle-kit push:mysql , but I got the error below after confirming the execution. Can someone point out for me what did i do wrong?

Error: target: admin-dashboard.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Error: target: admin-dashboard.-.primary: vttablet: rpc error: code = InvalidArgument desc = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

this is my schema

export const customers = mysqlTable("customer", {
  id: varchar("id", { length: 256 })
    .default(sql`UUID()`)
    .primaryKey(),
  fullName: varchar("full_name", { length: 256 }).notNull(),
  email: varchar("email", { length: 256 }),
  phoneNumber: varchar("phone_number", { length: 256 }).unique().notNull(),
  createdAt: datetime("created_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
  updatedAt: datetime("updated_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
});

export const customers = mysqlTable("customer", {
  id: varchar("id", { length: 256 })
    .default(sql`UUID()`)
    .primaryKey(),
  fullName: varchar("full_name", { length: 256 }).notNull(),
  email: varchar("email", { length: 256 }),
  phoneNumber: varchar("phone_number", { length: 256 }).unique().notNull(),
  createdAt: datetime("created_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
  updatedAt: datetime("updated_at", { fsp: 3 })
    .notNull()
    .default(sql`CURRENT_TIMESTAMP()`),
});

and this is the SQL generated by drizzle-kit push:mysql

CREATE TABLE `customer` (
    `id` varchar(256) NOT NULL DEFAULT UUID(),
    `full_name` varchar(256) NOT NULL,
    `email` varchar(256),
    `phone_number` varchar(256) NOT NULL,
    `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    CONSTRAINT `customer_id` PRIMARY KEY(`id`),
    CONSTRAINT `customer_phone_number_unique` UNIQUE(`phone_number`)
);

CREATE TABLE `customer` (
    `id` varchar(256) NOT NULL DEFAULT UUID(),
    `full_name` varchar(256) NOT NULL,
    `email` varchar(256),
    `phone_number` varchar(256) NOT NULL,
    `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    CONSTRAINT `customer_id` PRIMARY KEY(`id`),
    CONSTRAINT `customer_phone_number_unique` UNIQUE(`phone_number`)
);

Thank you

6 replies

Gaming

Programming