proposal: database/sql: add Raw method to Tx type

[eqld]

Proposal Details

Proposal: Add Raw Method to sql.Tx Type

Abstract

This proposal suggests adding a Raw() method to the sql.Tx type in Go's database/sql package, mirroring the existing Raw() method available on sql.Conn. This addition would provide safe, official access to underlying driver connections within transaction contexts, eliminating the need for dangerous reflection-based workarounds currently required for driver-specific functionality.

Background

Go's database/sql package provides excellent abstractions for database operations, including the sql.Tx type for managing transactions. However, it currently lacks a crucial feature that sql.Conn provides: direct access to the underlying driver connection.

Current State

The sql.Conn type offers a Raw() method:

// From database/sql/sql.go
func (c *Conn) Raw(f func(driverConn any) error) error

This method allows safe access to driver-specific functionality when not in a transaction context. However, the sql.Tx type has no equivalent method, creating a significant limitation.

The Problem

Without Tx.Raw(), developers face substantial challenges when they need:

1. Driver-specific functionality within transaction contexts
2. Performance-critical operations that require direct driver access
3. Advanced features not exposed through standard database/sql interfaces

Real-World Impact: PostgreSQL COPY FROM

A compelling example is PostgreSQL's COPY FROM functionality via the pgx driver. The pgx.CopyFrom method provides:

• 10-100x performance improvement over individual INSERT statements
• Essential for bulk data operations like ETL processes, data migrations, and large imports
• Critical for production systems handling high-volume data processing

However, using CopyFrom within transactions currently requires unsafe workarounds.

Current Workaround and Its Problems

Developers currently resort to reflection-based hacks to access the underlying driver connection from sql.Tx:

type Tx sql.Tx

// Current unsafe workaround (from example repository https://github.com/eqld/example-tx-raw)
func (tx *Tx) Raw(f func(driverConn any) error) (err error) {
    // Use reflection to access `tx.dc` (`driverConn`).
    txValue := reflect.ValueOf((*sql.Tx)(tx)).Elem()
    
    dcField := txValue.FieldByName("dc")
    if !dcField.IsValid() {
        return fmt.Errorf("cannot access dc field from transaction")
    }
    
    // Make the field accessible and get the `driverConn` pointer.
    dcField = reflect.NewAt(dcField.Type(), dcField.Addr().UnsafePointer()).Elem()
    dc := dcField.Interface()
    dcValue := reflect.ValueOf(dc).Elem()
    
    // Access `dc.ci` (`driver.Conn` interface).
    ciField := dcValue.FieldByName("ci")
    if !ciField.IsValid() {
        return fmt.Errorf("cannot access ci field from `driverConn`")
    }
    
    // Make the field accessible and get the underlying driver connection.
    ciField = reflect.NewAt(ciField.Type(), ciField.Addr().UnsafePointer()).Elem()
    ci := ciField.Interface()
    
    return f(ci)
}

Problems with This Approach

1. Breaks encapsulation by accessing unexported fields (dc, ci)
2. Fragile and version-dependent - can break between Go releases
3. Bypasses type safety mechanisms
4. Uses unsafe operations (UnsafePointer)
5. Adds complexity and maintenance burden
6. Not officially supported - no guarantees of continued functionality

Proposed Solution

Add a Raw() method to sql.Tx with the same signature as sql.Conn.Raw():

// Proposed addition to database/sql
func (tx *Tx) Raw(f func(driverConn any) error) error {
    // Safe, official implementation that:
    // 1. Provides access to the underlying driver connection
    // 2. Maintains transaction context and safety
    // 3. Handles errors appropriately
    // 4. Ensures proper cleanup
}

Usage Example

With the proposed Tx.Raw() method, the PostgreSQL CopyFrom example becomes clean and safe:

import (
	// ...
	"github.com/jackc/pgx/v5/stdlib"
)

// Clean usage with proposed Tx.Raw() method
func performBulkInsert(ctx context.Context, tx *sql.Tx, data [][]any) error {
    return tx.Raw(func(driverConn any) error {
        // Cast to pgx stdlib connection
        stdlibConn := driverConn.(*stdlib.Conn)
        pgxConn := stdlibConn.Conn()
        
        // Use high-performance CopyFrom within transaction
        _, err := pgxConn.CopyFrom(
            ctx,
            pgx.Identifier{"items"},
            []string{"name", "data"},
            pgx.CopyFromRows(data),
        )
        return err
    })
}

Detailed Design

Method Signature

func (tx *Tx) Raw(f func(driverConn any) error) error

Implementation Requirements

1. Transaction Safety: The method must maintain transaction context and ensure the driver connection is properly associated with the transaction
2. Error Handling: Errors from the callback function should be properly propagated
3. Resource Management: No additional resource cleanup should be required beyond normal transaction handling
4. Thread Safety: The method should be safe for concurrent use consistent with sql.Tx guarantees

Implementation Approach

The implementation would:

1. Access the transaction's underlying driverConn (similar to current reflection approach but officially supported)
2. Extract the driver.Conn interface from the connection
3. Execute the provided callback function with the driver connection
4. Handle any errors and maintain transaction state

Error Behavior

• If the callback function returns an error, Tx.Raw() returns that error
• The transaction remains in its current state (not automatically rolled back)
• Error handling follows the same patterns as other sql.Tx methods

Rationale

Performance Benefits

The PostgreSQL CopyFrom example demonstrates significant performance improvements:

• Standard INSERTs: ~1,000-10,000 rows/second
• CopyFrom: ~100,000-1,000,000 rows/second
• Use cases: Data migrations, bulk imports, ETL processes, log ingestion

API Consistency

Adding Tx.Raw() creates consistency with the existing sql.Conn.Raw() method:

• Same method name and signature
• Same usage patterns
• Same safety guarantees
• Familiar API for developers

Safety and Maintainability

The proposed solution eliminates:

• Unsafe reflection operations
• Version-dependent internal field access
• Complex workaround code
• Maintenance burden for application developers

Real-World Need

The example repository (https://github.com/eqld/example-tx-raw) demonstrates:

• Working reflection-based workaround
• Performance benefits of driver-specific functionality
• Proper transaction semantics (commit/rollback behavior)
• Clean API design with the proposed solution

Compatibility

This proposal is fully backward compatible:

• No breaking changes to existing code
• Pure addition to the sql.Tx API
• Follows Go 1 compatibility promise
• Optional functionality - existing code continues to work unchanged

Implementation

Phase 1: Core Implementation

• Add Raw() method to sql.Tx type
• Implement safe access to underlying driver connection
• Add comprehensive tests

Phase 2: Documentation and Examples

• Update package documentation
• Add usage examples
• Document best practices

Phase 3: Driver Testing

• Test with major database drivers (pgx, mysql, sqlite)
• Verify transaction semantics
• Performance validation

Alternative Approaches Considered

1. Driver-Specific Transaction Types

Approach: Encourage drivers to provide their own transaction types with Raw() methods.
Problems:

• Fragments the ecosystem
• Requires application code changes
• Loses standard library benefits

2. Callback-Based Transaction API

Approach: Add transaction-aware methods to driver interfaces.
Problems:

• Requires extensive driver modifications
• Complex API changes
• Backward compatibility issues

3. Connection Pooling Changes

Approach: Modify connection pooling to expose driver connections.
Problems:

• Architectural complexity
• Thread safety concerns
• Performance implications

Supporting Evidence

Example Repository

The complete working example is available at: https://github.com/eqld/example-tx-raw

This repository demonstrates:

• Current reflection-based workaround implementation
• Three scenarios: non-transactional, transactional commit, transactional rollback
• Performance benefits of pgx.CopyFrom
• Proper transaction semantics
• Clean API design with proposed solution

Performance Data

Based on PostgreSQL CopyFrom benchmarks:

• 10-100x performance improvement for bulk operations
• Critical for production systems handling large data volumes
• Essential for ETL processes and data migrations

Conclusion

Adding a Raw() method to sql.Tx would:

1. Solve a real problem faced by Go developers working with databases
2. Provide significant performance benefits for bulk operations
3. Eliminate unsafe workarounds currently required
4. Maintain API consistency with existing sql.Conn.Raw()
5. Ensure backward compatibility with no breaking changes
6. Enable driver innovation within transaction contexts

The proposed solution is minimal, safe, and addresses a concrete need demonstrated by the working example repository. It follows Go's principles of simplicity and consistency while enabling powerful functionality for performance-critical applications.

References

• Example Repository: https://github.com/eqld/example-tx-raw
• PostgreSQL pgx driver: https://github.com/jackc/pgx
• Go database/sql package: https://pkg.go.dev/database/sql
• Go 1 Compatibility Promise: https://go.dev/doc/go1compat

[gabyhelp]

Related Code Changes

• database/sql: add Conn.Raw to expose the driver Conn safely

_{(Emoji vote if this was helpful or unhelpful; more detailed feedback welcome in this discussion.)}

[seankhliao]

I'd say no as it breaks the safety of Tx since you can terminate the transactaction through the underlying connection.
Use of Raw already takes you outside the standard library, in which case, the underlying driver's transaction interface is probably more appropriate.