workers-sdk icon indicating copy to clipboard operation
workers-sdk copied to clipboard

SQL[lite] files fail for `.exec(...)` that succeed with CLI invokation

Open CNX-Inc opened this issue 6 months ago • 0 comments

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, );

  1. Run locally: npx wrangler d1 execute test-data --local --file="./assets/table_create.sql" ## This should work

  2. 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

CNX-Inc avatar Jun 04 '25 16:06 CNX-Inc