feat: add type-safe Go code generation from migration schema

[mfridman]

Opening this issue in case this might be interesting to others. Feeback welcome.

Extend goose to support type-safe Go code generation from SQL queries using the schema information from migration files.

Note that it's very possible this feature is out-of-scope for the goose project. But at the very least I want to track this request to see where the community stands and whether it's worth pursuing (it's been brought up by a few folks).

goose generate --dir=./sql/migrations --queries=./sql/queries

The idea is to add the ability to read SQL queries from a directory alongside the existing migrations directory, where developers can define their SQL queries that'll be converted to type-safe Go code.

Example

./sql
├── migrations/          # Existing migrations directory
│   ├── 00001_create_users.sql
│   └── 00002_add_user_roles.sql
└── queries/               # New queries directory
    ├── users_read.sql     # Read-only operations
    └── users_write.sql    # Write operations

One property I'd like to have, either through file naming conventions or an opinionated interface, is the ability to separate read-only vs read/write queries, which has benefits like using read-only database replicas out of the box.

Motivation

• goose already understands the database schema through migration files
• By adding support for a queries directory, we can provide type-safe query generation while maintaining goose's simple, file-based approach
• Reduces the need for separate tools in the development workflow
• Keeps SQL queries close to the schema that defines them

Extras

• I want some form of dynamic query building. There's a handful of extremely common (and dynamic) SQL boilerplate like WHERE predicates with ORDER BY
• Support only sql/db and pgx/v5 for postgres

... will update this issue as this gets flushed out a bit more

Related Projects

• sqlc (for reference on query syntax and code generation)