================ Using Go and pgx

.. note:: :code:pgx/v5 is supported starting from v1.18.0.

pgx is a pure Go driver and toolkit for PostgreSQL. It's become the default PostgreSQL package for many Gophers since lib/pq was put into maintenance mode.

^^^^^^^^^^^^^^^ Getting started ^^^^^^^^^^^^^^^

To start generating code that uses pgx, set the :code:sql_package field in your :code:sqlc.yaml configuration file. Valid options are :code:pgx/v4 or :code:pgx/v5

.. code-block:: yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "query.sql"
    gen:
      go:
        package: "db"
        sql_package: "pgx/v5"
        out: "db"

If you don't have an existing sqlc project on hand, create a directory with the configuration file above and the following :code:query.sql file.

.. code-block:: sql

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

Generating the code will now give you pgx-compatible database access methods.

.. code-block:: bash

sqlc generate

^^^^^^^^^^^^^^^^^^^^^^^^^^ Generated code walkthrough ^^^^^^^^^^^^^^^^^^^^^^^^^^

The generated code is very similar to the code generated when using :code:lib/pq. However, instead of using :code:database/sql, the code uses pgx types directly.

.. code-block:: go

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
    
	"example.com/sqlc-tutorial/db"
)

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

	q := db.New(conn)

	author, err := q.GetAuthor(context.Background(), 1)
	if err != nil {
		fmt.Fprintf(os.Stderr, "GetAuthor failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(author.Name)
}

.. note:: For production applications, consider using pgxpool for connection pooling:

.. code-block:: go

   import (
       "github.com/jackc/pgx/v5/pgxpool"
       "example.com/sqlc-tutorial/db"
   )

   func main() {
       pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to create connection pool: %v\n", err)
           os.Exit(1)
       }
       defer pool.Close()

       q := db.New(pool)
       // Use q the same way as with single connections
   }