workers-sdk
workers-sdk copied to clipboard
SQL[lite] files fail for `.exec(...)` that succeed with CLI invokation
What versions & operating system are you using?
wrangler 4.14.4
Please provide a link to a minimal reproduction
No response
Describe the Bug
Accessing a SQL file via command line will work: npx wrangler d1 execute test-data --local --file="./assets/table_create.sql"
However, it will fail when loading as a string and executing with an error like: incomplete input: SQLITE_ERROR
This seems to be because of \r\n that are present in the file that the wrangler command doesn't mind but cause errors when passed to exec() or when hardcoded.
Reproduce:
============== Sample SQL file: table_create.sql ================
CREATE TABLE IF NOT EXISTS metrics (
date TEXT PRIMARY KEY NOT NULL,
count INTEGER,
views INTEGER,
);
-
Run locally:
npx wrangler d1 execute test-data --local --file="./assets/table_create.sql"## This should work -
Access programmatically:
await step.do("Create table (if not exists) using schema", async () => {
const schemaUrl = 'https://assets.local/table_create.sql';
const schemaResponse = await this.env.ASSETS.fetch(schemaUrl);
if (!schemaResponse.ok) {
throw new Error(`Failed to fetch schema: ${schemaResponse.statusText}`);
}
const FAILS_schemaSql = await schemaResponse.text();
const SUCCEEDS_ schemaSql = `CREATE TABLE IF NOT EXISTS iq_ga_metrics ( \
date TEXT PRIMARY KEY NOT NULL, \
count INTEGER, \
views INTEGER, \
);`
let schemaSql = // SET Accordingly;
console.log(`Creating D1 table with ${schemaSql.length} SQL:`, schemaSql);
await this.env.DB.exec(schemaSql);
});
```
Using `FAILS_schemaSql` will fail with the error above *EVEN IF* you manually add escapes [`\`] to lines in the file.
3. Alternative: Loading the SQL file (from assets) and using the contents as a query will succeed *IF* you strip newlines and carriage returns:
`schemaSql = (await schemaResponse.text()).replace(/\r/g, " ").replace(/\n/g, ' ').replace(/\s+/g, " ").trim();`
### Please provide any relevant error logs
Error might also look something like: D1_EXEC_ERROR: Error in line 1: CREATE TABLE IF NOT EXISTS data(: incomplete input: SQLITE_ERROR