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 https://go.dev/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 PingContext method:

if err := db.PingContext(ctx); err != nil {
  log.Fatal(err)
}

After use, the database is closed using Close.

Executing queries

ExecContext is used for queries where no rows are returned:

result, err := db.ExecContext(ctx,
	"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.

QueryContext is used for retrieval:

rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age = $1", age)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()
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)
}

QueryRowContext is used where only a single row is expected:

var age int64
err := db.QueryRowContext(ctx, "SELECT age FROM users WHERE name = $1", name).Scan(&age)

Prepared statements can be created with PrepareContext:

age := 27
stmt, err := db.PrepareContext(ctx, "SELECT name FROM users WHERE age = $1")
if err != nil {
	log.Fatal(err)
}
rows, err := stmt.Query(age)
// process rows

ExecContext, QueryContext and QueryRowContext can be called on statements. After use, a statement should be closed with Close.

Transactions

Transactions are started with BeginTx:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
	log.Fatal(err)
}

The ExecContext, QueryContext, QueryRowContext and PrepareContext methods 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 sql.NullString
err := db.QueryRowContext(ctx, "SELECT name FROM names WHERE id = $1", id).Scan(&name)
...
if name.Valid {
	// use name.String
} else {
	// value is NULL
}

Only NullByte, NullBool, NullFloat64, NullInt64, NullInt32 NullInt16, NullString and NullTime are implemented in database/sql. Implementations of database-specific null types are left to the database driver. User types supporting NULL can be created by implementing interfaces database/sql/driver.Valuer and database/sql.Scanner.

You can also pass pointer types. Be careful for performance issues as it requires extra memory allocations.

var name *string
err := db.QueryRowContext(ctx, "SELECT name FROM names WHERE id = $1", id).Scan(&name)

AVX512 Articles AssemblyPolicy Benchmarks Blogs Books BoundingResourceUse CSSStyleGuide ChromeOS CodeReview CodeReviewComments CodeReviewConcurrency CodeTools Comments CommitMessage CommonMistakes CompilerOptimizations Conferences Configuring GoLand for WebAssembly Contributing to gopls CoreDumpDebugging Courses CreatingSubRepository CustomPprofProfiles Darwin DashboardBuilders Deprecated DesignDocuments Diagnostics Download build farm failed logs and debugging DragonFly BSD ErrorValueFAQ Errors ExperienceReports FileTreeDocumentation FreeBSD FromXToGo Frozen Fuzzing trophy case GOPATH Gardening GcToolchainTricks GccgoCrossCompilation GerritAccess GerritBot GithubAccess Go 1.10 Release Party Go 1.6 release party Go 1.8 Release Party Go Community Slides Go Release Cycle Go2 Go2ErrorHandlingFeedback Go2ErrorValuesFeedback Go2GenericsFeedback GoArm GoForCPPProgrammers GoGenerateTools GoGetProxyConfig GoGetTools GoMips GoStrings GoTalks GoUserGroups GoUsers Gomote Gopher HandlingIssues Home HostedContinuousIntegration How to ask for help HowToAsk IDEsAndTextEditorPlugins InstallFromSource InstallTroubleshooting InterfaceSlice InvalidFlag Iota Learn LearnConcurrency LearnErrorHandling LearnServerProgramming LearnTesting Linux LinuxKernelSignalVectorBug Livestreams LockOSThread MacOS12BSDThreadRegisterIssue MethodSets MinimumRequirements MinorReleases Mobile Modules MutexOrChannel NativeClient NetBSD NewSpeakers NoMeToo NoPlusOne NonEnglish OpenBSD PackagePublishing PanicAndRecover PerfDashboard Performance Plan9 Podcasts PortingPolicy PriorDiscussion Projects Proposals ProviderIntegration Questions Quiet Weeks Range RateLimiting ResearchPapers Resolving Problems From Modified Module Path Resources for slog SQLDrivers SQLInterface Screencasts SettingGOPATH SignalHandling SimultaneousAssignment SliceTricks SlowBots Solaris Spectre Spelling Style SuccessStories Switch TableDrivenTests TargetSpecific TestComments TestFailures Timeouts Training Ubuntu Watchflakes WebAccessibilityResourcesAndTips Well known struct tags WhyGo Windows WindowsBuild WindowsCrossCompiling WindowsDLLs X Repositories _Footer cgo golang tools gopherbot gopls integrator FAQ gopls heapdump13 heapdump14 heapdump15 through heapdump17 heapdump15