So I have created a D1 database with
So I have created a D1 database with
wrangler and I have added a table to the database using the D1 web console. I can get the db schema just fine via PRAGMA:
npx wrangler d1 execute spendydb --command='PRAGMA table_list' --remote
However if I try to execute any SQL with wrangler I get an auth error. This happens for remote and local instances:
npx wrangler d1 execute spendydb --file=./sql/create_d1.sql --remote^
ā [ERROR] not authorized: SQLITE_AUTH
npx wrangler whoami tells me I have d1 (write) permissions. Did I miss a setup step with wrangler? Note that it is up to date at 4.42.2. Is this a question for the wrangler channel? I don't see anything useful in the log, although this is interesting: "errorType": "UserError".
Please note that the SQL in my file works fine when entered via the web console.3 Replies
Huh. So I was following the PRAGMA instructions here:
https://developers.cloudflare.com/d1/sql-api/sql-statements/
and started my script with
PRAGMA foreigh_keys=on; and this line was causing the error. So, is that page incorrect? It looks like foreign keys work fine w/o forcing with the pragma. I haven't used sqlite in quite a few years, it used to be required to force it.Cloudflare Docs
SQL statements
D1 is compatible with most SQLite's SQL convention since it leverages SQLite's query engine. D1 supports a number of database-level statements that allow you to list tables, indexes, and inspect the schema for a given table or index.
In general,
[ERROR] not authorized: SQLITE_AUTH means that some function or statement used is not allowed by the SQLite build used in Workers runtime.Ok, cool. Thanks for that. I assumed as much once I got it working but that's strange wording for that kind of error š