git.sr.ht/~pingoo/stdx@v0.0.0-20240218134121-094174641f6e/dbx/db.go (about)

     1  package dbx
     2  
     3  import (
     4  	"context"
     5  	"database/sql"
     6  	"time"
     7  
     8  	"github.com/jmoiron/sqlx"
     9  )
    10  
    11  // Database is wrapper of `sqlx.DB` which implements `DB`
    12  type Database struct {
    13  	sqlxDB *sqlx.DB
    14  }
    15  
    16  // Connect to a database and verify the connections with a ping.
    17  // See https://www.alexedwards.net/blog/configuring-sqldb
    18  // and https://making.pusher.com/production-ready-connection-pooling-in-go
    19  // https://brandur.org/fragments/postgres-parameters
    20  // for the details
    21  func Connect(databaseURL string, poolSize int) (ret *Database, err error) {
    22  	sqlxDB, err := sqlx.Connect("pgx", databaseURL)
    23  	if err != nil {
    24  		return
    25  	}
    26  
    27  	ret = &Database{
    28  		sqlxDB: sqlxDB,
    29  	}
    30  
    31  	ret.SetMaxOpenConns(poolSize)
    32  	ret.SetMaxIdleConns(int(poolSize / 2))
    33  	ret.SetConnMaxLifetime(30 * time.Minute)
    34  	return
    35  }
    36  
    37  // Begin starts a transaction. The default isolation level is dependent on the driver.
    38  // The provided context is used until the transaction is committed or rolled back. If the context is
    39  // canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the
    40  // context provided to BeginTx is canceled.
    41  func (db *Database) Begin(ctx context.Context) (*Tx, error) {
    42  	sqlxTx, err := db.sqlxDB.BeginTxx(ctx, nil)
    43  	return &Tx{sqlxTx}, err
    44  }
    45  
    46  // BeginTx starts a transaction.
    47  //
    48  // The provided context is used until the transaction is committed or rolled back. If the context is
    49  // canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the
    50  // context provided to BeginTx is canceled.
    51  //
    52  // The provided TxOptions is optional and may be nil if defaults should be used. If a non-default
    53  // isolation level is used that the driver doesn't support, an error will be returned.
    54  func (db *Database) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error) {
    55  	sqlxTx, err := db.sqlxDB.BeginTxx(ctx, opts)
    56  	return &Tx{sqlxTx}, err
    57  }
    58  
    59  // Ping verifies a connection to the database is still alive, establishing a connection if necessary.
    60  func (db *Database) Ping(ctx context.Context) error {
    61  	return db.sqlxDB.PingContext(ctx)
    62  }
    63  
    64  // SetConnMaxLifetime sets the maximum amount of time a connection may be reused.
    65  func (db *Database) SetConnMaxLifetime(d time.Duration) {
    66  	db.sqlxDB.SetConnMaxLifetime(d)
    67  }
    68  
    69  // SetMaxIdleConns sets the maximum number of connections in the idle connection pool.
    70  func (db *Database) SetMaxIdleConns(n int) {
    71  	db.sqlxDB.SetMaxIdleConns(n)
    72  }
    73  
    74  // SetMaxOpenConns sets the maximum number of open connections to the database.
    75  func (db *Database) SetMaxOpenConns(n int) {
    76  	db.sqlxDB.SetMaxOpenConns(n)
    77  }
    78  
    79  // Stats returns database statistics.
    80  func (db *Database) Stats() sql.DBStats {
    81  	return db.sqlxDB.Stats()
    82  }
    83  
    84  // Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.
    85  func (db *Database) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error) {
    86  	return db.sqlxDB.ExecContext(ctx, query, args...)
    87  }
    88  
    89  // Get a single record. Any placeholder parameters are replaced with supplied args. An `ErrNoRows`
    90  // error is returned if the result set is empty.
    91  func (db *Database) GetContext(ctx context.Context, dest any, query string, args ...any) error {
    92  	return db.sqlxDB.GetContext(ctx, dest, query, args...)
    93  }
    94  
    95  // QueryContext executes a query that returns rows, typically a SELECT. The args are for any placeholder
    96  // parameters in the query.
    97  func (db *Database) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error) {
    98  	return db.sqlxDB.QueryContext(ctx, query, args...)
    99  }
   100  
   101  // Select an array of records. Any placeholder parameters are replaced with supplied args.
   102  func (db *Database) Select(ctx context.Context, dest any, query string, args ...any) error {
   103  	return db.sqlxDB.SelectContext(ctx, dest, query, args...)
   104  }
   105  
   106  func (db *Database) QueryRowxContext(ctx context.Context, query string, args ...any) *sqlx.Row {
   107  	return db.sqlxDB.QueryRowxContext(ctx, query, args...)
   108  }
   109  
   110  func (db *Database) QueryxContext(ctx context.Context, query string, args ...any) (*sqlx.Rows, error) {
   111  	return db.sqlxDB.QueryxContext(ctx, query, args...)
   112  }