go-jet / jet Goto Github PK

Hi again,

I have a question regarding the signature you chose on some functions.
For example, after using UPDATE(columns ...jet.Column), you have to use SET(value interface{}, values ...interface{}). So in the first function, you can comfortably use a slice, but not in the second one. A similar case that comes to mind is SELECT(projection Projection, projections ...Projection). Without digging too deep, I can't really tell why it needs to be like this. Could you explain?

(In my specific case, I had either one or two items in my slice. I tried SET(vals[0], vals[1:]...), but (iirc) with only one item JET would complain that the lenghts of the parameter lists to the update and set clauses is different. If the interface needs to stay that way, maybe this is something that can be fixed instead.)

Thanks for this great repo.

I'm wondering what the best way to use this repo is with gRPC. I have some proto messages that will be stored in some PG tables, and i'm wondering what the most efficient way to use jet would be in such a situation. Is it possible for me to unpack query results directly into structs generated via gRPC?

Here's an example struct and its associated proto message:

generated:

type Dataset struct {
	// A server generated ID that is unique across the system.
	Id string `protobuf:"bytes,1,opt,name=id,proto3" json:"id,omitempty"`
	// The name of the Dataset
	Name string `protobuf:"bytes,2,opt,name=name,proto3" json:"name,omitempty"`
	// The account ID that this Dataset belongs to.
	AccountId string `protobuf:"bytes,3,opt,name=account_id,json=accountId,proto3" json:"account_id,omitempty"`
	// This is the time the Dataset was created, in seconds from the epoch.
	CreateTime           *time.Time `protobuf:"bytes,4,opt,name=create_time,json=createTime,proto3,stdtime" json:"create_time,omitempty"`
	XXX_NoUnkeyedLiteral struct{}   `json:"-"`
	XXX_unrecognized     []byte     `json:"-"`
	XXX_sizecache        int32      `json:"-"`
}

proto message:

message Dataset {
    // A server generated ID that is unique across the system.
    string id = 1;

    // The name of the Dataset
    string name = 2;

    // The account ID that this Dataset belongs to.
    string account_id = 3;

    // This is the time the Dataset was created, in seconds from the epoch.
    google.protobuf.Timestamp create_time = 4 [(gogoproto.stdtime) = true];

}

To me, it seems like the only real option is to treat model.Dataset that Jet generates as a storage layer, and have functions that cast between model.Dataset and the Dataset struct that gRPC generates.

Any thoughts?

Hello,

I've successfully used this library with PostgreSql connected to Oracle through a foreign data wrapper.

Only 4 lines were added to generator/internal/metadata/schema_meta_data.go, as follows:

const (
     baseTable    = "BASE TABLE"
+     foreignTable = "FOREIGN" 
     view         = "VIEW"
)
// GetSchemaMetaData returns schema information from db connection.
 func GetSchemaMetaData(db *sql.DB, schemaName string, querySet DialectQuerySet) (schemaInfo SchemaMetaData) {
        schemaInfo.TablesMetaData = getTablesMetaData(db, querySet, schemaName, baseTable)
 +      for _, s := range getTablesMetaData(db, querySet, schemaName, foreignTable) {
 +              schemaInfo.TablesMetaData = append(schemaInfo.TablesMetaData, s)
 +      }
        schemaInfo.ViewsMetaData = getTablesMetaData(db, querySet, schemaName, view)
        schemaInfo.EnumsMetaData = querySet.GetEnumsMetaData(db, schemaName)

After these modifications, all pre-existing tests were successful (even those specific to MySql/Mariadb).

I have not written any tests for FOREIGN tables since once generated in the Go project, they are no different from 'normal' tables.

Since FDWs are standardized, this (potentially) opens the door to a large number of databases through PostgreSql: https://wiki.postgresql.org/wiki/Foreign_data_wrappers

I have not made a PR because I don't know if this approach would be useful for you, but since the modification is very slight it could be made directly by you in the source code if you find it useful.

go-jet/jet: v2.1.0
Postgresql: 11.5
https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_1_0
Oracle 11.2
Ubuntu 16.04

At present jet doesn't support passwords with spaces in them.

I was able to workaround the issue by doing a sql-injection like attack:

	err := postgres.Generate("./gen/db", postgres.DBConnection{
		Host:     "localhost",
		Port:     5432,
		User:     "dbuser",
		SslMode:  "disable",
		Password: "null password='password with spaces'",
		DBName:   "mydb",
	})

Thank you for your work on this library - a very nice design idea and very slick in use!

I'm driving the jet executable from go generate, and it works very well, except jet always adds the generation date which ends up being an only thing in the git diff after the generation step and requires me to then revert the jet generated files that are not actually changed.

//
// Code generated by go-jet DO NOT EDIT.
// Generated at Wednesday, 15-Jan-20 08:38:53 GMT
//

Would it be possible to remove or introduce a config switch for the date generation?

Thanks and well done on the library again!

I know it might be a lot of work but Oracle support would be a great help because most of the OLTP systems are still in Oracle

Hi.
Is it possible to allow connection via DSN (like postgres://username:password@localhost:5432/database_name), not separated flag params?
I see, that pgx and mysql driver understands this format.

I think it should looks like:

$ jet -conn="postgres://username:password@localhost:5432/jet" -schema=public -path=.

I can prepare pull request

Hi thanks for this repo.

It seems like the query interface is meant for libpq driver. Any chance this is compatible with pgx?

I am coming back to an older evaluation project of mine and thought I need to update the go.mod file to use v2 (github.com/go-jet/jet/v2). I did, and I updated jet itself too.

The jet command shows 2.3.0 as the version number.

When I generate the table schemas (MariaDB), it will use a non-v2 import in the generated sources, though! Building the project will ask me to add the non-v2 path to go.mod. When I do, it won't compile because of incompatible structures.

I have a clean go (1.16.3) installation with no GOPATH set (go env GOPATH shows ~/go). The project is a go.mod project and located at a different path.

When I don't use the v2 in the mod file, everything compiles fine (but probably not with the newest version of jet?).

When I use the v2 in the mod file, do a generate and manually add the v2 to all generated sources, it also compiles fine.

I also hat "one time" that jet actually generated v2/mysql imports, but I can't reproduce it.

It looks to me as if the jet tool does not correctly determine if the import paths have to use the v2 ending.

What am I doing wrong here?

Hi, I have a question.

Based on a selection of fields, I am dynamically joining tables by conditionally calling LEFT_JOIN and appending the joined table's columns to a []mysql.Projection which I use in the final SELECT(). This works pretty well.

Now I want to dynamically construct the ORDER_BY() based on the columns I joined, but I fail to see how. The function takes arguments of the internal type jet.OrderByClause, and I have not found an exported type (or interface) I can use to have a slice of these I can append to.

Did I miss something, or is there currently no way to do this? I would really prefer to avoid using subselects in the joined tables just for the ordering.

Thanks in advance!

It would be nice if the LoggerFunc interface also had provided to it the outcome of the query, such as the number of rows returned, execution latency and the error. This could also be achieved by providing a middleware interceptor.

Hello,

I have started using the library for a pet project of mine and I am really excited about it. It removes a lot of the boilerplate that I need to write for anything related to databases. However, I had some issues when I tried to unit test some my code.

To be more precise, I use the qrm.DB interface to pass it to any Query made from jet and mock it for the unit tests, but I couldn't find a way to mok it work properly. When jet calls Query it's seems that internally calls the QueryContext method of the DB interface which has the following signature QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error).
It may either return a sql.Rows pointer or an err, but sql.Rows is a struct that doesn't expose any of its internal fields and I cannot pass anyting else except a nil pointer, which makes QueryContext to panic and propagates to jet Query method.

Can you suggest a solution or an alternative solution to this issue?

Hey there, very interested in trying out this project.

Wondering where MS SQLServer support would be on the roadmap?

Thanks.

I was trying to do something similar to https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql/42217872#42217872

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )

But I don't think there is a VALUES function.

@go-jet , issues #43 and #76 mention SQLite support coming soon. Are there any plans on making this happen in the foreseeable future?

Hi there!

I love this idea, I'm building a test app with this library and I found a problem. I'm using a "user" table on PostgreSQL to store my users. This table is on a schema (image_gallery), and if I run:

 statement := SELECT(Picture.AllColumns).
		FROM(
			Picture.INNER_JOIN(User, Picture.UserID.EQ(User.UserID)),
		)

the query fails to execute. The problem is that schema is not being chained to the fields, so the query looks like this:

SELECT picture.picture_id AS "picture.picture_id",
     picture.user_id AS "picture.user_id",
     picture.image_id AS "picture.image_id",
     picture.title AS "picture.title",
     picture.description AS "picture.description",
     picture.created AS "picture.created",
     picture.num_likes AS "picture.num_likes",
     picture.num_comments AS "picture.num_comments"
FROM image_gallery.picture
     INNER JOIN image_gallery.user ON (picture.user_id = user.user_id); 

PostgreSQL complains about a wrong syntax on the "user.user_id". I guess "user" is some reserved word. I see that the solution is to scape the table names always like this:

SELECT "picture".picture_id AS "picture.picture_id",
       "picture".user_id AS "picture.user_id",
       "picture".image_id AS "picture.image_id",
       "picture".title AS "picture.title",
       "picture".description AS "picture.description",
       "picture".created AS "picture.created",
       "picture".num_likes AS "picture.num_likes",
       "picture".num_comments AS "picture.num_comments"
FROM image_gallery."picture"
         INNER JOIN image_gallery."user" ON ("picture".user_id = "user".user_id);

I think it is an easy solution and will help a lot to avoid collision with reserved words, don't you think? I think I can contribute to the project so if you see this solution as correct (I think for Mysql and MariaDB we can use ` scaping char) I can check if I can contribute to the project.

Cheers!

Add support for the LATERAL operator.

Currently there is no helper that accepts a uuid.UUID to a StringExpression.

We are currently storing monetary values as type Numeric in our PostgreSQL database and we perform all types of numeric operations. Because of this, the default golang Float64 datatype does not work for our needs as we cannot work with numerical approximations.

Is there any way to customize the model currently?

Not sure if it is currently possible to write something like:

WHERE (problem_id, option) IN ((4509, 'B'), (622, 'C'), (1066, 'D'), (4059, 'A'), (4740, 'A')...)

Hello,
excellent approach in the spirit of JOOQ (Java/Kotlin). Great project. Thank you.
Is there any specific reason why PostgreSQL "views" are excluded from SQL queries?

SELECT table_name FROM information_schema.tables where table_schema = $1 /* and table_type = 'BASE TABLE' */ ;

Hello,

I checked the documentation but I couldn't find anything saying about custom tags in the auto-generated models. Let's say that I have this table in my database:

CREATE TABLE IF NOT EXISTS users (
      id int GENERATED ALWAYS AS IDENTITY,
      username VARCHAR(255),
      email VARCHAR(255),
      password VARCHAR(255),
      balance float8,
      created_at TIMESTAMPTZ,
      updated_at TIMESTAMPTZ
);

At the moment I get this struct

type Users struct {
	ID        int32 `sql:"primary_key"`
	Username  string
	Email     string
	Password  string
	Balance   *float64
	CreatedAt *time.Time
	UpdatedAt *time.Time
}

but I want to get something like this:

type Users struct {
	ID        int32 `json:"id" sql:"primary_key"`
	Username  string `json:"username"`
	Email     string `json:"email"`
	Password  string `json:"password"`
	Balance   *float64 `json:"balance"`
	CreatedAt *time.Time `json:"createdAt"`
	UpdatedAt *time.Time `json:"updatedAt"`
}

I use my models as a result of an HTTP response, so it would be really handy if I had some control over the resulted struct. Is there a way to control this behaviour or its a feature that may be added in a later stage?

This project is really what I needed to work with go and sql, so thank you.

Since destinations types could be any type and generated packages do not depend on each other, it would be great to have the opportunity to opt out of specific packages generation, for example to avoid generation of the model package.

I'm thinking about a cmd flag model ( with maybe a corresponding field in a config struct for generation from code ? ) with default value true which could be set to false to avoid generation of that specific package in case I want to take advantage of my custom types.
Same for enum.

I'd be glad to work on it myself if you like the idea, just let me know what you think about it.

In MySQL it is possible to use ON DUPLICATE KEY UPDATE clause in batch inserts, like in the below example:

INSERT INTO `buoy_stations` (`id`, `coords`, `name`, `owner`, `pgm`, `met`, `currents`)
VALUES 
('00922', 'Point(30,-90)','name 1','owner 1','pgm 1','y','y'),
('00923', 'Point(30,-90)','name 2','owner 2','pgm 2','y','y'),
('00924', 'Point(30,-90)','name 3','owner 3','pgm 3','y','y'),
('00925', 'Point(30,-90)','name 4','owner 4','pgm 4','y','y'),
('00926', 'Point(30,-90)','name 5','owner 5','pgm 5','y','y')
ON DUPLICATE KEY
        UPDATE coords=values(coords), name=values(name), owner=values(owner), pgm=values(pgm), met=values(met), currents=values(currents);

However, it appears that it is impossible to perform batch inserts with go-jet's current ON DUPLICATE KEY UPDATE implementation.

We have an enum column Status in our table Node and also have a table named node_status. The (table-)model NodeStatus gets overwritten by the Enum which also resolves to NodeStatus. As we removed the enum from the node table, everything works fine (but removing or renaming is no option).

A possible solution we could think of, is passing a renaming argument (or config file) for tables or specific attributes. Or maybe just resolve automatically to a prefixed/suffixed name.

Hi nice job building this query builder 👍

Currently when I try to use custom models, when executing the SELECT statement for postgres an empty data is returned. Example 👇

db, err := config.GetDb()
defer db.Close()
if err != nil {
    return nil, err
}

var users = []User{} // User here is a custom model, not the one generated by jet

t := tbl.Users // Table generated for User by jet
stmt, _ := t.SELECT(t.AllColumns).FROM(t)
err = t.Query(db, &users)

fmt.Println(users) // Returns []

One reason for the custom model is: the table names are in plural but I want the model name to be singular, but jet generates the models as plurals just exactly from the db tables.

Is there a way I can write this to work explicitly with custom models instead of the ones generated by jet?

Hi,

I just noticed that when an enum has a numerical numerator, the thing crashes quite ambiguously:

Generating enum sql builder files...
14:18: expected expression (and 2 more errors)

Also a feature request: can you add a flag say -overwrite which will only overwrite files and keep other things intact, for now I've commented:

	// fmt.Println("Cleaning up destination directory...")
	// err := utils.CleanUpGeneratedFiles(destDir)
	// utils.PanicOnError(err)

Cheers

Add support for renaming the schema for generated code. Currently there is no way to use the generated code for identical database structure using two different schema names.

Use cases for this includes multitenant systems and/or using different schema name in test database than in development in order to avoid date-destroying accidents in production database. The issues is even more serious in MySQL, as the schema name is in fact the name of the database.

Furthermore, it would be nice to have support for using the default schema (i.e. leave out the schema name part in the SQL queries) for the above use cases.

Support can be build just like the AS -function is supported in the generated code. This allows the same generated code to access tables in multiple schemas even simultaneously.

Hello and thank you for publishing this.

I' been looking through the docs & samples and can't find any reference to transactions. Are transactions possible with go-jet (MySQL driver) ?

At the moment, the schema name is baked into the generated code. It would be helpful if the schema name could be globally overridden, so that a compiled program could be configured to hit different databases in different environments without changing code (or adding FromSchema to all queries).

If there is no Citext support, it would be great if jet can use string instead of Citext when generating.

Hi,

I am looking for the proper way to do an insert and fetch back the returned value from the insert.

For instance, how would I execute a query like the following using Jet?

INSERT INTO public.users (user_id,
                          email,
                          first_name,
                          last_name,
                          oidc_subject,
                          password_hash,
                          created_time,
                          updated_time)
VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())
RETURNING *;

Is there support for e.g. if I have a custom function, foo(text) returns text, can I call it with a placeholder value? e.g.:

...
.WHERE(
    CALL("foo(?)", MyTable.Column).EQ("bar"),
)

I see there's a RAW() function but it doesn't look like it can accept placeholders.

I have a table of records that has a date DATETIME field. I need to select the earliest date and the latest date. In raw SQL this would be

SELECT MIN(date) AS first, MAX(date) AS last FROM table;

However with jet there are only min/max funcs for integers and floats and no apparent way to convert a date field to an integer (effectively render it as unix time). How can I do this with Jet?

Currently it accepts a string which is a bit counter intuitive considering the model is also using []byte.
This leads to more verbose input code: Bytea(string(myHash[:]))).
Thanks!

Does Jet support QueryRow for only one row.

I'm planning to use jet for query builder then I can map query results to my own Struct. Currently, jet generates alias with table.column format. For example:

stmt := SELECT(Clients.AllColumns).FROM(Clients).WHERE(expression)
fmt.Println(stmt.DebugSql())

will prints

SELECT clients.id AS "clients.id",
     clients.created_at AS "clients.created_at",
     clients.updated_at AS "clients.updated_at",
     clients.username AS "clients.username"
FROM mydb.clients
WHERE clients.id = '12345';

However, I defined my struct like this:

type Client struct {
	ID        uuid.UUID `db:"id"` // tag name is different with generated alias
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
	Username  string    `db:"username"`
}

This, of course, will cause error when I try to map the result. I could do either:

1. Use the jet generated model Struct instead of my own Struct
2. Change the tags to include table name (for example change db:"id" to db:"clients.id"), or
3. Change query statement to this:

stmt := SELECT(
    Clients.ID.AS(Clients.ID.Name()),
    Clients.CreatedAt.AS(Clients.CreatedAt.Name()),
    Clients.UpdatedAt.AS(Clients.UpdatedAt.Name()),
    Clients.Username.AS(Clients.Username.Name())).
FROM(Clients).WHERE(expression)

I might go with the second solution, however it would be nicer if we have some way to control the default alias 😉

I am using an LTREE in one of the tables and I found out that the datatype of USER-DEFINED so it is handled like an enum (snake case of the type). I am wondering if we should remove it from the switch case so it uses the default case (which uses string). Even better if we had a way to tell the generator to use an import or a built-in type to map some types.
Reference: https://github.com/go-jet/jet/blob/master/generator/internal/metadata/column_meta_data.go#L79

When I execute sql queries with external go sql library like 'sql/sqlx', not using jet's. I can get the sql.Result or sql.Row or sql.Rows. Then, how can I convert them to Struct?

Hi there!
Awesome job, I really like the lib for far :)
So I have this particular query:

err := SELECT(Type.Name.AS("parentType"), Field.AllColumns).
		FROM(Field).
		FROM(Field.INNER_JOIN(Type, Type.ID.EQ(Field.ParentTypeID))).
		WHERE(Field.ParentTypeID.IN(types...)).
		Query(s.db, &existingFields)

Where types is []StringExpression.
I would have expected it to be accepted but it gives me an error:

Cannot use 't' (type []StringExpression) as type []Expression 

No sure what is wrong. I am using Postgres.

Is it currently possible to execute a delete query that includes a join? I'd like to be able to do that without running a sub query.

Here is my ideal sql:

DELETE posts
FROM posts
INNER JOIN projects ON projects.project_id = posts.project_id
WHERE projects.client_id = ?

This what I am stuck with now:

DELETE FROM posts
WHERE posts.project_id IN ((
          SELECT posts.project_id
          FROM posts
               INNER JOIN projects ON projects.project_id = posts.project_id
          WHERE project.client_id = ?
     ));

Add support for NOT operator in MySQL similarly as it is implemented in PostgreSQL. This enables NOT EXISTS subqueries in MySQL.

Currently the Raw() function takes a string, but sometimes functions require parameters from the user. Currently you have to sanitize yourself the input and use a fmt.Sprintf which is prone to SQL injections.
It would be nice if we could something like: Raw("my_function($1)", String("test")).

Sometimes, database contain tables that is not relates to domain itself and are serving as technical information. So we want it to be ignored by jet.
E.g. applied migrations list (https://github.com/pressly/goose creates table goose_db_version)

I propose to add some flag to cli, so generator would ignore tables passed to this flag.

$ jet -ignore="goose_db_version;locks"

Jet is awesome, but features are a bit limiting for my use cases. Any plan/timeline to add the following features (for postgres, I assume MySQL has an equivalent):

1. Conflict clause such as:

   INSERT INTO table (col) VALUES (val) ON CONFLICT DO NOTHING

2. WITH Queries (CTE) such as:

   WITH alias AS (
       SELECT x FROM y
   )
   SELECT * FROM z
   WHERE a IN (SELECT a FROM alias)

Thanks for the great work!

I have this table:

create table sessions (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references users(id) on delete cascade,
    identifier text not null default '',
    scopes text[] not null default '{}'
);

However, it generates this code:

type Sessions struct {
	ID         uuid.UUID `sql:"primary_key"`
	UserID     uuid.UUID
	Identifier string
	Scopes     string
}

scopes has been emitted as string rather than []string, and results in the funny Postgres {foo, bar} string. Is it possible to scan directly into an array?

I am trying to do an update of a column with a reserved word as name in mysql.

Models und set value generate invalid SQL:

type Model struct { ID: int, Load: string }
table.UPDATE(table.Load).MODELS(model{Load: "test"}).Where(...

AND

table.UPDATE(table.Load).SET(mysql.String("test")).WHERE(...

generates:

UPDATE table
SET load = 'test'
WHERE ...;

set column with value generates valid SQL:

table.UPDATE().SET(table.Load.SET(mysql.String("test"))).WHERE(...

generates the correct SQL:

UPDATE uwaa.node_status
SET `load` = 'test'
WHERE ...;

Hi
I know it would be backward incompatible, but I think jsonb and json columns should be mapped to go []byte instead of current string.

My points are:

• for parsing (json.Unmarshal()) json field from string we need to convert it to []byte. It adds overhead like additional copying or using unsafe
• Json is not a "text"

Given a database table mapped by go-jet to:

type Host struct {
	IDHost uint64 `sql:"primary_key"`
	Name string
	Addr string
	Port uint16
}

and

obj := model.Host {
    IDHost: 9980677793214571132,
    Name: "a-long-hash",
    Addr: "127.0.0.1",
    Port: 8080,
}

I should be able to do this

	stm := Host.INSERT(Host.AllColumns).
		MODEL(obj).
		ON_DUPLICATE_KEY_UPDATE(
			Host.IDHost.SET(UInt64(obj.IDHost)),
			Host.Name.SET(String(obj.Name)),
			Host.Addr.SET(String(obj.Addr)),
			Host.Port.SET(UInt16(obj.Port)),
			)

however jet-go has no UIntXX IntegerExpression. What am I missing?