db

README

debefix-db - Database and SQL handling for debefix

debefix is a Go library to seed database data and/or create fixtures for DB tests.

This module allows publishing fixtures to databases, including generating SQL statements.

Install

go get -u github.com/rrgmc/debefix-db/v2

Example

import (
    "context"
    "os"

    "github.com/rrgmc/debefix-db/v2/sql"
    "github.com/rrgmc/debefix-db/v2/sql/postgres"
    "github.com/rrgmc/debefix/v2"
)

func ExampleResolve() {
    ctx := context.Background()
    data := debefix.NewData()

    tableTags := debefix.TableName("public.tags")
    tableUsers := debefix.TableName("public.users")
    tablePosts := debefix.TableName("public.posts")
    tablePostTags := debefix.TableName("public.post_tags")

    data.AddValues(tableTags,
        debefix.MapValues{
            // this field value will be generated at resolve time, for example as a database autoincrement.
            "tag_id": debefix.ResolveValueResolve(),
            // sets the RefID to be targeted by `ValueRefID(tableTags, "go", "tag_id")`. Field is not added to the row values.
            "_refid": debefix.SetValueRefID("go"),
            "name":   "Go",
            // set the value as the resolver base time plus 1 hour.
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1)),
        },
        debefix.MapValues{
            "tag_id":     debefix.ResolveValueResolve(),
            "_refid":     debefix.SetValueRefID("javascript"),
            "name":       "JavaScript",
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1), debefix.WithAddMinutes(2)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1), debefix.WithAddMinutes(2)),
        },
        debefix.MapValues{
            "tag_id":     debefix.ResolveValueResolve(),
            "_refid":     debefix.SetValueRefID("cpp"),
            "name":       "C++",
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1), debefix.WithAddMinutes(2)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(1), debefix.WithAddMinutes(2)),
        },
    )

    data.AddValues(tableUsers,
        debefix.MapValues{
            "user_id": 1, // fixed ID, not generated by the database
            // sets the RefID to be targeted by `ValueRefID(tableUsers, "johndoe", "user_id")`. Field is not added to the row values.
            "_refid":     debefix.SetValueRefID("johndoe"),
            "name":       "John Doe",
            "email":      "john@example.com",
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddMinutes(30)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddMinutes(30)),
        },
        debefix.MapValues{
            "user_id":    2,
            "_refid":     debefix.SetValueRefID("janedoe"),
            "name":       "Jane Doe",
            "email":      "jane@example.com",
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddMinutes(30)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddMinutes(30)),
        },
    )

    // AddWithID returns an internal ID reference that can be used later.
    post1IID := data.AddWithID(tablePosts,
        debefix.MapValues{
            "post_id": debefix.ValueUUIDRandom(), // generates a random UUID value.
            "title":   "First post",
            "text":    "This is the text of the first post",
            // returns the value of the "user_id" field of the row with the "johndoe" RefID in the tableUsers table.
            // this also adds a dependency between "posts" and "users", so "users" will always be resolved before "posts".
            "user_id":    debefix.ValueRefID(tableUsers, "johndoe", "user_id"),
            "created_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(2)),
            "updated_at": debefix.ValueBaseTimeAdd(debefix.WithAddHours(2)),
        })
    post2IID := data.AddWithID(tablePosts,
        debefix.MapValues{
            "post_id": debefix.ValueUUIDRandom(), // generates a random UUID value.
            // data is inserted in the order they were added, so it is possible to reference another row in the same table.
            "parent_post_id": post1IID.ValueForField("post_id"),
            "title":          "Second post",
            "text":           "This is the text of the second post",
            "user_id":        debefix.ValueRefID(tableUsers, "johndoe", "user_id"),
            "created_at":     debefix.ValueBaseTimeAdd(debefix.WithAddHours(2)),
            "updated_at":     debefix.ValueBaseTimeAdd(debefix.WithAddHours(2)),
        },
    )

    data.AddValues(debefix.TableName(tablePostTags),
        debefix.MapValues{
            // returns the value of the "post_id" field of the row referenced by "post1IID", after it was resolved.
            "post_id": post1IID.ValueForField("post_id"),
            // tag_id is generated by the database, so the value will be resolved before being set here.
            "tag_id": debefix.ValueRefID(tableTags, "go", "tag_id"),
        },
        debefix.MapValues{
            "post_id": post1IID.ValueForField("post_id"),
            "tag_id":  debefix.ValueRefID(tableTags, "cpp", "tag_id"),
        },
        debefix.MapValues{
            // returns the value of the "post_id" field of the row referenced by "post2IID", after it was resolved.
            "post_id": post2IID.ValueForField("post_id"),
            "tag_id":  debefix.ValueRefID(tableTags, "javascript", "tag_id"),
        },
    )

    if data.Err() != nil {
        panic(data.Err())
    }

    // outputs all generated queries
    qi := sql.NewDebugQueryInterface(os.Stdout)

    // resolve the rows using a SQL query resolver.
    _, err := debefix.Resolve(ctx, data,
        postgres.ResolveFunc(qi))
    if err != nil {
        panic(err)
    }

    // =============== public.tags ===============
    // INSERT INTO "public.tags" ("created_at", "name", "updated_at") VALUES ($1, $2, $3) RETURNING "tag_id"
    // $$ ARGS: [0:"2024-11-29 08:30:16.028185 -0300 -03 m=+3600.002859876"] [1:"Go"] [2:"2024-11-29 08:30:16.028185 -0300 -03 m=+3600.002859876"]
    // --------------------INSERT INTO "public.tags" ("created_at", "name", "updated_at") VALUES ($1, $2, $3) RETURNING "tag_id"
    // $$ ARGS: [0:"2024-11-29 08:32:16.028185 -0300 -03 m=+3720.002859876"] [1:"JavaScript"] [2:"2024-11-29 08:32:16.028185 -0300 -03 m=+3720.002859876"]
    // --------------------INSERT INTO "public.tags" ("created_at", "name", "updated_at") VALUES ($1, $2, $3) RETURNING "tag_id"
    // $$ ARGS: [0:"2024-11-29 08:32:16.028185 -0300 -03 m=+3720.002859876"] [1:"C++"] [2:"2024-11-29 08:32:16.028185 -0300 -03 m=+3720.002859876"]
    // =============== public.users ===============
    // INSERT INTO "public.users" ("created_at", "email", "name", "updated_at", "user_id") VALUES ($1, $2, $3, $4, $5)
    // $$ ARGS: [0:"2024-11-29 08:00:16.028185 -0300 -03 m=+1800.002859876"] [1:"john@example.com"] [2:"John Doe"] [3:"2024-11-29 08:00:16.028185 -0300 -03 m=+1800.002859876"] [4:"1"]
    // --------------------INSERT INTO "public.users" ("created_at", "email", "name", "updated_at", "user_id") VALUES ($1, $2, $3, $4, $5)
    // $$ ARGS: [0:"2024-11-29 08:00:16.028185 -0300 -03 m=+1800.002859876"] [1:"jane@example.com"] [2:"Jane Doe"] [3:"2024-11-29 08:00:16.028185 -0300 -03 m=+1800.002859876"] [4:"2"]
    // =============== public.posts ===============
    // INSERT INTO "public.posts" ("created_at", "post_id", "text", "title", "updated_at", "user_id") VALUES ($1, $2, $3, $4, $5, $6)
    // $$ ARGS: [0:"2024-11-29 09:30:16.028185 -0300 -03 m=+7200.002859876"] [1:"91a27c77-ff00-4f3b-90d6-51e335b7ad36"] [2:"This is the text of the first post"] [3:"First post"] [4:"2024-11-29 09:30:16.028185 -0300 -03 m=+7200.002859876"] [5:"1"]
    // --------------------INSERT INTO "public.posts" ("created_at", "parent_post_id", "post_id", "text", "title", "updated_at", "user_id") VALUES ($1, $2, $3, $4, $5, $6, $7)
    // $$ ARGS: [0:"2024-11-29 09:30:16.028185 -0300 -03 m=+7200.002859876"] [1:"91a27c77-ff00-4f3b-90d6-51e335b7ad36"] [2:"c06a1f3e-3578-4b56-bf51-9fb949ae5dbf"] [3:"This is the text of the second post"] [4:"Second post"] [5:"2024-11-29 09:30:16.028185 -0300 -03 m=+7200.002859876"] [6:"1"]
    // =============== public.post_tags ===============
    // INSERT INTO "public.post_tags" ("post_id", "tag_id") VALUES ($1, $2)
    // $$ ARGS: [0:"91a27c77-ff00-4f3b-90d6-51e335b7ad36"] [1:"223eca8c-d2c3-46ef-bb1f-d175916c97a2"]
    // --------------------INSERT INTO "public.post_tags" ("post_id", "tag_id") VALUES ($1, $2)
    // $$ ARGS: [0:"91a27c77-ff00-4f3b-90d6-51e335b7ad36"] [1:"d5c6e911-f38e-4cef-958b-cd5d8fc787f2"]
    // --------------------INSERT INTO "public.post_tags" ("post_id", "tag_id") VALUES ($1, $2)
    // $$ ARGS: [0:"c06a1f3e-3578-4b56-bf51-9fb949ae5dbf"] [1:"bd2d497f-af58-4338-a040-a24002492436"]
}

License

MIT

Author

Rangel Reale (rangelreale@gmail.com)

Documentation

Index

• func ResolveFunc(callback ResolveDBCallback) debefix.ResolveCallback
• type ResolveDBCallback
• type ResolveDBInfo

Functions

func ResolveFunc

func ResolveFunc(callback ResolveDBCallback) debefix.ResolveCallback

ResolveFunc is a debefix.ResolveCallback helper to generate database records.

Types

type ResolveDBCallback

type ResolveDBCallback func(ctx context.Context, resolveInfo ResolveDBInfo, fields map[string]any,
	returnFields map[string]debefix.ResolveValue) (returnValues map[string]any, err error)

ResolveDBCallback will be called for each table row to be inserted. fields are the fields to be inserted. returnFieldNames are the fields whose values are expected to be returned in the return map. Their values may contain a resolve info if sent by the caller.

type ResolveDBInfo

type ResolveDBInfo struct {
	Type            debefix.ResolveType
	TableID         debefix.TableID
	UpdateKeyFields []string
}

ResolveDBInfo is the context for one database resolve operation.