If there is no way to alter D1 tables and add FOREIGN KEY i have fallowup question and that is, what

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

R

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

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

R

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

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

M

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

I

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

Thanks.

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

M

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

Seems like it works

MMax (@rozenmd)Seems like it works

I

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

From cloudflare console or with worker statement?

MMax (@rozenmd)Seems like it works

I

IvanNOP•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...

M

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

I

IvanNOP•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

M

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

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

I

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

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

I

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

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

I

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

That will save me a lot of time

I

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

Thanks

M

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

I

IvanNOP•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

L

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!

C

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",

C

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

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

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

C

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)

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

M

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?

L

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

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

C

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.

C

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

from wrangler it works, but from the code Astro it does not

C

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

the KV however works ..

L

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

D1 dashboard is stuck, is it only for me?

L

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

And an API token that worked up until 2 hours ago is not working, even though it says it’s active and no changes were made

L

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

@Alexander.Xtreme I get the same

L

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

Also if you check the network request for the dashboard, it gets similar error

L

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

I’ve opened a support ticket

G

gf•11/24/23, 9:53 PM

same here

C

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

@nora my remode database has no tables in it .. does the request is trying to look to the remote instead of the local db ?

C

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

[[d1_databases]]
binding = "DB"                                               # i.e. available in your Worker on env.DB
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"

[[d1_databases]]
binding = "DB"                                               # i.e. available in your Worker on env.DB
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"

[[d1_databases]]
binding = "DB"                                               # i.e. available in your Worker on env.DB
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"

[[d1_databases]]
binding = "DB"                                               # i.e. available in your Worker on env.DB
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"

C

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

this is my binding

C

Constantin•11/24/23, 10:02 PM

oh, found it

C

Cyb3r-Jak3•11/24/23, 10:03 PM

Raised the wrangler create issue

C

Constantin•11/24/23, 10:03 PM

[[d1_databases]]
binding = "DB"                                       # Should match preview_database_id
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "DB"                           # Required for Pages local development

[[d1_databases]]
binding = "DB"                                       # Should match preview_database_id
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "DB"                           # Required for Pages local development

[[d1_databases]]
binding = "DB"                                       # Should match preview_database_id
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "DB"                           # Required for Pages local development

[[d1_databases]]
binding = "DB"                                       # Should match preview_database_id
database_name = "user"
database_id = "97285d4c-b85d-4df9-b44f-d4411fe1cbbc"
preview_database_id = "DB"                           # Required for Pages local development

C

Constantin•11/24/23, 10:08 PM

ty @nora

Lliorgr D1 dashboard is stuck, is it only for me?

R

Rob M.•11/24/23, 10:32 PM

same here

B

Bakĉoj | Brandon•11/24/23, 10:34 PM

Oh thank god I'm not alone

R

Rob M.•11/24/23, 10:35 PM

I can access my D1 instance from the worker though blob_sweat

B

Bakĉoj | Brandon•11/24/23, 10:35 PM

Same

B

Bakĉoj | Brandon•11/24/23, 10:36 PM

But I can't run any migrations from wrangler

B

Bakĉoj | Brandon•11/24/23, 10:36 PM

It was working just fine, then all of a sudden everything stopped

J

James•11/24/23, 10:38 PM

You can't run any d1 commands with wrangler at all? What happens when you try?

B

Bakĉoj | Brandon•11/24/23, 10:41 PM

  POST method not allowed for the oauth_token authentication scheme [code: 10000]

  POST method not allowed for the oauth_token authentication scheme [code: 10000]

  POST method not allowed for the oauth_token authentication scheme [code: 10000]

  POST method not allowed for the oauth_token authentication scheme [code: 10000]

J

James•11/24/23, 10:42 PM

okay I think we're gonna raise this higher. Creating new DBs being broken is one thing, but migrations being broken is a big deal. Give us a moment

J

James•11/24/23, 10:42 PM

Thanks for the info all

If there is no way to alter D1 tables and add FOREIGN KEY i have fallowup question and that is, what

Similar Threads

Similar Threads

Similar Threads