Addresses #376, #1957, #2414 & #2605.

This PR is WIP Didn’t mark it as draft as I’d like feedback on the API/implementation details for the work currently done before applying the same changes to other drivers and dialects.

This PR aims to implement improvements for the debugging experience, mainly improving error handling and logging.

Driver-agnostic errors Previously, the way to handle query-related errors was dependent on the driver you used. Not the worst, but not the best. The shape of the error can be different depending on the driver and a lot of them don’t have any way of identifying a query error (just thrown as a generic Error) and don’t have type safety. We aim to improve this:

const users = pgTable('users', {
  id: serial().primaryKey(),
  email: text().unique()
});

await db.insert(users).values({ email: 'john@email.com' });

try {
  await db.insert(users).values({ email: 'john@email.com' });
} catch (err) {
  // Determines if it's a postgres unique constraint violation error
  if (is(err, PgError) && err.code === ERROR.INTEGRITY_CONSTRAINT_VIOLATION.UNIQUE_VIOLATION) {
    return error(400, 'email already exists')
  } else {
    // Some other kind of error...
  }
}

Errors are now only dependent on the dialect you’re using and are type safe. This only handles query-related errors though, other errors like connection issues will still come from the driver and/or DB provider.

Here’s another example using the getColumnNames method provided by Drizzle:

const posts = pgTable('posts', {
  id: serial().primaryKey(),
  slug: text().unique(),
  title: text(),
  userId: integer().notNull().references(() => users.id)
}, (t) => [
  unique('...').on(t.title, t.userId)
]);

try {
  await db.insert(posts).values(...);
} catch (err) {
  if (is(err, PgError) && err.code === ERROR.INTEGRITY_CONSTRAINT_VIOLATION.UNIQUE_VIOLATION) {
    const columns = err.getColumnNames();
    
    if (err.columnName === 'slug') {
      return error(400, 'slug already in use');
    } else if (columns[0] === 'title' && columns[1] === 'user_id' {
      return error(400, 'title already used by this user';
    } else {
      // Some other unique constraint violation...
    }
  }
}

More customization for TransactionRollbackError

try {
  await db.transaction(async (tx) => {
    await tx.insert(users).values({ balance: 100 });
    tx.rollback('custom message', { id: 'insert-user-tx' });
  });
} catch (err) {
  if (is(err, TransactionRollbackError)) {
    console.log(err.message); // Rollback: custom message;
    console.log(err.details); // { id: 'insert-user-tx' }
  }
}

Improved logging

Example 1: Query fails

await db.execute(sql`selec 1`).catch(() => {});
await db.execute(sql`select 1`);

Logs:

Failed query [166ms]: selec 1
Query [170ms]: select 1

Example 2: Query execution time for each operation

await db.select().from(User);
await db.insert(User).values({ name: 'Jon' });
await db.update(User).set({ name: 'John' }).where(eq(User.id, 1));
await db.delete(User).where(eq(User.id, 1));

Logs:

Query [173ms]: select "id", "name" from "user"
Query [338ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jon"]
Query [334ms]: update "user" set "name" = $1 where "user"."id" = $2 -- params: ["John", 1]
Query [341ms]: delete from "user" where "user"."id" = $1 -- params: [1]

Example 3: Better transaction logging. Includes where it begins, end and how long it took. Each query in a transaction can be identified by a randomly generated id/name.

await db.transaction(async (tx) => {
  await tx.insert(User).values({ name: 'Jane' });
});

Logs:

Begin transaction d46b
Query in transaction d46b [345ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jane"]
Commit transaction d46b [680ms]

Example 4: Specifying a name for the logs

await db.transaction(async (tx) => {
  await tx.insert(User).values({ name: 'Jane' });
}, {
  name: 'insert-user-tx'
});

Logs:

Begin transaction insert-user-tx
Query in transaction insert-user-tx [345ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jane"]
Commit transaction insert-user-tx [680ms]

Example 5: Rollback transaction

await db.transaction(async (tx) => {
  await tx.insert(User).values({ name: 'Jane' });
  tx.rollback();
}).catch(() => {});

Logs:

Begin transaction dce9
Query in transaction dce9 [334ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jane"]
Rollback transaction dce9 [675ms]

Example 6: Error inside transaction

await db.transaction(async (tx) => {
  await tx.insert(User).values({ name: 'Jane' });
  throw new Error('whoops!');
}).catch(() => {});

Logs:

Begin transaction 851b
Query in transaction 851b [333ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jane"]
Failed transaction 851b [681ms]

Example 7: Nested transactions

await db.transaction(async (tx) => {
  await tx.insert(User).values({ name: 'John' });
  
  await tx.transaction(async (tx) => {
    await tx.insert(User).values({ name: 'Jane' });
  }, {
    name: 'insert-jane-tx'
  });
}, {
  name: 'insert-john-tx'
});

Logs:

Begin transaction insert-john-tx
Query in transaction insert-john-tx [338ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["John"]
Begin savepoint insert-jane-tx
Query in savepoint insert-jane-tx [346ms]: insert into "user" ("id", "name") values (default, $1) -- params: ["Jane"]
Commit savepoint insert-jane-tx [515ms]
Commit transaction insert-john-tx [1201ms]

With all of these changes, there’s also more methods you can implement in your logger:

logQuery(
  query: string,
  params: unknown[],
  duration: number, // Query time execution
  failed: boolean, // If it errored
  transaction?: { // If the query was executed within a transaction
    name: string;
    type: 'transaction' | 'savepoint';
  } | undefined
)

logTransactionBegin(name: string, type: 'transaction' | 'savepoint')

logTransactionEnd(
  name: string,
  type: 'transaction' | 'savepoint',
  duration: number; // Total time to execute transaction
  status: 'commit' | 'rollback' | 'error';
)

Todo:

• Log iterator queries.
• Currently the error handling is only available for postgres.js driver, have to implement it for the rest of the drivers and dialects.