Update configuration file to support different Go database drivers

[kyleconroy]

sqlc has two versions of its configuration formats. Version 1 is widely used. Version 2 hasn't been, as it's only useful for people doing multi-language development. The proposed changes are backwards compatible for both versions.

A little bit of context: if you're using the database/sql package, you need to pick a driver for your database. When a driver is imported, it registers itself using sql.Register. As the docs say:

If Register is called twice with the same name or if driver is nil, it panics.

It's also important to realize that pgx and other database drivers encourage users to avoid the database/sql package. As with all code generated by sqlc, this decision is left up to the user. Our design should support a future where sqlc can generate code using these alternative interfaces.

Proposal

version: "1"
packages:
  - name: "authors"
    path: "internal/db"
    queries: "query.sql"
    schema: "schema.sql"
    interface: 
      stdlib:
        driver: "postgres"

version: "2"
sql:
  - queries: "query.sql"
    schema: "schema.sql"
    engine: "postgresql"
    gen:
      go:
        package: "authors"
        out: "."
        interface: 
          stdlib:
            driver: "postgres"

When the engine is postgresql, valid values for driver are postgres and pgx.

When the engine is mysql, valid values for driver are mysql and go-mysql.

When we add support for using pgx directly, we'd add a new key to interface

version: "1"
packages:
  - name: "authors"
    path: "internal/db"
    queries: "query.sql"
    schema: "schema.sql"
    interface: 
      pgx:
        version: "4"

Appendix

Engine	Driver Name	Package	Default
postgresql	postgres	github.com/lib/pq	✅
postgresql	pgx	github.com/jackc/pgx
mysql	mysql	github.com/go-sql-driver/mysql	✅
mysql	go-mysql	github.com/siddontang/go-mysql
sqlite	sqlite3	github.com/mattn/go-sqlite3	✅

Related to #472

[kevinburke]

version: "4" kinda commits yourself to supporting different versions of pgx and I'm not sure you want to do that.

[inconshreveable]

i get the attractiveness of making each of these their own separate object, but the nesting does make the config harder to read as a human. can the nesting be reduced without sacrificing too much flexibility?

- name: "authors"
  ...
  interface: "stdlib" | "pgx/4"
  driver: "pq" | "pgx/4"

does the config need a defaults section? seems cumbersome to add those annotations to every package definition

[kyleconroy]

version: "4" kinda commits yourself to supporting different versions of pgx and I'm not sure you want to do that.

I'm not sure what choice we have. If (and when) pgx launches v5, there are going to be users that want to upgrade, and users that want to stay on v4.

an the nesting be reduced without sacrificing too much flexibility?

If the only data we needed was driver and interface, sure. I'm imagining that each interface will eventually have its own configuration options. The reason I don't want to add driver as a top-level configuration is that it doesn't apply outside the context of the database/sql package.

[kyleconroy]

does the config need a defaults section?

Version 2 of the configuration file has a top-level gen section you can use.

version: "2"

# Individual SQL packages
sql:
  - queries: "query.sql"
    schema: "schema.sql"
    engine: "postgresql"
    gen:
      go:
        package: "authors"
        out: "."

# Defaults for all Go packages
gen:
  go:
    interface: 
      stdlib:
        driver: "postgres"