Add support for WITH AS VALUES CTE expressions

[mikeschinkel]

What do you want to change?

One of the things that I have really wanted in sqlc is the ability to write a single query that can insert or update multiple records from data that is not already in the database, such as data coming in from an API PUT request.

I currently use a real hack with JSON in Sqlite but it requires inserting JSON to one table and then joining to the table I want to update and using the JSON functionality in Sqlite to extract the values from a JSON string. It is a complete hack, and I would like to get away from it.

I recently realized it should theoretically be easy to support my desired function with a common table expression (CTE) and a new macro.

Consider the following Sqlite SQL:

DROP TABLE IF EXISTS example;
CREATE TABLE example
(
   id     INTEGER PRIMARY KEY AUTOINCREMENT,
   name   TEXT,
   rating REAL
);
WITH data(name,rating) AS (VALUES
   ("Kleenex",4.7),
   ("Q-tips",3.9),
   ("Scotch tape",4.1),
   ("Duck tape",5.0),
   ("Band-Aids",4.2),
   ("Tupperware",3.1)
)
INSERT INTO example
(name,rating)
SELECT
   name,rating
FROM data;

I envision that the following could work if we had a sqlc.values macro, used like so:

-- name: CreateExamples :exec
WITH data(name::text,rating::real) AS (
   VALUES sqlc.values('example_data')
)
INSERT INTO example
(name,rating)
SELECT
   name,rating
FROM data;

This could generate the following Go code:

type CreateExamplesValue struct {
	Name   string  `json:"name"`
	Rating float64 `json:"rating"`
}
type CreateExamplesParams struct {
	Values []CreateExamplesValue `json:"values"`
}
const createExamples = `-- name: CreateExamples :exec
;
WITH data(name::text,rating::real) AS (
   VALUES /*VALUES:example_data*/?
)
INSERT INTO example
(name,rating)
SELECT
   name,rating
FROM data
`
func (q *Queries) CreateExamplesValues(ctx context.Context, arg CreateExamplesParams) error {
	query := createExamples
	var queryParams []interface{}
	if len(arg.Values) > 0 {
		var placeholders string
		placeholders, queryParams = sqlc.BuildValues(arg.Values)
		query = strings.Replace(query, "/*VALUES:example_data*/?", placeholders, 1)
	} else {
		query = strings.Replace(query, "/*VALUES:example_data*/?", "(NULL)", 1)
	}
	_, err := q.db.ExecContext(ctx, query, queryParams...)
	return err
}

That code would depend on this new function to be added to sqlc I named BuildValues(), shown below (this supports Sqlite, but I expect it will need to be modified to support MySQL and maybe even for Postgres):

func BuildValues[S []T, T any](values S) (placeholders string, queryParams []interface{}) {
	if len(values) == 0 {
		return "", nil
	}
	valueType := reflect.TypeOf(values[0])
	queryParams = make([]interface{}, 0, len(values)*valueType.NumField())
	for _, v := range values {
		value := reflect.ValueOf(v)
		for i := 0; i < valueType.NumField(); i++ {
			queryParams = append(queryParams, value.Field(i).Interface())
		}
	}
	placeholders = fmt.Sprintf("(%s)", strings.Repeat(",?", valueType.NumField())[1:])
	placeholders = strings.Repeat(fmt.Sprintf(",%s", placeholders), len(values))[1:]
	return placeholders, queryParams
}

I think having this would be nice for improving performance when needing to make a large number of inserts or updates, and would certainly make coding in Go easier.

Assuming you see the value in it, I would be happy to create the PR if I only knew where to start. I've taken it as far as I can but I do not know how to get sqlc to recognize the parameters in the CTE when parsing in order to turn into a struct nor how to recognize the macro when parsing. With some guidance I could possibly create a PR, if you are open to the idea.

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

No response