dbmap

package module
v0.0.0-...-a7c836f Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 28, 2025 License: MIT Imports: 10 Imported by: 0

README

DBmap

dbmap is a minimalistic "ORM"/database mapper for Go that provides basic utilities for mapping Go structs to database tables with a focus on ease-of-use.

Example usage

The primary goal of DBMap is to reduce boilerplate and help developers fall into the "pit of success". For example, all queries run through dbmap use named parameters to avoid easy-to-make mistakes with positional parameters.

e.g. WHERE id = $ID instead of WHERE id = ? + positional args.

conn := sql.Open("sqlite3", ":memory:")
defer conn.Close()
db := dbmap.New(conn)

type User struct {
    ID   int    `db:"id"`
    Name string `db:"name"`

    // Created and Updated at timestamps are automatically updated
    UpdatedAt time.Time `db:"updated_at"`
    CreatedAt time.Time `db:"created_at"`
}

// By default, table names are pluralized struct names (i.e. "users" for User),
// but you can override this by implementing the TableName method.
func (u *User) TableName() string {
    return "my_users"
}

// Select a single record
var user User
// dbmap automatically generates the necessary columns and table name
err := db.Select(ctx, &user, "WHERE id = $ID", dbmap.Args{"ID": 1})

// Select multiple records
var users []User
err = db.Select(ctx, &users, "WHERE name LIKE $pattern", dbmap.Args{"pattern": "A%"})

// Insert a new record
newUser := User{Name: "Alice"}
err = db.Insert(ctx, &newUser)
fmt.Println("New user ID:", newUser.ID) // ID's are automatically populated after inserts

// Update a specific record by ID
user := &User{ID: 1, Name: "Alice"}
err = db.UpdateRecord(ctx, user, dbmap.Updates{"name": "Alicia"})
// The struct is automatically updated in memory
fmt.Println("Updated user name:", user.Name)

// Update arbitrary rows
rowsAffected, err := db.Update(ctx, &User{}, "WHERE name = $name", dbmap.Args{"name": "Alice"}, dbmap.Updates{"name": "Alicia"})
fmt.Println("Updated rows:", rowsAffected)

// Delete a specific record (uses ID)
user := User{ID: 1, Name: "Alice"}
rowsAffected, err = db.DeleteRecord(ctx, &user)

// Delete multiple records (uses ID)
users := []*User{
    {ID: 1, Name: "Alice"},
    {ID: 2, Name: "Bob"},
}
rowsAffected, err = db.DeleteRecords(ctx, users)

// Delete arbitrary records
rowsAffected, err = db.Delete(ctx, &User{}, "WHERE name = $name", dbmap.Args{"name": "Alicia"})
fmt.Println("Deleted rows:", rowsAffected)
Escaping $

Since dbmap uses $ for named parameters, if you need to use a literal $ in your SQL (e.g. in a string), you can escape it by using $$.

Features (and to-do)

  • Support for inserting structs via DB.InsertRecord.
  • Support for selecting structs via DB.Select.
  • Support for updateing data via DB.Update.
  • Support for updateing specific structs via DB.UpdateRecord.
  • Support for deleteing data via DB.Delete.
  • Support for deleteing single structs via DB.DeleteRecord.
  • Support for deleteing multiple structs via DB.DeleteRecords.
  • Support for transactions via DB.Transaction
  • Updates created_at and updated_at fields automatically.
  • Support for standard DB Exec with named parameters.
  • Support for standard DB Query with named parameters.
  • Pluralize table names by default
  • Support for Exists
  • Support for Count

Not in scope, but welcome contributions:

  • Support for non-MySQL databases

Got feature requests or suggestions? Please open an issue or a PR!

Documentation

Overview

package dbmap is a lightweight, struct based ORM for Go that simplifies database interactions without completely abstracting SQL.

All methods use named paramters instead of driver specific placeholders in SQL queries/fragments. For example, a simple SELECT statement will look like: 

var user User
db.Select(&user, "WHERE name = $name AND age > $age", micorm.Args{"name": "Fox", "age": 32})

This enables more readable queries while avoiding the boilerplate+pitfalls of positional query arguments.

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// ErrNoUpdates is returned when no updates are provided to an update operation
	ErrNoUpdates = errors.New("no updates provided")
)

Functions

This section is empty.

Types

type Args 

type Args = map[string]any

Args is a map of named parameters to their values for SQL queries.

type DB 

type DB struct {

	// Pluralizer is used to pluralize table names. You can provide your own
	// pluralizer by overriding this field.
	Pluralizer Pluralizer
	// contains filtered or unexported fields
}

DB is a wrapper around sql.DB that provides lightweight ORM-like functionality.

func New 

func New(db *sql.DB) *DB

New initializes a new DB instance with the provided sql.DB connection.

func (*DB) Close 

func (d *DB) Close() error

Close closes the underlying database connection.

func (*DB) Count 

func (d *DB) Count(ctx context.Context, structType any, queryFragment string, args Args) (int64, error)

func (*DB) Delete 

func (d *DB) Delete(ctx context.Context, modelRef any, queryFragment string, args Args) (int64, error)

Delete deletes records from the database based on the provided struct type and SQL fragment with named parameters. The model argument should be a pointer to a struct type representing the table to delete from.

It returns the number of rows affected

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Delete users
rowsAffected, err := db.Delete(ctx, &User{}, "WHERE email LIKE $pattern AND active = $active", dbmap.Args{
	"pattern": "%@xfiles.gov",
	"active":  false,
})
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Deleted %d users\n", rowsAffected)

Output:

Deleted 1 users

func (*DB) DeleteRecord 

func (d *DB) DeleteRecord(ctx context.Context, model any) (int64, error)

DeleteRecord deletes a single record from the database based on the provided struct. The dest parameter should be a pointer to a struct representing the record to delete.

It returns the number of rows affected, or an error if the operation fails.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Get an existing user to delete
var user User
err := db.Select(ctx, &user, "WHERE email = $email", dbmap.Args{
	"email": "[email protected]",
})
if err != nil {
	log.Fatal(err)
}

// Delete the specific user record
rowsAffected, err := db.DeleteRecord(ctx, &user)
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Deleted %d user\n", rowsAffected)

Output:

Deleted 1 user

func (*DB) DeleteRecords 

func (d *DB) DeleteRecords(ctx context.Context, models any) (int64, error)

DeleteRecords deletes multiple records from the database based on the provided slice of structs. The dest parameter should be a pointer to a slice of structs representing the records to delete. It deletes each record by its ID inside of a transaction. If you need to delete in a single statement, use `DB.Delete`.

It returns the number of rows affected, or an error if the operation fails.

func (*DB) Exec 

func (d *DB) Exec(ctx context.Context, sql string, args map[string]any) (sql.Result, error)

Exec calls the underlying sql.DB Exec method, but uses named parameters like other dbmap methods.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Execute a raw SQL statement
result, err := db.Exec(ctx, `
		UPDATE users
		SET active = $active
		WHERE email LIKE $pattern
	`, dbmap.Args{
	"active":  false,
	"pattern": "%@xfiles.gov",
})
if err != nil {
	log.Fatal(err)
}

rowsAffected, err := result.RowsAffected()
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Updated %d users\n", rowsAffected)

Output:

Updated 3 users

func (*DB) Exists 

func (d *DB) Exists(ctx context.Context, structType any, queryFragment string, args Args) (bool, error)

func (*DB) InsertRecord 

func (d *DB) InsertRecord(ctx context.Context, model any) error

InsertRecord inserts a new record into the database based on the provided struct.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Insert a new user - ID and timestamps will be set automatically
user := &User{
	Name:   "C.G.B Spender",
	Email:  "[email protected]",
	Active: true,
}

err := db.InsertRecord(ctx, user)
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Inserted user with ID: %d\n", user.ID)
fmt.Printf("Active: %t\n", user.Active)

Output:

Inserted user with ID: 5
Active: true

func (*DB) Query 

func (d *DB) Query(ctx context.Context, sql string, args map[string]any) (*sql.Rows, error)

Query calls the underlying sql.DB Query method, but uses named parameters like other dbmap methods. Query returns sql.Rows, which the caller is responsible for closing.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Execute a raw query with named parameters
rows, err := db.Query(ctx, `
		SELECT name, email
		FROM users
		WHERE email LIKE $pattern AND active = $active
		ORDER BY name LIMIT 2
	`, dbmap.Args{
	"pattern": "%@xfiles.gov",
	"active":  true,
})
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
	var name, email string
	if err := rows.Scan(&name, &email); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("%s (%s)\n", name, email)
}

Output:

Dana Scully ([email protected])
Fox Mulder ([email protected])

func (*DB) Select 

func (d *DB) Select(ctx context.Context, model any, queryFragment string, args Args) error

Select executes a query and scans the result into the provided model struct or slice of structs.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Select a single user by email
var user User
err := db.Select(ctx, &user, "WHERE email = $email", dbmap.Args{
	"email": "[email protected]",
})
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Found user: %s (%s)\n", user.Name, user.Email)

// Select multiple active users
var activeUsers []User
err = db.Select(ctx, &activeUsers, "WHERE email LIKE $pattern AND active = $active ORDER BY name LIMIT 2", dbmap.Args{
	"pattern": "%@xfiles.gov",
	"active":  true,
})
if err != nil {
	log.Fatal(err)
}
fmt.Printf("Found %d active users\n", len(activeUsers))

Output:

Found user: Fox Mulder ([email protected])
Found 2 active users

func (*DB) Transaction 

func (d *DB) Transaction(ctx context.Context, fn func(tx *DB) error) error

Transaction executes the provided function within a database transaction. If the function returns an error, the transaction is rolled back, otherwise it is committed.

Transactions can not be nested at this time.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

err := db.Transaction(ctx, func(tx *dbmap.DB) error {
	// Insert a new user
	user := &User{
		Name:   "Monica Reyes",
		Email:  "[email protected]",
		Active: true,
	}
	if err := tx.InsertRecord(ctx, user); err != nil {
		return err
	}

	// Update another user in the same transaction
	_, err := tx.Update(ctx, &User{}, "WHERE email = $email", dbmap.Args{
		"email": "[email protected]",
	}, dbmap.Updates{
		"Active": false,
	})
	return err
})

if err != nil {
	log.Fatal(err)
}

fmt.Println("Transaction completed successfully")

Output:

Transaction completed successfully

func (*DB) Update 

func (d *DB) Update(ctx context.Context, structType any, queryFragment string, args Args, updates Updates) (int64, error)

Update updates records in the database based on the provided struct type, SQL fragment with named parameters, and a map of field-value pairs to update. The structType parameter should be a pointer to a struct type representing the table to update.

It returns the number of rows affected, or an error if the operation fails.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Update user status
rowsAffected, err := db.Update(ctx, &User{}, "WHERE email = $email", dbmap.Args{
	"email": "[email protected]",
}, dbmap.Updates{
	"Active": true,
})
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Activated %d users\n", rowsAffected)

Output:

Activated 1 users

func (*DB) UpdateRecord 

func (d *DB) UpdateRecord(ctx context.Context, model any, updates Updates) error

UpdateRecord updates a single record in the database based on the provided struct. The dest parameter should be a pointer to a struct of the record to update.

Example 
sqlDB, cleanup := setupDB()
defer cleanup()
db := dbmap.New(sqlDB)
ctx := context.Background()

// Get an existing user
var user User
err := db.Select(ctx, &user, "WHERE email = $email", dbmap.Args{
	"email": "[email protected]",
})
if err != nil {
	log.Fatal(err)
}

// Update the user record - UpdatedAt will be set automatically
err = db.UpdateRecord(ctx, &user, dbmap.Updates{
	"Name": "Fox Mulder (FBI Agent)",
})
if err != nil {
	log.Fatal(err)
}

fmt.Printf("Updated user: %s\n", user.Name)

Output:

Updated user: Fox Mulder (FBI Agent)

type Pluralizer 

type Pluralizer interface {
	Pluralize(word string) string
}

Pluralizer is an interface for pluralizing words

type TableNamer 

type TableNamer interface {
	TableName() string
}

TableNamer is an interface models can implement to override the default `snake_case`d, pluralized table name.

*Warning*: This value will be cached, so do not return dynamic values.

type Updates 

type Updates = map[string]any

Updates is a map of struct fields to their values for Update* methods

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.