Logger or output of raw SQL generated about pg HOT 19 CLOSED

As it mentioned in wiki it also can be done this way:

type dbLogger struct { }

func (d dbLogger) BeforeQuery(c context.Context, q *pg.QueryEvent) (context.Context, error) {
	return c, nil
}

func (d dbLogger) AfterQuery(c context.Context, q *pg.QueryEvent) error {
	fmt.Println(q.FormattedQuery())
	return nil
}

db := pg.Connect(&pg.Options{...})
db.AddQueryHook(dbLogger{})

from pg.

No, but in development environment you can adjust PostgreSQL to log all queries:

log_statement = 'all'
log_min_duration_statement = 0

Then postgresql-9.4-main.log will contain generated SQL. Is it enough for your needs?

from pg.

@PhillyWebGuy you likely have an incorrect import. Make sure you have imported:

"github.com/go-pg/pg/v10"
not
"github.com/go-pg/pg"

from pg.

tableName string pg:"test_user" sql:"test_user"

This worked

from pg.

That works for now. Thanks.

from pg.

When I try that, I get this:
cannot use dbLogger literal (type dbLogger) as type pg.QueryHook in argument to db.baseDB.AddQueryHook: dbLogger does not implement pg.QueryHook (wrong type for AfterQuery method) have AfterQuery(context.Context, *pg.QueryEvent) error want AfterQuery(*pg.QueryEvent)

from pg.

This seems to work when trying to AfterQuery method. I haven't worked out the BeforeQuery method yet:

`type dbLogger struct{}

func (d dbLogger) AfterQuery(q *pg.QueryEvent) {
fmt.Println(q.FormattedQuery())
return
}

db := pg.Connect(&pg.Options{...})
db.AddQueryHook(dbLogger{})`

from pg.

I am not able to print my query
Can someone help me with a example

from pg.

@g-harshit

https://github.com/monetr/monetr/blob/9f3129958e49e2ef94497e1daa7a3595b6d3f89d/pkg/logging/pg.go

This file has an example of a logrus hook that I made to print queries along with some basic context around them, as well as sending query information to sentry.io.

Specifically:

func (h *PostgresHooks) BeforeQuery(ctx context.Context, event *pg.QueryEvent) (context.Context, error) {
	query, err := event.FormattedQuery()
	if err != nil {
		return ctx, nil
	}
	if strings.TrimSpace(strings.ToLower(string(query))) != "select 1" {
		h.log.WithContext(ctx).Trace(strings.TrimSpace(string(query)))
	}

	return ctx, nil
}

from pg.

@elliotcourant

Also let me know how to use your code.

type Hook struct {
	Id    int
	Value string
}

func (d Hook) BeforeQuery(c context.Context, q *pg.QueryEvent) (context.Context, error) {
	q.StartTime = time.Now()
	return c, nil
}

func (d Hook) AfterQuery(c context.Context, q *pg.QueryEvent) error {
	fmt.Println("----DEBUGGER----")
	fmt.Println(q.FormattedQuery())
	fmt.Printf("%s%s\n\n", time.Since(q.StartTime), q.Err.Error())
	return nil
}

h := Hook{}
h.BeforeQuery(conn.Context(), &pg.QueryEvent{})
h.AfterQuery(conn.Context(), &pg.QueryEvent{})
conn.AddQueryHook(h)

This is giving panic

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x2 addr=0x18 pc=0x102c3f9f8]

goroutine 1 [running]:
gitlab.com/g-harshit/plib/database/postgresql.Hook.AfterQuery({0x140004f3d08?, {0x102a2b6f0?, 0x102e26a88?}}, {0x14000134008?, 0x14000134008?}, 0x140004c1ae0)
	/Users/harshitgupta/go/src/gitlab.com/g-harshit/plib/test/vendor/gitlab.com/g-harshit/plib/database/postgresql/hook.go:19 +0xd8
github.com/go-pg/pg/v10.(*baseDB).afterQueryFromIndex(0x140004ac140, {0x102e26a88, 0x14000134008}, 0x140001aa000?, 0x140004f3cb8?)
	/Users/harshitgupta/go/src/gitlab.com/g-harshit/plib/test/vendor/github.com/go-pg/pg/v10/hook.go:130 +0x78
github.com/go-pg/pg/v10.(*baseDB).afterQuery(0x140004ac140?, {0x102e26a88?, 0x14000134008?}, 0x140004f3e28?, {0x102e263c0?, 0x1400014e0a0?}, {0x0?, 0x0?})
	/Users/harshitgupta/go/src/gitlab.com/g-harshit/plib/test/vendor/github.com/go-pg/pg/v10/hook.go:125 +0xb4
github.com/go-pg/pg/v10.(*baseDB).query(0x140004ac140, {0x102e26a88, 0x14000134008}, {0x102d53ee0, 0x14000128690}, {0x102d6b920, 0x140004bbe50}, {0x0, 0x0, 0x0})
	/Users/harshitgupta/go/src/gitlab.com/g-harshit/plib/test/vendor/github.com/go-pg/pg/v10/base.go:324 +0x368
github.com/go-pg/pg/v10.(*baseDB).Query(0x140004ac140, {0x102d53ee0, 0x14000128690}, {0x102d6b920, 0x140004bbe50}, {0x0, 0x0, 0x0})
	/Users/harshitgupta/go/src/gitlab.com/g-harshit/plib/test/vendor/github.com/go-pg/pg/v10/base.go:286 +0x7c

from pg.

At a glance it looks like the line

fmt.Printf("%s%s\n\n", time.Since(q.StartTime), q.Err.Error())

is the one thats causing the panic, becausee q.Err would be nil when you call h.AfterQuery(conn.Context(), &pg.QueryEvent{})

from pg.

@elliotcourant

It worked but how can I print file name and function name and line no . Where I will get them

from pg.

The file name and function that is calling the query? You'd have to traverse the stack trace a bit. https://golang.hotexamples.com/examples/runtime/-/Caller/golang-caller-function-examples.html these might be some good examples to get started?

from pg.

Thanks

from pg.

Can you help me in different issue

That issue was solved. Thanks for the help. I have some other issue
I am facing issue in creating table
//TestUser Table structure as in DB
type TestUser struct {
tableName struct{} sql:"test_user"
UserID int json:"user_id" sql:"user_id,type:serial PRIMARY KEY"
Username string json:"username" sql:"username,type:varchar(255)"
Password string json:"-" sql:"password,type:varchar(255) NULL"
PassSalt string json:"-" sql:"pass_salt,type:varchar(255) NULL"
Email string json:"email" sql:"email,type:varchar(255) UNIQUE"
Name string json:"name" sql:"name,type:varchar(255)"
AltContactNo string json:"alt_contact_no" default:"true" sql:"alt_contact_no,type:varchar(20)"
AltPhoneCode string json:"alt_phonecode" default:"true" sql:"alt_phonecode,type:varchar(20)"
Landline string json:"landline" default:"null" sql:"landline,type:text NULL DEFAULT NULL"
Department string json:"department" default:"null" sql:"department,type:varchar(100)"
Designation string json:"designation" default:"null" sql:"designation,type:varchar(100)"
EmailVerified string json:"email_verified,omitempty" sql:"email_verified,type:yesno_type NOT NULL DEFAULT 'no'"
PhoneVerified string json:"phone_verified,omitempty" sql:"phone_verified,type:yesno_type NOT NULL DEFAULT 'no'"
WhatsappVerified string json:"whatsapp_verified,omitempty" sql:"whatsapp_verified,type:yesno_type NOT NULL DEFAULT 'no'"
Attribute map[string]interface{} json:"attribute,omitempty" sql:"attribute,type:jsonb NOT NULL DEFAULT '{}'::jsonb"
LastLogin time.Time json:"last_login" sql:"last_login,type:timestamp"
CreatedBy int json:"-" sql:"created_by,type:int NOT NULL REFERENCES test_user(user_id) ON DELETE RESTRICT ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED"
CreatedAt time.Time json:"-" sql:"created_at,type:timestamp NOT NULL DEFAULT NOW()"
UpdatedAt time.Time json:"-" sql:"updated_at,type:timestamp NOT NULL DEFAULT NOW()"
}

This is my table

if err = tx.Model(tableModel).CreateTable(
&orm.CreateTableOptions{IfNotExists: true}); err == nil {
}
This is I am using for creating table
But this is generating wrong create query

CREATE TABLE IF NOT EXISTS "test_users" ("user_id" bigint, "username" text, "password" text, "pass_salt" text, "email" text, "name" text, "alt_contact_no" text, "alt_phone_code" text, "landline" text, "department" text, "designation" text, "email_verified" text, "phone_verified" text, "whatsapp_verified" text, "attribute" jsonb, "last_login" timestamptz, "created_by" bigint, "created_at" timestamptz, "updated_at" timestamptz)

See the table name has extra s at last. Can you tell me how I can resolve this.

from pg.

If you change the tableName field to be:

tableName string `pg:"test_user"`

does it work then?

from pg.

No

from pg.

Hello
Can you Tell How can I remove a hook in connection if once added

from pg.

I believe you'd have to initialize a new DB object entirely and simply not register that hook. It'd probably be better to have the hook functions itself though be aware of when you do and do not want them to be fired rather than trying to "remove the hook".

from pg.