Derock
Derock
Explore posts from servers
RRailwaySSolidJSTtRPCKKyselyTTCTheo's Typesafe CultDTDrizzle Team
PostsComments
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
trying that exact same approach with drizzle-orm doesn't work: 
      // create a generation for the service
      const trxResult = await ctx.db.transaction(async (trx) => {
        // @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
        const db: Database = trx.session.client;

        db.pragma(`defer_foreign_keys = ON`);

        const generationId = uuidv7();
        const serviceId = uuidv7();

        // create initial generation
        await trx.insert(serviceGeneration).values({
          id: generationId,
          serviceId: serviceId,
          source: ServiceSource.Docker,
          dockerImage: "traefik/whoami",
        });

        logger.debug("inserted generation");

        // create the service
        await trx
          .insert(service)
          .values({
            id: serviceId,
            name: input.name,
            projectId: ctx.project.getData().id,
            latestGenerationId: generationId,
            redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
              "hex",
            ),
          })
          .returning({
            id: serviceGeneration.id,
          });

        logger.debug("inserted service");

        return serviceId;
      })
      // create a generation for the service
      const trxResult = await ctx.db.transaction(async (trx) => {
        // @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
        const db: Database = trx.session.client;

        db.pragma(`defer_foreign_keys = ON`);

        const generationId = uuidv7();
        const serviceId = uuidv7();

        // create initial generation
        await trx.insert(serviceGeneration).values({
          id: generationId,
          serviceId: serviceId,
          source: ServiceSource.Docker,
          dockerImage: "traefik/whoami",
        });

        logger.debug("inserted generation");

        // create the service
        await trx
          .insert(service)
          .values({
            id: serviceId,
            name: input.name,
            projectId: ctx.project.getData().id,
            latestGenerationId: generationId,
            redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
              "hex",
            ),
          })
          .returning({
            id: serviceGeneration.id,
          });

        logger.debug("inserted service");

        return serviceId;
      })
15:20:57.412Z debug database: insert into "service_generation" ("id", "service_id", "deployment_id", "source", "environment", "docker_image", "docker_registry_username", "docker_registry_password", "github_username", "github_repository", "github_branch", "git_url", "git_branch", "build_method", "build_path", "command", "entrypoint", "replicas", "max_replicas_per_node", "deploy_mode", "zero_downtime", "max_cpu", "max_memory", "max_pids", "restart", "restart_delay", "restart_max_attempts", "healthcheck_enabled", "healthcheck_command", "healthcheck_interval", "healthcheck_timeout", "healthcheck_retries", "healthcheck_start_period", "logging_max_size", "logging_max_files", "created_at") values (?, ?, null, ?, null, ?, null, null, null, null, null, null, null, ?, ?, null, null, ?, null, ?, ?, ?, ?, ?, ?, ?, null, ?, null, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
15:20:57.421Z error trpc:server: Internal server error on mutation: projects.services.create FOREIGN KEY constraint failed
    SqliteError: FOREIGN KEY constraint failed
        at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)
        ... 3 lines matching cause stack trace ...
        at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
      [stack]: 'SqliteError: FOREIGN KEY constraint failed\n' +
        '    at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)\n' +
15:20:57.412Z debug database: insert into "service_generation" ("id", "service_id", "deployment_id", "source", "environment", "docker_image", "docker_registry_username", "docker_registry_password", "github_username", "github_repository", "github_branch", "git_url", "git_branch", "build_method", "build_path", "command", "entrypoint", "replicas", "max_replicas_per_node", "deploy_mode", "zero_downtime", "max_cpu", "max_memory", "max_pids", "restart", "restart_delay", "restart_max_attempts", "healthcheck_enabled", "healthcheck_command", "healthcheck_interval", "healthcheck_timeout", "healthcheck_retries", "healthcheck_start_period", "logging_max_size", "logging_max_files", "created_at") values (?, ?, null, ?, null, ?, null, null, null, null, null, null, null, ?, ?, null, null, ?, null, ?, ?, ?, ?, ?, ?, ?, null, ?, null, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
15:20:57.421Z error trpc:server: Internal server error on mutation: projects.services.create FOREIGN KEY constraint failed
    SqliteError: FOREIGN KEY constraint failed
        at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)
        ... 3 lines matching cause stack trace ...
        at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
      [stack]: 'SqliteError: FOREIGN KEY constraint failed\n' +
        '    at PreparedQuery.run (/home/derock/Documents/Code/hostforge/node_modules/.pnpm/[email protected]_@[email protected]_@[email protected]_@[email protected]_bett_hndeffta6o6q5vdmfsppooayoi/node_modules/src/better-sqlite3/session.ts:103:20)\n' +
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
was able to get this working in a very hacky way: 
// create a generation for the service
      const trxResult = await ctx.db.transaction(async (trx) => {
        // @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
        const db: Database = trx.session.client;

        db.pragma(`defer_foreign_keys = ON`);

        const generationId = uuidv7();
        const serviceId = uuidv7();

        // create initial generation
        const dialect = new SQLiteSyncDialect();
        const createGenerationQuery = dialect.sqlToQuery(
          trx
            .insert(serviceGeneration)
            .values({
              id: generationId,
              serviceId: serviceId,
              source: ServiceSource.Docker,
              dockerImage: "traefik/whoami",
            })
            .getSQL(),
        );

        const genCreateResult = db
          .prepare(createGenerationQuery.sql)
          .run(...createGenerationQuery.params);

        logger.debug(
          "inserted generation",
          createGenerationQuery.sql,
          genCreateResult,
        );

        // create the service
        const createServiceQuery = dialect.sqlToQuery(
          trx
            .insert(service)
            .values({
              id: serviceId,
              name: input.name,
              projectId: ctx.project.getData().id,
              latestGenerationId: generationId,
              redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
                "hex",
              ),
            })
            .returning({
              id: serviceGeneration.id,
            })
            .getSQL(),
        );

        const createResult = db
          .prepare(createServiceQuery.sql)
          .run(...createServiceQuery.params);

        logger.debug("inserted service", createServiceQuery.sql, createResult);

        return serviceId;
      })
// create a generation for the service
      const trxResult = await ctx.db.transaction(async (trx) => {
        // @ts-expect-error using drizzle-orm doesnt work, keep getting foreign key constraint error after the first insert despite it being deferred
        const db: Database = trx.session.client;

        db.pragma(`defer_foreign_keys = ON`);

        const generationId = uuidv7();
        const serviceId = uuidv7();

        // create initial generation
        const dialect = new SQLiteSyncDialect();
        const createGenerationQuery = dialect.sqlToQuery(
          trx
            .insert(serviceGeneration)
            .values({
              id: generationId,
              serviceId: serviceId,
              source: ServiceSource.Docker,
              dockerImage: "traefik/whoami",
            })
            .getSQL(),
        );

        const genCreateResult = db
          .prepare(createGenerationQuery.sql)
          .run(...createGenerationQuery.params);

        logger.debug(
          "inserted generation",
          createGenerationQuery.sql,
          genCreateResult,
        );

        // create the service
        const createServiceQuery = dialect.sqlToQuery(
          trx
            .insert(service)
            .values({
              id: serviceId,
              name: input.name,
              projectId: ctx.project.getData().id,
              latestGenerationId: generationId,
              redeploySecret: randomBytes(env.REDEPLOY_SECRET_BYTES).toString(
                "hex",
              ),
            })
            .returning({
              id: serviceGeneration.id,
            })
            .getSQL(),
        );

        const createResult = db
          .prepare(createServiceQuery.sql)
          .run(...createServiceQuery.params);

        logger.debug("inserted service", createServiceQuery.sql, createResult);

        return serviceId;
      })
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
ok so they are actual transactions, https://discord.com/channels/1043890932593987624/1240832420945596416/1240958720033165354 still tryna figure out why this isn't working then
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
this errors with cannot commit - no transaction is active
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
No description
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
are drizzle transactions not real database transactions?
8 replies
DTDrizzle Team
Created by Derock on 5/15/2024 in #help
need help with drizzle transactions and foreign key constraints
bump
8 replies
DTDrizzle Team
Created by Derock on 4/23/2024 in #help
SQLite: set fk constraint to be "DEFERRABLE INITIALLY DEFERRED"
opened an issue: https://github.com/drizzle-team/drizzle-orm/issues/2252
3 replies
DTDrizzle Team
Created by Derock on 4/23/2024 in #help
SQLite: set fk constraint to be "DEFERRABLE INITIALLY DEFERRED"
bump
3 replies
DTDrizzle Team
Created by Derock on 4/27/2024 in #help
ts: Object literal may only specify known properties except the property is known
oh turns out webhook.token is of string | null but ive created it as a non-nullable column. dont know why the ts error was so misleading
5 replies
DTDrizzle Team
Created by Derock on 4/27/2024 in #help
ts: Object literal may only specify known properties except the property is known
nevermind again, its not expecting an array
5 replies
DTDrizzle Team
Created by Derock on 4/27/2024 in #help
ts: Object literal may only specify known properties except the property is known
just noticed the little [] at the end
5 replies
DTDrizzle Team
Created by Derock on 4/27/2024 in #help
ts: Object literal may only specify known properties except the property is known
nvm im dumb its wanting an array not one object
5 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
actually ignore this, my stack trace is being very weird -- code completely unrelated to the issue for some reason is part of the trace.
19 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
looking closer at the stack trace, i think the error is coming from somewhere else in my code: 
  const [project] = await ctx.db
    .select({
      id: projects.id,
      friendlyName: projects.friendlyName,
      internalName: projects.internalName,
      createdAt: projects.createdAt,
    })
    .from(projects)
    .where(
      or(
        eq(projects.id, input.projectId), // << this line is what my stack trace is telling me 
        eq(projects.internalName, input.projectId),
      ),
    )
    .limit(1);
  const [project] = await ctx.db
    .select({
      id: projects.id,
      friendlyName: projects.friendlyName,
      internalName: projects.internalName,
      createdAt: projects.createdAt,
    })
    .from(projects)
    .where(
      or(
        eq(projects.id, input.projectId), // << this line is what my stack trace is telling me 
        eq(projects.internalName, input.projectId),
      ),
    )
    .limit(1);
19 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
i deleted the old database and created a brand new one (I'm using better-sqlite3) the only difference is that the old database also has a table called __old_push_sessions while the new one has no __old_push_ tables
19 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
searching __ AND push comes up with nothing good either
19 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
so im very confused as to where the __old_push gets added in
19 replies
DTDrizzle Team
Created by Derock on 12/19/2023 in #help
`no such table: main.__old_push_projects` after db push
i cant even find in the drizzle-orm source code any references to __old_push or __old and searching for __ NOT __drizzle comes up with nothing useful (using github search)
19 replies
DTDrizzle Team
Created by louis on 10/2/2023 in #help
No such table: main.__old_push_[TABLENAME]
@Louis / @stan Did either of you make an GitHub issue for this? I'm running into the same problem (https://discord.com/channels/1043890932593987624/1186793412549148753) and if you haven't made an issue yet, I'll go ahead and make one.
77 replies