vividcortex / go-database-sql-tutorial Goto Github PK
View Code? Open in Web Editor NEWA tutorial for Go's database/sql package
Home Page: http://go-database-sql.org
License: Creative Commons Attribution Share Alike 4.0 International
A tutorial for Go's database/sql package
Home Page: http://go-database-sql.org
License: Creative Commons Attribution Share Alike 4.0 International
Drivers can implement some level of convenience, but ultimately the user can still be exposed to the low-level way the data is transmitted in the driver. For example, the mysql driver will do a strconv.ParseXXX to scan into integers and floats and so on, which avoids some code in your application sometimes. But it is still possible for users to see the differences in binary versus textual protocol; see for example go-sql-driver/mysql#211
What if the database server is set to autocommit=0? Then every new connection is in a transaction automatically. Seems like this will cause problems. We should explain whether this is true or not.
I think it would be easier to navigate through the pages if each page had "previous" and "next" links at the bottom.
Currently you have to go through the list and find the page where you currently are before clicking the link to the next page (the list item below).
when work with gopkg.in/rana/ora.v3 or github.com/mattn/go-oci8
how to Working with Unknown Columns
my code
values := make([]sql.RawBytes, len(cols)) scans := make([]interface{}, len(cols)) for i := range values { scans[i] = &values[i] } results := make(map[int]map[string]string) i := 0 for query.Next() { if err := query.Scan(scans...); err != nil { fmt.Println("scan err:", err) return } row := make(map[string]string) for j, v := range values { key := cols[j] fmt.Println("xx", j, v) row[key] = string(v) } results[i] = row i++ }
and the error is :
scan err: sql: Scan error on column index 0: unsupported Scan, storing driver.Value type time.Time into type *sql.RawBytes
I've seen "too many prepared statements" errors in MySQL when I didn't close a statement after using it (and created new statements in a loop). We need to make it clear that statements shouldn't be created and closed a lot if they're going to be reused -- but they equally shouldn't be left open forever.
At least with the go-sql-drivers/mysql driver, I see that I can kill database connections and continue to call stmt.Query() without any trouble. Either database/sql or the driver is catching the error from the closed connection, opening a new one, preparing a new statement, and retrying the Query() without returning an error to my code.
Which one is doing the magic? I think this should be documented.
I think a page illustrating them would be a huge benefit. We (VividCortex) recently implemented a type that does seamless encrypt/decrypt and our code is absolutely clean. What a great thing. Jason Moiron's article on these is excellent. I think I would like to adapt it, trim it down, and give him credit: http://jmoiron.net/blog/built-in-interfaces/
Hi. Thanks for the useful tutorial.
When preparing a statement with placeholder parameters, both the official "database/sql" docs and your tutorial say to use question marks. Surprisingly, that causes PostgreSQL to spit a cryptic error. Turns out it wants syntax like VALUES($1, $2, $3)
and not VALUES(?, ?, ?)
.
I know you explicitly narrow in on MySQL up front, but as a friendlier alternative to the official docs, it would be helpful to note that ?
isn't universal.
A common problem is, that applications often reach too high concurrency at some point. This leads to too many open connections.
With the Go-MySQL-Driver this leads to Error 1040: Too many connections
errors.
Some people confuse DB.SetMaxIdleConns with having control over the maximum number of connections, but unfortunately in reality you have no control over it via the database/sql package: https://code.google.com/p/go/issues/detail?id=4805
It would be great if you could add a section which covers this issue and some possible solutions. For example:
I won't have time to prepare a PR until mid. August, so I hope someone other finds a few good words on this issue ;-)
In the MySQL driver I like, the idiomatic code looks like
if mysqlErr, ok := err.(*mysql.MySQLError); ok {
// ERROR 1045 (28000): Access denied for user 'foo'@'::1' (using password: NO)
if mysqlErr.Number != 1045 {
This driver doesn't export error codes, for good reasons. Some drivers such as https://github.com/lib/pq do, and so the code would look slightly different. Also, VividCortex is maintaining a list of MySQL error codes here: https://github.com/VividCortex/mysqlerr
This is a pretty common FAQ on the MySQL driver's issue list. Worth documenting and explaining the variations and the available resources?
Could you help us out by adding a linkable section with frequently encountered problems to the database tutorial?
I'd really love to have a bunch of short linkable answers ready when closing issues for the mysql driver - but I lack the experience in technical writing (and am not a native speaker).
What I have in mind is a way to quickly answer and close issues like
"SELECT ?
does not work with a slice".
Hi,
thanks for this resource; it's been very useful for a golang newbie like me.
In particular, it gave me the first clue about how to fix my error, that looked
like: "sql: statement expects 0 inputs; got 1".
That clue was in the fourth paragraph of your tutorial and went like "...the most
surprising [difference] can be the need to use different syntax for placeholder
parameters in prepared statements."
Here was I using the standard Java convention of "?" placeholder characters, when
after some investigation, it turns out that Oracle likes very different placeholders,
whose first character is a colon. For example;
"select * from myTable where myColumn = :a"
Since you already mention the syntax for MySQL and PostgreSQL, could
I suggest you add an example of what the Oracle syntax looks like.
I'm sure it will be useful to someone, in the future.
David
What license is this project released under? Will it be okay if I incorporated content form here verbatim in an open source book I am writing?
The link at the end of each page is broken.
Example: index.md
The link to [Overview of Go's database/sql Package] should be:
https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/overview.md,
but it's
https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/overview.html
Hello, I really appreciate the site! But, as I follow it, when I want to access some data, I can't because I don't have any. So I had to Google to see if there's an idiomatic way to insert before I could return to the tutorial. If you're amenable, I'd be happy to make a pull request adding a paragraph or so creating and inserting data in the "Retrieving Result Sets" page.
Hi!
First of all, amzing tutorial. Thank you very much.
In the Error section of the tutorial you say:
This code is still ugly. Comparing to 1045, a magic number, is a code smell. Some drivers (though not the MySQL one, for reasons that are off-topic here) provide a list of error identifiers.
Browsing through the source code of the MySQL driver you use I find that they do have exported error values.
Using sql.NullString as a property type leaves a bad taste in the mouth when designing structs, as it seems to tightly couple SQL to models that may generally be independent of SQL.
I've found that using *string
(*int
, etc) is more palatable. With automatic dereferencing, it hasn't been much of an issue. I'm curious re: your take on this, and re: your thoughts on including it in the section on NULL values.
rows.Close and rows.Err are kind of jumbled, as documented in #61 .
In many examples you're using log.Fatal, this will exit the program immediately with os.Exit(1) and probably isn't something you should encourage newcomers to the language to use. It would be nice if the examples just returned errors or logged them, not exited the program (which should be very unusual in go code). It also means things like defer statements you have in there wouldn't run.
So this example is misleading for example:
http://go-database-sql.org/prepared.html
because the defer is never called, since you call log.Fatal which calls os.Exit.
The comment about Go 1.4 will probably cause confusion now too as it is really out of date, probably better just to remove that now that the fix is in, or demote it to a footnote?
LOCK TABLES is not transaction-safe
And
UNLOCK TABLES implicitly commits any active transaction
Recommended to do:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
But per golang transactions doc
Its recommended to:
End the transaction with (Commit, Rollback)
Hence the Mysql recommendation contradicts the golang recommendation. I haven't found proper guidance on how to use transactions with locking in golang.
Do you have guidance (with actual example) of how to do transactions with lock tables?
Would that guidance be don't use transactions but use a dedicated connections?
A doc on this would be very helpful.
FWIW, my use case a standard go webapp running as a pool behind a LB. Any webapp can write to the DB, I want to ensure consistency when writing.
Seems quite standard but I'm having a surprisingly hard time finding examples of this. Any help/guidance would be much appreciated.
I just got an email from Github. Copying it below...
Hi VividCortex,
The custom domain for your GitHub Pages site VividCortex/go-database-sql-tutorial needs attention. You must take immediate corrective action to ensure that your site remains available after December 1st, 2014.
Please follow the instructions for setting up a custom domain with GitHub Pages to update your custom domain’s DNS settings to point to the proper GitHub IP addresses.
Why the change?
Nearly a year ago, we announced improvements to how we serve GitHub Pages sites. This week we’re making that change permanent by deprecating our old GitHub Pages infrastructure. If your custom domain is pointed at these legacy IPs, you’ll need to update your DNS configuration immediately to keep things running smoothly.
How long do I have to make the switch?
Starting the week of November 10th, pushing to a misconfigured site will result in a build error and you will receive an email stating that your site’s DNS is misconfigured. Your site will remain available to the public, but changes to your site will not be published until the DNS misconfiguration is resolved.
For the week of November 17th, there will be a week-long brownout for improperly configured GitHub Pages sites. If your site is pointed to a legacy IP address, you will receive a warning message that week, in place of your site’s content. Normal operation will resume at the conclusion of the brownout.
Starting December 1st, custom domains pointed to the deprecated IP addresses will no longer be served via GitHub Pages. No repository or Git data will be affected by the change.
Okay, I’m sold. What do I need to do?
Please follow the instructions for setting up a custom domain with GitHub Pages to update your custom domain’s DNS settings to point to the proper GitHub IP addresses.
Questions? We’re here to help.
Happy Publishing!
— The GitHub Pages Team
The titles on the left are veeeeeeery long.
They mirror the section headers, but each should probably be shortened to fit in one line.
Navigation titles can be changed here: _layouts/article.html - we should probably also raise the span in line 51 if we can't shorten the titles enough.
Formatting of the sidebar resides in _includes/leftnav.html, but I don't think anything has to be changed there.
First, thanks for the incredibly useful website.
I chose the word might in the title of the Issue because I personally found the website much easier to read by opening the Safari Web Inspector and using Helvetica instead of Open Sans. Looking at other examples of Open Sans on the web, it seems that there's something strange going on in this website's kerning type settings.
Obviously, feel free to close and ignore this if not useful 😄
I feel that the Go community is missing good example database/sql
applications that exhibit these best practices and others in terms of how to design a larger application that needs to also interact with a database.
It'd be nice if this guide could link to those apps so programmers can see these techniques put to use. (Unfortunately I can't really offer any suggestions, I've not found any open source apps yet that are good examples.)
I want to translate this tutorial into Chinese. How should I organize the translated documents?
I fork this repo and add zh-CN
folder into it for now, but I noticed that some css or js have broken after building.
You can visit this page for more information. https://rayhy.github.io/go-database-sql-tutorial/zh-CN/
At the database level, a prepared statement is bound to a single database connection.
When you execute the Stmt, it tries to use the connection. If it’s not available because it’s closed or busy doing something else, it gets another connection from the pool and re-prepares the statement with the database on another connection.
Is that any other existing connection? Or does the database create a new connection?
https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/surprises.md
In the Surprises page, it is stated that the database/sql
package does not support multiple result sets/statements but this does not seem to be true anymore.
There is an example on the go database/sql showing how to do multiple result sets.
A user was creating a transaction but then accessing the DB, and didn't realize that those statements weren't inside the transaction.
The other helpful property of transactions is to bind statements to a single connection, for things like session state, user-defined variables, temporary tables, etc. We could illustrate this.
First off all it's a great guide to get started with the database/sql
package. You start with the import of drivers, creating a connection and in the next step you fetch data. But before you do that it would be good if you describe how to set up tables in a database.
This special-case error should be handled specially in many circumstances.
There's apparently an effort underway to improve the database/sql documentation and provide examples.
Issue: https://groups.google.com/forum/#!topic/golang-dev/bUTGb77jYFs
Page: https://code.google.com/p/go-wiki/wiki/SQLInterface
This is a very, very vague issue, so please close if it's not helpful.
In case it's not clear, we should warn people against a long-lived function using defer()
to close results. The deferred code keeps a reference to the *sql.Rows, and that eats up memory steadily.
Newer versions of database/sql allow passing a context object; it would be good to mention that.
I can't find nav in the website.
Part of the HTML code:
<div class="container">
<div class="row">
<div class="span3">
<div class="leftnav">
<ul class="nav nav-list">
</ul>
<hr />
If there was an error that made rows.Next() return false, then it's already Close()d. The only reason to close it would be if you exited the loop for some reason.
In Fetching Data from the Database, the explanatory text says Notice, however, that we check the error first, and only call rows.Close() if there isn't an error, in order to avoid a runtime panic.
; but this "check error and close if no error" isn't there in the code:
var (
id int
name string
)
rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
err := rows.Scan(&id, &name)
if err != nil {
log.Fatal(err)
}
log.Println(id, name)
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}
I see only a defer rows.Close()
. Nowhere else is rows.Close()
called.
As discussed in this thread, go-sql-driver/mysql#221, some products will not support prepared statements and using a driver with them that does support prepared statements can be confusing.
Hi
Thanks for the excellent article. I found it quite informative with lots of little gotchas. Would it be possible for you to comment on the following
Thanks
-Rao
Hello,
The link on https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/index.md to "Overview of Go's database/sql Package" is broken :)
Thank you.
Martin.
Your site is using Maruku, the default Markdown interpreter. Maruku is now obsolete and may cause builds to fail for sites with invalid Markdown or HTML. See https://help.github.com/articles/migrating-your-pages-site-from-maruku for more information on upgrading to a newer Markdown interpreter.
For information on troubleshooting Jekyll see:
https://help.github.com/articles/using-jekyll-with-pages#troubleshooting
In your examples, you first write "defer rows.Close()" and then you are checking for an error. If I do so and there is an error in the query, I get a panic ("panic: runtime error: invalid memory address or nil pointer dereference"). So I think the order should be reversed (first error checking, then closing):
rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
BTW, thanks for the nice tutorial! I wish I had something similar when I started...
Michael
When trying to run the sample code, it throws this error:
"cannot use vals (type []sql.RawBytes) as type []interface {} in function argument"
The code is:
cols, err := rows.Columns() // Get the column names; remember to check err
vals := make([]sql.RawBytes, len(cols)) // Allocate enough values
ints := make([]interface{}, len(cols)) // Make a slice of []interface{}
for i := range ints {
vals[i] = &ints[i] // Copy references into the slice
}
for rows.Next() {
err := rows.Scan(vals...)
// Now you can check each element of vals for nil-ness,
// and you can use type introspection and type assertions
// to fetch the column into a typed variable.
}
At the database level, a prepared statement is bound to a single database connection.
When you execute the Stmt, it tries to use the connection. If it’s not available because it’s closed or busy doing something else, it gets another connection from the pool and re-prepares the statement with the database on another connection.
Is that any other existing connection? Or does the database create a new connection?
Not sure if this is adequately covered already, but in go-sql-driver/mysql#287 someone is not aware that the queries are being run on different connections.
Just found your site and love it -- thanks very much! I noticed your tutorial model assumes mySQL and I wondered if you'd considered using sqlite3, since that db is so pervasive (pre-installed on many platforms), it's quite simple, and there'a Go driver available. Just wondered. I think I'm going to follow through your tutorial using sqlite3 and see how far I can get.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.