sqlx bridge

[jeromer]

(Related issue : jmoiron/sqlx#193)
(Related commit : https://github.com/jeromer/pgx/commit/5097a8100cb350853e1aa8bcf05787ff41c69216)

When using certain postgres type, like int arrays sqlx fails to scan what
postgres returns probably because it does not seem to call pgx's scanner but
database/sql's instead.

The problem is that when you want to use certain postgres types there is
actually no way to use sqlx and pgx at the same type. Either you use pgx and
all the scanning works fine but you can not use Struct tags. Or you use
sqlx with Struct tags but limit yourself to what sqlx (and eventually
database/sql) can scan.

I tried to modify sqlx so that it calls pgx's scanner but it fails
miserably. So I tried to modify pgx instead by providing a StructScan
method which can be used like this:

var s = new(MyStruct)
db.QueryRow(someSql).StructScan(&s)

StructScan only acts as a proxy to pgx.Scan so no Scan methods were
modified.

There is a test file in cmd/main.go just modifiy postgres credential to match
your test environnent and run go run main.go.

@jackc and @jmoiron I'd love to get your feedback on this.

Please note that I dit not touch Go for around 3 years and working back with Go
after 3 years of Erlang is quite a cultural change so my modifications may be
completely horrible or not idiomatic go.

sqlx and pgx are both great libs. I am sure providing some kind of bridge
between those libs would be beneficial to a lot of people.

Thanks for reading

:)

[jackc]

I don't have the experience with sqlx to know the best way to bridge the gap between these libraries. However, if I understand correctly part of the problem is incompatibilities between database/sql types expecting text protocol values, and pgx expecting binary protocol values. This should be mitigated in a future v3 of pgx. v3 has an entirely new type system that supports an even wider array of types in binary and text protocols. These types also implement the database/sql interfaces so they can be used anywhere database/sql is used, even with non-pgx PostgreSQL drivers. This may resolve the incompatibilities.

https://github.com/jackc/pgx/tree/v3/pgtype

[sachin-walia]

@jackc - What I've read so far about pgx really made me very much interested in using it. I have two requirements that I couldn't figure out if pgx supports it already:

• named parameter support such as :username, :email
• directly scanning rows into a struct
  I am at present using sqlx with pg to support these but would be very interested in using pgx if it supports it natively.
  If it is not supported already are these items on your roadmap?

[james-lawrence]

directly scanner rows into a struct should not be handle in the driver imo.

[iorlas]

Same problem with types. I need to use slices, jsonb, nulls. sqlx has few basic null types, that's all. So I'm forced to use naked pgx. But I would like to use sqlx because of things like StructScan. If pgx had StructScan, it could postpone problem.

[jackc]

I don't think that struct mapping belongs in the driver directly -- though maybe it could fit as a sub-package. I don't have any plans for doing it myself though. And if I did do something it would probably run more along code generation for struct mapping than reflection.

Regarding sqlx and more advanced types, the v3 beta supports even more PostgreSQL types than the present stable v2 and those types implement the database/sql interfaces. So they should be usable with sqlx (or even other PostgreSQL drivers). Haven't tried it with sqlx, but given it is just uses the standard database/sql interface I don't see why it would not work.

[iorlas]

@jackc Agree with you. It is not a problem driver should solve. But! PGX is considered to be used directly, without any sql builder, mapper. But anyway, it does not mean that it should handle mapping at all. I gonna write some easy sql builder and mapper on the top of pgx, but as separated project.

The bad this is, we already have many and many libraries around: sqlx, sql, gorm, pop, upper.io/db, etc. But they are compatible only on basic things. Like, sqlx is a great tool, but can't handle some fields when pgx is used.

[jeromer]

And if I did do something it would probably run more along code generation for struct mapping than reflection.

@jackc I love this option I am actually considering it as an alternative to sqlx. Do you have any pointers or libs that can do code generation like I would do in Lisp or Elixir ?

[james-lawrence]

@jeromer oddly enough I'm working on a code generation for sql, genieql, might fit what your looking for, though i havent directly implemented pgx for it yet its on my list. I'd love for you to take a look and provide feedback if nothing else. its still in flux but its mostly stable. right now most of the work I'm putting into it is sensible defaults to ease usage and corner case clean up of some experimental stuff I was trying.

there is another one - xo found it around the same time I was well into genieql.

[jackc]

I experimented with with a code generation tool of my own: https://github.com/jackc/pgxdata. I finished it enough to use it with https://github.com/jackc/tpr, but I'm not actively developing it at the moment.

[tsingson]

https://github.com/jackc/pgxdata is good for me , Usage like this:

pgxdata

a tool to generate a jackc/pgx database go package tailored to exists postgresql database schema.

Usage

STEP 1 first install pgxdata

go get -u github.com/jackc/pgxdata

STEP 2 add GOPATH/bin to PATH , make sure pgxdata runing anywhere

STEP 3 then create a postgresql schema like this

    createdb pgxdata
    psql pgxdata -f test/structure.sql

STEP 4 run pgxdata once to create a new go package name "dbi" within $GOPATH/src

   cd $GOPATH/src
   pgxdata init dbi

this command will create a directory name "dbi" and two file inside "dbi"

ls ./dbi
config.toml
pgxdata_db.go

cat the ./dbi/config.toml like this

cat ./dbi/config.toml
package = "dbi"

# Database connection information can be specified here or in PG* environment variables
#
# [database]
# host = "127.0.0.1"
# port = 5432
# database = "myapp_development"
# user = "myuser"
# password = "secret"

[[tables]]
table_name = "customer"
# struct_name = "Customer"

STEP 5 edit the config file dbi/config.toml

vi dbi/config.toml

with this look like

package = "dbi"

[database]
host = "127.0.0.1"
port = 5432
database = "pgxdata"
user = "myuser"
password = "secret"

[[tables]]
table_name = "customer"
struct_name = "Customer"

[[tables]]
table_name = "widget"
struct_name = "Widget"

[[tables]]
table_name = "part"
struct_name = "Part"
primary_key = ["code"]

[[tables]]
table_name = "semester"
struct_name = "Semester"
primary_key = ["year", "season"]

[[tables]]
table_name = "customer"
struct_name = "RenamedFieldCustomer"

  [[tables.columns]]
  column_name = "first_name"
  field_name = "FName"

[[tables]]
table_name = "blob"
struct_name = "Blob"

STEP 6 goto dbi directory and run generate

cd dbi
pgxdata generate

Final check $GOPATH/src/dbi to see all generated go package for postgres database pgxdata

ls $GOPATH/src/dbi

8596271720 drwxr-xr-x  12 qinshen  staff    384 Jul 22 01:17 ./
8596271719 drwxr-xr-x   5 qinshen  staff    160 Jul 22 01:40 ../
8596271721 -rw-r--r--   1 qinshen  staff    505 Jul 22 01:17 config.toml
8596271722 -rw-r--r--   1 qinshen  staff  13952 Jul 22 01:17 crud_test.go
8596271723 -rw-r--r--   1 qinshen  staff    909 Jul 22 01:17 integration_test.go
8596271724 -rw-r--r--   1 qinshen  staff   2929 Jul 22 01:17 pgxdata_blob.go
8596271725 -rw-r--r--   1 qinshen  staff   4203 Jul 22 01:17 pgxdata_customer.go
8596271726 -rw-r--r--   1 qinshen  staff   1817 Jul 22 01:17 pgxdata_db.go
8596271727 -rw-r--r--   1 qinshen  staff   3024 Jul 22 01:17 pgxdata_part.go
8596271728 -rw-r--r--   1 qinshen  staff   4491 Jul 22 01:17 pgxdata_renamed_field_customer.go
8596271729 -rw-r--r--   1 qinshen  staff   3651 Jul 22 01:17 pgxdata_semester.go
8596271730 -rw-r--r--   1 qinshen  staff   3283 Jul 22 01:17 pgxdata_widget.go

[jeromer]

I just pushed https://github.com/jeromer/sqrible (caution: fresh paint !). I'll use it on 2 projects.