Inconsistent behavior between libSQL server and SQLite for double-quoted strings

[riazus]

Hello, I encountered a non-trivial issue that concerns both turso and drizzle-kit.

For deep understanding of the further explanations, I highly recommend you first to read the issue on @drizzle-kit side.

For adding the primary key to my table, I used the drizzle-kit generate command that generated the following SQL script:

PRAGMA foreign_keys=OFF;--> statement-breakpoint
CREATE TABLE `__new_test_table` (
	`id` integer PRIMARY KEY NOT NULL,
	`name` text
);
--> statement-breakpoint
INSERT INTO `__new_test_table`("id", "name") SELECT "id", "name" FROM `test_table`;--> statement-breakpoint
DROP TABLE `test_table`;--> statement-breakpoint
ALTER TABLE `__new_test_table` RENAME TO `test_table`;--> statement-breakpoint
PRAGMA foreign_keys=ON;

NOTE: At the line "INSERT INTO __new_test_table("id", "name") SELECT "id", "name" FROM test_table;" we're trying to access the id column, but it doesn't exist yet.

The most important part related to Turso is when we're trying to apply migration changes to the database:

• Applying changes to the remote database works without any errors. This is unexpected behavior, because the migration script is incorrect?
• Applying changes to the local turso server (launched with the turso dev command) throws an error, which is the expected and correct behavior.
• Applying the same changes directly using the sqlite3 command on the local database file does not throw an error. I'm not sure why this happens - it's very strange.

Versions of my packages:

$ turso --version
turso version v1.0.8
$ turso dev --version
sqld sqld 0.24.31 (e88c6b51 2025-01-06)
$ sqlite3 --version
3.43.2 2023-10-10 13:08:14 1b37c146ee9ebb7acd0160c0ab1fd11017a419fa8a3187386ed8cb32b709aapl (64-bit)

[penberg]

Hey @riazus!

As you mention, SQLite is totally fine with the invalid query:

sqlite> CREATE TABLE `test_table` (`name` text);
sqlite> INSERT INTO test_table VALUES ('hello, world');
sqlite> SELECT "id", "name" FROM `test_table`;
id|hello, world

That's because SQLite treats double-quoted strings as literals unless they're a column:

https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

The software on Turso Cloud is, therefore, the expected behavior as it follows SQLite's (arguably weird) semantics and the local dev server has a compatibility bug essentially.