Introduction

Go developers face challenges with database interactions. ORMs offer convenience but sacrifice control. Raw SQL provides control but lacks safety. pgx/v5 and sqlc bridge this gap.

pgx/v5 acts as a PostgreSQL driver. It offers high performance and low-level control. sqlc generates Go code from SQL queries. It provides type safety and IDE support.

This combination allows you to write efficient, type-safe database code in Go.

Why Use Raw SQL?

Raw SQL gives you direct control over database operations.

Benefits include:

Performance: Write optimized queries for your specific use cases
Control: Use all features of your database system
Visibility: See exactly what queries run in your code
Learning: Improve your SQL skills through practice
Optimization: Directly address performance issues in queries

Raw SQL shines in complex scenarios. It allows fine-tuning of database interactions.

Getting Started with `pgx/v5`

pgx/v5 serves as a PostgreSQL driver and toolkit for Go. It offers features beyond a basic driver.

Installation

pgx/v5 installs easily with Go’s package manager. It integrates smoothly into Go projects.

Install pgx/v5:

go get github.com/jackc/pgx/v5

Import it:

import (
    "context"
    "fmt"
    "os"
    "github.com/jackc/pgx/v5"
)

Database Connections

pgx/v5 simplifies database connections. It handles connection pooling and management. This allows your application to use database resources efficiently.

Connect to PostgreSQL:

connString := "postgres://username:password@localhost:5432/database_name"
conn, err := pgx.Connect(context.Background(), connString)
if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
    os.Exit(1)
}
defer conn.Close(context.Background())

Query Execution

With pgx/v5, you execute SQL queries directly. This gives you full control over your database interactions. You can run SELECT, INSERT, UPDATE, and DELETE operations with ease.

Execute a query:

rows, err := conn.Query(context.Background(), "SELECT id, name FROM users WHERE active = true")
if err != nil {
    fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
    return
}
defer rows.Close()

for rows.Next() {
    var id int
    var name string
    err = rows.Scan(&id, &name)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Scan failed: %v\n", err)
        return
    }
    fmt.Printf("User: %d %s\n", id, name)
}

Advanced `pgx/v5` Features

pgx/v5 offers advanced features for complex database operations.

Prepared Statements

Prepared statements improve performance for repeated queries. They also protect against SQL injection attacks.

Use prepared statements for repeated queries:

stmt, err := conn.Prepare(context.Background(), "getUserByID", "SELECT id, name, email FROM users WHERE id = $1")
if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to prepare statement: %v\n", err)
    return
}

row := conn.QueryRow(context.Background(), "getUserByID", 1)

var id int
var name, email string
err = row.Scan(&id, &name, &email)
if err != nil {
    fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
    return
}

Transactions

Transactions ensure data consistency. They allow you to group multiple operations. These operations succeed or fail as a unit.

Ensure data integrity with transactions:

tx, err := conn.Begin(context.Background())
if err != nil {
    fmt.Fprintf(os.Stderr, "Unable to start transaction: %v\n", err)
    return
}
defer tx.Rollback(context.Background())

_, err = tx.Exec(context.Background(), "INSERT INTO orders (user_id, product_id, quantity) VALUES ($1, $2, $3)", 1, 100, 2)
if err != nil {
    fmt.Fprintf(os.Stderr, "Failed to insert order: %v\n", err)
    return
}

_, err = tx.Exec(context.Background(), "UPDATE inventory SET stock = stock - $1 WHERE product_id = $2", 2, 100)
if err != nil {
    fmt.Fprintf(os.Stderr, "Failed to update inventory: %v\n", err)
    return
}

err = tx.Commit(context.Background())
if err != nil {
    fmt.Fprintf(os.Stderr, "Failed to commit transaction: %v\n", err)
    return
}

Batch Operations

Batch operations reduce network round trips. They allow you to send multiple queries in one request. This improves performance for multiple related operations.

Execute multiple queries in one round-trip:

batch := &pgx.Batch{}
batch.Queue("INSERT INTO logs (message) VALUES ($1)", "Log message 1")
batch.Queue("INSERT INTO logs (message) VALUES ($1)", "Log message 2")
batch.Queue("SELECT count(*) FROM logs")

results := conn.SendBatch(context.Background(), batch)

for i := 0; i < batch.Len(); i++ {
    _, err := results.Exec()
    if err != nil {
        fmt.Fprintf(os.Stderr, "Error executing batch operation %d: %v\n", i, err)
    }
}

var count int
err := results.QueryRow().Scan(&count)
if err != nil {
    fmt.Fprintf(os.Stderr, "Error scanning result: %v\n", err)
} else {
    fmt.Printf("Total log count: %d\n", count)
}

err = results.Close()
if err != nil {
    fmt.Fprintf(os.Stderr, "Error closing batch: %v\n", err)
}

Null Handling

pgx/v5 provides robust null handling. It offers types that distinguish between null and zero values. This prevents errors from null database fields.

Handle NULL values with pgx types:

import (
    "github.com/jackc/pgx/v5/pgtype"
)

var id int
var name string
var email pgtype.Text

err := conn.QueryRow(context.Background(), "SELECT id, name, email FROM users WHERE id = $1", 1).Scan(&id, &name, &email)
if err != nil {
    fmt.Fprintf(os.Stderr, "Query failed: %v\n", err)
    return
}

fmt.Printf("ID: %d, Name: %s\n", id, name)
if email.Valid {
    fmt.Printf("Email: %s\n", email.String)
} else {
    fmt.Println("Email: NULL")
}

Introducing sqlc

sqlc generates Go code from SQL queries. It combines the control of raw SQL with the safety of generated code.

Benefits of sqlc

sqlc offers several advantages:

Type Safety: Catch errors at compile time, not runtime
IDE Support: Get autocomplete and refactoring for database code
Performance: Avoid runtime reflection and query building
SQL First: Write standard SQL, use all database features

Installation

sqlc installs as a command-line tool. It integrates into your development workflow.

Install sqlc:

go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

Create a configuration file sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    queries: "./sql/queries/"
    schema: "./sql/schema/"
    gen:
      go:
        package: "database"
        out: "./database"
        emit_prepared_queries: true
        emit_interface: true
        emit_exact_table_names: false

Writing Queries with sqlc

sqlc uses SQL files to generate Go code.

You write schema files to define your database structure. Query files contain the SQL queries you want to execute.

sqlc reads these files and generates corresponding Go functions. These functions are type-safe and easy to use in your Go code.

Create SQL files for schema and queries.

Schema file ./sql/schema/users.sql:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Query file ./sql/queries/users.sql:

-- name: GetUser :one
SELECT * FROM users
WHERE id = $1 LIMIT 1;

-- name: ListUsers :many
SELECT * FROM users
ORDER BY name;

-- name: CreateUser :one
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING *;

-- name: UpdateUser :one
UPDATE users
SET name = $2, email = $3
WHERE id = $1
RETURNING *;

-- name: DeleteUser :exec
DELETE FROM users
WHERE id = $1;

Generate Go code:

sqlc generate

Use generated code:

conn, err := pgx.Connect(context.Background(), "postgres://username:password@localhost:5432/database_name")
if err != nil {
    log.Fatal(err)
}
defer conn.Close(context.Background())

queries := database.New(conn)

user, err := queries.CreateUser(context.Background(), database.CreateUserParams{
    Name:  "John Doe",
    Email: "[email protected]",
})
if err != nil {
    log.Fatal(err)
}

fmt.Printf("Created user: %+v\n", user)

users, err := queries.ListUsers(context.Background())
if err != nil {
    log.Fatal(err)
}

for _, u := range users {
    fmt.Printf("User: %s (%s)\n", u.Name, u.Email)
}

Best Practices

Effective database operations require good practices.

Key practices include:

Error handling: Always check and handle errors from database operations
Query organization: Group related queries logically
Prepared statements: Use for frequently executed queries
Batch operations: Group related operations to reduce network traffic
Indexing: Create appropriate indexes based on your query patterns
Pagination: Use LIMIT and OFFSET for large datasets
Query analysis: Use EXPLAIN ANALYZE to understand and optimize query performance

Comparison with ORMs

Raw SQL with pgx/v5 and sqlc offers advantages in specific scenarios.

It works well for:

Performance-critical applications
Complex queries
Using database-specific features
Teams with strong SQL skills

ORMs might be preferable for:

Smaller projects with simple database needs
Rapid development scenarios
Teams more comfortable with OOP than SQL
Projects requiring support for multiple database types

Consider your project’s specific needs when choosing between these approaches.

Conclusion

pgx/v5 and sqlc offer a powerful combination for Go database operations. They provide the control of raw SQL with the safety of generated code.

This approach requires more initial setup than ORMs. However, it offers long-term benefits for complex projects. It allows fine-grained control over database interactions while maintaining type safety.

Consider your project requirements when deciding between raw SQL and ORMs. Each approach has its place in Go development.

Further Resources

Explore these resources to deepen your understanding:

Need Help?

Are you facing challenging problems, or need an external perspective on a new idea or project? I can help! Whether you're looking to build a technology proof of concept before making a larger investment, or you need guidance on difficult issues, I'm here to assist.

Services Offered:

Problem-Solving: Tackling complex issues with innovative solutions.
Consultation: Providing expert advice and fresh viewpoints on your projects.
Proof of Concept: Developing preliminary models to test and validate your ideas.

If you're interested in working with me, please reach out via email at [email protected].

Let's turn your challenges into opportunities!

Introduction

Why Use Raw SQL?

Getting Started with pgx/v5

Installation

Database Connections

Query Execution

Advanced pgx/v5 Features

Prepared Statements

Transactions

Batch Operations

Null Handling

Introducing sqlc

Benefits of sqlc

Installation

Writing Queries with sqlc

Best Practices

Comparison with ORMs

Conclusion

Further Resources

Need Help?

Services Offered:

Getting Started with `pgx/v5`

Advanced `pgx/v5` Features