if i do create a dummy file, it literally say this. ``` mkdir drizzle touch drizzle/0000_short_lockh

if i do create a dummy file, it literally say this.

mkdir drizzle
touch drizzle/0000_short_lockheed.sql
wrangler d1 execute 'cloudflare-db' --local --file='./drizzle/0000_short_lockheed.sql'

✘ [ERROR] Error: must provide --command or --file.


If you think this is a bug then please create an issue at https://github.com/cloudflare/workers-sdk/issues/new/choose
🪵  Logs were written to "/Users/administrator/Library/Preferences/.wrangler/logs/wrangler-2023-11-23_11-23-02_962.log"

mkdir drizzle
touch drizzle/0000_short_lockheed.sql
wrangler d1 execute 'cloudflare-db' --local --file='./drizzle/0000_short_lockheed.sql'

✘ [ERROR] Error: must provide --command or --file.


If you think this is a bug then please create an issue at https://github.com/cloudflare/workers-sdk/issues/new/choose
🪵  Logs were written to "/Users/administrator/Library/Preferences/.wrangler/logs/wrangler-2023-11-23_11-23-02_962.log"

mkdir drizzle
touch drizzle/0000_short_lockheed.sql
wrangler d1 execute 'cloudflare-db' --local --file='./drizzle/0000_short_lockheed.sql'

✘ [ERROR] Error: must provide --command or --file.


If you think this is a bug then please create an issue at https://github.com/cloudflare/workers-sdk/issues/new/choose
🪵  Logs were written to "/Users/administrator/Library/Preferences/.wrangler/logs/wrangler-2023-11-23_11-23-02_962.log"

mkdir drizzle
touch drizzle/0000_short_lockheed.sql
wrangler d1 execute 'cloudflare-db' --local --file='./drizzle/0000_short_lockheed.sql'

✘ [ERROR] Error: must provide --command or --file.


If you think this is a bug then please create an issue at https://github.com/cloudflare/workers-sdk/issues/new/choose
🪵  Logs were written to "/Users/administrator/Library/Preferences/.wrangler/logs/wrangler-2023-11-23_11-23-02_962.log"

HearseOP•11/23/23, 11:24 AM

wrangler 3.17.1

wrangler 3.17.1

btw, it should be latest.

Rob M.•11/23/23, 11:44 AM

maybe, there is a newline symbol after "--"?

HHearse if i do create a dummy file, it literally say this. ``` mkdir drizzle touch driz...

Max (@rozenmd)•11/23/23, 12:06 PM

thats weird, do you mind filing a github issue? looks like a bug

Max (@rozenmd)•11/23/23, 12:07 PM

ohhhh

HearseOP•11/23/23, 12:07 PM

Will do when i wake up

Max (@rozenmd)•11/23/23, 12:07 PM

no thats just a bad error message

Max (@rozenmd)•11/23/23, 12:07 PM

it throws "Error: must provide --command or --file" if there's an empty string too

Max (@rozenmd)•11/23/23, 12:08 PM

I think you need to run

npm run generate

npm run generate

for drizzle to create the SQL

MMax (@rozenmd)I think you need to run `npm run generate` for drizzle to create the SQL

HearseOP•11/23/23, 12:14 PM

huh thats weird.

HearseOP•11/23/23, 12:14 PM

because in the quickstart it doesnt address that

HearseOP•11/23/23, 12:14 PM

https://orm.drizzle.team/docs/quick-sqlite/d1

Cloudflare D1 - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

MMax (@rozenmd)I think you need to run `npm run generate` for drizzle to create the SQL

HearseOP•11/23/23, 12:15 PM

literally just says have your wrangler.toml which i already know is right

HearseOP•11/23/23, 12:15 PM

and then run the command.

HearseOP•11/23/23, 12:16 PM

ill do that though

Max (@rozenmd)•11/23/23, 12:16 PM

actually wait

Max (@rozenmd)•11/23/23, 12:16 PM

it seems like their docs are written for you to clone their example project

Max (@rozenmd)•11/23/23, 12:16 PM

because the file is here: https://github.com/drizzle-team/drizzle-orm/blob/main/examples/cloudflare-d1/drizzle/0000_short_lockheed.sql

HearseOP•11/23/23, 12:18 PM

wtf

HearseOP•11/23/23, 12:27 PM

oh okay, i got it, you have to generate the sql file with ur schema and then use the generated file name

forslunds•11/23/23, 12:59 PM

Yes, generate migrations/schema with drizzle and upload with wrangler/d1

Rob M.•11/24/23, 10:57 AM

Hey

If I have two simultaneous write operations that increment the same cell in D1 using one-shot sql query. Will it work right out of the box or will I need to implement something special for this?

Rob M.•11/24/23, 11:01 AM

SQL Example:

UPDATE table_name 
SET column_name = column_name + 1
WHERE condition;

UPDATE table_name 
SET column_name = column_name + 1
WHERE condition;

RRob M.Hey 👋 If I have two simultaneous write operations that increment the same cell ...

Max (@rozenmd)•11/24/23, 11:14 AM

it'll work, D1 uses a single writer

IvanN•11/24/23, 11:29 AM

Hello everyone, have a question about D1, is there a way to ALTER table and add FOREIGN KEY on existing table?

Ccras6 @Ivan Najdovski i have the same question actually

IvanN•11/24/23, 1:44 PM

If i find a way i will make sure to get you informed

IvanN•11/24/23, 1:46 PM

If there is no way to alter D1 tables and add FOREIGN KEY i have fallowup question and that is, what is the best way to create database backup and restore database

IIvanN If there is no way to alter D1 tables and add FOREIGN KEY i have fallowup questi...

Rob M.•11/24/23, 2:01 PM

maybe "Time Travel":
https://developers.cloudflare.com/d1/learning/time-travel/

Rob M.•11/24/23, 2:02 PM

or manually via
https://github.com/nora-soderlund/cloudflare-d1-backups

Max (@rozenmd)•11/24/23, 2:02 PM

For undoing mistakes/restoring data, use Time Travel. For downloading the db and loading locally, nora's tool: https://github.com/nora-soderlund/cloudflare-d1-backups

MMax (@rozenmd)For undoing mistakes/restoring data, use Time Travel. For downloading the db and...

IvanN•11/24/23, 2:04 PM

Thanks.

IIvanN Hello everyone, have a question about D1, is there a way to ALTER table and add...

Max (@rozenmd)•11/24/23, 2:14 PM

Seems like it works

MMax (@rozenmd)Seems like it works

IvanN•11/24/23, 2:15 PM

From cloudflare console or with worker statement?

MMax (@rozenmd)Seems like it works

IvanN•11/24/23, 2:16 PM

My next move was to try to alter the table from the worker because from cloudflare console i got syntax error

IIvanN My next move was to try to alter the table from the worker because from cloudfla...

Max (@rozenmd)•11/24/23, 2:17 PM

if you get a syntax error in the console, you'll get a syntax error in the worker too, it's the same engine

IvanN•11/24/23, 2:20 PM

Did you managed to alter and add foreign key, asking because if that works might be something wrong with my column definitions

Max (@rozenmd)•11/24/23, 2:20 PM

I did, here's an example:

CREATE TABLE sensors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO sensors (name) VALUES ('sensor1');
INSERT INTO sensors (name) VALUES ('sensor2');

CREATE TABLE sensor_data (
    id INTEGER PRIMARY KEY,
    value REAL NOT NULL,
    timestamp INTEGER NOT NULL
);

INSERT INTO sensor_data (value, timestamp) VALUES (1.0, 1234);
INSERT INTO sensor_data (value, timestamp) VALUES (2.0, 1235);

ALTER TABLE sensor_data ADD COLUMN sensor_id INTEGER REFERENCES sensors(id);

CREATE TABLE sensors (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO sensors (name) VALUES ('sensor1');
INSERT INTO sensors (name) VALUES ('sensor2');

CREATE TABLE sensor_data (
    id INTEGER PRIMARY KEY,
    value REAL NOT NULL,
    timestamp INTEGER NOT NULL
);

INSERT INTO sensor_data (value, timestamp) VALUES (1.0, 1234);
INSERT INTO sensor_data (value, timestamp) VALUES (2.0, 1235);

ALTER TABLE sensor_data ADD COLUMN sensor_id INTEGER REFERENCES sensors(id);

MMax (@rozenmd)I did, here's an example: ``` CREATE TABLE sensors ( id INTEGER PRIMARY KEY,...

IvanN•11/24/23, 2:23 PM

But you created new column and add key, i want to alter existing column

IvanN•11/24/23, 2:24 PM

However if that works i can add new column add key and run sql to populate the column

IvanN•11/24/23, 2:24 PM

That will save me a lot of time

IvanN•11/24/23, 2:25 PM

Thanks

Max (@rozenmd)•11/24/23, 2:25 PM

sqlite is a bit funky with alter table, only a subset of the sql spec is implemented: https://www.sqlite.org/lang_altertable.html

Ccras6 @Ivan Najdovski i have the same question actually

IvanN•11/24/23, 5:56 PM

hey @cras6 so this solution works for me,

Create new column "temp_column"
Update temp_column with value from column that you want to have the FOREIGN KEY

UPDATE your_table
SET temp_column = column_foreign_key_but_not_set;

Drop the column_foreign_key_but_not_set that you want to have the FOREIGN KEY
Create the same column with FOREIGN KEY column_foreign_key
solution by @rozenmd thanks btw.
ALTER TABLE your_table ADD COLUMN column_foreign_key INTEGER REFERENCES ref_table(ref_column);
Copy content from temp_column to the one you created in step 4 column_foreign_key
Drop the temp_column

A bit of steps but works

Lambo•11/24/23, 8:17 PM

Hi, I have a question around generated columns in D1.

I've read through this page and I think it does what I want, but I'm a bit unsure how to apply it.

Essentially, I have columns A and B, I want to create column C which is a hashed value of A and B.
I could do this through code in my worker, but ideally I want it done at DB level (seems safer and it would pollute my worker code).

I see that SQLite has no hashing functions built in though, so is this even possible?

Thanks!

Constantin•11/24/23, 9:05 PM

hey, following D1 customers schema tutorial, i can run select with wrangler, but when using programatycaly with Astro, i'm getting : "D1_ERROR: no such table: Customers",

Constantin•11/24/23, 9:05 PM

const { results } = await ENV.DB.prepare("SELECT * from Customers")
    .bind("Customers")
    .all();

const { results } = await ENV.DB.prepare("SELECT * from Customers")
    .bind("Customers")
    .all();

Constantin•11/24/23, 9:07 PM

    Cause:
  Error: no such table: Customers
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.all (cloudflare-internal:d1-api:175:29)

    Cause:
  Error: no such table: Customers
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:67:24)
      at async D1PreparedStatement.all (cloudflare-internal:d1-api:175:29)

CConstantin hey, following D1 customers schema tutorial, i can run select with wrangler, but...

Matt Silverlock•11/24/23, 9:27 PM

Are you getting “no such table: users” (as per your comment) or “no such table: customers” as per the error text you posted? Did you create the table first?

liorgr•11/24/23, 9:29 PM

Am I the only one experiencing issues with D1?
Dashboard doesn’t load & API calls are failing

Constantin•11/24/23, 9:29 PM

sorry i was testing with both users and Consumers. yes the consumers table is created and i can query it.

if i do create a dummy file, it literally say this. ``` mkdir drizzle touch drizzle/0000_short_lockh

Similar Threads

Similar Threads

Similar Threads