Add bulk insert support

Aflynn50 commented 3 months ago

SQLair currently supports insert expressions, but not bulk inserts. To insert multiple rows into a database with SQLair, users currently have to execute the same statement multiple times using a for loop or write a query that inserts a fixed number of rows by explicitly writing out each row with separate input expressions for every column.

Additionally, users may want to bulk insert where network latency needs to be minimised such as in Juju where the distributed database requires multiple trips.

Bulk inserts are supported using the same syntax currently used for INSERTS. If one or more of the types passed to Query is a slice instead of a simple value, then it becomes a bulk insert with any other values in the INSERT remaining constant.

For example:

type S struct {
    v string `db:"col1"`
}

type T struct {
   v string `db:"col4"`
}

s := sqlair.MustPrepare(
    "INSERT INTO t (col1, col2, col3, col4) VALUES ($S.*, $M.col2, "v3", $T.col4)",
    S{},
    sqlair.M{},
    T{},
)

db.Query(
    s,
    []S{{v: "v1.1"}, {v: "v1.2"}, {v: "v1.3"}},
    sqlair.M{"col2": "v2"},
    []T{{v: "v4.1"}, {v: "v4.2"}, {v: "v4.3"}},
)

The query sent to the DB from the above example will be:

INSERT INTO  t (col1, col2, col3, col4) VALUES (@sqlair_0, @sqlair_3, "v3", @sqalir_4), (@sqlair_1, @sqlair_3, "v3", @sqalir_5), (@sqlair_2, @sqlair_3, "v3", @sqalir_6)

Where the placeholders are: Name	Value
sqlair_0	v1.1
sqlair_1	v1.2
sqlair_2	v1.3
sqlair_3	v2
sqlair_4	v4.1
sqlair_5	v4.2
sqlair_6	v4.3

Note that SQLair will check that all the slices passed to Query for bulk INSERTS are of the same cardinality, else throw out an error.

SimonRichardson commented 3 months ago

Out of curiosity more than anything. From the PR description, the SQL sent to the driver is:

INSERT INTO  t (col1, col2, col3, col4) VALUES (@sqlair_0, @sqlair_1, "v3", @sqalir_2), (@sqlair_3, @sqlair_4, "v3", @sqalir_5), (@sqlair_6, @sqlair_7, "v3", @sqalir_8)

Yet, col2, will always be a map for a constant value, so the query could be turned into?

INSERT INTO  t (col1, col2, col3, col4) VALUES (@sqlair_0, @sqlair_1, "v3", @sqalir_2), (@sqlair_3, @sqlair_1, "v3", @sqalir_4), (@sqlair_5, @sqlair_1, "v3", @sqalir_6)

Aflynn50 commented 3 months ago

Out of curiosity more than anything. From the PR description, the SQL sent to the driver is:
INSERT INTO  t (col1, col2, col3, col4) VALUES (@sqlair_0, @sqlair_1, "v3", @sqalir_2), (@sqlair_3, @sqlair_4, "v3", @sqalir_5), (@sqlair_6, @sqlair_7, "v3", @sqalir_8)
Yet, col2, will always be a map for a constant value, so the query could be turned into?
INSERT INTO  t (col1, col2, col3, col4) VALUES (@sqlair_0, @sqlair_1, "v3", @sqalir_2), (@sqlair_3, @sqlair_1, "v3", @sqalir_4), (@sqlair_5, @sqlair_1, "v3", @sqalir_6)

Thats a good point, no reason not to do this. I've changed the code and updated the PR description to reflect this.

Aflynn50 commented 3 months ago

I'd love to see a full end to end test added to package_test.go that baked in the bulk insert changes.

I think the more common approach that I regularly want to use bulk insert in sqlair for is the following:
type S struct {
    a string `db:"col1"`
    b string `db:"col2"`
    c string `db:"col3"`
    d string `db:"col4"`
}

s := sqlair.MustPrepare(
    "INSERT INTO t (col1, col2, col3, col4) VALUES ($S.*)",
    S{},
)

db.Query(
    s,
    []S{
        {a: "a1", b: "b1", c: "c1", d: "d1"}, 
        {a: "a2", b: "b2", c: "c2", d: "d2"},
    },
)
Name Value sqlair_0 "a1" sqlair_1 "b1" sqlair_2 "c1" sqlair_3 "d1" sqlair_4 "a2" sqlair_5 "b2" sqlair_6 "c2" sqlair_7 "d2"

Good point, I'd been meaning to add something to package_test but had forgotten. I've added a test now that has a simple case and a more complex case of a bulk insert.

canonical / sqlair

Add bulk insert support #145