Most backend applications need a database.
Whether you are building a REST API, web server, e-commerce platform, authentication system, SaaS application, or microservice, your Go application will often need to store and retrieve data from a relational database.
Go provides the built-in database/sql package for working with SQL databases.
With database/sql, you can connect Go applications to databases such as PostgreSQL, MySQL, SQLite, and others through database drivers.
In this complete guide, you will learn how database programming works in Go, how to connect to a SQL database, execute queries, insert records, read rows, update and delete data, use prepared statements, handle transactions, work with context, configure connection pools, prevent SQL injection, and structure database code properly.
What Is SQL Database Programming in Go?
SQL database programming means allowing a Go application to communicate with a relational database using SQL queries.
A typical architecture looks like:
Client
|
v
Go Application
|
v
database/sql
|
v
Database Driver
|
v
SQL Database
The database may be:
PostgreSQL
MySQL
SQLite
Microsoft SQL Server
Your Go application sends SQL queries such as:
SELECT * FROM users;
or:
INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');
The database processes the query and returns the result to your Go application.
What Is database/sql?
Go includes:
import "database/sql"
The database/sql package provides a standard interface for working with relational databases.
It handles concepts such as:
Database connections
Queries
Rows
Prepared statements
Transactions
Connection pooling
Context cancellation
However, database/sql does not communicate directly with PostgreSQL, MySQL, or another database.
You also need a database driver.
database/sql vs Database Driver
Think of it like this:
Your Go Code
|
v
database/sql
|
v
Database Driver
|
v
PostgreSQL / MySQL / SQLite
database/sql provides the common API.
The driver knows how to communicate with the specific database.
Popular SQL Databases with Go
Common relational databases used with Go include:
PostgreSQL
MySQL
SQLite
MariaDB
Microsoft SQL Server
PostgreSQL is especially popular for modern backend development.
Step 1: Create a Go Project
Create a directory:
mkdir go-sql-example
Enter it:
cd go-sql-example
Initialize a Go module:
go mod init go-sql-example
Create:
main.go
Your project:
go-sql-example/
│
├── go.mod
└── main.go
Step 2: Install a Database Driver
For PostgreSQL, a commonly used option is pgx.
Install:
go get github.com/jackc/pgx/v5/stdlib
Then you can use pgx through database/sql.
Step 3: Import Required Packages
Example:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/jackc/pgx/v5/stdlib"
)
The underscore import:
_ "github.com/jackc/pgx/v5/stdlib"
registers the database driver.
You usually do not call anything directly from that imported package when using it only as a database/sql driver.
Step 4: Create a Database
Suppose you are using PostgreSQL.
Create a database:
CREATE DATABASE go_app;
Then create a users table:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This table contains:
id
name
email
created_at
Step 5: Connect Go to PostgreSQL
Create a connection string:
postgres://username:password@localhost:5432/go_app
Then open the database:
db, err := sql.Open(
"pgx",
"postgres://username:password@localhost:5432/go_app",
)
if err != nil {
log.Fatal(err)
}
Important:
sql.Open()
does not necessarily establish a physical database connection immediately.
It creates a database handle and prepares the connection pool.
Verify the Database Connection
Use:
err = db.Ping()
Example:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/jackc/pgx/v5/stdlib"
)
func main() {
db, err := sql.Open(
"pgx",
"postgres://username:password@localhost:5432/go_app",
)
if err != nil {
log.Fatal(err)
}
defer db.Close()
err = db.Ping()
if err != nil {
log.Fatal(err)
}
fmt.Println("Database connected successfully")
}
Why Use defer db.Close()?
Once your application is finished using the database, close the database handle:
defer db.Close()
In a long-running web server, this generally happens when the application shuts down.
Do not open and close a new sql.DB for every HTTP request.
sql.DB Is a Connection Pool
One of the most important things to understand is that:
*sql.DB
is not simply one database connection.
It manages a pool of connections.
Conceptually:
Go Application
|
v
sql.DB
/ | \
Conn Conn Conn
\ | /
Database
This allows multiple requests to interact with the database efficiently.
Creating a User Model
Create a Go struct:
type User struct {
ID int
Name string
Email string
CreatedAt time.Time
}
You can use this struct when reading database rows.
Inserting Data with Exec
You can insert data using:
db.Exec()
Example:
result, err := db.Exec(
`
INSERT INTO users (name, email)
VALUES ($1, $2)
`,
"John",
"john@example.com",
)
For PostgreSQL, placeholders commonly look like:
$1
$2
$3
Why Use Placeholders?
Never create SQL by manually concatenating untrusted user input.
Bad:
query :=
"INSERT INTO users (name) VALUES ('" +
name +
"')"
This can introduce SQL injection vulnerabilities.
Better:
db.Exec(
`
INSERT INTO users (name, email)
VALUES ($1, $2)
`,
name,
email,
)
The driver safely handles the values separately from the SQL statement.
Insert and Return the New ID
With PostgreSQL, you often use:
RETURNING id
Example:
var userID int
err := db.QueryRow(
`
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING id
`,
"John",
"john@example.com",
).Scan(&userID)
Then:
fmt.Println("Created user:", userID)
Exec vs Query vs QueryRow
These three methods are fundamental.
Use:
Exec
when you do not expect rows back.
Examples:
UPDATE
DELETE
Some INSERT operations
Use:
Query
when you expect multiple rows.
Example:
SELECT all users
Use:
QueryRow
when you expect one row.
Example:
SELECT one user by ID
Reading One Row with QueryRow
Suppose you want:
SELECT id, name, email, created_at
FROM users
WHERE id = 1;
In Go:
var user User
err := db.QueryRow(
`
SELECT id, name, email, created_at
FROM users
WHERE id = $1
`,
1,
).Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
Handling sql.ErrNoRows
If no record exists, QueryRow().Scan() returns:
sql.ErrNoRows
Example:
err := db.QueryRow(
`
SELECT id, name, email
FROM users
WHERE id = $1
`,
userID,
).Scan(
&user.ID,
&user.Name,
&user.Email,
)
if err == sql.ErrNoRows {
fmt.Println("User not found")
return
}
if err != nil {
log.Println(err)
return
}
This is an important pattern in Go database programming.
Reading Multiple Rows with Query
To fetch all users:
rows, err := db.Query(
`
SELECT id, name, email, created_at
FROM users
ORDER BY id
`,
)
Always handle the error:
if err != nil {
log.Fatal(err)
}
Then close the rows:
defer rows.Close()
Iterating Through Rows
Use:
rows.Next()
Example:
var users []User
for rows.Next() {
var user User
err := rows.Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return
}
users = append(
users,
user,
)
}
Always Check rows.Err()
After iteration:
if err := rows.Err(); err != nil {
log.Println(err)
}
Why?
An error can occur while iterating through database rows.
Checking rows.Err() helps ensure you do not silently ignore it.
Complete Get All Users Example
func getUsers(
db *sql.DB,
) ([]User, error) {
rows, err := db.Query(
`
SELECT
id,
name,
email,
created_at
FROM users
ORDER BY id
`,
)
if err != nil {
return nil, err
}
defer rows.Close()
var users []User
for rows.Next() {
var user User
err := rows.Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
users = append(
users,
user,
)
}
if err := rows.Err(); err != nil {
return nil, err
}
return users, nil
}
Updating Data
Use:
db.Exec()
Example:
result, err := db.Exec(
`
UPDATE users
SET name = $1,
email = $2
WHERE id = $3
`,
"John Updated",
"john.updated@example.com",
1,
)
Checking Affected Rows
After:
result, err := db.Exec(...)
you can use:
rowsAffected, err :=
result.RowsAffected()
Example:
if rowsAffected == 0 {
fmt.Println("User not found")
}
Delete Data
Delete a user:
result, err := db.Exec(
`
DELETE FROM users
WHERE id = $1
`,
userID,
)
Then:
rowsAffected, err :=
result.RowsAffected()
If:
0
rows were affected, the user probably did not exist.
Complete CRUD Functions
A typical repository may contain functions like:
CreateUser()
GetUserByID()
GetUsers()
UpdateUser()
DeleteUser()
Create User Function
func createUser(
db *sql.DB,
name string,
email string,
) (int, error) {
var id int
err := db.QueryRow(
`
INSERT INTO users (
name,
email
)
VALUES ($1, $2)
RETURNING id
`,
name,
email,
).Scan(&id)
if err != nil {
return 0, err
}
return id, nil
}
Get User Function
func getUserByID(
db *sql.DB,
id int,
) (*User, error) {
var user User
err := db.QueryRow(
`
SELECT
id,
name,
email,
created_at
FROM users
WHERE id = $1
`,
id,
).Scan(
&user.ID,
&user.Name,
&user.Email,
&user.CreatedAt,
)
if err != nil {
return nil, err
}
return &user, nil
}
Update User Function
func updateUser(
db *sql.DB,
id int,
name string,
email string,
) error {
result, err := db.Exec(
`
UPDATE users
SET
name = $1,
email = $2
WHERE id = $3
`,
name,
email,
id,
)
if err != nil {
return err
}
rowsAffected, err :=
result.RowsAffected()
if err != nil {
return err
}
if rowsAffected == 0 {
return sql.ErrNoRows
}
return nil
}
Delete User Function
func deleteUser(
db *sql.DB,
id int,
) error {
result, err := db.Exec(
`
DELETE FROM users
WHERE id = $1
`,
id,
)
if err != nil {
return err
}
rowsAffected, err :=
result.RowsAffected()
if err != nil {
return err
}
if rowsAffected == 0 {
return sql.ErrNoRows
}
return nil
}
Working with Context
For backend applications, prefer context-aware database operations.
Instead of:
db.Query()
use:
db.QueryContext()
Instead of:
db.Exec()
use:
db.ExecContext()
Instead of:
db.QueryRow()
use:
db.QueryRowContext()
Why Context Matters
Context can handle:
Request cancellation
Timeouts
Deadlines
Client disconnects
Suppose an HTTP request is cancelled.
If your database operation uses:
r.Context()
the database query may also be cancelled.
Query with Request Context
Inside an HTTP handler:
ctx :=
r.Context()
rows, err :=
db.QueryContext(
ctx,
`
SELECT id, name, email
FROM users
`,
)
This connects the database operation to the HTTP request lifecycle.
Creating a Database Timeout
Example:
ctx, cancel :=
context.WithTimeout(
context.Background(),
3*time.Second,
)
defer cancel()
Then:
row :=
db.QueryRowContext(
ctx,
`
SELECT id, name
FROM users
WHERE id = $1
`,
userID,
)
If the query takes too long, the context can cancel it.
Prepared Statements
Prepared statements can be useful when the same SQL statement is executed repeatedly.
Create:
stmt, err :=
db.Prepare(
`
INSERT INTO users (
name,
email
)
VALUES ($1, $2)
`,
)
Then:
defer stmt.Close()
Execute:
_, err =
stmt.Exec(
"John",
"john@example.com",
)
Prepared Statement Example
func insertUsers(
db *sql.DB,
) error {
stmt, err :=
db.Prepare(
`
INSERT INTO users (
name,
email
)
VALUES ($1, $2)
`,
)
if err != nil {
return err
}
defer stmt.Close()
users :=
[]User{
{
Name: "John",
Email: "john@example.com",
},
{
Name: "Alice",
Email: "alice@example.com",
},
}
for _, user :=
range users {
_, err :=
stmt.Exec(
user.Name,
user.Email,
)
if err != nil {
return err
}
}
return nil
}
What Is a Transaction?
A transaction groups multiple SQL operations into one logical unit.
Either:
All operations succeed
or:
All operations are rolled back
This is extremely important for operations involving related changes.
Transaction Example
Imagine transferring money:
Subtract money from Account A
Add money to Account B
Both operations must succeed.
If the second one fails, the first should not remain committed.
Starting a Transaction
Use:
tx, err :=
db.Begin()
Then execute queries using:
tx.Exec()
instead of:
db.Exec()
Commit a Transaction
If everything succeeds:
err = tx.Commit()
Roll Back a Transaction
If something fails:
tx.Rollback()
Complete Transaction Example
func transferMoney(
db *sql.DB,
fromID int,
toID int,
amount float64,
) error {
tx, err :=
db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
_, err =
tx.Exec(
`
UPDATE accounts
SET balance =
balance - $1
WHERE id = $2
`,
amount,
fromID,
)
if err != nil {
return err
}
_, err =
tx.Exec(
`
UPDATE accounts
SET balance =
balance + $1
WHERE id = $2
`,
amount,
toID,
)
if err != nil {
return err
}
return tx.Commit()
}
The deferred rollback is safe because after a successful commit, it has no effect.
Transactions with Context
You can also use:
db.BeginTx()
Example:
tx, err :=
db.BeginTx(
ctx,
nil,
)
This lets the transaction respect context cancellation and deadlines.
SQL NULL Values
SQL supports:
NULL
Go value types such as:
string
int
bool
cannot directly represent SQL NULL in every scanning situation.
The database/sql package provides nullable types.
Examples:
sql.NullString
sql.NullInt64
sql.NullBool
sql.NullTime
sql.NullString Example
Suppose:
phone_number
can be NULL.
Use:
var phone sql.NullString
Scan:
err :=
row.Scan(
&phone,
)
Check:
if phone.Valid {
fmt.Println(phone.String)
}
Struct with Nullable Field
type User struct {
ID int
Name string
Phone sql.NullString
}
Connection Pool Configuration
Because sql.DB manages a connection pool, you can configure it.
For example:
db.SetMaxOpenConns(25)
This controls the maximum number of open connections.
Maximum Idle Connections
Use:
db.SetMaxIdleConns(10)
This controls how many idle connections can remain available.
Connection Lifetime
Use:
db.SetConnMaxLifetime(
30 * time.Minute,
)
You can also configure idle time:
db.SetConnMaxIdleTime(
5 * time.Minute,
)
Example Pool Configuration
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(
30 * time.Minute,
)
db.SetConnMaxIdleTime(
5 * time.Minute,
)
Do not blindly copy connection values into every application.
Pool settings depend on:
Database capacity
Server resources
Traffic
Number of application instances
Query duration
Infrastructure limits
Database Connection Pooling Explained
Without pooling:
Request
|
Create connection
|
Run query
|
Close connection
Doing this repeatedly can be expensive.
With pooling:
Request 1 ─┐
Request 2 ─┼─> Connection Pool ─> Database
Request 3 ─┘
Existing connections can be reused.
Database Statistics
You can inspect pool statistics:
stats :=
db.Stats()
Useful values include:
stats.OpenConnections
stats.InUse
stats.Idle
stats.WaitCount
These can help diagnose database connection problems.
SQL Injection
SQL injection is one of the most important database security risks.
Suppose you write:
query :=
"SELECT * FROM users WHERE email = '" +
email +
"'"
An attacker may provide malicious input that changes the SQL query.
Prevent SQL Injection
Use parameterized queries:
row :=
db.QueryRow(
`
SELECT id, name, email
FROM users
WHERE email = $1
`,
email,
)
Never directly insert untrusted input into SQL strings.
Dynamic SQL Requires Extra Care
Values can usually be parameterized.
But identifiers such as:
Column names
Table names
Sort directions
often cannot be safely passed as normal SQL parameters.
For example, do not directly use:
sort :=
r.URL.Query().Get("sort")
inside:
"ORDER BY " + sort
without validation.
Use an allowlist.
Example:
allowedSorts :=
map[string]string{
"name": "name",
"created_at": "created_at",
}
Then only use known allowed values.
Pagination with SQL
A REST API often needs pagination.
Example:
GET /users?page=2&limit=20
SQL:
SELECT id, name, email
FROM users
ORDER BY id
LIMIT $1
OFFSET $2;
Calculate:
offset :=
(page - 1) * limit
Then:
rows, err :=
db.Query(
`
SELECT id, name, email
FROM users
ORDER BY id
LIMIT $1
OFFSET $2
`,
limit,
offset,
)
Filtering
Suppose you want:
/users?name=John
SQL could be:
SELECT id, name, email
FROM users
WHERE name = $1;
In Go:
rows, err :=
db.Query(
`
SELECT id, name, email
FROM users
WHERE name = $1
`,
name,
)
Searching
PostgreSQL example:
SELECT id, name, email
FROM users
WHERE name ILIKE $1;
Go:
search :=
"%" + keyword + "%"
Then:
rows, err :=
db.Query(
`
SELECT id, name, email
FROM users
WHERE name ILIKE $1
`,
search,
)
The SQL itself still remains parameterized.
Sorting
A request may use:
/users?sort=name
You should map client values to known SQL identifiers.
Example:
sortColumn :=
"created_at"
switch sort {
case "name":
sortColumn = "name"
case "email":
sortColumn = "email"
}
Then build only from trusted internal values.
Database Indexes
An index can make queries faster.
Example:
CREATE INDEX idx_users_email
ON users(email);
This can improve queries such as:
SELECT *
FROM users
WHERE email = $1;
However, indexes also use storage and can add overhead to writes.
Use them based on actual query patterns.
Unique Constraints
A unique constraint can prevent duplicate email addresses.
Example:
email VARCHAR(150) UNIQUE NOT NULL
Even if your Go code checks for duplicates first, database constraints are still important.
They protect data integrity.
Foreign Keys
Suppose you have:
users
orders
An order may belong to a user.
Example:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
total NUMERIC(10,2) NOT NULL,
FOREIGN KEY (user_id)
REFERENCES users(id)
);
Foreign keys help maintain relationships between tables.
SQL JOIN with Go
Suppose you want orders with user information.
SQL:
SELECT
orders.id,
users.name,
orders.total
FROM orders
JOIN users
ON users.id =
orders.user_id;
In Go:
rows, err :=
db.Query(
`
SELECT
orders.id,
users.name,
orders.total
FROM orders
JOIN users
ON users.id =
orders.user_id
`,
)
Then scan:
for rows.Next() {
var orderID int
var userName string
var total float64
err :=
rows.Scan(
&orderID,
&userName,
&total,
)
if err != nil {
return err
}
}
Repository Pattern
As your application grows, avoid putting SQL directly inside HTTP handlers.
Instead of:
HTTP Handler
|
v
SQL Query
prefer:
HTTP Handler
|
v
Service
|
v
Repository
|
v
Database
Example Repository
type UserRepository struct {
db *sql.DB
}
Constructor:
func NewUserRepository(
db *sql.DB,
) *UserRepository {
return &UserRepository{
db: db,
}
}
Repository Method
func (
r *UserRepository,
) GetByID(
ctx context.Context,
id int,
) (*User, error) {
var user User
err :=
r.db.QueryRowContext(
ctx,
`
SELECT
id,
name,
email
FROM users
WHERE id = $1
`,
id,
).Scan(
&user.ID,
&user.Name,
&user.Email,
)
if err != nil {
return nil, err
}
return &user, nil
}
This makes the code easier to organize and test.
Recommended Project Structure
A larger Go backend may look like:
go-app/
│
├── cmd/
│ └── api/
│ └── main.go
│
├── internal/
│ ├── handlers/
│ │ └── user_handler.go
│ │
│ ├── services/
│ │ └── user_service.go
│ │
│ ├── repositories/
│ │ └── user_repository.go
│ │
│ ├── models/
│ │ └── user.go
│ │
│ └── database/
│ └── database.go
│
├── migrations/
│
├── go.mod
└── go.sum
Database Package Example
package database
import (
"database/sql"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
)
func Open(
databaseURL string,
) (*sql.DB, error) {
db, err :=
sql.Open(
"pgx",
databaseURL,
)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(
30 * time.Minute,
)
if err :=
db.Ping();
err != nil {
db.Close()
return nil, err
}
return db, nil
}
Environment Variables for Database Configuration
Do not hardcode production credentials.
Avoid:
databaseURL :=
"postgres://admin:password123@localhost/app"
Instead:
databaseURL :=
os.Getenv(
"DATABASE_URL",
)
Example environment variable:
DATABASE_URL=postgres://user:password@localhost:5432/go_app
Never Commit Database Passwords
Do not commit:
Passwords
Database URLs with credentials
Private keys
API secrets
to public Git repositories.
Use:
Environment variables
Secret managers
Deployment platform secrets
Database Migrations
As your database evolves, you need to track schema changes.
For example:
001_create_users.sql
002_create_orders.sql
003_add_phone_to_users.sql
A migrations directory might look like:
migrations/
│
├── 001_create_users.sql
├── 002_create_orders.sql
└── 003_add_user_status.sql
Migrations make database changes reproducible across:
Development
Testing
Staging
Production
Example Migration
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Rollback migration might contain:
DROP TABLE users;
Handling Database Errors
Do not expose raw database errors directly to API clients.
Bad response:
duplicate key value violates unique constraint users_email_key
Better:
{
"success": false,
"message": "Email is already registered"
}
Log the detailed error internally.
Return a safe message to the client.
Logging Database Errors
Example:
user, err :=
repository.GetByID(
ctx,
id,
)
if err != nil {
log.Printf(
"get user error: %v",
err,
)
}
Avoid logging sensitive database credentials or personal data unnecessarily.
Integrating SQL with an HTTP Handler
Example:
func getUserHandler(
db *sql.DB,
) http.HandlerFunc {
return func(
w http.ResponseWriter,
r *http.Request,
) {
id := 1
var user User
err :=
db.QueryRowContext(
r.Context(),
`
SELECT
id,
name,
email
FROM users
WHERE id = $1
`,
id,
).Scan(
&user.ID,
&user.Name,
&user.Email,
)
if err ==
sql.ErrNoRows {
http.Error(
w,
"User not found",
http.StatusNotFound,
)
return
}
if err != nil {
http.Error(
w,
"Internal server error",
http.StatusInternalServerError,
)
return
}
json.NewEncoder(
w,
).Encode(
user,
)
}
}
Database Operations in REST APIs
A typical CRUD API maps like this:
POST /users
↓
INSERT INTO users
GET /users
↓
SELECT users
GET /users/1
↓
SELECT user by ID
PUT /users/1
↓
UPDATE users
DELETE /users/1
↓
DELETE FROM users
Go SQL vs ORM
You can write SQL directly with:
database/sql
or use an ORM.
Popular Go database tools include:
GORM
Ent
Bun
sqlc
Benefits of Raw SQL
Using database/sql with SQL directly gives you:
Full query control
Clear SQL behavior
Minimal abstraction
Easy performance tuning
Direct access to database features
Benefits of an ORM
An ORM may provide:
Model mapping
Relationships
Query builders
Convenience methods
Schema support
But it can also hide what SQL is actually being executed.
Even if you plan to use an ORM, you should still understand SQL.
What Is sqlc?
Another popular approach is generating type-safe Go code from SQL queries.
Conceptually:
Write SQL
|
v
Code Generator
|
v
Typed Go Functions
This approach lets developers keep control over SQL while reducing repetitive scanning and query boilerplate.
Common Go SQL Mistakes
Common mistakes include:
Opening a new database handle for every request
Ignoring rows.Close()
Ignoring rows.Err()
Not handling sql.ErrNoRows
Building SQL with string concatenation
Using no context or timeout
Using unlimited connection pools
Hardcoding database credentials
Ignoring transaction rollback
Using SELECT * everywhere
Not adding database indexes
Returning raw database errors
Avoid SELECT * in Production Queries
Instead of:
SELECT *
FROM users;
prefer:
SELECT
id,
name,
email,
created_at
FROM users;
Why?
Explicit columns make your application clearer and less dependent on future table changes.
Always Match Scan Order
If your SQL is:
SELECT id, name, email
FROM users;
scan in the same order:
rows.Scan(
&user.ID,
&user.Name,
&user.Email,
)
Incorrect scan order can produce wrong values or errors.
Use Appropriate Data Types
Database and Go types should be compatible.
For example:
SQL INTEGER → Go int/int64
SQL VARCHAR/TEXT → Go string
SQL BOOLEAN → Go bool
SQL TIMESTAMP → Go time.Time
Nullable fields may require:
sql.NullString
sql.NullInt64
sql.NullBool
sql.NullTime
Testing Database Code
Database logic should be tested.
You can test:
Repository functions
Transactions
Queries
Constraints
Error handling
Integration with the real database
For repository tests, many teams use either:
A temporary test database
Containerized databases
Database mocks
Integration tests are especially useful for verifying actual SQL behavior.
Test Database Separation
Do not run automated tests against your production database.
Use a separate database such as:
go_app_test
This prevents tests from modifying real production data.
Database Performance Tips
Good database performance often depends more on SQL and schema design than the programming language.
Important areas include:
Indexes
Efficient queries
Pagination
Avoiding unnecessary columns
Connection pooling
Avoiding N+1 queries
Batch operations
Transactions
Query timeouts
Database monitoring
Avoid N+1 Query Problems
Suppose you fetch 100 users and then perform one additional query for every user.
That may create:
1 query
+
100 additional queries
=
101 queries
Sometimes a JOIN, batch query, or different data-loading strategy can reduce this.
Batch Inserts
Instead of executing hundreds of unrelated insert operations individually, consider batch techniques when appropriate.
The best method depends on your database and driver.
Batching can reduce network overhead for large write operations.
Production Database Checklist
Before deploying a Go application using SQL, make sure you have:
Database credentials stored securely
Connection pooling configured
Context timeouts
Parameterized queries
Transactions where required
Indexes for important queries
Migration system
Database backups
Monitoring
Safe error handling
Tests
Graceful application shutdown
Complete Beginner Example
Here is a simplified program that connects to PostgreSQL, creates a user, and reads users.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"os"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
)
type User struct {
ID int
Name string
Email string
}
func main() {
databaseURL :=
os.Getenv(
"DATABASE_URL",
)
if databaseURL == "" {
log.Fatal(
"DATABASE_URL is required",
)
}
db, err :=
sql.Open(
"pgx",
databaseURL,
)
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(10)
db.SetConnMaxLifetime(
30 * time.Minute,
)
ctx, cancel :=
context.WithTimeout(
context.Background(),
5*time.Second,
)
defer cancel()
if err :=
db.PingContext(ctx);
err != nil {
log.Fatal(err)
}
fmt.Println(
"Database connected",
)
var userID int
err =
db.QueryRowContext(
ctx,
`
INSERT INTO users (
name,
email
)
VALUES ($1, $2)
RETURNING id
`,
"John",
"john@example.com",
).Scan(
&userID,
)
if err != nil {
log.Fatal(err)
}
fmt.Println(
"Created user ID:",
userID,
)
rows, err :=
db.QueryContext(
ctx,
`
SELECT
id,
name,
email
FROM users
ORDER BY id
`,
)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var user User
err :=
rows.Scan(
&user.ID,
&user.Name,
&user.Email,
)
if err != nil {
log.Fatal(err)
}
fmt.Printf(
"%d | %s | %s\n",
user.ID,
user.Name,
user.Email,
)
}
if err :=
rows.Err();
err != nil {
log.Fatal(err)
}
}
Recommended Learning Order
If you are learning Go database programming, follow this order:
1. Learn basic SQL
2. Understand relational databases
3. Learn database/sql
4. Connect PostgreSQL or MySQL
5. Practice INSERT
6. Practice SELECT
7. Practice UPDATE
8. Practice DELETE
9. Learn QueryRow and Query
10. Learn sql.ErrNoRows
11. Learn prepared statements
12. Learn transactions
13. Learn context
14. Learn connection pooling
15. Learn indexes
16. Learn migrations
17. Build a REST API with a database
Practice Project: User Management API
A good beginner project is a user management API.
Database:
users
Endpoints:
POST /users
GET /users
GET /users/{id}
PUT /users/{id}
DELETE /users/{id}
SQL:
INSERT
SELECT
UPDATE
DELETE
Once this works, add:
Authentication
Pagination
Search
Filtering
Transactions
Logging
Testing
Docker
Deployment
Frequently Asked Questions
What is database/sql in Go?
database/sql is Go’s standard library package for interacting with SQL databases through database drivers.
Does database/sql include PostgreSQL?
No.
You need a PostgreSQL driver in addition to the database/sql package.
What does sql.Open() do?
It creates a database handle that manages a connection pool. It does not guarantee that the database connection has already been established.
Use:
db.Ping()
or:
db.PingContext()
to verify connectivity.
What is the difference between Query and QueryRow?
Use Query when you expect multiple rows.
Use QueryRow when you expect one row.
When should I use Exec?
Use Exec for operations that do not need rows returned, such as many UPDATE, DELETE, and some INSERT statements.
What is sql.ErrNoRows?
It is returned when QueryRow().Scan() expected a row but the query returned none.
Is sql.DB one database connection?
No.
sql.DB manages a pool of database connections.
Should I open the database for every request?
No.
Usually create one long-lived sql.DB for the application and share it safely between request handlers.
How do I prevent SQL injection?
Use parameterized queries and never concatenate untrusted user input directly into SQL strings.
Should I learn SQL before GORM?
Yes.
Understanding SQL helps you design databases, debug queries, optimize performance, and use ORMs correctly.
Do I need transactions?
Use transactions when multiple related database operations must succeed or fail together.
Is PostgreSQL good with Go?
Yes.
PostgreSQL is a strong choice for Go backend applications and is commonly used for APIs, SaaS applications, microservices, and other production systems.
Conclusion
Database programming is one of the most important skills for Go backend development.
Go provides the database/sql package as a standard interface for working with relational databases.
The basic workflow is:
Open database
↓
Verify connection
↓
Execute SQL
↓
Scan results
↓
Handle errors
↓
Return application data
You should become comfortable with:
sql.Open
Ping
Exec
Query
QueryRow
Scan
Rows
Prepared statements
Transactions
Context
Connection pooling
NULL handling
You should also understand core SQL concepts such as:
SELECT
INSERT
UPDATE
DELETE
JOIN
Indexes
Constraints
Transactions
Pagination
For real applications, combine these skills with:
REST APIs
PostgreSQL
Authentication
Validation
Migrations
Testing
Docker
Logging
Monitoring
Security
Once you can build a Go REST API connected to a SQL database with safe queries, transactions, proper error handling, and connection pooling, you will have one of the most important foundations required for professional Go backend development.
