Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

PRAGMA queries are not recognised #3237

Open
sybrenstuvel opened this issue Mar 2, 2024 · 6 comments
Open

PRAGMA queries are not recognised #3237

sybrenstuvel opened this issue Mar 2, 2024 · 6 comments
Labels
📚 sqlite enhancement New feature or request

Comments

@sybrenstuvel
Copy link

sybrenstuvel commented Mar 2, 2024

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

@sybrenstuvel sybrenstuvel added bug Something isn't working triage New issues that hasn't been reviewed labels Mar 2, 2024
@kyleconroy kyleconroy added 📚 sqlite and removed triage New issues that hasn't been reviewed labels Mar 4, 2024
@kyleconroy
Copy link
Collaborator

#3239 will fix the queries getting combined.

@kyleconroy kyleconroy changed the title PRAGMA queries are not recognised, and are combined until the first recognised one PRAGMA queries are not recognised Mar 4, 2024
@kyleconroy kyleconroy added enhancement New feature or request and removed bug Something isn't working labels Mar 4, 2024
@ErikKalkoken
Copy link

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
Copy link
Author

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
Copy link
Author

sybrenstuvel commented Apr 11, 2024

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
Copy link

ncruces commented Aug 5, 2024

For modernc you want:

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

@sybrenstuvel
Copy link
Author

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
📚 sqlite enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

4 participants