Paul Posts - Answer Overflow

Paul

Explore posts from servers

CDCloudflare Developers TTCTheo's Typesafe Cult HHono DTDrizzle Team

•Created by Paul on 6/16/2024 in #help

How can I append a subquery as an array in the main query select?

How can I append a subquery as an array in the main query select? I'm currently using the alternative which is to use relational queries, but when using the db.select() syntax, is it possible to append subqueries or any leftJoins for that matter as an array in the final select?

const subquery = db
  .select({
    id: myTable1.id,
  })
  .from(myTable1)
  .as("subquery");

const query = db
  .select({
    id: myTable2.id,
    table1Ids: sql`array(${subquery})`.as("table1Ids")
    // Error. Syntax error at or near "subquery" 
  })
  .from(myTable2)
  .leftJoin(
    subquery,
    eq(subquery.id, myTable2.table1Id)
  );

const subquery = db
  .select({
    id: myTable1.id,
  })
  .from(myTable1)
  .as("subquery");

const query = db
  .select({
    id: myTable2.id,
    table1Ids: sql`array(${subquery})`.as("table1Ids")
    // Error. Syntax error at or near "subquery" 
  })
  .from(myTable2)
  .leftJoin(
    subquery,
    eq(subquery.id, myTable2.table1Id)
  );

1 replies

DTDrizzle Team

•Created by Paul on 6/3/2024 in #help

How to set a GIN index on the JSONB column?

How can I set a GIN index on the whole jsonb column and also on a single column?

import { sql } from "drizzle-orm";
import { index, jsonb, pgTable } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  metadata: jsonb("metadata").$type<Record<string, any>>(),
}, t => ({
  ginIndex: index("gin_idx").on(t.metadata).using(sql`gin`),
  ginIndex2: index("gin_idx_2").on(t.metadata).using(sql`GIN ((metadata->'name'))`),
}));

import { sql } from "drizzle-orm";
import { index, jsonb, pgTable } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  metadata: jsonb("metadata").$type<Record<string, any>>(),
}, t => ({
  ginIndex: index("gin_idx").on(t.metadata).using(sql`gin`),
  ginIndex2: index("gin_idx_2").on(t.metadata).using(sql`GIN ((metadata->'name'))`),
}));

Is currently giving me this only:

CREATE TABLE IF NOT EXISTS "users" (
    "metadata" jsonb
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_gin_idx" ON "users" ("metadata");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_name_gin_idx" ON "users" ("metadata");

CREATE TABLE IF NOT EXISTS "users" (
    "metadata" jsonb
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_gin_idx" ON "users" ("metadata");--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "users_metadata_name_gin_idx" ON "users" ("metadata");

3 replies

DTDrizzle Team

•Created by Paul on 6/3/2024 in #help

When using a foreignKey({}), is there a way to specify onDelete: "cascade'?

I'm looking to custom name my foreign keys so I need to use the foreignKey() function. However, how can I specify an action: {onDelete: "cacade"} using this function?

  userIdReference: foreignKey(
    {
      columns: [table.userId],
      foreignColumns: [users.userId],
      name: "accounts_user_id_fk",
    },
  ),

  userIdReference: foreignKey(
    {
      columns: [table.userId],
      foreignColumns: [users.userId],
      name: "accounts_user_id_fk",
    },
  ),

To my understanding, if I use the .references() method, it doesn't allow for naming foreign keys

userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' })

userId: integer('user_id').references(() => users.id, { onDelete: 'cascade' })

4 replies

DTDrizzle Team

•Created by Paul on 5/25/2024 in #help

General question: Should I use external ids or internal ids when inserting data from a third party?

I'm storing Facebook ads in my database and I'm struggling to figure out whether to use external ids or internal ids. Relevant Stack: Using postgres, drizzle-orm, supabase Background: A facebook ad breaks down into several components: Facebook page, adCreative, ad, locations, 1) On a high level, I could ingest the facebook ad, break it down into it's component parts, add my own internal ids to each and reference each component with the internal ids; OR 2) I could rely mainly on the external ids as indexes (maybe primary key, maybe not), and link them based on these external ids Internal IDs: - Pros: My own data is protected from changes from Facebook - Cons: This makes inserting data in bulk way more sophisticated. Any ad will have the external ids, but I'll need to query for all the fields based on the external id in my database, check if they exist, if not, appending the corresponding internal id to the referencing table for each component. For example, if an ad references a page based on an internal id, I'll need to query for if that external pageId exists, if so, use that page's internalId and add it to the ad so that it is linked properly. External IDs: - Pros: Makes inserting into my database so much easier - Cons: Subject to changes from Facebook (realistically, it will be fine) as my ids are dependent on a third party's ids. I started using internal Ids because chatgpt said internal is better for more control, but the insertion process is really sophisticated and difficult to reason about. Can someone give me some insight, opinion, or recommendation on what they would do?

1 replies

DTDrizzle Team

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

Does Drizzle-Orm Expose Wrapper Types?

So I want to write a wrapper around drizzle-orm where I can pass in things like a where filter or order filter so in case I ever change to a different ORM I can do so. Does drizzle-orm expose the types for each of these methods? Or is there some example of folks writing a general wrapper around it? For example, type-orm exposes a lot of input option types like below:

import {
  FindManyOptions,
  FindOneOptions,
  FindOperator,
  FindOptionsSelect,
  FindOptionsWhere,
  OrderByCondition
} from "typeorm";

import { FindOptionsOrder } from "typeorm/find-options/FindOptionsOrder";
import { FindOptionsRelations } from "typeorm/find-options/FindOptionsRelations";

import {
  FindManyOptions,
  FindOneOptions,
  FindOperator,
  FindOptionsSelect,
  FindOptionsWhere,
  OrderByCondition
} from "typeorm";

import { FindOptionsOrder } from "typeorm/find-options/FindOptionsOrder";
import { FindOptionsRelations } from "typeorm/find-options/FindOptionsRelations";

5 replies

DTDrizzle Team

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

How to implement Row Level Security in Postgres?

In my chat app, I have chats and messages. There are multiple messages in each chat. I'm trying to prevent user1 from editing user2's messages. ChatGPT suggests row-level security like the following

-- Enable row-level security on the messages table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows users to access only their own messages
CREATE POLICY messages_user_policy
  FOR ALL
  USING (current_setting('app.user_id')::text = chat_id);

-- Set the current user ID when connecting to the database
SET app.user_id = 'user1';

-- Now, when querying the messages table, only messages belonging to 'user1' will be returned
SELECT * FROM messages;

-- Enable row-level security on the messages table
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows users to access only their own messages
CREATE POLICY messages_user_policy
  FOR ALL
  USING (current_setting('app.user_id')::text = chat_id);

-- Set the current user ID when connecting to the database
SET app.user_id = 'user1';

-- Now, when querying the messages table, only messages belonging to 'user1' will be returned
SELECT * FROM messages;

Is there any guidance on where/how I can implement this via drizzle-orm? I'm okay to use the "magical" sql function. But.. 1) When defining the policy, is there a place to define it in my pgSchema() call? 2) When querying, can I slot in the SET app.user_id = 'user1'; before querying in my typical query somehow? Oh I guess await db.execute(sqlSET app.user_id = 'user1';) works....? How about question 1 then? I guess also await db.execute(sql``)?

4 replies

DTDrizzle Team

•Created by Paul on 10/19/2023 in #help

How to create unique lowercase index?

I'm trying to recreate the following sql statement. I don't think what I'm writing with the where clause is correct. But writing .on(sqllower(domain)) results in a type error. Is there a way to do it in drizzle currently?

create unique index sso_domains_domain_idx on auth.sso_domains using btree (lower(domain)) tablespace pg_default;

create unique index sso_domains_domain_idx on auth.sso_domains using btree (lower(domain)) tablespace pg_default;

export const ssoDomains = authSchema.table(
  "sso_domains",
    {
      domain: text("domain").notNull(),
    },
    table => ({
      ssoDomainsDomainIdx: uniqueIndex("sso_domains_domain_idx").on(table.domain).where(sql`lower(${table.domain.name})`),
    })
);

export const ssoDomains = authSchema.table(
  "sso_domains",
    {
      domain: text("domain").notNull(),
    },
    table => ({
      ssoDomainsDomainIdx: uniqueIndex("sso_domains_domain_idx").on(table.domain).where(sql`lower(${table.domain.name})`),
    })
);

4 replies

DTDrizzle Team

•Created by Paul on 10/19/2023 in #help

Discussion: cuid2 vs ulid?

Is it better to have a sortable primayKey like ulid but leak the creation date? Or a non-sortable and slower one for enhanced security? Which is best for production apps? The section here in the docs references cuid2, but not ulid interestingly. https://orm.drizzle.team/docs/column-types/pg#constraints--defaults

8 replies

DTDrizzle Team

•Created by Paul on 9/24/2023 in #help

Is it possible to run javascript between database transactions?

In my authentication flow, I need to do the following things on signup 1. Check if user exists 2. Check if email verification token exists 3. If not, create new verification token So.... instead of hitting the database 3 times, I thought it'd be a good idea to hit it once via a transaction. My understanding is that even if just querying twice and updating once, it's still better to do it all in one transaction. But wait.... Inbetween the db calls, I throw errors if the user or token already exists. When I think about it, if the queries are being sent to the database as one transaction and the erros thrown are written in javascript and inbetween those transactions, doesn't that mean the database won't run the code inbetween the transactions? Can someone confirm that this will indeed need to be 3 transactions in order for me to throw properly. I can't do all this in one transaction because the errors won't throw right? Because the database won't receive the javascript. For example (pseudo-code below):

await db.transaction(async tx => {
  const hasUser = await tx.query.users.findFirst(...); 
  if (hasUser) {
    throw new Error("User already exists"); 
  }
  // ^ This doesn't work right?

  const hasToken = await tx.query.token.findFirst(...);
  if (hasToken) {
    throw new Error("Token already exists");
  } 
  // ^ Neither does this?

  const token = await tx.insert(token).values(...);
});

await db.transaction(async tx => {
  const hasUser = await tx.query.users.findFirst(...); 
  if (hasUser) {
    throw new Error("User already exists"); 
  }
  // ^ This doesn't work right?

  const hasToken = await tx.query.token.findFirst(...);
  if (hasToken) {
    throw new Error("Token already exists");
  } 
  // ^ Neither does this?

  const token = await tx.insert(token).values(...);
});

9 replies

DTDrizzle Team

•Created by Paul on 9/22/2023 in #help

TypeError: Seems like the schema generic is missing - did you forget to add it to your DB type?

How can I solve the following generic error relating to the db.query.users in my code below?

Property 'users' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?"> | { [x: string]:RelationalQueryBuilder<ExtractTablesWithRelations<any>, { ...; }>; }'. 

Property 'users' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'.ts(2339)

Property 'users' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?"> | { [x: string]:RelationalQueryBuilder<ExtractTablesWithRelations<any>, { ...; }>; }'. 

Property 'users' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'.ts(2339)

I'm trying to create an adapter that accepts a generic schema to instantiate a PostgresJsDatabase instance, or colloquially db. I've created the schema that is needed for this adapter, but how can I satisfy typescript when running db.query.user.findFirst()?

import { PostgresJsDatabase, drizzle } from "drizzle-orm/postgres-js";
import { Sql } from "postgres";
import * as schema from "./schema";
import { eq } from "drizzle-orm";

export function postgresAdapter<T extends typeof schema = any>(
  sql: Sql,
  schema: T
) {
  const db = drizzle(sql, { schema });

  const { users } = schema;

  // This function works below
  async function insertUser(
    user: typeof users.$inferInsert
  ): Promise<void> {
    await db
      .insert(users)
      .values(user)
      .onConflictDoNothing({ target: users.id });
  }

  // I'm getting an error here at db.query
  async function getUserById(
    id: string
  ): Promise<typeof schema.users.$inferSelect | null> {
    const user = await db.query.users.findFirst({
                               ^ // Property 'users' does
                                 // not exist on type
      where: (users: any, { eq }) => eq(users.id, id),
     });
    return user || null;
  }
}

import { PostgresJsDatabase, drizzle } from "drizzle-orm/postgres-js";
import { Sql } from "postgres";
import * as schema from "./schema";
import { eq } from "drizzle-orm";

export function postgresAdapter<T extends typeof schema = any>(
  sql: Sql,
  schema: T
) {
  const db = drizzle(sql, { schema });

  const { users } = schema;

  // This function works below
  async function insertUser(
    user: typeof users.$inferInsert
  ): Promise<void> {
    await db
      .insert(users)
      .values(user)
      .onConflictDoNothing({ target: users.id });
  }

  // I'm getting an error here at db.query
  async function getUserById(
    id: string
  ): Promise<typeof schema.users.$inferSelect | null> {
    const user = await db.query.users.findFirst({
                               ^ // Property 'users' does
                                 // not exist on type
      where: (users: any, { eq }) => eq(users.id, id),
     });
    return user || null;
  }
}

5 replies

DTDrizzle Team

•Created by Paul on 8/3/2023 in #help

When pushing migrations to Supabase, it skips saying the schema "drizzle" already exists.

When I update my schema and try to push the migration to Supabase, I get the following error. How can I move forward with pushing it as opposed to having it skip? I guess part of the issue is I don't understand how migrations really work so not sure what this error is telling me is wrong. In my database, I already have 2 entries in a table called __drizzle_migrations under the drizzle schema that was auto-created by drizzle-kit when migrating the first few times

Migrating...
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

Migrating...
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P06',
  message: 'schema "drizzle" already exists, skipping',
  file: 'schemacmds.c',
  line: '128',
  routine: 'CreateSchemaCommand'
}
{
  severity_local: 'NOTICE',
  severity: 'NOTICE',
  code: '42P07',
  message: 'relation "__drizzle_migrations" already exists, skipping',
  file: 'parse_utilcmd.c',
  line: '209',
  routine: 'transformCreateStmt'
}

My migration file looks like this:

import postgres from "postgres";
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";

import { env } from "@core/env";

const client = postgres(env.POSTGRES_DB_URL, {
    ssl: { rejectUnauthorized: false },
});

async function postgresMigrate() {
    try {
        const db: PostgresJsDatabase = drizzle(client);

        // Migrate
        console.log("Migrating...");
        await migrate(db, { migrationsFolder: "drizzle" });
        console.log("Done!");
    } finally {
        process.exit(0);
    }
}

postgresMigrate();

import postgres from "postgres";
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";

import { env } from "@core/env";

const client = postgres(env.POSTGRES_DB_URL, {
    ssl: { rejectUnauthorized: false },
});

async function postgresMigrate() {
    try {
        const db: PostgresJsDatabase = drizzle(client);

        // Migrate
        console.log("Migrating...");
        await migrate(db, { migrationsFolder: "drizzle" });
        console.log("Done!");
    } finally {
        process.exit(0);
    }
}

postgresMigrate();

6 replies

DTDrizzle Team

•Created by Paul on 4/24/2023 in #help

How to create tables?

So my understanding is that running a "migration" means creating the table with the schema in your db.
My understanding is the code below should do it as per the docs. I've also separately run npx drizzle-kit generate:pg beforehand to generate my schema.

// db.ts
const pool = new Pool({
  connectionString: process.env.POSTGRES_DB_URL,
});

const db = drizzle(pool, { logger: true });

(async () => {
  await migrate(db, { migrationsFolder: "./drizzle" });
})();

// db.ts
const pool = new Pool({
  connectionString: process.env.POSTGRES_DB_URL,
});

const db = drizzle(pool, { logger: true });

(async () => {
  await migrate(db, { migrationsFolder: "./drizzle" });
})();

1) Is there a better way to run migrate via CLI before the app starts? Currently migrate runs whenever a route is hit... I tried running the drizzle-kit introspect:pg --out=migrations/ --connectionString=postgresql://user:pass@host:port/db_name command with my own details but it doesn't create the tables - I assume because introspect doesn't create and only checks your db to see if it conforms? 2) So my understanding is that the default behaviour is to run the migration whenever the first route is hit when your app is running? So when I query my route for the first time and my tables are not created, I get the error, error - unhandledRejection: error: no schema has been selected to create in when it tries to create a table. How can I fix this? Or alternatively, what is the recommended way to create our tables? Should we be pasting the sql into psql or smth?

1 replies

Gaming

Programming