sqlmap

README

sqlmap

Convert a native go datatype to and from its sql null datatype equivalent.

Why does this exist?

Recently I started using a tool called sqlc as a pseudo-ORM in one of my projects. Its a great tool but it has a couple of quirks. This package addresses one of those quirks.

If your database schema contains a null value, the generated code requires you to transform a pointer value to a sql.NulXYZ type.

    // We have a string pointer but need a sql.NullString type.
    var biz *string

    str := sql.NullString{}

    if biz != nil {
		str.String = *in
		str.Valid = true
	}

    // the str var is only initialized if biz is not null.

    // insertRowInDatabase(context.Context, sql.NullString)
    err := insertRowInDatabase(ctx, str)

This package provides helper code to enable you to do the conversion inline.

    // We have a string pointer but need a sql.NullString type.
    var biz *string

    // insertRowInDatabase(context.Context, sql.NullString) (sql.NullString, error)
    val, _ := insertRowInDatabase(ctx, sqlmap.ToNullString(biz))

	// It also allows you to "unwrap" the resultant `sql.NulXYZ` value to make it actually usable.
	biz = sqlmap.UnwrapString(val)

Example

Say you have a database schema with a null column like so, and would like to insert some data into the table.

-- schema.sql

CREATE TABLE foo (
	bar VARCHAR(128) NULL,
    biz VARCHAR(400) NOT NULL
);

-- query.sql

-- Insert a Foo into the foo table.
-- name: CreateFoo :one
INSERT INTO foo (
	bar,
    biz
) VALUES (
	$1,
	$2
)

This will generate some code:

// models.go

type Foo struct {
	Bar sql.NullString
	Biz string
}

// query.sql.go

const createFoo = `-- name: CreateFoo :one
INSERT INTO foo (
	bar,
    biz
) VALUES (
	$1,
	$2
)
RETURNING bar, biz
`

type CreateFooParams struct {
	Bar sql.NullString
	Biz string
}

// Insert a Foo into the foo table.
func (q *Queries) CreateFoo(ctx context.Context, arg CreateFooParams) (Foo, error) {
	row := q.db.QueryRowContext(ctx, createFoo, arg.Bar, arg.Biz)
	var i Foo
	err := row.Scan(&i.Bar, &i.Biz)
	return i, err
}

Use sqlmap inline with your params to avoid boilerplate code. Super useful for CRUD operations where you need to map a request to your datastores representation.

func main() {
    var db *sql.db
    // Assume you have connected to db.

    queries := datastore.New(db)

    var biz *string

	foo, err := queries.CreateFoo(ctx, datastore.CreateFooParams{
		Bar: sqlmap.NullString(biz),
		Biz: "This is biz",
	})

	// Note: foo.Bar is of type sql.NullString, which isn't really useful to us. 
	// So we make use of the unwrap function to convert it to a more canonical go datatype.
	biz = sqlmap.UnwrapString(foo.Bar)
}

License

This program is released under the GNU Lesser General Public License v3 or later.

Documentation

Index

• func NullBoolean[T bool | *bool](b T) sql.NullBool
• func NullByte[T byte | *byte](b T) sql.NullByte
• func NullFloat64[T float64 | *float64](f T) sql.NullFloat64
• func NullInt16[T int16 | *int16](i T) sql.NullInt16
• func NullInt32[T int32 | *int32](i T) sql.NullInt32
• func NullInt64[T int64 | *int64](i T) sql.NullInt64
• func NullString[T string | *string](s T) sql.NullString
• func NullTime[T time.Time | *time.Time](t T) sql.NullTime
• func NullTimeFromTimestamp(t *timestamppb.Timestamp) sql.NullTime
• func NullUUID[T uuid.UUID | *uuid.UUID](id T) uuid.NullUUID
• func Serial(in string) (uint64, error)
• func UnwrapBoolean(b sql.NullBool) *bool
• func UnwrapByte(b sql.NullByte) *byte
• func UnwrapFloat64(f sql.NullFloat64) *float64
• func UnwrapInt16(i sql.NullInt16) *int16
• func UnwrapInt32(i sql.NullInt32) *int32
• func UnwrapInt64(i sql.NullInt64) *int64
• func UnwrapString(s sql.NullString) *string
• func UnwrapTime(t sql.NullTime) time.Time
• func UnwrapTimePtr(t sql.NullTime) *time.Time
• func UnwrapTimestamp(t sql.NullTime) *timestamppb.Timestamp
• func UnwrapUUID(id uuid.NullUUID) uuid.UUID
• func UnwrapUUIDPtr(id uuid.NullUUID) *uuid.UUID

Functions

func NullBoolean

func NullBoolean[T bool | *bool](b T) sql.NullBool

NullBoolean converts the native go boolean type to a sql.NullBool type.

func NullByte

func NullByte[T byte | *byte](b T) sql.NullByte

ToNullByte converts the native go byte type to a sql.NullByte type.

func NullFloat64

func NullFloat64[T float64 | *float64](f T) sql.NullFloat64

NullFloat64 converts the native go float64 type to a sql.NullFloat64 type.

func NullInt16

func NullInt16[T int16 | *int16](i T) sql.NullInt16

NullInt16 converts the native go int16 type to a sql.NullInt16 type.

func NullInt32

func NullInt32[T int32 | *int32](i T) sql.NullInt32

NullInt32 converts the native go int32 type to a sql.NullInt32 type.

func NullInt64

func NullInt64[T int64 | *int64](i T) sql.NullInt64

NullInt64 converts the native go int64 type to a sql.NullInt64 type.

func NullString

func NullString[T string | *string](s T) sql.NullString

NullString converts the native go string type to a sql.NullString type.

func NullTime

func NullTime[T time.Time | *time.Time](t T) sql.NullTime

NullTime converts an time pointer to a sql NullTime type. This function will treats the Zero time as valid.

func NullTimeFromTimestamp

func NullTimeFromTimestamp(t *timestamppb.Timestamp) sql.NullTime

NullTimeFromTimestamp converts an timestamppb.Timestamp pointer to a sql.NullTime type. This will save you a conversion when dealing with gRPC requests.

func NullUUID

func NullUUID[T uuid.UUID | *uuid.UUID](id T) uuid.NullUUID

NullUUID converts a google UUID to a uuid.NullUUID type.

func Serial

func Serial(in string) (uint64, error)

Serial is a notational convenience for creating unique identifier columns. It is an auto-incrementing integer starting from zero. https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

func UnwrapBoolean

func UnwrapBoolean(b sql.NullBool) *bool

UnwrapBoolean unwraps the sql.NullBool to a boolean pointer.

func UnwrapByte

func UnwrapByte(b sql.NullByte) *byte

UnwrapByte unwraps the sql.NullByte to a byte pointer.

func UnwrapFloat64

func UnwrapFloat64(f sql.NullFloat64) *float64

UnwrapFloat64 unwraps the sql.NullFloat64 to a float64 pointer.

func UnwrapInt16

func UnwrapInt16(i sql.NullInt16) *int16

UnwrapInt16 unwraps the sql.NullInt16 to a int16 pointer.

func UnwrapInt32

func UnwrapInt32(i sql.NullInt32) *int32

UnwrapInt32 unwraps the sql.NullInt32 to a int32 pointer.

func UnwrapInt64

func UnwrapInt64(i sql.NullInt64) *int64

UnwrapInt64 unwraps the sql.NullInt64 to a int64 pointer.

func UnwrapString

func UnwrapString(s sql.NullString) *string

UnwrapString unwraps the sql null string to a string pointer.

func UnwrapTime

func UnwrapTime(t sql.NullTime) time.Time

UnwrapTime unwraps the sql null time to a time.Time struct. If the value is null the function will return an empty time.Time struct.

func UnwrapTimePtr

func UnwrapTimePtr(t sql.NullTime) *time.Time

UnwrapTimePtr unwraps the sql null time to a time.Time pointer.

func UnwrapTimestamp

func UnwrapTimestamp(t sql.NullTime) *timestamppb.Timestamp

UnwrapTime unwraps the sql null time to a timestamppb.Timestamp pointer.

func UnwrapUUID

func UnwrapUUID(id uuid.NullUUID) uuid.UUID

UnwrapUUID unwraps the sql null UUID to a uuid.UUID struct. If the value is null the function will return an empty uuid.UUID struct. Use UnwrapUUIDPointer() if you want a pointer value.

func UnwrapUUIDPtr

func UnwrapUUIDPtr(id uuid.NullUUID) *uuid.UUID

UnwrapUUIDPtr unwraps the sql null UUID to a uuid.UUID pointer.