SQL View / API Versioning Strategy

[egtann]

Hi there :)

I'm trying to build an HTTP API around sqlc's generated types. I'd like to add versioning to this API, so my strategy has been to create one package per version, such as this:

{
        "version": 1,
        "packages": [{
                "path": "storeV1",
                "queries": "v1_queries"
        }, {
                "path": "storeV2",
                "queries": "v2_queries"
        }]
}

Each file in the versioned queries pulls from a different view which never changes, such as:

-- name: GetPlayer :one
SELECT * FROM v1_players WHERE id=$1;

-- name: CreatePlayer :one
INSERT INTO v1_player_passwords (
        email, password
) VALUES (
        $1, $2
)
RETURNING id, email, created_at;

-- name: UpdatePlayer :one
UPDATE v1_players
SET email=$1
WHERE id=$2
RETURNING *;

-- name: DeletePlayer :exec
DELETE FROM v1_players WHERE id=$1;

This way I don't need to write any manual structs/conversion logic to keep the HTTP API consistent as the underlying DB changes, since the views are now versioned and static.

The only downside is that sqlc outputs the V1 in the name of the structs itself, so I wind up needing to use:

import ".../postgres/storeV1"

storeV1.V1Player

The V1 on V1Player is redundant, as the versioning is happening at the package level. I can remove this using sed in my Makefile after the code is generated, but it's a little tricky since sqlc also generates unused structs in models.go, outputting the never-used Player in addition to V1Player, so trimming V1 from all struct names results in a duplicate struct definition which requires additional work to remove.

I was wondering if this isn't a common use-case? Is it best to put all these different versions into the same sqlc package and separate them via different function names, like -- name: GetPlayerV1 :one instead? How are others solving this?

Would it be a valuable addition/workflow to sqlc directly to handle versioning as a special case, or is this too unique to my setup and not widely useful? Alternatively, even just having a setting for sqlc to not emit structs for unused tables would make the post-processing sed step pretty straightforward.

[egtann]

For those who arrive at this thread in the future, my solution for now has been to put everything in a single package and prefix functions with the version, such as V1GetPlayer, but I'd be very interested if others have different or better approaches.