Golang & Postgres Interaction - Subtract 1 From int64 Table Every Query

Question

I'm learning to create HTTP API's,

I'm creating a dummy API credit system, as that's what I'm interested in specifically.

This is what i came up with: (I'm using PQ Driver)

if !dummy.creds <= 0 {
    c.JSON(404, gin.H{
        "success": false,
        "message": "No Credits!",
    })
    return
}

However, let's say i edit the SQL table to say 50 Credits, How do subtract 1 credit everytime the dummy API is queried?

So for example, if i Query the API and it returns the successful dummy data (in JSON) from the SQL table, how can i subtract one credit, so that i only have 49 credits, then 48, 47, 46, etc.

UPDATE: This is what i came up with

_, err := db.Exec("UPDATE dummy SET creds = -1 WHERE email = $1")
if err != nil {
    log.Fatal(err)

}

Instead of working, it fails to do anything.

What am i doing wrong?

Any answers & knowledge into this would be amazing!

Thanks!

@mkopriva can you give me an example/reference for this? Wouldn't know where to start, tried to google for a few days and even purchased a few go guides. — AndrewWilliams, Aug 16 '18 at 23:03
I believe the question is about returning consistent results. It's not about go then. It's about database. Search "optimistic locking" vs "pessimistic locking", "select for update" etc. — Seva, Aug 17 '18 at 06:48
Here is also a basic (but good) introduction to working with databases in go (if you need it): http://go-database-sql.org/index.html — Seva, Aug 17 '18 at 06:49
@Seva That helped me understand somethings, but i still can't manage to pass a SQL query into postgres from go. Can you check out the updated question and see if you can spot any mistakes? — AndrewWilliams, Aug 17 '18 at 08:47
First of all, you aren't decrementing creds there - you set it to -1. Secondly, you have declared a $1 parameter, but didn't supply a value for it. I believe what you meant should have looked more along these lines: userEmail:="whoever@whereever.com"; _, err := db.Exec("UPDATE dummy SET creds = creds-1 WHERE email = $1", userEmail) — Seva, Aug 20 '18 at 08:57

score 0 · Accepted Answer · answered Aug 19 '18 at 11:02

As posted by @seva

I believe the question is about returning consistent results. It's not about go then. It's about database. Search "optimistic locking" vs "pessimistic locking", "select for update" etc. Here is also a basic (but good) introduction to working with databases in go (if you need it): go-database-sql.org/index.html

Golang & Postgres Interaction - Subtract 1 From int64 Table Every Query

1 Answers1