Skip to content

PRAGMA queries are not recognised #3237

Open
@sybrenstuvel

Description

@sybrenstuvel

Version

1.25.0

What happened?

SQLite requires a PRAGMA query to enable foreign key constraints. Unfortunately, sqlc does not seem to recognise PRAGMA as a valid query, and actually collects everything until the first query it does recognise. Let me illustrate. In the queries.sql below, there are three named PRAGMA queries followed by an INSERT. Instead of generating four query functions, sqlc actually just creates one:

const pragmaForeignKeysEnable = `-- name: PragmaForeignKeysEnable :exec

PRAGMA foreign_keys = 1;

PRAGMA foreign_keys = 0;

PRAGMA foreign_keys;



INSERT INTO jobs (
  created_at,
  uuid,
  name,
  job_type,
  priority,
  status,
  activity,
  settings,
  metadata,
  storage_shaman_checkout_id
)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
`

type PragmaForeignKeysEnableParams struct {
	CreatedAt               time.Time
	Uuid                    string
	Name                    string
	JobType                 string
	Priority                int64
	Status                  string
	Activity                string
	Settings                json.RawMessage
	Metadata                json.RawMessage
	StorageShamanCheckoutID sql.NullString
}

// PRAGMA queries
//
// Jobs / Tasks queries
func (q *Queries) PragmaForeignKeysEnable(ctx context.Context, arg PragmaForeignKeysEnableParams) error {
	_, err := q.db.ExecContext(ctx, pragmaForeignKeysEnable,
		arg.CreatedAt,
		arg.Uuid,
		arg.Name,
		arg.JobType,
		arg.Priority,
		arg.Status,
		arg.Activity,
		arg.Settings,
		arg.Metadata,
		arg.StorageShamanCheckoutID,
	)
	return err
}

As you can see, instead of using -- name: ... as query boundary, sqlc groups everything together until the INSERT.

Relevant log output

No response

Database schema

CREATE TABLE jobs (
  id integer NOT NULL,
  created_at datetime NOT NULL,
  updated_at datetime,
  uuid varchar(36) UNIQUE DEFAULT '' NOT NULL,
  name varchar(64) DEFAULT '' NOT NULL,
  job_type varchar(32) DEFAULT '' NOT NULL,
  priority smallint DEFAULT 0 NOT NULL,
  status varchar(32) DEFAULT '' NOT NULL,
  activity varchar(255) DEFAULT '' NOT NULL,
  settings jsonb NOT NULL,
  metadata jsonb NOT NULL,
  delete_requested_at datetime,
  storage_shaman_checkout_id varchar(255) DEFAULT '',
  PRIMARY KEY (id),
)

SQL queries

-- PRAGMA queries
--

-- name: PragmaForeignKeysEnable :exec
PRAGMA foreign_keys = 1;

-- name: PragmaForeignKeysDisable :exec
PRAGMA foreign_keys = 0;

-- name: PragmaForeignKeysGet :one
PRAGMA foreign_keys;


-- Jobs / Tasks queries
--

-- name: CreateJob :exec
INSERT INTO jobs (
  created_at,
  uuid,
  name,
  job_type,
  priority,
  status,
  activity,
  settings,
  metadata,
  storage_shaman_checkout_id
)
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );

Configuration

No response

Playground URL

https://play.sqlc.dev/p/ce20411be4624a0295c2e51b8eb075a4e1069ac3d6951778cd6a54f032831a3c

What operating system are you using?

Windows

What database engines are you using?

SQLite

What type of code are you generating?

Go

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions