SQL query execution fails with SQL_PARSE_ERROR

[razr22]

Hey,

Using turso's sqlite/libsql wrapper and drizzle-kit to execute queries on the db.

When running SQL commands I receive this error:

LibsqlError: SQL_PARSE_ERROR: SQL string could not be parsed: near LP, "None": syntax error at (2, 38)

Example SQL:

ALTER TABLE table RENAME TO oldtable;
CREATE TABLE table (
        id text PRIMARY KEY DEFAULT uuid4() NOT NULL,
        ref_id text NOT NULL,
        last_login integer,
        created_at integer DEFAULT CURRENT_TIMESTAMP NOT NULL,
        updated_at integer DEFAULT CURRENT_TIMESTAMP NOT NULL
);

INSERT INTO table SELECT * FROM oldtable;
DROP TABLE oldtable; 

[CodingDoug]

Some notes on this SQL. There are two syntax problems here.

1. If the name of the table is to be "table" (a SQL keyword), then the name of the table needs to be in backticks.

For example:

create table `table` ( .. );

2. uuid4() is not a valid is a default value, and is probably the cause of the message you see (LP likely means "Left Parenthesis").

This will parse correctly if you put uuid4() in parenthesis. According to the SQLite docs for create table:

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.

So, this should work:

CREATE TABLE `table` (
        id text PRIMARY KEY DEFAULT (uuid4()) NOT NULL,
        ref_id text NOT NULL,
        last_login integer,
        created_at integer DEFAULT CURRENT_TIMESTAMP NOT NULL,
        updated_at integer DEFAULT CURRENT_TIMESTAMP NOT NULL
);

Also note that uuid4() is part of a SQLean extension, so extensions need to be enabled on the database for this to run.

Also see a filed bug for improving these parse errors: #459

[razr22]

issue was related to uuidv4() not being enclosed with ().

ex.

id text PRIMARY KEY DEFAULT uuid4() NOT NULL

TO

id text PRIMARY KEY DEFAULT (uuid4()) NOT NULL