sqlc-template

sqlc-template is a sqlc plugin that generates code from SQL into any language by using a user provided Golang template. This plugin was inspired by the sqlc-gen-from-template plugin.

Usage

To use the plugin the following options must be defined in the sqlc configuration file (usually named sqlc.yaml):

• filename: The generated code output file name.
• template: The Golang template.

Usage example:

sqlc.yaml:

version: "2"
plugins:
    - name: sqlc-template
      wasm:
          url: https://github.com/NMFR/sqlc-template/releases/download/v1.1.0/sqlc-template.wasm
          sha256: b66ad58f7468aa1f14b4afe4b432ca405f4b64ea14e4562744e1c48adb1b3a43
sql:
    - engine: "postgresql"
      queries: "example/database/postgresql/query.sql"
      schema: "example/database/postgresql/schema.sql"
      codegen:
          - out: example/test/
            plugin: sqlc-template
            options:
                filename: queries.yaml
                template: |
                    queries:
                    {{- range .Queries }}
                    - name: {{ .Name | ToLowerCamel }}
                      cmd: {{ .Cmd }}
                      text: |{{ .Text | trim | nindent 4 }}
                      params: {{ if (eq (len .Params) 0) }}[]{{ end }}
                      {{- range .Params }}
                      - name: {{ .Column.Name | ToLowerCamel }}
                        type: {{ .Column.Type.Name -}}
                      {{ end }}
                      columns: {{ if (eq (len .Columns) 0) }}[]{{ end }}
                      {{- range .Columns }}
                      - name: {{ .Name | ToLowerCamel }}
                        type: {{ .Type.Name -}}
                      {{ end }}
                    {{ end -}}

Running the sqlc generate command will create the following file:

queries.yaml:

queries:
    - name: getAuthor
      cmd: :one
      text: |
          SELECT id, name, bio FROM authors
          WHERE id = $1 LIMIT 1
      params:
          - name: id
            type: bigserial
      columns:
          - name: id
            type: bigserial
          - name: name
            type: text
          - name: bio
            type: text

    - name: listAuthors
      cmd: :many
      text: |
          SELECT id, name, bio FROM authors
          ORDER BY name
      params: []
      columns:
          - name: id
            type: bigserial
          - name: name
            type: text
          - name: bio
            type: text

    - name: createAuthor
      cmd: :one
      text: |
          INSERT INTO authors (
            name, bio
          ) VALUES (
            $1, $2
          )
          RETURNING id, name, bio
      params:
          - name: name
            type: text
          - name: bio
            type: text
      columns:
          - name: id
            type: bigserial
          - name: name
            type: text
          - name: bio
            type: text

    - name: updateAuthor
      cmd: :exec
      text: |
          UPDATE authors
            set name = $2,
            bio = $3
          WHERE id = $1
      params:
          - name: id
            type: bigserial
          - name: name
            type: text
          - name: bio
            type: text
      columns: []

    - name: deleteAuthor
      cmd: :exec
      text: |
          DELETE FROM authors
          WHERE id = $1
      params:
          - name: id
            type: bigserial
      columns: []

Template

The template uses the Golang template "language".

The data object available at the root of the template ({{ . }}) is the sqlc GenerateRequest object that provides access to the SQL schema, queries and some sqlc configuration fields.

All of the sprig functions are available to be called from within the template with the exception of:

• osBase
• osDir
• osClean
• osExt
• env
• expandenv
• kindOf
• kindIs
• typeOf
• typeIs
• typeIsLike
• deepEqual
• getHostByName