<p>I have just started learning Go and I am trying to build a simple blog (basic CRUD with HTML forms). I am using only the standard library.</p>
<p>When I try to save a post, I want to validate that the title and slug are present and they are at least n characters in length. I am able to do this successfully using the technique mentioned <a href="http://www.alexedwards.net/blog/form-validation-and-processing" rel="nofollow">here</a>.</p>
<p>Now the problem that I am facing is that there is a unique index on the 'slug' field. When I try to save a post, the error that is returned is not something that I can display to the users. </p>
<p>I think I will face the same issue when I try to implement a login/signup system (with unique emails).</p>
<p>Is there a way to map the SQL error to readable messages? </p>
<hr/>**评论：**<br/><br/>yRZ6UqzkuYJhKrf4HY3K: <pre><p>You could do something like this if you are using Postgres:</p>
<pre><code>if pgerr, ok := err.(*pq.Error); ok {
if pgerr.Code == "23505" {
//handle duplicate insert
}
}
</code></pre></pre>_learner_: <pre><p>Hmm, does this say which field caused the failure? (e.g: if there is a unique index on the slug and a foreign key constraint on another field).</p>
<p>I was hoping if there was a way to handle this dynamically.</p></pre>rwbcxrz: <pre><p>For unique constraint violations, I believe Postgres reports the constraint name, not the field name (since multiple fields could be involved).</p>
<p>I'd recommend testing it out and checking all of the fields in <a href="https://godoc.org/github.com/lib/pq#Error" rel="nofollow"><code>pq.Error</code></a> to see what data Postgres provides.</p></pre>_learner_: <pre><p>Yes, I just tried it out. I think I can work with this.</p>
<p><code>pgerr.Constraint</code> will give the name of the constraint (e.g: <code>users_email_index</code>). I can use that to write out a friendly message.</p>
<p>Thanks!</p></pre>yRZ6UqzkuYJhKrf4HY3K: <pre><p>You mean inserting a foreign key value that doesn't exist? That should be a different error code. List of errors can be found in pq/error.go: <a href="https://github.com/lib/pq/blob/master/error.go#L177" rel="nofollow">https://github.com/lib/pq/blob/master/error.go#L177</a></p>
<p>If you have two unique columns on the table, you could follow up the error with a select to the table to find out which value was a duplicate. Something like this:</p>
<pre><code>select coalesce(select 1 from table where slug = $1,0) as slug_is_dupe
,coalesce(select 1 from table where id = $2,0) as id_is_dupe
</code></pre></pre>nabla: <pre><p>This is a bad practice. PITA to debug and I'm not sure how does it affect current transaction.</p>
<p>Just check if the slug is already in the database before inserting new one.</p></pre>_learner_: <pre><p>Would that not mean two SQL queries in place of one.</p>
<p>Also what happens in the scenario when 2 simultaneous web processes are making requests with the same slug? I think the SELECT query will succeed for both, and a duplicate record will be created.</p></pre>nabla: <pre><p>Ask yourself: </p>
<p>Ad1. Are you doing premature optimization? </p>
<p>Ad2. How many customers or how many request/s you'll to need to have to make this situation real? What transaction level are using? </p>
<p>Duplicate record will not be created. You've go constraint on that. </p>
<p>My solution is a trade off: Simpler code and epsilonial propablity of failed insert vs complex code. Simpler is cheaper to maintain in long term.</p></pre>gordon-gopher: <pre><p>How can making 2 queries and then handling 2 errors be simpler than 1 of each?</p>
<p>I'm a little surprised to see someone in a Go forum intentionally, and presumably with a straight face, suggest a race condition as a solution to anything and then try to play it off like it's no big deal.</p></pre>nabla: <pre><p>I've made assumption: all SQL statements are executed within transaction. </p>
<p>Here is a simple code. How does it change tables foo and bar in the database?</p>
<h2>init.sql:</h2>
<pre><code> -- main entity
create table foo(id int primary key);
-- other table e.g. activity log, accounting, whatever
create table bar(bar int);
-- database initial state:
insert into foo values(1);
</code></pre>
<hr/>
<h2>test.go</h2>
<pre><code>package main
import (
"database/sql"
"fmt"
"log"
"github.com/lib/pq"
)
func main() {
checkErr := func(err error) {
if err != nil {
log.Fatal(err)
}
}
dbinfo := fmt.Sprintf("user=%s password=%s dbname=%s sslmode=disable", "test", "test", "test")
db, err := sql.Open("postgres", dbinfo)
checkErr(err)
defer db.Close()
// here is a begin of request handling
// we've got single transaction while handling a request
//
tx, err := db.Begin()
checkErr(err)
// do some pre-business logic and pollute other table
_, err = tx.Exec("insert into bar values(1)")
checkErr(err)
// now we're trying to add duplicated value
_, err = tx.Exec("insert into foo values(1)")
if pgerr, ok := err.(*pq.Error); ok {
if pgerr.Code == "23505" {
log.Println("Sorry, 1 already exists.")
}
}
// we've got another business logic here
_, err = tx.Exec("insert into bar values(2)")
checkErr(err)
// end of request handling,
// commit changes
err = tx.Commit()
checkErr(err)
}
</code></pre>
<hr/></pre>
