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

Ccras6 @Ivan Najdovski i have the same question actually

I

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

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

I

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

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?

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

Similar Threads