PRAGMA queries are not recognised

[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

[kyleconroy]

https://github.com/sqlc-dev/sqlc/pull/3239 will fix the queries getting combined.

[ErikKalkoken]

I am currently evaluating slqc for my project and stumbled over this question.

Couldn't you enable foreign key checks via options to your sqlite connection string instead of pragmas?

For example initializing your DB like this would enable foreign key support:

db, err := sql.Connect("sqlite3", "file:test.db?_foreign_keys=on")

Would that work with slqc or am I missing something?

This assumes you are using the go-sqlite3 driver. For details can be found here: Connection String

[sybrenstuvel]

This is interesting. I'm using the 'modernc' sqlite implementation, which is considerably less documented when it comes to the connection string. I can see if those connection string options work with that too.

[sybrenstuvel]

Unfortunately, no, this does not work. This is the code I used to test:

package main

import (
	"database/sql"
	"fmt"

	_ "modernc.org/sqlite"
)

func main() {
	db, err := sql.Open("sqlite", "file:flamenco-manager.sqlite?_foreign_keys=on")
	if err != nil {
		panic(err)
	}

	row := db.QueryRow("PRAGMA foreign_keys")

	var enabled bool
	if err := row.Scan(&enabled); err != nil {
		panic(err)
	}
	fmt.Println("Enabled:", enabled)
}

[ncruces]

For modernc you want:

db, err := sql.Connect("sqlite", "file:test.db?_pragma=foreign_keys(on)")

[sybrenstuvel]

Note that, even though this might work for this particular case, it's not a workaround for the general issue with PRAGMA queries. I also want to do a PRAGMA integrity_check; at startup of my application, which suffers from the same issue.

[dhinogz]

Had the same issue and ended up with this implementation using mattn:

func InitSqlite() (*sql.DB, error) {
	extensions, err := readExtensions()
	if err != nil {
		return nil, fmt.Errorf("reading extensions: %s", err)
	}

	sql.Register("sqlite3_ext",
		&sqlite3.SQLiteDriver{
			ConnectHook: func(conn *sqlite3.SQLiteConn) error {
				_, err := conn.Exec(`
					PRAGMA busy_timeout       = 10000;
					PRAGMA journal_mode       = WAL;
					PRAGMA journal_size_limit = 200000000;
					PRAGMA synchronous        = NORMAL;
					PRAGMA foreign_keys       = ON;
					PRAGMA temp_store         = MEMORY;
					PRAGMA cache_size         = -16000;
				\`, nil)
				return err
			},
			Extensions: extensions,
		})

	conn, err := sql.Open("sqlite3_ext", dbPath)
	if err != nil {
		return nil, fmt.Errorf("opening sqlite db: %s", err)
	}
	return conn, nil
}

Got it from the pocketbase library.

Here's how that project set up PRAGMA using modernc.