🐛 BUG: D1 IDENTITY or AUTOINCREMENT neither work

[PeterHindes]

Which Cloudflare product(s) does this pertain to?

D1

What version(s) of the tool(s) are you using?

beta

What version of Node are you using?

No response

What operating system are you using?

windows

Describe the Bug

INSERT's into tables with IDENTITY(0,1) for example don't accept DEFAULT as a value for the column that is taged with IDENTITY
to reproduce try the following on a d1 database
CREATE TABLE emails (id INT IDENTITY(0,1) NOT NULL UNIQUE, email VARCHAR(256) NOT NULL UNIQUE, PRIMARY KEY (id));
INSERT INTO emails (email) VALUES ('test@example.com');
or
INSERT INTO emails (id, email) VALUES (DEFAULT, 'test@example.com');
or
INSERT INTO emails (id, email) VALUES (NULL, 'test@example.com');

None of these inserts behave as expected. I expect them to create the new row and set an appropriate id by incrementing on the largest value (or starting with the provided 0 value for the first insert) but they don't.

Please provide a link to a minimal reproduction

No response

Please provide any relevant error logs

Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on ...:

X [ERROR] A request to the Cloudflare API (/accounts/.../d1/database/.../query) failed.

NOT NULL constraint failed: emails.id [code: 7500]

[PeterHindes]

as for AUTOINCREMENT it is simply not supported for some reason. even though it is in the demo on the d1 workers docs.

[jide]

Found out it works if AUTOINCREMENT is at the end :

-- Create groups table
CREATE TABLE groups (
    id integer PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL
);