hachoter Posts - Answer Overflow

Topics

hachoter

Explore posts from servers

TtRPC TTCTheo's Typesafe Cult DTDrizzle Team

•Created by hachoter on 9/25/2024 in #help

Is there a way to reference multiple fields in a many to one relation?

I have this schema

export const users = pgTable('users', {
    id:uuid('id').primaryKey().notNull().defaultRandom(),
})

export const privateChats = pgTable('private_chats', {
    id:uuid('id').primaryKey().notNull().defaultRandom(),
    userId:uuid('user_id').references(() => users.id),
    chatPartnerId:uuid('chat_partner_id').references(() => users.id),
})

export const privateChatsRelations = relations(privateChats, ({ one }) => ({
    user: one(users, {
        fields: [privateChats.userId],
        references: [users.id],
    }),
    chatPartner: one(users, {
        fields: [privateChats.chatPartnerId],
        references: [users.id],
    }),
}))

export const userRelations = relations(users, ({many }) => ({
    privateChats: many(privateChats),
}))

export const users = pgTable('users', {
    id:uuid('id').primaryKey().notNull().defaultRandom(),
})

export const privateChats = pgTable('private_chats', {
    id:uuid('id').primaryKey().notNull().defaultRandom(),
    userId:uuid('user_id').references(() => users.id),
    chatPartnerId:uuid('chat_partner_id').references(() => users.id),
})

export const privateChatsRelations = relations(privateChats, ({ one }) => ({
    user: one(users, {
        fields: [privateChats.userId],
        references: [users.id],
    }),
    chatPartner: one(users, {
        fields: [privateChats.chatPartnerId],
        references: [users.id],
    }),
}))

export const userRelations = relations(users, ({many }) => ({
    privateChats: many(privateChats),
}))

I want users.privateChats to be a complete list of chats including ones where privateChats.userId and privateChats.chatPartnerId are the users id, I know for this particular example I should probably create a different data model but in my real example (which is over 100 lines so I won't share it) it's not really possible, is there any way to achieve this?

1 replies

•Created by hachoter on 5/27/2024 in #help

Is it possible to batch update based on id?

I have a table with two columns id and position, I want to update the position based on the id, I am creating a list like this

[{id:1, position:0}, {id:3, position:1}, {id:2, position:2}]

[{id:1, position:0}, {id:3, position:1}, {id:2, position:2}]

And I want to update all records with the corresponding position, the only way I can think of is with an onConflict but for insert I need all the columns and I don't want to pass all that when the only data I need is id and position, is there a simpler way to do this?

1 replies

•Created by hachoter on 5/26/2024 in #help

has anyone managed to get drizzle to work with tauri sql plugin?

i believe they are using sqlx and I am not sure if it's the same as sqlite, I am currently working with this example I got off github

export const getDrizzleClient = (sqlite: SqliteClient) => {
  return drizzle<typeof schema>(
    async (sql, params, method) => {
      let rows: any = [];
      let results = [];

      // If the query is a SELECT, use the select method
      if (isSelectQuery(sql)) {
        rows = await sqlite.select(sql, params).catch((e) => {
          console.error("SQL Error:", e);
          return [];
        });
      } else {
        // Otherwise, use the execute method
        rows = await sqlite.execute(sql, params).catch((e) => {
          console.error("SQL Error:", e);
          return [];
        });
        return { rows: [] };
      }

      rows = rows.map((row: any) => {
        return Object.values(row);
      });

      // If the method is "all", return all rows
      results = method === "all" ? rows : rows[0];

      return { rows: results };
    },
    // Pass the schema to the drizzle instance
    { schema: schema, logger: true }
  );
};

export const getDrizzleClient = (sqlite: SqliteClient) => {
  return drizzle<typeof schema>(
    async (sql, params, method) => {
      let rows: any = [];
      let results = [];

      // If the query is a SELECT, use the select method
      if (isSelectQuery(sql)) {
        rows = await sqlite.select(sql, params).catch((e) => {
          console.error("SQL Error:", e);
          return [];
        });
      } else {
        // Otherwise, use the execute method
        rows = await sqlite.execute(sql, params).catch((e) => {
          console.error("SQL Error:", e);
          return [];
        });
        return { rows: [] };
      }

      rows = rows.map((row: any) => {
        return Object.values(row);
      });

      // If the method is "all", return all rows
      results = method === "all" ? rows : rows[0];

      return { rows: results };
    },
    // Pass the schema to the drizzle instance
    { schema: schema, logger: true }
  );
};

I am getting an error subRows is not defined when using the query API, is there a different adapter that might be better suited for this use case?

2 replies

•Created by hachoter on 5/21/2024 in #help

Has anyone used drizzle with sql.js? how do I create migrations?

I am building a software that will run on the users browser using sql.js, has anyone used it with drizzle? I saw an example project in the drizzle repo but they are importing a sql file instead of migrations, I would rather run migrations using drizzle-kit or whatever is available

1 replies

•Created by hachoter on 1/5/2024 in #help

Cannot reference a table that references back to the first table

I am getting this typescript error

'clubs' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

'clubs' implicitly has type 'any' because it does not have a type annotation and is referenced directly or indirectly in its own initializer.

When I am trying to create a foreign key on one of the columns to a table that also has a foreign key constraint back the the clubs table, is this not allowed or is there a different way to do it?

3 replies

•Created by hachoter on 1/3/2024 in #help

PostgresError: there is no unique constraint matching given keys for referenced table "table_name"

I am getting this error, I looked at the migration file and i cannot see anything that would cause this error, I am not referencing the table table_name anywhere other than creating foreign key constraints to the id of this table which exists so I am not sure how it would cause any errors

3 replies

•Created by hachoter on 10/26/2023 in #help

migrate function hangs without any result

I have this in my migrate.js file

// @ts-check

const { drizzle } = require("drizzle-orm/node-postgres");
const { migrate } = require("drizzle-orm/node-postgres/migrator");
const { Client } = require("pg");
require("dotenv").config();
const path = require("path");

// for query purposes
const config = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || ""),
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  connectionTimeoutMillis: 10000,
};
console.table(config);
const client = new Client(config);

console.log(path.join(process.cwd(), "drizzle"));

async function main() {
  try {
    await migrate(drizzle(client), {
      migrationsFolder: path.join(process.cwd(), "drizzle"),
    });
    console.log("success");
    client.end();
    process.exit(0);
  } catch (error) {
    console.log("error: ", error);
    client.end();
    process.exit(1);
  }
}
await main();

// @ts-check

const { drizzle } = require("drizzle-orm/node-postgres");
const { migrate } = require("drizzle-orm/node-postgres/migrator");
const { Client } = require("pg");
require("dotenv").config();
const path = require("path");

// for query purposes
const config = {
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || ""),
  user: process.env.DB_USER,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  connectionTimeoutMillis: 10000,
};
console.table(config);
const client = new Client(config);

console.log(path.join(process.cwd(), "drizzle"));

async function main() {
  try {
    await migrate(drizzle(client), {
      migrationsFolder: path.join(process.cwd(), "drizzle"),
    });
    console.log("success");
    client.end();
    process.exit(0);
  } catch (error) {
    console.log("error: ", error);
    client.end();
    process.exit(1);
  }
}
await main();

when I run the file with bun everything hangs and nothing get's executed, the log functions log the correct credentials and file path but the execution stops after the migrate call, it hangs longer than the timeout so I don't think it is a database issue

20 replies

•Created by hachoter on 10/15/2023 in #help

is there a way to create virtual columns in postgres?

I could just create a migration but I want the type to be correct, if it allows null it will be wrong and if it doesn't it will require it in inserts

1 replies

•Created by hachoter on 9/27/2023 in #help

Type cannot be inferred when using returning on insert

I have this function and it is causing typescript errors

export async function inviteUsers(data: z.infer<typeof sendInvitesValidator>) {
  const res = await db
    .insert(schema.invites)
    .values(
      data.map((invite) => ({
        email: invite.email,
        roles: invite.roles,
        accepted: false,
      }))
    )
    .returning({ id: schema.invites.id })
    .execute();

  return res;
}

export async function inviteUsers(data: z.infer<typeof sendInvitesValidator>) {
  const res = await db
    .insert(schema.invites)
    .values(
      data.map((invite) => ({
        email: invite.email,
        roles: invite.roles,
        accepted: false,
      }))
    )
    .returning({ id: schema.invites.id })
    .execute();

  return res;
}

res is types correctly but I am getting this error

The inferred type of 'inviteUsers' cannot be named without a reference to '../../node_modules/drizzle-orm/query-builders/select.types'. This is likely not portable. A type annotation is necessary.

The inferred type of 'inviteUsers' cannot be named without a reference to '../../node_modules/drizzle-orm/query-builders/select.types'. This is likely not portable. A type annotation is necessary.

This is a simple project with no qorkspace, everything is installed in the same package

13 replies

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

I need help generating a query for a schema

Hi guys I have the following schema

export const companies = pgTable('company', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    owner:integer('owner' as string).notNull().references(() => users.id),
    manager:integer('manager' as string).notNull().references(() => users.id),
    employees:integer('employees' as string).notNull().references(() => users.id),
})

export const users = pgTable('users', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    role:varchar('role' as string, {enum:['owner', 'manager', 'employee']}).notNull(),
})

export const userEmails = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const userPhoneNumbers = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const profiles = pgTable('profiles', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),

})

export const companies = pgTable('company', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    owner:integer('owner' as string).notNull().references(() => users.id),
    manager:integer('manager' as string).notNull().references(() => users.id),
    employees:integer('employees' as string).notNull().references(() => users.id),
})

export const users = pgTable('users', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),
    role:varchar('role' as string, {enum:['owner', 'manager', 'employee']}).notNull(),
})

export const userEmails = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const userPhoneNumbers = pgTable('user_emails', {
    id:serial('id' as string).primaryKey(),
    userId:integer('userId' as string).notNull().references(() => users.id),
    email:varchar('email' as string).notNull(),
    position:integer('position' as string).notNull(),
    profile:integer('profile' as string).notNull().references(() => profiles.id),
})

export const profiles = pgTable('profiles', {
    id:serial('id' as string).primaryKey(),
    name:varchar('name' as string).notNull(),

})

I want to create a single query that will generate something along the lines of this type

interface _EmployeeType{
    id:number
    role:'owner' | 'manager' | 'employee'
    name:string
    emails:{
        email:string
        profile:{
            name:string
            id:number
        } | null
    }[]
    phoneNumber:{
        phoneNumber:string
        profile:{
            name:string
            id:number
        } | null
    }[]
}
export interface Company{
    id:number
    name:string
    owner:_EmployeeType
    manager:_EmployeeType
    employees:_EmployeeType[]
}

interface _EmployeeType{
    id:number
    role:'owner' | 'manager' | 'employee'
    name:string
    emails:{
        email:string
        profile:{
            name:string
            id:number
        } | null
    }[]
    phoneNumber:{
        phoneNumber:string
        profile:{
            name:string
            id:number
        } | null
    }[]
}
export interface Company{
    id:number
    name:string
    owner:_EmployeeType
    manager:_EmployeeType
    employees:_EmployeeType[]
}

Basically I need to create a left join for manager, owner and employee and attach it to the correct key, I also need the user to include an array of emails and phone number (ideally already sorted by position) and join the profiles on top of that The actual database schema is not for this type of data so the schema actually makes sense, I don't want to use rqb because I want to learn how to do something like this in sql as well

1 replies

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

permission denied on insert

I have a drizzle user with full permissions set but I am getting permission denied errors, I have rls enabled

1 replies

•Created by hachoter on 8/2/2023 in #help

0 tables fetched when introspecting a postgres database

I granted all permissions to a drizzle user like this

GRANT ALL PRIVILEGES ON SCHEMA public TO prisma;

GRANT ALL PRIVILEGES ON SCHEMA public TO prisma;

But when I try to introspect with this connection string

postgresql://drizzle:[password]@db.[db_url].supabase.co:5432/postgres?searchpath=public

postgresql://drizzle:[password]@db.[db_url].supabase.co:5432/postgres?searchpath=public

I get this

[✓] 0  tables fetched
[✓] 0  columns fetched
[✓] 12 enums fetched
[✓] 0  indexes fetched
[✓] 0  foreign keys fetched

[✓] 0  tables fetched
[✓] 0  columns fetched
[✓] 12 enums fetched
[✓] 0  indexes fetched
[✓] 0  foreign keys fetched

It only fetches enums but no tables, I know there are tables and the user should have access to it drizzle-kit v0.19.12

3 replies

•Created by hachoter on 6/27/2023 in #help

What is the type of transaction prop?

When creating a transaction I get this prop

const transaction = client.transaction((tx) => {
  
})

const transaction = client.transaction((tx) => {
  
})

I am trying to define a function like this

async function createUser(client:Client | Transaction = db, data:Data){
client.insert(...)
}

async function createUser(client:Client | Transaction = db, data:Data){
client.insert(...)
}

This way I can reuse the same functions with and without a transaction, I am having trouble typing the Transaction type, the type in vscode is a long complex type and I was wondering if there is some exported type I could use

5 replies

•Created by hachoter on 6/26/2023 in #help

Is typescript slow for anyone else?

Typescript is incredibly slow for anything related to drizzle, I have an m1 macbook pro with decent memory so I don't think it's my machine, is anyone else experiencing this or is it unrelated to drizzle

6 replies

•Created by hachoter on 6/12/2023 in #help

What's the overhead (if any) of using the new relational api?

I love the new relational api but I am wondering about the performance overhead

10 replies

•Created by hachoter on 6/11/2023 in #help

I need some guidance on joins

Hi I am using joins because it makes the most sense in my application, I was gonna try out the relations api but I want to work on my sql skills as well, so I have this schema and I am trying to understand how to get all teh tables returned in a single query here is the schema

export const items = pgTable('items', {
  id: uuid('id').primaryKey()
});

export const productOptions = pgTable('product_options', {
  id: uuid('id').primaryKey(),
  productId: uuid('product_id').references(() => items.id)
});

export const productOptionChoices = pgTable('product_option_choices', {
  id: uuid('id').primaryKey(),
  productOptionId: uuid('product_option_id').references(() => productOptions.id)
});

export const items = pgTable('items', {
  id: uuid('id').primaryKey()
});

export const productOptions = pgTable('product_options', {
  id: uuid('id').primaryKey(),
  productId: uuid('product_id').references(() => items.id)
});

export const productOptionChoices = pgTable('product_option_choices', {
  id: uuid('id').primaryKey(),
  productOptionId: uuid('product_option_id').references(() => productOptions.id)
});

I stripped all the extra columns so there might be a syntax error in there, sorry in advance What I am trying to get is something like this

{
  id:'id',
  productoptions:[
    {
      id:'id',
      productoptionChoices:[
          {id:'id'}
      ]
    }
  ]
}

{
  id:'id',
  productoptions:[
    {
      id:'id',
      productoptionChoices:[
          {id:'id'}
      ]
    }
  ]
}

And of course all the other fields So far I have this query which at least in typescript looks ok (my db is not set up so I can't test at the moment) but I don't know how to get the nested tables

client
    .select()
    .from(schema.items)
    .leftJoin(schema.productOptions, eq(schema.productOptions.productId, schema.items.id))

client
    .select()
    .from(schema.items)
    .leftJoin(schema.productOptions, eq(schema.productOptions.productId, schema.items.id))

I probably need another left join but where do I put that?

7 replies

•Created by hachoter on 5/29/2023 in #help

Maximum call stack exceeded with relation to self

I have the following table

export const category = pgTable('categories', {
    id: serial('id').primaryKey().notNull(),
    parentCategoryId: integer('parent_category_id').references((): AnyPgColumn => category.id)
});

// relation
export const categoryRelations = relations(category, ({ many, one }) => ({
    categories: many(category),
    parentCategory: one(category, {
        fields: [category.parentCategoryId],
        references: [category.id]
    })
}));

export const category = pgTable('categories', {
    id: serial('id').primaryKey().notNull(),
    parentCategoryId: integer('parent_category_id').references((): AnyPgColumn => category.id)
});

// relation
export const categoryRelations = relations(category, ({ many, one }) => ({
    categories: many(category),
    parentCategory: one(category, {
        fields: [category.parentCategoryId],
        references: [category.id]
    })
}));

I am running the following query

db.select().from(category).where(isNull(category.parentCategoryId));

db.select().from(category).where(isNull(category.parentCategoryId));

and I am getting a maximum call stack exceeded error, here is the full log

Maximum call stack size exceeded
RangeError: Maximum call stack size exceeded
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:517:17
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29

Maximum call stack size exceeded
RangeError: Maximum call stack size exceeded
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:517:17
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29
    at Array.map (<anonymous>)
    at SQL.buildQueryFromSourceParams (file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:515:36)
    at file:///Users/john/Desktop/project/node_modules/.pnpm/[email protected][email protected]/node_modules/drizzle-orm/relations-c9e54a8f.mjs:594:29

4 replies

•Created by hachoter on 5/21/2023 in #help

Related object is not typed correctly

I have the following schema

export const menus = pgTable('menus', {
  id: uuid('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

// categories 
export const categories = pgTable('categories', {
  id: uuid('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  menuId: uuid('menu_id').references(() => menus.id),
});

export const menuCategories = relations(menus, ({ many, one }) => {
  return {
    categories: many(categories)
  };
});

export const menus = pgTable('menus', {
  id: uuid('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
});

// categories 
export const categories = pgTable('categories', {
  id: uuid('id').primaryKey(),
  name: varchar('name', { length: 256 }).notNull(),
  menuId: uuid('menu_id').references(() => menus.id),
});

export const menuCategories = relations(menus, ({ many, one }) => {
  return {
    categories: many(categories)
  };
});

And I am querying like this

async function main(){
    const res = await db.query.menus.findMany({
        with:{
            categories:true
        }
    })

    const cat = res[0].categories[0]
}

async function main(){
    const res = await db.query.menus.findMany({
        with:{
            categories:true
        }
    })

    const cat = res[0].categories[0]
}

cat is of type

{
  [x:string]:any
}

{
  [x:string]:any
}

I was expecting it to be typed correctly

9 replies

•Created by hachoter on 5/21/2023 in #help

I am confused on how the new relational queries works

In the docs I see the following

import { pgTable, serial, text, integer, boolean } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
    invitedBy: integer('invited_by'),
});
 
export const usersRelations = relations(users, ({ one, many }) => ({
    invitee: one(users, {
        fields: [users.invitedBy],
        references: [users.id],
    }),
}));

import { pgTable, serial, text, integer, boolean } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name'),
    invitedBy: integer('invited_by'),
});
 
export const usersRelations = relations(users, ({ one, many }) => ({
    invitee: one(users, {
        fields: [users.invitedBy],
        references: [users.id],
    }),
}));

What does the usersRelations do? in the actual query it is not used Is it just to add the tables without specifying unique constraints on the id fields? does it add additional fields to the actual table? and is this required to use the with keyword in a query?

24 replies