sqlc - SQL Client

The sqlc package provides a fluent API for building and executing SQL queries against relational databases. It supports MySQL and PostgreSQL, and offers connection management, query building, and transaction handling.

Getting Started

Add the dependency to your Go module:

go get github.com/gosoline-project/sqlc@v0.1.0

Then import the package in your Go code:

import "github.com/gosoline-project/sqlc"

Configuration

A gosoline application can have multiple sqlc clients, each identified by a unique name (e.g., default, analytics, readonly). Every client you intend to use must be configured before use — attempting to create a client without a matching configuration entry will result in an error at startup.

Configure database connections under the sqlc key in your configuration file, with each connection name as a sub-key:

sqlc:
  default:       # primary database
    # ...
  analytics:     # separate analytics database
    # ...

Each connection requires driver and connection details as shown below:

config.dist.yml

sqlc:
  default:
    driver: mysql
    uri:
      host: 127.0.0.1
      port: 3306
      user: root
      password: gosoline
      database: blog
    migrations:
      enabled: true
      path: migrations

Connection Settings

Setting	Description	Default
driver	Database driver (mysql or postgres)	Required
uri.host	Database host	localhost
uri.port	Database port	3306 (MySQL) / 5432 (PostgreSQL)
uri.user	Database user	Required
uri.password	Database password	Required
uri.database	Database name	Required
charset	Character set	utf8mb4
max_open_connections	Maximum open connections	0 (unlimited)
max_idle_connections	Maximum idle connections	2
connection_max_lifetime	Maximum connection lifetime	120s
connection_max_idletime	Maximum idle time	120s

Migrations

The sqlc package can automatically run database migrations when the client is created. It uses goose as the default migration provider.

Enabling Migrations

Add migration settings to your database configuration:

sqlc:
  default:
    migrations:
      enabled: true
      path: migrations

Migration Settings

Setting	Description	Default
enabled	Run migrations automatically	false
path	Path to migration files	Required if enabled
provider	Migration provider	goose
reset	Drop and recreate database before migrations	false
prefixed_tables	Prefix tables with application name	false

Migration Files

Create migration files in the specified path using the goose format:

-- +goose Up
CREATE TABLE authors (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- +goose Down
DROP TABLE authors;

The reset: true option is useful for development - it drops and recreates the database before running migrations, ensuring a clean state.

Creating the Client

Create a client using sqlc.NewClient() within a gosoline application. The client reads configuration from the specified connection name.

main.go

var err error
var client sqlc.Client

if client, err = sqlc.NewClient(ctx, config, logger, "default"); err != nil {
  return nil, fmt.Errorf("failed to create sqlc client: %w", err)
}

The client provides:

Query Methods (from Querier interface):

• Get(ctx, dest, query, args...) - Executes a query returning at most one row, scans into dest
• Select(ctx, dest, query, args...) - Executes a query and scans all rows into a slice
• Query(ctx, query, args...) - Executes a query returning rows for iteration
• QueryRow(ctx, query, args...) - Executes a query returning at most one row
• Exec(ctx, query, args...) - Executes a query without returning rows (INSERT, UPDATE, DELETE)
• NamedExec(ctx, query, arg) - Executes a named query using :name syntax from struct/map
• Prepare(ctx, query) - Creates a prepared statement for later execution

Transaction Methods:

• WithTx(ctx, fn, opts...) - Executes a function within a transaction (auto commit/rollback)
• BeginTx(ctx, opts...) - Starts a new transaction manually

Other Methods:

• Q() - Returns a QueryBuilder for constructing SQL queries
• Close() - Closes the database connection

Data Models

Define struct types that map to your database tables using db struct tags:

main.go

type Author struct {
  Id        int64     `db:"id"`
  Name      string    `db:"name"`
  Email     string    `db:"email"`
  CreatedAt time.Time `db:"created_at"`
  UpdatedAt time.Time `db:"updated_at"`
}

The db tag specifies the column name. Create composite structs for join results by embedding base types and adding additional fields.

INSERT Operations

Single Record

Use Into() to create an INSERT builder, then Records() to pass a struct:

main.go

func (s *BlogService) createAuthor(ctx context.Context, name, email string) (*Author, error) {
  author := &Author{
    Name:  name,
    Email: email,
  }

  result, err := s.client.Q().Into("authors").Records(author).Exec(ctx)
  if err != nil {
    return nil, fmt.Errorf("failed to insert author: %w", err)
  }

  id, err := result.LastInsertId()
  if err != nil {
    return nil, fmt.Errorf("failed to get last insert id: %w", err)
  }

  author.Id = id

  return author, nil
}

The Exec() method returns a Result with LastInsertId() and RowsAffected().

Bulk Insert

Pass a slice of structs to Records() for bulk insertion:

main.go

func (s *BlogService) createTags(ctx context.Context, names []string) ([]Tag, error) {
  tags := make([]Tag, len(names))
  for i, name := range names {
    tags[i] = Tag{Name: name}
  }

  _, err := s.client.Q().Into("tags").Records(tags).Exec(ctx)
  if err != nil {
    return nil, fmt.Errorf("failed to insert tags: %w", err)
  }

  return tags, nil
}

Query Operations

Simple Queries

Use From() to create a SELECT builder. Chain methods like Where(), OrderBy(), and Limit():

main.go

func (s *BlogService) queryPostsByAuthor(ctx context.Context, authorId int64) ([]Post, error) {
  var posts []Post

  err := sqlc.From("posts").
    WithClient(s.client).
    Where(sqlc.Col("author_id").Eq(authorId)).
    OrderBy("created_at DESC").
    Select(ctx, &posts)
  if err != nil {
    return nil, fmt.Errorf("failed to query posts: %w", err)
  }

  return posts, nil
}

Queries with JOINs

Build joins using LeftJoin(), InnerJoin(), RightJoin(), etc. Each join returns a JoinBuilder that must be finalized with On():

main.go

func (s *BlogService) queryPostsWithJoins(ctx context.Context) ([]PostWithAuthor, error) {
  var results []PostWithAuthor

  err := sqlc.From("posts").As("p").
    Columns("p.id", "p.author_id", "p.title", "p.body", "p.status", "p.created_at", "p.updated_at").
    LeftJoin("authors").As("a").On("p.author_id = a.id").
    Columns(
      sqlc.Col("a.name").As("author_name"),
      sqlc.Col("a.email").As("author_email"),
    ).
    Where(sqlc.Col("p.status").Eq("published")).
    OrderBy("p.created_at DESC").
    WithClient(s.client).
    Select(ctx, &results)
  if err != nil {
    return nil, fmt.Errorf("failed to query posts with joins: %w", err)
  }

  return results, nil
}

Use As() for table aliases and column aliases. Call Columns() before and after joins to specify which columns to select.

UPDATE Operations

Use Update() to create an UPDATE builder. Chain Set() for column values and Where() for conditions:

main.go

func (s *BlogService) updatePostStatus(ctx context.Context, postId int64, status string) (*Post, error) {
  result, err := sqlc.Update("posts").
    WithClient(s.client).
    Set("status", status).
    Where(sqlc.Col("id").Eq(postId)).
    Exec(ctx)
  if err != nil {
    return nil, fmt.Errorf("failed to update post: %w", err)
  }

  rowsAffected, err := result.RowsAffected()
  if err != nil {
    return nil, fmt.Errorf("failed to get rows affected: %w", err)
  }

  if rowsAffected == 0 {
    return nil, fmt.Errorf("no post found with id %d", postId)
  }

  var post Post
  err = sqlc.From("posts").
    WithClient(s.client).
    Where(sqlc.Col("id").Eq(postId)).
    Get(ctx, &post)
  if err != nil {
    return nil, fmt.Errorf("failed to get updated post: %w", err)
  }

  return &post, nil
}

Multiple Set() calls are combined in the UPDATE clause. Use SetExpr() for SQL expressions:

sqlc.Update("posts").
    SetExpr("updated_at", "NOW()").
    Set("status", "published")

DELETE Operations

Use Delete() to create a DELETE builder with Where() conditions:

main.go

func (s *BlogService) deleteComment(ctx context.Context, commentId int64) error {
  result, err := sqlc.Delete("comments").
    WithClient(s.client).
    Where(sqlc.Col("id").Eq(commentId)).
    Exec(ctx)
  if err != nil {
    return fmt.Errorf("failed to delete comment: %w", err)
  }

  rowsAffected, err := result.RowsAffected()
  if err != nil {
    return fmt.Errorf("failed to get rows affected: %w", err)
  }

  if rowsAffected == 0 {
    return fmt.Errorf("no comment found with id %d", commentId)
  }

  return nil
}

Transactions

Use WithTx() to execute multiple operations atomically. The transaction automatically commits on success or rolls back on error:

main.go

func (s *BlogService) createAuthorWithPost(ctx context.Context, authorName, authorEmail, postTitle, postBody string) (*Author, *Post, error) {
  var author *Author
  var post *Post

  err := s.client.WithTx(ctx, func(tx sqlc.Tx) error {
    author = &Author{
      Name:  authorName,
      Email: authorEmail,
    }

    result, err := tx.Q().Into("authors").Records(author).Exec(ctx)
    if err != nil {
      return fmt.Errorf("failed to insert author: %w", err)
    }

    authorId, err := result.LastInsertId()
    if err != nil {
      return fmt.Errorf("failed to get last insert id: %w", err)
    }
    author.Id = authorId

    post = &Post{
      AuthorId: authorId,
      Title:    postTitle,
      Body:     postBody,
      Status:   "draft",
    }

    result, err = tx.Q().Into("posts").Records(post).Exec(ctx)
    if err != nil {
      return fmt.Errorf("failed to insert post: %w", err)
    }

    postId, err := result.LastInsertId()
    if err != nil {
      return fmt.Errorf("failed to get last insert id: %w", err)
    }
    post.Id = postId

    return nil
  })
  if err != nil {
    return nil, nil, err
  }

  return author, post, nil
}

Inside the transaction callback, use tx.Q() instead of client.Q() to execute queries within the transaction scope. If the callback returns an error, all changes are rolled back; if it returns nil, changes are committed.