[FEATURE]: Manual transactions

[MatanYadaev]

Describe what you want

Expected behavior:

const transaction = database.beginTransaction();

// Do something with the DB

transaction.commit(); // or transaction.rollback()

Prior Art

Laravel Manual Transactions: https://laravel.com/docs/10.x/database#manually-using-transactions

[gudleik]

This would be very useful in test suites (beforeEach/afterEach)

[CanRau]

In general I would prefer this more explicit API instead of throwing than catching etc which can be reserved for "actual" exceptions

Think it makes error management cleaner

Any update on if this is being considered? 😇

[97albertus]

this is an absolute must-have imo, especially considering how lacking the docs on dirzzle's transaction behaviours are currently

[tposch]

I, too, would like to see this feature in Drizzle. I used a similar feature with Sequelize's Unmanaged Transactions.

[ehsan2003]

it's a nice feature to have but at the end of the day it's not hard to manually create that kind of api

[zmr-dev]

Having more control is super important, especially for the tricky stuff like importing routines and other complicated tasks. I'm struggling to build something that would initially only be placing commits in specific phases of the routine.

[khalvai]

hi there i recentely opend issue for implementing unit of work with this orm. now i found out this issue, if we have this we can easly implement unit of work.

[oliveryasuna]

Are there any plans to implement this? Unit-of-work is important. Right now, we would have to use higher-order functions, which gets really messy.

[oliveryasuna]

it's a nice feature to have but at the end of the day it's not hard to manually create that kind of api

Do you have a solution?

[maxdhn]

@oliveryasuna I have the same issue. First, we defined a "typed wrapper" (DBService) around Drizzle to simplify integration with Nest.js and dependency injection. Then, we added a new method to our DBService called createTransaction, which creates a scoped DB client (drizzle(client)) tied to a specific connection.

From the node-postgres documentation :

You must use the same client instance for all statements within a transaction. PostgreSQL isolates a transaction to individual clients. This means if you initialize or use transactions with the pool.query method you will have problems. Do not use transactions with the pool.query method.

import { Pool, PoolClient } from 'pg';
import * as schemas from './schemas';
import { drizzle, NodePgDatabase } from 'drizzle-orm/node-postgres';

export type DBServiceTransaction = {
  tx: Omit<DBService, 'createTransaction' | '$client' | 'transaction'>;
  commit(): Promise<void>;
  rollback(): Promise<void>;
};

export abstract class DBService
  extends NodePgDatabase<typeof schemas>
  implements ReturnType<typeof drizzle<typeof schemas>>
{
  $client: Pool;

  abstract createTransaction(): Promise<DBServiceTransaction>;
}

export function dBServiceFactory(pool: Pool): DBService {
  const baseDb = drizzle(pool, { schema: schemas });

  const dbService = baseDb as DBService;

  dbService.createTransaction = async function () {
    const client: PoolClient = await pool.connect();
    await client.query('BEGIN');

    const txDbBase = drizzle(client, { schema: schemas });
    return {
      tx: txDbBase as Omit<
        DBService,
        'createTransaction' | '$client' | 'transaction'
      >,
      commit: async () => {
        try {
          await client.query('COMMIT');
        } catch (err) {
          throw err;
        } finally {
          client.release();
        }
      },
      rollback: async () => {
        try {
          await client.query('ROLLBACK');
        } catch (err) {
          throw err;
        } finally {
          client.release();
        }
      },
    };
  };

  return dbService;
}

And then you can use it like :

// This should be instantiated via a factory and injected through the constructor is you are working with Nest.js
const db = dBServiceFactory(pool);

const { tx, commit, rollback } = await db.createTransaction();

try {
  await tx.users.insert(...);
  await tx.orders.insert(...);
  await commit();
} catch (e) {
  await rollback();
}

Hope this implementation can help until unmanaged transactions are officially supported by Drizzle.

[grig]

Ran into the issue when trying to unit test some ORM-related code via vitest, here's what I came up with. The useTransaction function effectively spawns a call to db.transaction() in background, yields the transaction back to the test runner and then resumes the background transaction when the test is complete. This works due to the fact that (for Postgres at least), drizzle's PgTransaction type is assignable to PgDatabase, so you can use these more or less interchangeably.

The function's API mimics react's useEffect in that the callback is expected to return a cleanup function that is called after the transaction is rolled back.

Hope that helps!

Usage:

import { test, beforeAll, beforeEach } from 'vitest'
import { useTransaction } from './useTransaction'
import * as schema from '@/db/schema'
import { pglite } from '@electric-sql/pglite'
                                                                                
type Database = PgDatabase<PgQueryResultHKT, typeof schema>

let db: Database
                                                                                
beforeAll(async () => {
 db = drizzle({ client: pglite, schema })
 // create the schema
})
                                                                                
beforeEach(async () => {
  await useTransaction(db, (tx) => {
    db = tx
    return (originalDb) => {
      db = originalDb
    }
  })  
})
                                                                                
test('...', async () => {
  db.insert(...).values(...) // this will be rolled back at the end of the test
})

Code:

import { onTestFinished } from "vitest"
import { type Database } from "@/lib/db"

type CallbackFn = (tx: Database) => ((db: Database) => void) | undefined

/**
 * Wrap the database calls made within the test in a transaction that is rolled back after the test
 *
 * The callback is called with the transaction object and it is expected to return a function that 
 * would be called to reverse any of the side effects done in the callback.
 *
 * Usage:
 * ````ts
 * import { useTransaction } from './runTestWithTransaction'
 * import * as schema from '@/db/schema'
 * import { pglite } from '@electric-sql/pglite'
 *
 * type Database = PgDatabase<PgQueryResultHKT, typeof schema>
 * let db: Database
 *
 * beforeAll(async () => {
 *  db = drizzle({ client: pglite, schema })
 *  // create the schema
 * })
 *
 * beforeEach(async () => {
 *   await useTransaction(db, (tx) => {
 *     db = tx
 *     return (originalDb) => {
 *       db = originalDb
 *     }
 *   })  
 * })
 *
 * test('...', async () => {
 *   db.insert(...).values(...) // this will be rolled back at the end of the test
 * })
 * ````
 */
export async function useTransaction(db: Database, callback: CallbackFn) {
  let restore: ReturnType<CallbackFn> = undefined;

  return new Promise<void>((yieldToTestFramework) => {
    // asynchronously start the new transaction, resolving the parent promise
    // as soon as the database is wrapped correctly
    db.transaction(async (tx) => {
      // Let the caller know that the transaction is ready and it can save it
      // for later usage
      restore = callback(tx)

      // create a "latch" that resolves when the test is finished
      const testFinished = new Promise<void>((resolve) => {
        onTestFinished(() => resolve())
      })

      // return control to the test framework
      // TODO: consider an explicit async return via process.nextTick
      yieldToTestFramework()

      try {
        // wait until the test is done (i.e. onTestFinished() hook has resolved
        // the promise)
        await testFinished

        // roll back the changes made in the transaction
        tx.rollback()
      } finally {
        // let the caller do the cleanup
        restore?.(db)
      }
    }).catch((error) => {
      if (error.name === 'DrizzleError' && error.message === 'Rollback') {
        // ignore the error: the transaction is expected to be rolled back
      } else {
        // Handle transaction error
        // TODO: consider re-raising the error so that it is caught by
        // `unhandledRejection` handler. Note that there's (apparently) no
        // way to inject the error back into the test framework
        console.error('Transaction error:', error)
      }
    })
  })
}

[f1yingbanana]

@grig's workaround works like a charm! For those that wants the shorter version, I've set up my drizzle db in my setup file:

beforeEach(async () => {
  await new Promise<void>((yieldToTestFramework) =>
    globalThis.globalTestDatabase
      .transaction(async (transaction) => {
        const testFinished = new Promise<void>((resolve) => {
          onTestFinished(() => resolve());
        });

        // I'm using this global variable in my tests
        globalThis.testDatabase = transaction;

        // Resolve the current promise, allowing the test to run, but continue on
        // to wait for cleanup.
        yieldToTestFramework();

        await testFinished;
        transaction.rollback();
      })
      .catch((error) => {
        if (
          error instanceof Error &&
          error.name === "DrizzleError" &&
          error.message === "Rollback"
        ) {
          // This is thrown by transaction.rollback()
        } else {
          throw error;
        }
      }),
  );
});

[AndriiSherman]

Hey everyone!

I've created this message to send in a batch to all opened issues we have, just because there are a lot of them and I want to update all of you with our current work, why issues are not responded to, and the amount of work that has been done by our team over ~8 months.

I saw a lot of issues with suggestions on how to fix something while we were not responding – so thanks everyone. Also, thanks to everyone patiently waiting for a response from us and continuing to use Drizzle!

We currently have 4 major branches with a lot of work done. Each branch was handled by different devs and teams to make sure we could make all the changes in parallel.

---

First branch is drizzle-kit rewrite

All of the work can be found on the alternation-engine branch. Here is a PR with the work done: https://github.com/drizzle-team/drizzle-orm/pull/4439

As you can see, it has 167k added lines of code and 67k removed, which means we've completely rewritten the drizzle-kit alternation engine, the way we handle diffs for each dialect, together with expanding our test suite from 600 tests to ~9k test units for all different types of actions you can do with kit. More importantly, we changed the migration folder structure and made commutative migrations, so you won't face complex conflicts on migrations when working in a team.

What's left here:

• We are finishing handling defaults for Postgres, the last being geometry (yes, we fixed the srid issue here as well).
• We are finishing commutative migrations for all dialects.
• We are finishing up the command, so the migration flow will be as simple as drizzle-kit up for you.

Where it brings us:

• We are getting drizzle-kit into a new good shape where we can call it [email protected]!

Timeline:

• We need ~2 weeks to finish all of the above and send this branch to beta for testing.

Second big branch is a complex one with several HUGE updates

• Bringing Relational Queries v2 finally live. We've done a lot of work here to actually make it faster than RQBv1 and much better from a DX point of view. But in implementing it, we had to make another big rewrite, so we completely rewrote the drizzle-orm type system, which made it much simpler and improved type performance by ~21.4x:

(types instantiations for 3300 lines production drizzle schema + 990 lines relations)

TS v5.8.3: 728.8k -> 34.1k
TS v5.9.2: 553.7k -> 25.4k

You can read more about it here.

What's left here:

• We have 1 issue with TS that is already in progress of being fixed. The issue and Post about fixing.

Where it brings us:

• We are getting drizzle-orm into a new good shape where we can call it [email protected]!

Breaking changes:

• We will have them, but we will have open channels for everyone building on top of drizzle types, so we can guide you through all the changes.

Third branch is adding support for CockroachDB and MSSQL dialects

Support for them is already in the alternation-engine branch and will be available together with the drizzle-kit rewrite.

Summary

All of the work we are doing is crucial and should be done sooner rather than later. We've received a lot of feedback and worked really hard to find the best strategies and decisions for API, DX, architecture, etc., so we can confidently mark it as v1 and be sure we can improve it and remain flexible for all the features you are asking for, while becoming even better for everyone building on top of the drizzle API as well.

We didn't want to stay with some legacy decisions and solutions we had, and instead wanted to shape Drizzle in a way that will be best looking ahead to 2025–2026 trends (v1 will get proper effect support, etc.).

We believe that all of the effort we've put in will boost Drizzle and benefit everyone using it.

Thanks everyone, as we said, we are here to stay for a long time to build a great tool together!

Timelines

We are hoping to get v1 for drizzle in beta this fall and same timeline for latest. Right after that we can go through all of the issues and PRs and resond everyone. v1 for drizzle should close ~70% of all the bug tickets we have, so on beta release we will start marking them as closed!

[bonrow]

Hey, any update on this? @f1yingbanana's and @grig's approaches work, but they’re inherently messy and imperative. Having a more declarative, built-in abstraction for this would be much cleaner.

Thanks!

P.S.: I know there is a lot more important things on the roadmap, just asking for clarity here.

[nickdeis]

Hey @bonrow, not sure if this helps, but if you want declarative transactions, I've implemented a transaction context here based off this spec. I've also added decorators that make it feel very declarative. Also looking for people to break this library in testing scenarios. Here's a snippet:

const { Transactional, SavePoint, useTransaction, useSavePoint } =
  createTransactionContext(db);
class A {
  @Transactional
  async insertCustomer() {
    const tx = useTransaction();
    const [result] = await tx
      .insert(customer)
      .values(g.customer())
      .returning({ customer_id: customer.customer_id });
    await this.insertOrder(result!.customer_id);
    return result!.customer_id;
  }
  @SavePoint
  private async insertOrder(customer_id: string) {
    const sp = useSavePoint();
    const [result] = await sp
      .insert(order)
      .values(g.order(customer_id))
      .returning({ order_id: order.order_id });
    await this.insertItem(result!.order_id);
  }
  @SavePoint("a")
  private async insertItem(order_id: string) {
    const sp = useSavePoint();
    await sp.insert(items).values(g.item(order_id));
  }
}
class B {
  @Transactional({ accessMode: "read only" })
  async getCustomer(customer_id: string) {
    const tx = useTransaction();
    const [result] = await tx
      .select()
      .from(customer)
      .where(eq(customer.customer_id, customer_id));
    return result;
  }
}
const a = new A();
const b = new B();
const customer_id = await a.insertCustomer();
await b.getCustomer(customer_id);

[DanTsk]

If anyone still needs it, I put together a workaround for PostgreSQL. I leaned pretty heavily on Drizzle internals to keep things stable and predictable.

This controlled transaction doesn’t support a nested controller transaction. But Drizzle's built-in/managed nested transactions still work just fine.

There’s also quite a bit of type casting to keep everything type-safe across the project.

---

Create type definitions to access Drizzle's internal properties safely:

/**
 * Internal structure of NodePgDatabase for type-safe access to session
 */
export interface NodePgDatabaseInternals<
    TFullSchema extends Record<string, unknown>,
    TSchema extends TablesRelationalConfig
> extends NodePgDatabase<TFullSchema> {
    readonly session: NodePgSession<TFullSchema, TSchema>;
}

/**
 * Internal structure of NodePgSession for accessing client and dialect
 */
export interface NodePgSessionInternals<TSchema extends TablesRelationalConfig> {
    readonly client: NodePgClient;
    readonly dialect: PgDialect;
    readonly options?: NodePgSessionOptions;
    readonly schema: RelationalSchemaConfig<TSchema>;
}

/**
 * Internal transaction methods for SQL generation
 */
export interface NodePgTransactionInternals {
    getTransactionConfigSQL(config: PgTransactionConfig): string;
}

The next part is the Controlled Transaction Class wrapper for transaction commit/rollback exposure and override base throwable rollback method:

export class NodePgControlledTransaction<
    TFullSchema extends Record<string, unknown>,
    TSchema extends TablesRelationalConfig
> extends NodePgTransaction<TFullSchema, TSchema> {

    constructor(
        protected dialect: PgDialect,
        protected session: NodePgSession<TFullSchema, TSchema>,
        protected schema: RelationalSchemaConfig<TSchema> | undefined,
    ) {
        super(dialect, session, schema);
    }

    public async commit(): Promise<void> {
        await this.execute(sql`commit`);
        this.release();
    }

    // @ts-expect-error - Intentionally overriding parent's rollback to allow controlled transaction rollback
    public async rollback(): Promise<void> {
        await this.execute(sql`rollback`);
        this.release();
    }

    private release(): void {
        const sessionInternals = this.session as unknown as NodePgSessionInternals<TSchema>;

        if (!this.isPoolClient(sessionInternals.client)) {
            return;
        }

        sessionInternals.client.release();
    }

    private isPoolClient(client: NodePgClient): client is PoolClient {
        return (client as PoolClient).release !== undefined;
    }
}

And my Database Connection class that manages the connection pool and creates controlled transactions:

export class PGConnector {
    private client: Pool;
    private database: NodePgDatabase<DBSchema>;

    constructor(private readonly connectionString: string) {
        this.client = new Pool({
            connectionString: this.connectionString,
        });

        this.database = drizzle({
            client: this.client,
            schema
        });
    }

    public async startControlledTransaction(
        config?: PgTransactionConfig
    ): Promise<NodePgControlledTransaction<DBSchema, ExtractTablesWithRelations<DBSchema>>> {

        const connection = await this.client.connect();

        const transactionSession = new NodePgSession<DBSchema, ExtractTablesWithRelations<DBSchema>>(
            connection,
            this.currentSession.dialect,
            this.currentSession.schema,
            this.currentSession.options
        );

        const tx = new NodePgControlledTransaction<DBSchema, ExtractTablesWithRelations<DBSchema>>(
            this.currentSession.dialect,
            transactionSession,
            this.currentSession.schema
        );

        const internalsTx = tx as unknown as NodePgTransactionInternals;

        await tx.execute(sql`begin${config ? sql` ${internalsTx.getTransactionConfigSQL(config)}` : undefined}`);

        return tx;
    }

    private get currentSession(): NodePgSessionInternals<ExtractTablesWithRelations<DBSchema>> {
        const dbInternals = this.database as NodePgDatabaseInternals<DBSchema, ExtractTablesWithRelations<DBSchema>>;
        return dbInternals.session as unknown as NodePgSessionInternals<ExtractTablesWithRelations<DBSchema>>;
    }
}

---

Then it can be used like this:

// Start transaction with specific isolation level
const tx = await connector.startControlledTransaction({
    isolationLevel: 'serializable',
    accessMode: 'read write',
    deferrable: false
});

try {
    // Critical operations requiring serializable isolation
    await tx.insert(accounts).values({ balance: 1000 });
    await tx.commit();
} catch (error) {
    await tx.rollback();
    throw error;
}