SQLInterface
Pages 133
- Home
- Articles
- Benchmarks
- Blogs
- Books
- BoundingResourceUse
- cgo
- ChromeOS
- CodeReview
- CodeReviewComments
- CodeTools
- Comments
- CommitMessage
- CommonMistakes
- CompilerOptimizations
- Conferences
- CoreDumpDebugging
- Courses
- CustomPprofProfiles
- Darwin
- DashboardBuilders
- DesignDocuments
- DevExp
- Diagnostics
- DragonFly BSD
- Errors
- ExperienceReports
- FileTreeDocumentation
- FreeBSD
- FromXToGo
- Gardening
- GccgoCrossCompilation
- GcToolchainTricks
- GerritAccess
- GerritBot
- GithubAccess
- GitHubCodeLayout
- Go 1.10 Release Party
- Go 1.6 release party
- Go 1.8 Release Party
- Go Release Cycle
- Go1point1Gotchas
- GoArm
- GoForCPPProgrammers
- GoGenerateTools
- GoGetProxyConfig
- GoGetTools
- Gomote
- GOPATH
- Gopher
- GoStrings
- GoTalks
- GoUserGroups
- GoUsers
- GoVsGenerics
- HandlingIssues
- Hashing
- heapdump13
- heapdump14
- heapdump15
- HostedContinuousIntegration
- How to ask for help
- HowToAsk
- HttpFetch
- HttpStaticFiles
- IDEsAndTextEditorPlugins
- InstallFromSource
- InstallTroubleshooting
- InterfaceSlice
- InvalidFlag
- Iota
- IssueLabels
- Learn
- LearnConcurrency
- LearnErrorHandling
- LearnServerProgramming
- LearnTesting
- Linux
- LockOSThread
- MethodSets
- MinimumRequirements
- Mobile
- MultipleGoRoots
- MutexOrChannel
- NativeClient
- NetBSD
- NewSpeakers
- NoMeToo
- NonEnglish
- OlderVersions
- OpenBSD
- PackageManagementTools
- PackagePublishing
- PanicAndRecover
- PerfDashboard
- Performance
- Plan9
- Podcasts
- PortingPolicy
- PriorDiscussion
- Projects
- ProviderIntegration
- Questions
- RaceDetector
- Range
- RateLimiting
- Rationales
- ResearchPapers
- Screencasts
- SendingMail
- SettingGOPATH
- SignalHandling
- SimultaneousAssignment
- SliceTricks
- Solaris
- SQLDrivers
- SQLInterface
- Style
- SubRepositories
- SuccessStories
- Switch
- TableDrivenTests
- Timeouts
- Training
- Ubuntu
- vgo
- WebAccessibilityResourcesAndTips
- Well known struct tags
- WhyGo
- Windows
- WindowsBuild
- WindowsCrossCompiling
- WindowsDLLs
- Show 118 more pages…
Clone this wiki locally
Introduction
The database/sql package provides a generic interface around SQL (or SQL-like) databases. See the official documentation for details.
This page provides example usage patterns.
Database driver
The database/sql package must be used in conjunction with a database driver. See http://golang.org/s/sqldrivers for a list of drivers.
The documentation below assumes a driver has been imported.
Connecting to a database
Open is used to create a database handle:
db, err := sql.Open(driver, dataSourceName)
Where driver specifies a database driver and dataSourceName specifies database-specific connection information such as database name and authentication credentials.
Note that Open does not directly open a database connection: this is deferred until a query is made. To verify that a connection can be made before making a query, use the Ping function:
if err := db.Ping(); err != nil {
log.Fatal(err)
}
After use, the database is closed using Close.
Executing queries
Exec is used for queries where no rows are returned:
result, err := db.Exec(
"INSERT INTO users (name, age) VALUES ($1, $2)",
"gopher",
27,
)
Where result contains the last insert ID and number of rows affected. The availability of these values is dependent on the database driver.
Query is used for retrieval:
rows, err := db.Query("SELECT name FROM users WHERE age = $1", age)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil {
log.Fatal(err)
}
fmt.Printf("%s is %d\n", name, age)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
QueryRow is used where only a single row is expected:
var age int64
row := db.QueryRow("SELECT age FROM users WHERE name = $1", name)
err := row.Scan(&age)
Prepared statements can be created with Prepare:
age := 27
stmt, err := db.Prepare("SELECT name FROM users WHERE age = $1")
if err != nil {
log.Fatal(err)
}
rows, err := stmt.Query(age)
// process rows
Exec, Query and QueryRow can be called on statements. After use, a statement should be closed with Close.
Transactions
Transactions are started with Begin:
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
The Exec, Query, QueryRow and Prepare functions already covered can be used in a transaction.
A transaction must end with a call to Commit or Rollback.
Dealing with NULL
If a database column is nullable, one of the types supporting null values should be passed to Scan.
For example, if the name column in the names table is nullable:
var name NullString
err := db.QueryRow("SELECT name FROM names WHERE id = $1", id).Scan(&name)
...
if name.Valid {
// use name.String
} else {
// value is NULL
}
Only NullBool, NullFloat64, NullInt64 and NullString are implemented in database/sql. Implementations of database-specific null types are left to the database driver.