sqlxgen
is a tool to generate sqlx compatible code from database schema and sql queries in your project.
- Run postgres query or mysql query to generate model code.
- Run your query without selecting any rows to generate model code for the output and input structs.
- support for postgres and mysql databases.
brew install aakash-rajur/tap/sqlxgen
checkout releases here
go install -v github.com/aakash-rajur/sqlxgen/cmd/sqlxgen@latest
- generate
sqlxgen.yml
file in your project root directory with the following command.
sqlxgen init
- edit
sqlxgen.yml
file to suit your needs. examplehost
takes precedence overurl
.- with
host
other parameters can be omitted. - just provide
url
if you want to use it as is. - default connection for postgres is
postgres://postgres:@localhost:5432/postgres?sslmode=disable
- default connection for mysql is
root:@tcp(localhost:3306)/mysql?parseTime=true
# expand env vars, host takes precedence over url
# url: postgres://u:p@h:5432/db?sslmode=disable
# host: h1
# result: postgres://postgres:@h1:5432/db?sslmode=disable
- generate table model and query model code with the following command. Picks
sqlxgen.yml
from current directory.
sqlxgen generate [--config <path-to-config-file>]
example can be found here
following movies table generates movies.gen.go
erDiagram
movies {
text title
text original_title
text original_language
text overview
integer runtime
date release_date
text tagline
text status
text homepage
double precision popularity
double precision vote_average
integer vote_count
bigint budget
bigint revenue
text[] keywords
tsvector title_search
tsvector keywords_search
bigint id
}
following query generates get_actor.gen.go
select
a."id" as "id",
a."name" as "name",
coalesce(
(
select
jsonb_agg(
jsonb_build_object(
'id', ma.movie_id,
'title', m.title,
'releaseDate', m.release_date,
'character', ma.character
) order by m.release_date desc
)
from movies_actors ma
inner join movies m on ma.movie_id = m.id
where true
and ma.actor_id = a.id
),
'[]'
) as "movies"
from actors a
where a.id = :id; -- :id type: bigint
::
type casting is broken in sqlx- parameters are required to be annotated with typings
-- :param_name type: <param type>
as such,
select
u.*
from users u
where true
and id = :user_id; -- :user_id type: bigint
- otherwise, they'll have
interface{}
type and will not be type safe. - json (
json
orjsonb
) column selects required to annotated with-- column: <column_name> json_type: <array | object>
as such,
select
u.id,
u.metadata -- column: metadata json_type: object
from users u
where true
and id = :user_id; -- :user_id type: bigint
- otherwise, they'll have
json.RawMessage
type. json[b]_agg
,json[b]_build_object
,json_arrayagg
,json_objectagg
,json_object
and etc are taken care of ref
- age-old sql generation vs sql debate, i prefer writing sql queries over sql generation. (eat your greens!)
- sqlx expects provides excellent support for writing named queries and mapping query results to structs. Writing struct for each one of my tables and queries is tedious and error prone.
- sqlc generates models for table and queries but has the following challenges:
- dumps all generated code in a single place, not allowing me to organize my code more contextually.
- does not introspect my queries through database unless I type cast my selects explicitly.
- introduces sqlc syntax for writing queries, which is not sql. Fine in most cases but if i want to run that query in my database client, i have to rewrite it.
- does not generate crud operations for my tables.
Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.
Please feel free to open an issue if you have any feedback or suggestions.