generate compatible interfaces for exchangeable sql dialects

[torbenschinke]

It is a bit related to #444 but not exactly: I wonder, how you plan to exchange the sql dialect implementation e.g. postgresql vs mysql vs sqlite? A similar discussion related around code generation can be found also at golang/go#8082.

possible solutions:

• A partial solution may be to generate anonymous structs or interfaces or
• to extract equal structs and interfaces into a common package

How to solve it?

[torbenschinke]

To elaborate that with an example:

mysql schema and query:

CREATE TABLE user (
  id   binary(16) PRIMARY KEY,
  firstname varchar(255) NOT NULL,
  lastname varchar(255),
  login  varchar(255) NOT NULL
);

/* name: ListUsers :many */
SELECT * FROM user;

/* name: CreateUser :exec */
INSERT INTO user (id,firstname,lastname,login) VALUES (?,?,?,?);

/* name: DeleteUser :exec */
DELETE FROM user WHERE id = ?;

postgresql schema and query

CREATE TABLE "user" (
  id   UUID PRIMARY KEY,
  firstname varchar(255) NOT NULL,
  lastname varchar(255),
  login  varchar(255) NOT NULL
);

-- name: ListUsers :many
SELECT * FROM "user";

-- name: CreateUser :exec
INSERT INTO "user" (id,firstname,lastname,login) VALUES ($1,$2,$3,$4);

-- name: DeleteUser :exec
DELETE FROM "user" WHERE id = $1;

Expected to work:

	var pgsUsers postgresql.Querier
	pgsUsers = postgresql.New(db)

	var mysqlUsers mysql.Querier
	mysqlUsers = mysql.New(db)

	pgsUsers = mysqlUsers // <- not assignable

Quickfix:

...
type User = struct { // alias instead of type User struct
...

type CreateUserParams = struct { // alias instead of type CreateUserParams struct
...

[kyleconroy]

how you plan to exchange the sql dialect implementation e.g. postgresql vs mysql vs sqlite?

You're actually the first person to ask this question! And it's a good one.

I think there's two ways sqlc can support this going forward. The first is enforcing a strict subset of SQL such that your queries do not need to be modified to run on a different database engine. While a laudable goal, I don't think in practice this would be very helpful, especially if you're including SQLite.

The second is to borrow from protocol buffers and have some concept of a service definition with structs. The difficult part here is that sqlc currently doesn't require defining input and output structs for queries; those are inferred automatically. We'd need to either define those structs up front, or have an option to mark two different packages as implementing the same interface and return an error when queries of the same name do not match the expected types.

Notice in your example that the user structs for MySQL and PostgreSQL aren't the same; the ID columns are different types.

[torbenschinke]

Notice in your example that the user structs for MySQL and PostgreSQL aren't the same; the ID columns are different types.

Well, I used your great package override option to solve that ;-)

...
 "overrides": [
        {
          "column": "user.id",
          "go_type": "github.com/google/uuid.UUID"
        }

And now, if I set the type-def of the generated structs to aliases by hand, the go compiler is happy to assign them (and the interfaces) vice-versa, as long as I can ensure that the generated (anonymous) structs have identical fields (e.g. per override).

What do you think about a generator option, to create aliases instead of structs? It is clear, that there is no guarantee that generated things will match, but at least the compiler will let me know...