Comments (4)
AC
- Find and read article(s) discussing how the Go SQL libraries use Prepare
- Post links in this issue and "at" Daved.
- If any new issues are noticed, please create them.
- Daved should review current SQL usage and create issues as noticed.
from openboard.
The idiomatic ways to form queries are listed in the Go Database/SQL Tutorial.
There are several idiomatic operations to retrieve results from the datastore.
- Execute a query that returns rows.
- Prepare a statement for repeated use, execute it multiple times, and destroy it.
- Execute a statement in a once-off fashion, without preparing it for repeated use.
- Execute a query that returns a single row. There is a shortcut for this special case.
I have made queries that are used once then closed. That means the second way (using db.Prepare()
) is overkill. I do execute queries that only returns rows, so the first way is probably how I should be writing queries, like the Go SQL tutorial example,
rows, err := db.Query("select id, name from users where id = ?", 1)
I use the third case to set the deleted_at
field where I don't care about the returned rows. The example given by the Go SQL tutorial was
_, err := db.Exec("DELETE FROM users") // OK
As of PR #83, I am using the following which seems fine:
_, err := s.db.Exec(
"UPDATE user SET deleted_at = ? WHERE user_id = ?",
time.Now(),
sid,
)
I also use the fourth case where queries return single rows. Those queries would use a shortcut:
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
@daved I believe I'm overusing the db.Prepare()
method of querying the database and since I'm not using these queries multiple times, I should replace them all with db.Query()
, db.Exec()
, or db.QueryRow()
. I should not use db.Prepare()
.
If you agree, I can make a new issue to get those queries converted.
from openboard.
Please review http://go-database-sql.org/prepared.html#avoiding-prepared-statements, along with the linked article (https://orangematter.solarwinds.com/2014/11/19/analyzing-prepared-statement-performance/), and ping me via Slack to setup a chat to discuss this further.
from openboard.
I've read the section on "Avoiding Prepared Statements" in go-database-sql.org several times. I realized during my last reading that db.Prepare
is a confusing name. To me, it seemed to imply that db.Query
does not prepare statements. However, from that section, db.Query
does also prepare statements:
Go creates prepared statements for you under the covers. A simple db.Query(sql, param1, param2), for example, works by preparing the sql, then executing it with the parameters and finally closing the statement.
So, how does one execute a query without preparing the statements? It requires more steps:
If you don’t want to use a prepared statement, you need to use fmt.Sprint() or similar to assemble the SQL, and pass this as the only argument to db.Query() or db.QueryRow(). And your driver needs to support plaintext query execution, which is added in Go 1.1 via the Execer and Queryer interfaces, documented here.
The second article at orangematter.solarwinds.com says there's a case for taking the extra steps the write plaintext query executions:
If we change from a prepared statement to a plaintext query here, we’ll reduce round-trips on the network and free up a lot of resources in the database server. Simple queries like this don’t benefit from being prepared. We’ll handle the security aspect of this query by validating input (which our API framework already does for us).
The simple queries in question were of the form SELECT shard_hostname FROM customer.env WHERE id = ?
.
The trade off here is reduction to database load vs. writing more lines of code to avoiding preparing statements. And even if we did write extra code, will it work? Does our Go sql driver even support plaintext query execution?
I'll send a ping on Slack to discuss further.
from openboard.
Related Issues (20)
- Update the AUTHORS document to include all contributors so far HOT 2
- Find posts by more than one keyword HOT 1
- Project automation info lacking context
- Code of Conduct links should live in CoC doc
- Bug: MariaDB setup problems
- Bug: Postsvc Tests
- Add script (or modify existing) to track/update proto dependencies (e.g. grpc third-party)
- Update AUTHORS doc to include myself
- Investigate possible race condition created by log/dbg packages
- The serve() and stop() functions should return accumulated errors
- Add "Kind" message enums
- Add "Kind" enum to FndPostsReq message HOT 1
- Add Categories message to posts.proto
- Add "Categories" message to other messages
- Expand FndPostsReq
- Add user id info to PostResp
- Add "search by user id" to FndUserReq
- Add "Settled" and "Expired" timestamps to Post messages
- XipXape should behave when work or a branch is added via github editor
- Feature: Clarify issue-to-PRs relationship in docs
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from openboard.