rubberduckies Posts - Answer Overflow

rubberduckies

Explore posts from servers

KPCKevin Powell - Community HHono DTDrizzle Team

•Created by rubberduckies on 8/24/2024 in #help

Help with nested query

Hey everyone, I have the following query and it works as expected.

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

const companies = await db.query.Companies.findMany({
  where: and(eq(Companies.parentCompanyId, 0), notInArray(Companies.id, existingCompanyIds)),
  with: {
    users: {
      where: eq(Users.role, 'Owner'),
      limit: 1,
      orderBy: asc(Users.createdAt),
    },
  },
});

I would like to improve it in order to only return the companies that have one or more users with the role 'Owner' so, if the amount of users returned is zero, not return that company. Is it possible? How? Thank you

1 replies

DTDrizzle Team

•Created by rubberduckies on 8/20/2024 in #help

trouble creating Enum

Hi everyone, here's my code:

import { paymentPlansNames } from 'misc/payment-plans';
import { pgTable, pgEnum, json, varchar } from 'drizzle-orm/pg-core';
import { bigint, bigserial } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

console.log(paymentPlansNames); // [ "Subscription Starter", "Subscription Pro" ]

const subscriptionPlanEnum = pgEnum('subscription_name', paymentPlansNames);
const frequencyEnum = pgEnum('frequency', ['monthly', 'yearly']);

export const Subscriptions = pgTable('subscriptions', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  companyId: bigint('company_id', { mode: 'number' }),
  customerId: bigint('customer_id', { mode: 'number' }),
  subscriptionId: varchar('subscription_id', { length: 256 }),
  subscriptionName: subscriptionPlanEnum('subscription_name'),
  frequency: frequencyEnum('frequency'),
  // trialStart: bigint('trial_start', { mode: 'number' }),
  // trialEnd: bigint('trial_end', { mode: 'number' }),
  currentPeriodStart: bigint('current_period_start', { mode: 'number' }),
  currentPeriodEnd: bigint('current_period_end', { mode: 'number' }),
  canceledAt: bigint('canceled_at', { mode: 'number' }),
  // canceledAtPeriodEnd: bigint('canceled_at_period_end', { mode: 'number' }),
  // createdAt: bigint('created_at', { mode: 'number' }),
  // updatedAt: bigint('updated_at', { mode: 'number' }),
});

import { paymentPlansNames } from 'misc/payment-plans';
import { pgTable, pgEnum, json, varchar } from 'drizzle-orm/pg-core';
import { bigint, bigserial } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

console.log(paymentPlansNames); // [ "Subscription Starter", "Subscription Pro" ]

const subscriptionPlanEnum = pgEnum('subscription_name', paymentPlansNames);
const frequencyEnum = pgEnum('frequency', ['monthly', 'yearly']);

export const Subscriptions = pgTable('subscriptions', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  companyId: bigint('company_id', { mode: 'number' }),
  customerId: bigint('customer_id', { mode: 'number' }),
  subscriptionId: varchar('subscription_id', { length: 256 }),
  subscriptionName: subscriptionPlanEnum('subscription_name'),
  frequency: frequencyEnum('frequency'),
  // trialStart: bigint('trial_start', { mode: 'number' }),
  // trialEnd: bigint('trial_end', { mode: 'number' }),
  currentPeriodStart: bigint('current_period_start', { mode: 'number' }),
  currentPeriodEnd: bigint('current_period_end', { mode: 'number' }),
  canceledAt: bigint('canceled_at', { mode: 'number' }),
  // canceledAtPeriodEnd: bigint('canceled_at_period_end', { mode: 'number' }),
  // createdAt: bigint('created_at', { mode: 'number' }),
  // updatedAt: bigint('updated_at', { mode: 'number' }),
});

I'm getting the errors: error: type "frequency" does not exist error: type "subscription_name" does not exist I'm doing exactly the same thing on other tables, not sure what i'm doing wrong here... Thanks for any help

8 replies

HHono

•Created by rubberduckies on 6/29/2024 in #help

middleware redirect from HTTPS to HTTP

I have set up this middleware to run on all my authenticated routes : (simplified version) (notice the commented lines setting the protocol)

export const orgAndWebsiteMiddleware = async (c, next) => {
  const { user, token } = c.var;
  const { org, ws } = c.req.query();

  if (!org) {
    const url = new URL(c.req.url);
    url.searchParams.set('org', companies[0].id);
    url.searchParams.set('ws', companies[0].websites[0]?.id);
    // url.protocol = isProduction ? 'https' : 'http';
    return c.redirect(url.toString());
  } else if (!ws) {
    const url = new URL(c.req.url);
    const company = companies.find((company) => company.id === +c.req.query('org'));
    url.searchParams.set('ws', company?.websites[0]?.id);
    // url.protocol = isProduction ? 'https' : 'http';
    return c.redirect(url.toString());
  } else {
    const company = companies.find((company) => company.id === +c.req.query('org'));
    const website = company.websites.find((website) => website.id === +c.req.query('ws'));
    c.set('company', company);
    c.set('website', website);
  }
  return next();
};

export const orgAndWebsiteMiddleware = async (c, next) => {
  const { user, token } = c.var;
  const { org, ws } = c.req.query();

  if (!org) {
    const url = new URL(c.req.url);
    url.searchParams.set('org', companies[0].id);
    url.searchParams.set('ws', companies[0].websites[0]?.id);
    // url.protocol = isProduction ? 'https' : 'http';
    return c.redirect(url.toString());
  } else if (!ws) {
    const url = new URL(c.req.url);
    const company = companies.find((company) => company.id === +c.req.query('org'));
    url.searchParams.set('ws', company?.websites[0]?.id);
    // url.protocol = isProduction ? 'https' : 'http';
    return c.redirect(url.toString());
  } else {
    const company = companies.find((company) => company.id === +c.req.query('org'));
    const website = company.websites.find((website) => website.id === +c.req.query('ws'));
    c.set('company', company);
    c.set('website', website);
  }
  return next();
};

It all works works well, on page loads. I am using HTMX and, without some extra work, all my ajax requests are also served through this middleware (by design) Everything works well, on my computer, working with HTTP. Once I run it in production (Bun, Hono, proxied through NGINX), my ajax requests stop working, as this middleware redirects to HTTP instead of HTTPS. so, if i uncomment the lines setting url.protocol = 'https, it now works. This feels like undesired behaviour, is it? Thank you

5 replies

HHono

•Created by rubberduckies on 6/26/2024 in #help

Problems with Form POST in Safari and IOS

I have this simple router, relying on a simple HTML form.

export const authSigninRouter = new Hono();

const SigninForm = ({ email = '', errors }) => {
  return (
    <form class="mt-5 grid gap-2 lg:gap-8" method="POST" action="/auth/signin">
      <label class="grid w-full gap-3">
        <span>Email address</span>
        <input type="email" name="email" />
      </label>
      <label class="grid w-full gap-3">
        <span>Password</span>
        <input type="password" name="password" />
      </label>
      <button>Signin</button>
    </form>
  );
};

authSigninRouter.get('/', async (c) => {
  return c.render(<SigninForm />);
});

authSigninRouter.post('/', async (c) => {
  const { email, password } = await c.req.parseBody();
  ///...
  const token = await sign(tokenUser);

  setCookie(c, 'bearer_token', token, { secure: true, httpOnly: true });

  return c.redirect('/');
});

export const authSigninRouter = new Hono();

const SigninForm = ({ email = '', errors }) => {
  return (
    <form class="mt-5 grid gap-2 lg:gap-8" method="POST" action="/auth/signin">
      <label class="grid w-full gap-3">
        <span>Email address</span>
        <input type="email" name="email" />
      </label>
      <label class="grid w-full gap-3">
        <span>Password</span>
        <input type="password" name="password" />
      </label>
      <button>Signin</button>
    </form>
  );
};

authSigninRouter.get('/', async (c) => {
  return c.render(<SigninForm />);
});

authSigninRouter.post('/', async (c) => {
  const { email, password } = await c.req.parseBody();
  ///...
  const token = await sign(tokenUser);

  setCookie(c, 'bearer_token', token, { secure: true, httpOnly: true });

  return c.redirect('/');
});

Using chrome, on my computer, i have no problems whatsoever Using Safari on the computer, or safari or chrome from my iphone, or safari on the iPhone simulator, it doesn't work. I get the message on my server: TypeError: Request aborted, code: "ABORT_ERR" I'm using Hono with Bun, like so:

const app = new Hono();

Bun.serve({
  port: PORT,
  reload: true,
  fetch: app.fetch,
});

app.use('*', serveStatic({ root: 'public/' }));

app.route('/', appRouter);

const app = new Hono();

Bun.serve({
  port: PORT,
  reload: true,
  fetch: app.fetch,
});

app.use('*', serveStatic({ root: 'public/' }));

app.route('/', appRouter);

any insights on why this may be happening? Thanks

57 replies

KPCKevin Powell - Community

•Created by rubberduckies on 6/26/2024 in #front-end

CSS list-style-type not being applied on <details><summary> on safari

8 replies

HHono

•Created by rubberduckies on 6/25/2024 in #help

Hono-Sessions, Cookie-Store, working for multiple domains

I have a client React app that consumes data from multiple backends. in development, those backends are - http://localhost:8001, - http://localhost:8002, - http://localhost:8003, ... and the authentication is handled from http://localhost:8000 In production, authentication is handled from - https://auth.domain.com and i have the servers spread out through different subdomains of the same domain. Right now, i'm using Bearer Authentication, with a token generated in the auth server, decoded by the same middleware imported in every other service. I would love to switch to cookie based session management, but i don't want to change the backend architecture setup. Is this possible with hono and hono-sessions? How? Thank you

5 replies

DTDrizzle Team

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

duplicate key value violates unique constraint when trying to insert

I have this simple table:

export const ExperimentData = pgTable('experiment_data', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  timestamp: bigint('timestamp', { mode: 'number' }),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  event: varchar('event', { mode: 'string', length: 255 }),
  data: json('data'),
});

export const ExperimentData = pgTable('experiment_data', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  timestamp: bigint('timestamp', { mode: 'number' }),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  event: varchar('event', { mode: 'string', length: 255 }),
  data: json('data'),
});

and i have a script that reads data from redis and writes to this table:

await statisticsdb.insert(ExperimentData).values({ timestamp, websiteId, experimentId, variantId, event, data });

await statisticsdb.insert(ExperimentData).values({ timestamp, websiteId, experimentId, variantId, event, data });

this script runs is supposed to run every hour, triggered by a cronjob and now it doesn't run because i get the error

PostgresError: duplicate key value violates unique constraint "experiment_data_pkey"
 code: "23505"

      at ErrorResponse (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:792:23)
      at handle (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:811:5)
      at data (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:562:3)
      at emit (native:1:1)
      at addChunk (native:1:1)
      at readableAddChunk (native:1:1)
      at data (native:1:1)

PostgresError: duplicate key value violates unique constraint "experiment_data_pkey"
 code: "23505"

      at ErrorResponse (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:792:23)
      at handle (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:811:5)
      at data (/home/ubuntu/production/pertento/node_modules/postgres/src/connection.js:562:3)
      at emit (native:1:1)
      at addChunk (native:1:1)
      at readableAddChunk (native:1:1)
      at data (native:1:1)

this error is caused on the first insertion try. - I haven't done anything, haven't changed the schema or the data in the database - as you can see, the only primary key i have in the table is the id field - am not trying to force the id I don't know exactly what to do here, this is kind of urgent as our company's analytics depends on this. Also, don't want to mess up the data in Postgres nor in Redis. Any help would be much appreciated. Thank you

3 replies

HHono

•Created by rubberduckies on 5/31/2024 in #help

where cookies??? where res?

24 replies

DTDrizzle Team

•Created by rubberduckies on 5/30/2024 in #help

weird error in production (TypeError: Right side of assignment cannot be destructured)

1 replies

HHono

•Created by rubberduckies on 5/29/2024 in #help

middleware on a router level

Right now, i have this setup, where canExperiment is also a middleware:

export const statisticsRouter = new Hono();
statisticsRouter.get('/monitor', monitorHandler);
statisticsRouter.get('/:experimentId/statistics', canExperiment, significanceMiddleware, significanceHandler);
statisticsRouter.get('/:experimentId/chart-data', canExperiment, chartDataMiddleware, chartDataHandler);
statisticsRouter.get('/:experimentId/currencies', canExperiment, currenciesHandler);

export const statisticsRouter = new Hono();
statisticsRouter.get('/monitor', monitorHandler);
statisticsRouter.get('/:experimentId/statistics', canExperiment, significanceMiddleware, significanceHandler);
statisticsRouter.get('/:experimentId/chart-data', canExperiment, chartDataMiddleware, chartDataHandler);
statisticsRouter.get('/:experimentId/currencies', canExperiment, currenciesHandler);

in express, i would do something like:

export const statisticsRouter = new Hono();
statisticsRouter.get('/monitor', monitorHandler);
statisticsRouter.use(canExperiment); 
statisticsRouter.get('/:experimentId/statistics', significanceMiddleware, significanceHandler);
statisticsRouter.get('/:experimentId/chart-data', chartDataMiddleware, chartDataHandler);
statisticsRouter.get('/:experimentId/currencies', currenciesHandler);

export const statisticsRouter = new Hono();
statisticsRouter.get('/monitor', monitorHandler);
statisticsRouter.use(canExperiment); 
statisticsRouter.get('/:experimentId/statistics', significanceMiddleware, significanceHandler);
statisticsRouter.get('/:experimentId/chart-data', chartDataMiddleware, chartDataHandler);
statisticsRouter.get('/:experimentId/currencies', currenciesHandler);

but i can't seem to make this work in Hono. to clarify: - i want all the routes that start with :experimentId to run through canExperiment where i need access to the experimentId parameter. - i have tried several ways, including creating a sub-router, but i can't seem to make it work Thank you

101 replies

DTDrizzle Team

•Created by rubberduckies on 5/29/2024 in #help

using array of ids as a where clause, using magic sql operator

I really need some help here:

const query = sql`
        SELECT 
          ${Statistics.variantId}, 
          ${Statistics.from}, 
          SUM(${Statistics.count}) AS conversions
        FROM 
          ${Statistics}
        WHERE 
          ${Statistics.variantId} IN (
            ${variantIds.join(',')}
          ) 
        GROUP BY 
          ${Statistics.variantId}, 
          ${Statistics.from}
        ORDER BY 
          ${Statistics.from} ASC;
      `;

const query = sql`
        SELECT 
          ${Statistics.variantId}, 
          ${Statistics.from}, 
          SUM(${Statistics.count}) AS conversions
        FROM 
          ${Statistics}
        WHERE 
          ${Statistics.variantId} IN (
            ${variantIds.join(',')}
          ) 
        GROUP BY 
          ${Statistics.variantId}, 
          ${Statistics.from}
        ORDER BY 
          ${Statistics.from} ASC;
      `;

I'm getting an error on variantIds.join(',') saying invalid input syntax for type bigint: "1920,1921" these variant ids come from a different database so i can't use a subquery like i was using before. Thank you

11 replies

KPCKevin Powell - Community

•Created by rubberduckies on 5/20/2024 in #front-end

css pseudo element ::before

Hello, you masters of the CSS. I have reviewed the videos from Kevin on pseudo elements to try and see if he mentioned what i'm looking for. Would love your input on this: Right now, the element, when "selected" is getting a "selected" class that adds that background to it. What i would really like, would be something like:

.selected::before {
  position: absolute; // or fixed maybe
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  background: ...;
}

.selected::before {
  position: absolute; // or fixed maybe
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  background: ...;
}

what i'm trying to achive and haven't been successful is: - a ::before pseudo element, - that would stand behind my selected element (can be any element, inline or block) - would take the exact dimensions of the selected element is this possible? how? Thank you

21 replies

DTDrizzle Team

•Created by rubberduckies on 5/13/2024 in #help

magic sql column naming

Ok, here's a doozy: One of the things i like best about drizzle vs prisma is the fact that we can keep the standard naming conventions at the database level and still get the objects with cool camelCase properties. For example:

export const Purchases = pgTable('purchases', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  from: bigint('from', { mode: 'number' }),
  currencyCode: varchar('currency_code', { length: 4 }),
  purchases: integer('purchases'),
  revenue: bigint('revenue', { mode: 'number' }),
});

export const Purchases = pgTable('purchases', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  websiteId: bigint('website_id', { mode: 'number' }),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  from: bigint('from', { mode: 'number' }),
  currencyCode: varchar('currency_code', { length: 4 }),
  purchases: integer('purchases'),
  revenue: bigint('revenue', { mode: 'number' }),
});

Yesterday, i was working on a custom query, so i decided to use the magical sql operator, provided by drizzle ORM.

sql`
  SELECT 
    ${Purchases.variantId}, 
    ${Purchases.currencyCode}, 
    ${Purchases.from}, 
    SUM(${Purchases.revenue}) AS revenue
  FROM 
    ${Purchases}
  WHERE 
    ${Purchases.variantId} IN (
      Select ${Variants.id} From ${Variants} WHERE ${Variants.experimentId} = ${experimentId}
    ) 
    AND ${Purchases.currencyCode} = ${currency}
  GROUP BY 
    ${Purchases.variantId}, 
    ${Purchases.currencyCode},
    ${Purchases.from}
  ORDER BY 
    ${Purchases.from} ASC;
`;

sql`
  SELECT 
    ${Purchases.variantId}, 
    ${Purchases.currencyCode}, 
    ${Purchases.from}, 
    SUM(${Purchases.revenue}) AS revenue
  FROM 
    ${Purchases}
  WHERE 
    ${Purchases.variantId} IN (
      Select ${Variants.id} From ${Variants} WHERE ${Variants.experimentId} = ${experimentId}
    ) 
    AND ${Purchases.currencyCode} = ${currency}
  GROUP BY 
    ${Purchases.variantId}, 
    ${Purchases.currencyCode},
    ${Purchases.from}
  ORDER BY 
    ${Purchases.from} ASC;
`;

The problem here is that the resulting objects i get come with the database column naming: variant_id, currency_code ... Even if i try using AS, the return doesn't keep the capitalize characters in the column name

 SELECT 
    ${Purchases.variantId} as variantId, 
    ${Purchases.currencyCode} as currencyCode,

 SELECT 
    ${Purchases.variantId} as variantId, 
    ${Purchases.currencyCode} as currencyCode,

still gives me back:

{
  variantid,
  currencycode
}

{
  variantid,
  currencycode
}

My questions are: - Is there a way to make this work as expected? - if not, why should i use the drizzle magic sql operator, instead of just a normal call through the postgres driver, which would allow me to just write pure SQL instead of having to use: ${Purchases.variantId} ? TY

2 replies

DTDrizzle Team

•Created by rubberduckies on 5/11/2024 in #help

relations to the same table

Is this not possible to do?

export const CompaniesRelations = relations(Companies, ({ many, one }) => ({
  clients: many(Companies),
  parentCompany: one(Companies, {
    fields: [Companies.parentCompanyId],
    references: [Companies.id],
  }),
  websites: many(Websites),
}));

export const CompaniesRelations = relations(Companies, ({ many, one }) => ({
  clients: many(Companies),
  parentCompany: one(Companies, {
    fields: [Companies.parentCompanyId],
    references: [Companies.id],
  }),
  websites: many(Websites),
}));

I get an error when rinning the studio: Error: There are multiple relations between "__public__.Companies" and "companies". Please specify relation name this error disappears if i remove the self referential relations

9 replies

DTDrizzle Team

•Created by rubberduckies on 10/9/2023 in #help

update incrementing value (postgres)

So, i have a table:

export const VisitorCount = pgTable('visitor-count', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  count: bigint('count', { mode: 'number' }).default(0),
});

export const VisitorCount = pgTable('visitor-count', {
  id: bigserial('id', { mode: 'number' }).primaryKey(),
  experimentId: bigint('experiment_id', { mode: 'number' }),
  variantId: bigint('variant_id', { mode: 'number' }),
  count: bigint('count', { mode: 'number' }).default(0),
});

at the time i want to do the update, i know the required rows are there, initialized at 0 and i'll do batch updates with objects that look like:

{
  '8-25': 4,
  '8-26': 4,
  '8-23': 3,
  '8-22': 3,
  '8-24': 4,
};

{
  '8-25': 4,
  '8-26': 4,
  '8-23': 3,
  '8-22': 3,
  '8-24': 4,
};

where "8" is experimentId, "25" is variantId and i want to update the relevant line, adding 4to the existing value that was there before... Is there a good way to do this?

3 replies

DTDrizzle Team

•Created by rubberduckies on 9/20/2023 in #help

Minimum value for Postgres bigserial

I want my id tables to use bigserial, but i want the first row to be inserted with the value 222222. Is there a way to do this? id: bigserial('id', { mode: 'number', ...}).primaryKey()

1 replies

DTDrizzle Team

•Created by rubberduckies on 8/4/2023 in #help

trying out neon.tech should i use "Pooled Connection"?

the title says it all, i'm going to try moving to Neon database (postgres) but i'm not sure if i should use a pooled connection or not. Thank you

1 replies

DTDrizzle Team

•Created by rubberduckies on 7/27/2023 in #help

Postgres: install plugin during migration

So, I'm using Drizzle with Postgres and am using uuid for the id fields. For that, i need to run the command: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; after creating the database. Is there a way to automate this in the first migration file? Is it safe to just paste it at the begining of the first migration file? Thank you

4 replies

DTDrizzle Team

•Created by rubberduckies on 7/10/2023 in #help

postges, auto-generated uuid as primary key

I'm looking to change all my tables from having a serial int primary key as ID what is the best way for generating the column definition in the drizzle schema file? Thank you

12 replies

DTDrizzle Team

•Created by rubberduckies on 6/9/2023 in #help

how to empty a table (postgres)

What's the best way to programatically empty a database table? I'm using

const emptied = await db.execute(sql`DELETE FROM featured_salons;`)

const emptied = await db.execute(sql`DELETE FROM featured_salons;`)

in the meantime Thank you

7 replies

Gaming

Programming