How do I deal with JSONB types while code generation?

[vamshiaruru-virgodesigns]

Hi guys, thanks for the great library. I am however unable to figure out how to deal with JSONB types while generating the types with Jet. I am trying to port an existing application which uses a postgres DB. I have dozens of tables, and most of the tables have a column called private_metadata which is a JSONB type.

When I try to generate the models for this schema, that column is decoded as string which isn't what I want. I have looked up the issues on this repo and I know there is a way to customise the types and other data (such as json tags), but I don't know how to implement it or run it.

If anyone could provide me with a runnable example, I'd greatly appreciate it. Thank you!

[vamshiaruru-virgodesigns]

For instance, I am trying this:

package main

import (
	"fmt"
	"strings"

	"github.com/go-jet/jet/v2/generator/metadata"
	"github.com/go-jet/jet/v2/generator/postgres"
	"github.com/go-jet/jet/v2/generator/template"
	postgres2 "github.com/go-jet/jet/v2/postgres"
	_ "github.com/lib/pq"
)

func main() {
	err := postgres.Generate("./jet_gen", postgres.DBConnection{
		Host:     "localhost",
		Port:     5432,
		User:     "username",
		Password: "password",
		DBName:   "db",
		SslMode:  "disable",
	},
		template.Default(postgres2.Dialect).
			UseSchema(func(schemaMetaData metadata.Schema) template.Schema {
				return template.DefaultSchema(schemaMetaData).
					UseSQLBuilder(template.DefaultSQLBuilder().
						UseTable(func(table metadata.Table) template.TableSQLBuilder {
							return template.DefaultTableSQLBuilder(table).
								UseColumn(func(column metadata.Column) template.TableSQLBuilderColumn {
									fmt.Println(column.Name)
									defaultColumn := template.DefaultTableSQLBuilderColumn(column)

									if strings.Contains(defaultColumn.Name, "metadata") {
										defaultColumn.Type = "map[string]interface{}"
									}
									return defaultColumn
								})
						}),
					)
			}),
	)
	if err != nil {
		panic(err)
	}
}

1. It was not obvious to me that I have to use pq instead of pgx (which is the more maintained one) to generate models.
2. Running this puts the following output:

Connecting to postgres database: postgresql://username:password@localhost:5432/db?sslmode=disable
Retrieving schema information...
        FOUND 0 table(s), 0 view(s), 0 enum(s)

Even though the DB has many tables. Infact, using the jet command line tool, I am able to generate output, like so

jet -dsn="postgresql://username:password@localhost:5432/db?sslmode=disable" -path "./jet_gen"

But I can't customize types using this.

What am I doing wrong?

[houten11]

Hi @vamshiaruru-virgodesigns ,

I could spot a couple of mistakes:

1. You're missing SchemaName in DBConnection. That's probably why no tables are found.
2. You're using the wrong code example. You're code tries to customize sql builder types, not model types. Example of model type customization - https://github.com/go-jet/jet/wiki/Generator#generator-customization.
3. map[string]interface{} does not implement Scanner and Valuer interface, so scan would not work for this type. You need to define a new type:

// for any JSON types
type JsonType map[string]interface{}

func (m *JsonType) Scan(value interface{}) error {
	return json.Unmarshal(value.([]byte), m)
}

func (m JsonType) Value() (driver.Value, error) {
	return json.Marshal(m)
}

Or even better you can define a specific type-safe struct for each json column:

type ColumnJson1 struct {
    A int
    B string
}

func (m *ColumnJson1) Scan(value interface{}) error {
	return json.Unmarshal(value.([]byte), m)
}

func (m ColumnJson1) Value() (driver.Value, error) {
	return json.Marshal(m)
}

[vamshiaruru-virgodesigns]

Thanks for the help @houten11 ! I was able to get it working with your steps. Thanks :)