uptrace/bun

SQL-first Golang ORM

bun's Issues

Primary key

Models/schema

type BaseModel struct {
	bun.BaseModel
	ID int64 `bun:"pk,autoincrement"`
}

type User struct {
	BaseModel
	//this represents the customer

	FirstName  string `bun:"type:varchar(255)"`
	SecondName string `bun:"type:varchar(255)"`

	PasswordHash string

	Email *string

	AddressID int
	///address_id is a foreign_key from the address table....
	CreatedAt *time.Time
	UpdatedAt *time.Time

	Links []*Link `bun:"rel:has-many"`
}

type Link struct {
	BaseModel
	Title   string `bun:"type:varchar(255)"`
	Address string `bun:"type:varchar(255)"`
	UserID  int64
}

Migration

	Migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
		db.RegisterModel((*models.User)(nil), (*models.Link)(nil))
		return nil
	}, nil)

When running the migration i get panic: bun: model=User does not have primary keys

How do i inform bun of User's ID key being primary key

Docs on transaction

There are no docs on transaction. I am new to golang ORM. So having docs on transaction will be very helpful.

Thanks

Should there be a way to migrate to a specific target?

I want to suggest add a method to migrate.Migrations to migrate to a specific group number / target. E.g.

func(m *Migrations) MigrateTo(ctx context.Context, db *bun.DB, target int64) error

Or an example on how to achieve this without this method.

Use-case:

1. Deploy new API that require the latest schema version (e.g. 5).
2. Find there is a bug in the API, and roll it back to a previous version that requires an earlier db schema (e.g. 3).
3. Run to migrate to group 3 (automatically rollback any migrations with a higher group number, and apply any migrations with a lower or eqaul group number).

Assumes that migrations group numbers can be set from the source.

Cursor pagination example

I don't know if this is the right place to open this: I'm sorry if not.

I can see here: https://github.com/go-bun/bun-realworld-app/blob/3f1662d4893bb3e11f83fef172b9d5321cab1df2/blog/article_handler.go#L38-L44 and example of pagination with Offset and Limit.

What do you think of an example on how to implement a cursor pagination?

• https://medium.com/swlh/how-to-implement-cursor-pagination-like-a-pro-513140b65f32

• https://slack.engineering/evolving-api-pagination-at-slack/

Multi tenant, an elegant and easy way

Now that you have refactored a lot of things in go-pg and Bun is born, I think it's the right time to ask you something that I have long wanted to solve easily with go-pg.

I'm talking about the filter with tenant_id for tenantable models.

Example:

If Player is tenantable (has Tenant field):

type Tenant struct {
  ID int
  Name string
}

type Player struct {
  Tenant
  name string
  age int
}

I would like to add on each query a where filter like:

SELECT * FROM players where name LIKE 'John'
AND where tenant_id = 123 -- this is automagically added

123 here comes from user's tenant_id column.

I saw that there are a lot of hooks available but not the one before a Select which is what we need here.

Can you suggest an elegant way?

Since you also worked on a solution here: https://github.com/elliotcourant/go-pg-multitenant, I would like to hear your opinion on Bun too, @elliotcourant.

"undefined: feature.InsertTableAlias" when installing pgdialect

Hi, it appears the latest release introduces an issue for github.com/uptrace/bun/dialect/pgdialect

go get github.com/uptrace/bun/dialect/pgdialect
# github.com/uptrace/bun/dialect/pgdialect
../../../go/pkg/mod/github.com/uptrace/bun/dialect/[email protected]/dialect.go:29:3: undefined: feature.InsertTableAlias

Scan error: handling empty arrays

Thanks for all your work on this 👍

Are uuid arrays supported, and if so how?

I'm trying to migrate from github.com/go-pg/pg which was quite fine with the following go user Model element:

	FollowedGroups         []uuid.UUID `sql:",type:uuid[]" pg:",array"`

I've migrated this to:

	FollowedGroups         []uuid.UUID `bun:",type:uuid[],array"`

Migration appears successfully setting this field in Postgres table up as:

    followed_groups uuid[],

Upon attempting to select the table records with bun:

	dberr := s.db.NewSelect().
		Model(&users).
		Scan(ctx)

I'm getting:

sql: Scan error on column index 8, name "followed_groups": bun: got <nil>, wanted []byte or string"

Updating value to {} manually in pgAdmin leads to:

sql: Scan error on column index 8, name \"followed_groups\": json: cannot unmarshal object into Go value of type []string

Updating to example data, e..g {d8014c4e-7c90-42a4-bd1f-2ebd7ccb4545,d8014c4e-7c90-42a4-bd1f-2ebd7ccb4545} leads to:

sql: Scan error on column index 8, name \"followed_groups\": invalid character 'd' looking for beginning of object key string",

Exclude this field with a Column statement and the query works.

Do we support dynamic model?

In our scenario, we have many tables with the same schema, but different table names. Could we use a dynamic model in select method, like the following:

err := db.NewSelect().Table("bsc_cake_0x58f876857a02d6762e0101bb5c46a8c1ed44dc16_kline_1m_u").Order("time desc").Limit(100).Model(xxxx).Scan(ctx)

Or some other solution?

Unable to run migrations (migrations table always locked)

Attempting to run migrations on a brand new database throws the error below

[bun]  18:24:14.317   INSERT                  100µs  INSERT INTO bun_migration_locks AS l (`id`, `table_name`) VALUES (DEFAULT, 'bun_migrations') 	  *mysql.MySQLError: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS l (`id`, `table_name`) VALUES (DEFAULT, 'bun_migrations')' at line 1
2021/07/07 18:24:14 bun: migrations table is already locked (Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS l (`id`, `table_name`) VALUES (DEFAULT, 'bun_migrations')' at line 1)

My go.mod file is as below:

module mymodule

go 1.16

require (
	github.com/PuerkitoBio/goquery v1.7.0
	github.com/go-sql-driver/mysql v1.6.0
	github.com/spf13/cobra v1.2.1
	github.com/spf13/viper v1.8.1
	github.com/uptrace/bun v0.2.14
	github.com/uptrace/bun/dialect/mysqldialect v0.2.14
	github.com/uptrace/bun/extra/bundebug v0.2.14
	github.com/urfave/cli/v2 v2.3.0
	go4.org v0.0.0-20201209231011-d4a079459e60
)

Do we will support [email protected]?

[email protected] does not support AS in DELETE.

https://dev.mysql.com/doc/refman/5.7/en/delete.html

If we try to support [email protected], auto alias is a bug.

ExcludeColumn(columns ...string) *InsertQuery

Hello, I was trying to exclude some columns from an insert query and noticed that method is not supported, Would it make sense to add it? excludeColumns already exists in baseQuery

LastInsertId is not available

I seem to run into issues when inserting a user struct. I get the following error : "LastInsertId is not available"

Is there potentially another way to access the returned id? I have tried to explicitly add ".Returning("id")" but get the same error.

func StoreUser(user *models.User) int64 {
	db := db_test_connect()
	defer db.Close()

	ctx := context.Background()

	result, err := db.NewInsert().Model(user).Exec(ctx)
	if err != nil {
		fmt.Println(err)
		panic(err)
	}
	fmt.Println(result) // Debugging
	userId, err := result.LastInsertId()
	if err != nil {
		fmt.Println(err)
		panic(err)
	}


	return userId
}

Go version:
go1.16.5 darwin/amd64

Bun version:
uptrace/bun v0.2.14
uptrace/bun/dialect/pgdialect v0.2.14

Map embedded structures to fields

I think this feature will be nice to have

type Permissions struct {
	View   bool
	Create bool
	Update bool
	Delete bool
}

type Role struct {
	Enable   bool
	Name     string
	Users    Permissions `bun:"embedded;embeddedPrefix:users_"`
	Profiles Permissions `bun:"embedded;embeddedPrefix:profiles_"`
	Roles    Permissions `bun:"embedded;embeddedPrefix:roles_"`
}

Many to many relation (panic: bun: model=UserRole does not have primary keys)

After update to v0.2.0 i get this error:

panic: bun: model=UserRole does not have primary keys

Role model:

type Role struct {
	Model
	Enable bool
	Name   string

User model:

type User struct {
	Model
	HashedPassword string
	Password       string `bun:"-"`
	Active         bool
	Email          string
	Admin          bool
	Roles          []Role `bun:"m2m:user_role"`
}

UserRole model:

type UserRole struct {
	bun.BaseModel `bun:"user_role"`
	UserID        uuid.UUID
	User          *User `bun:"rel:has-one"`
	RoleID        uuid.UUID
	Role          *Role `bun:"rel:has-one"`
}

reflect: call of reflect.Value.Bytes on ptr Value on *json.RawMessage column

Hey, I'm probably trying to do something wrong but I've gotten call of reflect.Value.Bytes on ptr Value using this model struct:

type User struct {
	ID                 uuid.UUID        `bun:"type:uuid,default:uuid_generate_v4()"`
	Got 				*json.RawMessage
	Email              string
}

user = &User{
	ID:                 uuid,
	Got: 		    	otok,
	Email:              profile.EmailAddress,
}

This thing works fine:

if _, err := db.NewUpdate().Table("users").Model(&map[string]interface{}{"got": otok}).Where("id=?", user.ID).Exec(ctx); err != nil {
	return err
}

However this gives an error:

if _, err := db.NewUpdate().Column('got').Model(user).Where("id=?", user.ID).Exec(ctx); err != nil {
	return err
}

2021/05/28 11:36:13 http: panic serving [::1]:33166: reflect: call of reflect.Value.Bytes on ptr Value
goroutine 7 [running]:
net/http.(*conn).serve.func1(0xc00032c640)
	/rc/go/src/net/http/server.go:1824 +0x153
panic(0xfa0640, 0xc00081c1e0)
	/rc/go/src/runtime/panic.go:971 +0x499
reflect.flag.mustBe(...)
	/rc/go/src/reflect/value.go:221
reflect.Value.Bytes(0xfb2b20, 0xc0006264c0, 0x196, 0xc0006264b0, 0x199, 0x1)
	/rc/go/src/reflect/value.go:289 +0xfc
github.com/uptrace/bun/schema.appendJSONRawMessageValue(0x1262780, 0xc0004521e0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc0007e4000, 0x36, 0x1000, ...)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/schema/append_value.go:216 +0x4c
github.com/uptrace/bun/schema.(*Field).AppendValue(0xc00012eb00, 0x1262780, 0xc0004521e0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc0007e4000, 0x36, ...)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/schema/field.go:79 +0x1ed
github.com/uptrace/bun.(*UpdateQuery).appendSetStruct(0xc00012e840, 0x1262780, 0xc0004521e0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc0007e4000, 0x1d, ...)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/query_update.go:266 +0x457
github.com/uptrace/bun.(*UpdateQuery).mustAppendSet(0xc00012e840, 0x1262780, 0xc0004521e0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc0007e4000, 0x18, ...)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/query_update.go:218 +0x2e5
github.com/uptrace/bun.(*UpdateQuery).AppendQuery(0xc00012e840, 0x1262780, 0xc0004521e0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xc0007e4000, 0x7, ...)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/query_update.go:174 +0x29e
github.com/uptrace/bun.(*UpdateQuery).Exec(0xc00012e840, 0x125c520, 0xc000110120, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
	/rc/gopath/pkg/mod/github.com/uptrace/[email protected]/query_update.go:312 +0x185
git.engelvoelkers.com/ev/go3/go3-misc/internal/services/user.(*Service).RegisterGoogleAuthCode(0xc00021f7f7, 0x125c520, 0xc000110120, 0x125f798, 0xc000328bd0, 0xc00013c000, 0x49, 0x7f36c5557778, 0xe916b6)
	/rc/rp/internal/services/user/auth.go:54 +0x66d
git.engelvoelkers.com/ev/go3/go3-misc/internal/api/routes.subscribeRoute(0x125f798, 0xc000328bd0, 0xc000132100, 0x203000, 0x41b21b, 0xc000110120, 0x30)

integrating with aws xray for tracing

Hi ,
I am using aws xray for tracing (https://github.com/aws/aws-xray-sdk-go)
I was able to integrate aws-x-ray with bun without any error by doing this:

	connStr := config.GetConfig().ReadPostgresqlConnStr

	instrumentedDB, err := xray.SQLContext("pg", connStr)
	log.Println(err)

	readDBPool = bun.NewDB(instrumentedDB, pgdialect.New())
	readDBPool.AddQueryHook(bundebug.NewQueryHook())

It did not throw any error.

but when I do this:

var result int
	rows, err := db.QueryContext(ctx, "SELECT 1+1")
	if err != nil {
		return false, errors.Wrap(err, errors.DBConnectionFailedException)
	}

	err = db.ScanRows(ctx, rows, &result)
	if err != nil {
		return false, errors.Wrap(err, errors.DBConnectionFailedException)
	}
	return true, nil

It gives me this error:
sql: expected 1 arguments, got 0

same if my psql select query returns 10 values then error says,
sql: expected 10 arguments, got 0

I could not solve this error. Another way I can think of integrating aws xray using hooks. Though that will require more code changes so I prefer to integrate in a above mentioned method.

I created this issue first in aws xray repo but got no reponse there.

Though it was working fine when I did not integrated aws-xray
Note
and I am doing this in local so I have done this:
os.Setenv("AWS_XRAY_SDK_DISABLED", "TRUE")

Call query hook for count queries

Getting wrong data

I'm having a weird issue where I can't wrap my head around.
I'm trying to get an item with ID 13, but it returns a wrong one.

fmt.Println(id)
	mdl := &db.Model{}
	if err := r.client.NewSelect().Model(mdl).Where("id = ?", id).Scan(context.Background()); err != nil {
		return nil, fmt.Errorf("get model: %w", err)
	}

	properties := make([]*model.Property, 0)
	if err := json.Unmarshal([]byte(mdl.Properties), &properties); err != nil {
		return nil, err
	}

	fmt.Println("before return", mdl.Id, mdl.Slug)
	return &model.Model{
		Id:         mdl.Id,
		Name:       mdl.Name,
		Slug:       strings.ToLower(mdl.Slug),
		TitleField: mdl.TitleField,
		Properties: properties,
	}, nil

Logs:
In my controller I fetch the ID from the url: requesting model entities 13
Before running the query the ID is: 13
Query log: [bun] 16:49:16.332 SELECT 460µs SELECT "model"."id", "model"."name", "model"."slug", "model"."title_field", "model"."properties" FROM "models" AS "model" WHERE (id = 13)

Before I return the data, id and slug are from a different record: ID = 14 and slug = aap.

Any pointers?

Is is possible with Bun to insert with relationships?

Is it possible with Bun to insert a Team with it's has-many Players using a single insert?

Example:

team := Team{
  name: "Real"
  players: []Player{
    {name: "John"},
    {name: "Frank"}
  }
}

db.NewInsert().Model(Team).Exec(ctx)

Is this possible?

Something like go-pg/pg#478.

Are fixtures for sql.Nullstring supported?

I'm getting this when trying to load fixtures

2021/06/10 15:18:28 yaml: unmarshal errors:
  line 47: cannot unmarshal !!str `https:/...` into sql.NullString

.yaml extract:

- model: Client
  rows:
    - id: 3392e754-ba3e-424f-a687-add9a8ab39c9
      application_name: test_client_1
      key: test_client_1
      secret: '$2a$10$CUoGytf1pR7CC6Y043gt/.vFJUV4IRqvH5R6F0VfITP8s2TqrQ.4e'
      redirect_uri: https://www.example.com

Model

type Client struct {
	IDRecord
	Key                 string         `bun:"type:varchar(254),unique,notnull"`
	Secret              string         `bun:"type:varchar(60),notnull"`
	RedirectURI         sql.NullString `bun:"type:varchar(200)"`
	ApplicationName     sql.NullString `bun:"type:varchar(200)"`
	ApplicationHostname sql.NullString `bun:"type:varchar(200)"`
	ApplicationURL      sql.NullString `bun:"type:varchar(200)"`
}

Add warning when zero time ('0001-01-01 00:00:00+00:00') is appended

Should I create slices of structs with capacity 0?

I have an old doubt about what's the most performant version of this code (or if it's the same).

I just read https://philpearl.github.io/post/bad_go_not_sizing_arrays/ too.

I'm switching to Bun so I'm asking it right now, I hope you'll help me understand this once for all, @vmihailenco. 😄 😢

This is the code I'm using:

func (r PlayerRepository) Players(ctx context.Context) ([]player.Player, error) {
  limit := 100

  players := make([]Player, limit) // <<<<---------- this line

  query := db.NewCustomQuery(ctx, r.db, &players)

  query.Limit(limit)

  err := query.Select()
  if err != nil {
    return nil, err
  }

  return players, nil
}

The doubt is about using this line:

players := make([]Player, limit)

or this one:

players := make([]Player, 0, limit)

Should I use the first or the second one?

Is this unnecessary for go-pg?

Is the same for go-pg and Bun?

Write/find some script that bump versions/dependencies

It might be good to make the plugin modules explicitly mark their dependency on the main bun module with the same version.

So for example in bun/dialect/pgdialect/go.mod it should say

require github.com/uptrace/bun v0.2.13

Currently it says v0.1.1 instead of v0.2.13

Select only one field from related model on has:one relation

On struct like this

type User struct {
ID string 
AuthenticationProvider *AuthenticationProvider `bun:"rel:has-one"`
}

type AuthenticationProvider struct {
	bun.BaseModel    `bun:"authentication_provider,alias:authentication_provider"`
	UniqueID         string `json:"-" bun:"-"`
	AuthenticationID string `json:"authentication_id"`
	UserId           string `json:"-"`
	ProviderName     string `json:"-"`
}

When I fill values in User struct var and print it , it comes like this:

{
id: "jkdojd",
AuthenticationProvider: {
authentication_id: "jjhkjjlk"
}
}

but I want it like this:

{
id: "jkdojd",
authentication_id: "jjhkjjlk"
}

Right now I am achieving this by changing user struct to like this:

type User struct {
ID string 
AuthenticationProvider *AuthenticationProvider `bun:"rel:has-one"`
AuthenticationID       string                  `json:"authentication_id" bun:"-"`
}

Is there any direct way to achieve same result without adding AuthenticationID field to User struct?

and how does I handle foreign key in structs?

Documentation mistake?

Now

err := db.NewSelect().Model(&users).Scan(ctx)
res, err := db.NewInsert().Model(&users).Exec(ctx)
res, err := pg.NewUpdate().Model(&users).WherePK().Exec(ctx)
res, err := pg.NewDelete().Model(&users).WherePK().Exec(ctx)

Is pg. meant to be in the New section?

Is the documentation website for Bun on github at all?

'fk' tag equivalent?

Continuing migration from go-pg, I've come across this warning:

WARN: bun: 2021/05/24 17:43:52 User.OwnerOfGroups has unknown tag option: "fk"

eg

	OwnerOfGroups          []UserGroup `bun:"fk:owner_id"`

I could not identify this from the (otherwise thorough) documentation provided ...

Is there support/an equivalent in bun? thanks!

`DATETIME` before MySQL 8.0 does not support time zone.

In MySQL 8.0.19 and later, you can specify a time zone offset when inserting a TIMESTAMP or DATETIME value into a table. See Section 9.1.3, “Date and Time Literals”, for more information and examples.
Link: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

mysqldialect append for time.Time should consider this.

bun/dialect/append.go

Line 97 in 57ab844

func AppendTime(b []byte, tm time.Time) []byte {

ERROR #42P02 there is no parameter $1 when using pgdriver with SQLBoiler

I was impressed by the benchmarks relative to pgx, so tried it out on my project that's already using SQLBoiler. However, most of my tests fail with:

ERROR #42P02 there is no parameter $1

For reference, the project is here: https://github.com/hortbot/hortbot

If you want to test this, changing the driver for the entire project is as simple as changing the _ import and driver name here: https://github.com/hortbot/hortbot/blob/master/internal/db/driver/driver.go

I can enable SQLBoiler's debugging to get a trace of the SQL queries it tried for one of the tests:

--- FAIL: TestScripts (0.00s)
    --- FAIL: TestScripts/misc_errors (1.16s)
        print.go:265: SELECT r.* FROM repeated_commands r JOIN channels c ON r.channel_id = c.id WHERE r.enabled AND c.active
        print.go:265: []
        print.go:265: SELECT s.* FROM scheduled_commands s JOIN channels c ON s.channel_id = c.id WHERE s.enabled AND c.active
        print.go:265: []
        write_syncer.go:66: 2021-06-28T20:04:32.448-0700	ERROR	bot/handle.go:81	error during handle	{"xid": "c3d8rg2fi36ho6imuqog", "irc_command": "PRIVMSG", "error": "ERROR #42P02 there is no parameter $1", "message": "@id=d079f2bc-12cc-4c06-a9a4-2212c69decd8;room-id=999;user-id=1 :[email protected] PRIVMSG #hortbot :!join"}
            github.com/hortbot/hortbot/internal/bot.(*Bot).Handle
            	/home/jake/zikaeroh/hortbot/hortbot2/internal/bot/handle.go:81
            github.com/hortbot/hortbot/internal/bot/btest.(*scriptTester).handleM.func1
            	/home/jake/zikaeroh/hortbot/hortbot2/internal/bot/btest/irc.go:57
            github.com/hortbot/hortbot/internal/bot/btest.(*scriptTester).test
            	/home/jake/zikaeroh/hortbot/hortbot2/internal/bot/btest/script_tester.go:207
            github.com/hortbot/hortbot/internal/bot/btest.RunScript
            	/home/jake/zikaeroh/hortbot/hortbot2/internal/bot/btest/script_tester.go:49
            github.com/hortbot/hortbot/internal/bot_test.TestScripts.func1
            	/home/jake/zikaeroh/hortbot/hortbot2/internal/bot/script_test.go:28
            testing.tRunner
            	/usr/lib/go/src/testing/testing.go:1193
        irc.go:129: assertion failed: expression is false: st.sender.SendMessageCallCount() > callNum: SendMessage not called: line 2
FAIL
FAIL	github.com/hortbot/hortbot/internal/bot	1.655s
FAIL

Transactions

Hello!
How do transactions work?
In the docs I find db.Begin() and db.BeginTx() but there is no mention of db.Commit() or db.Rollback() (or equivalent functions)
I've also noticed the functions such as NewSelectQuery() do not support transactions.

Hence my question, are transactions not supported yet? or is the support there but it works in a non obvious way?
Thank you.

Empty model on BeforeInsert hook

My User model

type User struct {
	ID        uuid.UUID `gorm:"default:uuid_generate_v4();->"`
	CreatedAt time.Time
	UpdatedAt time.Time
	Admin          bool
}
func (u *User) BeforeInsert(ctx context.Context, query *bun.InsertQuery) error {
  // u.Email => "" not "[email protected]"
  u.CreatedAt = time.Now()
  u.UpdatedAt = time.Now()
 return nil
}

var signUpUser = &User{
   Email: "[email protected]"
}
tx.NewInsert().Model(signUpUser).Exec(ctx)

INSERT INTO "users" ("id", "created_at", "updated_at", "email") VALUES (DEFAULT, '0001-01-01 00:00:00+00:00', '0001-01-01 00:00:00+00:00', '', '[email protected]') RETURNING "id"

created_at => 0
updated_at => 0

Why I must use query.GetModel() all time instead use self object?

I have this code:

type ChainHookFn func(context.Context) error

func HooksChain(ctx context.Context, hookFns ...ChainHookFn) error {
	for _, hookFn := range hookFns {
		if err := hookFn(ctx); err != nil {
			return err
		}
	}
	return nil
}

I have hook chains in my models and all was good)

func (u *User) BeforeUpdate(ctx context.Context, query *bun.UpdateQuery) error {
	return HooksChain(ctx, u.updatePassword, u.validate)
}
func (u *User) updatePassword(_ context.Context) error {
	if len(u.Password) > 0 {
		if result, err := bcrypt.GenerateFromPassword([]byte(u.Password), bcrypt.DefaultCost); err != nil {
			return err
		} else {
			u.HashedPassword = string(result)
		}
	}
	return nil
}

func (u User) validate(ctx context.Context) error {
	validators := []*validator.Validator{
		validator.Validate("email", u.Email).Rules(
			rules.IsPresence(),
			rules.IsEmail()
		),
	}
	if len(u.HashedPassword) == 0 {
		validators = append(validators, validator.Validate("password", u.Password).Rules(
			rules.IsPresence(),
		))
	}
	return validator.Scope(validators...).Validate(ctx)
}

I can create long hook-chains for each models by logic.
After update, I must all time use query.Get Model().Value().(*SomeModel) boilerplate... :( and must thinking about type casting.

Unexpected data populated into model instance after SELECT

@vmihailenco (similar to #25)

thisuser := new(model.User)

err = s.db.NewSelect().
  Model(thisuser).
  Where("id = ?", OwnerUUID).
  Scan(ctx)

This was populating the wrong user in the model instance thisuser despite the CORRECT query being run and the correct data returning if run directly in, say, pgAdmin.

Once I did this, however, it worked! (despite the same query being apparently generated each time!):

err = s.db.NewSelect().
  Model(thisuser).
  Where("? = ?", bun.Ident("id"), OwnerUUID).
  Scan(ctx)

Is that expected behaviour?

FYI, this may partially explain it, I'm doing something funky with my IDs

Each model shares this struct:

type IDRecord struct {
	ID        uuid.UUID `bun:"type:uuid,default:uuid_generate_v4()"`
	CreatedAt time.Time `bun:",nullzero,notnull,default:current_timestamp"`
	UpdatedAt time.Time
	DeletedAt time.Time `bun:",soft_delete"`
}

so my User model begins:

type User struct {
	IDRecord

This is done to avoid repetition and inconsitency, but may create issues with bun?

YAML fixtures should provide a better way to work with sql.Null*

E.g. it should be possible to use YAML null. Model:

type User struct {
    UpdatedAt sql.NullBool
}

Fixture:

- model: User
  rows:
    - _id: admin
      updated_at: null

Also #17

Can Bun create tables if I don't know how to?

With go-pg I was using this code:

func main() {
	opts := &orm.CreateTableOptions{
		IfNotExists:   true,
		FKConstraints: true,
	}

	tables := []interface{}{
		(*Team)(nil),
		(*Player)(nil),
		(*User)(nil),
		(*Invoice)(nil),
		(*Address)(nil),
		(*Tax)(nil),
		(*Service)(nil),
		(*Setting)(nil),
	}

	for _, model := range tables {
		err := db.Model(model).CreateTable(opts)
		// if err...
	}
}

and this was quite awesome (especially if you don't know how to create such tables in Postgres or SQLite)!

Is this possible with Bun?

Logrus logging

I've added a small package that provides a hook to enable logging with logrus, Is it implemented correctly? I've based it on how the logging is done internally: https://github.com/oiime/logrusbun

panic: sql: Scan error on column index 6, name "network": invalid character '.' after top-level value

migrating from go-pg, is net.IPNet no longer supported?

type Foo struct{
   Network net.IPNet `bun:"network,type:inet"`
}

func main() {
   var f  []Foo;
   var err = Database.NewSelect().Model(&f).Scan(context.Background());
}

panic: sql: Scan error on column index 6, name "network": invalid character '.' after top-level value #38

Typo in hook cheking

https://github.com/uptrace/bun/blob/v0.1.17/schema/table.go#L788 mistake

Error checking when creating new db with invalid DSN?

If my DSN is, for example, the string "bogus", this will not generate an error:

	sqldb, err := sql.Open("pgx", cnf.Database.PSN)

This is exacerbated by this not returning an error attribute:

  db := bun.NewDB(sqldb, pgdialect.New())

Should the connection be tested at this point and return an error if it's an erroneous PSN?

I can force a health query at this stage in setup, but should we need to? e.g.:

_, err = db.Exec("SELECT 1=1")

db.RunInTx helper (similar to what we have in go-pg)

Make migrations more explicit and generic

I've started to use bun migrations and I have a few suggestions for possible improvements and would like to hear your opinions on them. And if you approve some of them I could start working on a PR.

1. Remove context and bun.DB parameters from migrations.CreateGo or migrations.CreateSQL function since they are not used. Is it planned for the future?
2. Make migration directory configurable. Currently, it gets the caller directory, which means I have to alias Create(.*) functions in the directory where my migrations are located since I'm calling Create function from some other directory.
3. Make package name configurable for CreateGo migration (and/or item 6.)

func CreateGo(directory, name, package string) error
func CreateSQL(directory, name string) error

4. Replace logging with fmt.Printf with user configurable logger. Maybe global log function for migration package? Something like:

var log = func(format string, a ...interface{}) {
	fmt.Printf(format, a...)
}

func SetLogFn(fn func(format string, a ...interface{})) {
	log = fn
}

5. Add more code to generated go template like the one bellow since this is a MVP for go migration file.

package {{ package_name }}

import (
	"context"

	"github.com/uptrace/bun"
)

func init() {
	Migrations.MustRegister(func(ctx context.Context, db *bun.DB) error {
		fmt.Print(" [up migration] ")
		return nil
	}, func(ctx context.Context, db *bun.DB) error {
		fmt.Print(" [down migration] ")
		return nil
	})
}

6. Add option for adding user template for go and sql migration file creation.
7. Add a check before migrate if the required migration tables exist, if not, initialize. Quick and dirty way is to run Init(...) on every migration. Might be cheaper to execute then checking first. But speed is not an issue here.
8. Status function should return a struct containing migration status and not print to console.

Use MySQL for `example/` is better.

As the MySQL database server is the world's most popular open source database.

Use MySQL for example/ is better then SQLite.

Is Bun docs project public? Can I subscribe to it?

I'm switching to Bun (from go-pg) and I would like to follow all the news in the documentation, but I can't find the docs in Github. Is the source public? So I can subscribe to any news.

Add mariadb dialect

Compared with MySQL, it supports DEFAULT keyword in inserts - https://mariadb.com/kb/en/insert/

Plans for a migration tool similar to go-pg's?

Do you have any plans to make a migration tool similar to what you did with go-pg? It would be nice to have an orm that also supported a tool like that easily that could be used with multiple databases. Maybe even database specific migrations?

Like SQLite requires a clause to enable foreign keys, so it wouldn't make sense to run such a script against a PostgreSQL database.

View migrator feature?

So far, most of the logic I have seen for handling migrations, is written to handle table creation. But database views are also a very powerful concept that is relevant to database related development as it can greatly simplify the database queries in the application.

Views are different from most tables in that you never alter a view; you drop it and recreate it. However, it is still useful to describe multiple versions of a view, so that you can "migrate" to a specific view version. While doing so, you don't want to drop and recreate all the intermediate view versions.

So a "migrator" for a view would, instead of tracking "applied" migrations, track only a single version per view (or group of views). If the applied version does not match the target version, run the "target" migration "up". We can assume this migration starts with a DROP VIEW x IF EXISTS; before creation.

The approach can also be used to manage materialized views.

Having a separate migration concept for view could be a real power feature. If it's useful to include in the scope of bun or bun/migrate is a completely different question, so please feel free to reject this.

How to reset `deleted_at` if in "soft delete" mode?

This query fails:

  db.NewUpdate().
    Model(new(model.User)).
    Set("deleted_at = NULL").
    WhereAllWithDeleted().
    Exec(ctx)

(I'm trying to reset state during a test suite run)

Roadmap

• Rework hooks. Leave only BeforeScan and AfterScan. The rest will be replaced with hooks that accept queries so the following should be possible

func (u *User) BeforeCreateTableQuery(ctx context.Context, q *CreateTableQuery) error {
    if q.GetDB().Dialect().Name() == dialect.SQLite {
        // do something
    }
    return nil
}

func (u *User) AfterCreateTableQuery(ctx context.Context, q *CreateTableQuery) error {
    _, err := q.GetDB().NewCreateIndex().Foo().Bar().Exec()
    return err
}

• Add CreateIndex and DropIndex queries
• Add AddColumn, ModifyColumn, add DropColumn queries
• Port migrations
• Possibly add YAML fixtures
• Possibly add AutoMigrate - use truncate + fixtures
• Add truncate to use with fixtures
• Provide a starter kit that integrates everything together - https://github.com/go-bun/bun-starter-kit

Build fail, error in db.go

Hi ,
I have a github action in my project that checks if build is success or not.
This action is throwing me this error:

./../go/pkg/mod/github.com/uptrace/[email protected]/db.go:417:39: row.Err undefined (type *sql.Row has no field or method Err)

And in bun repo

bun/db.go

Line 275 in 24e0fb3

c.db.afterQuery(ctx, event, nil, row.Err())

I think this is a bug in bun , though project is running fine in my system

Remove WherePK and instead provide helpers db.Insert, db.Update, and db.Delete

Relations: "on_delete" tag option and do I still need notnull?

With go-pg I had:

type Player struct {
  // ... many columns

  ProfileID int       `pg:",notnull,on_delete:CASCADE"`
  Profile   *Profile  `pg:"rel:has-one"`
}

I changed it to:

type Player struct {
  // ... many columns

  ProfileID int       `bun:",notnull,on_delete:CASCADE"`
  Profile   *Profile  `bun:"rel:has-one"`
}

But:

1. it writes in console:

   WARN: bun: 2021/05/28 23:48:25 Player.ProfileID has unknown tag option: "on_delete"
   

   Why?

2. do I need notnull in this line?

   ProfileID int `bun:",notnull,on_delete:CASCADE"`

Do we have prepared statements and do we cache them?

Do we have prepared statements and do we cache them?

Can we add a page in docs explaining this?