I did, here's an example: ``` CREATE TABLE sensors ( id INTEGER PRIMARY KEY, name TEXT NOT N

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

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)OP•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.

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

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

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

the KV however works ..

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

D1 dashboard is stuck, is it only for me?

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

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

@Alexander.Xtreme I get the same

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

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

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

I’ve opened a support ticket

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

same here

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 ?

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"

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

this is my binding

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

oh, found it

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

Raised the wrangler create issue

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

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

ty @nora

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

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

same here

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

Oh thank god I'm not alone

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

I can access my D1 instance from the worker though blob_sweat

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

Same

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

But I can't run any migrations from wrangler

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

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

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

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

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]

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

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

Thanks for the info all

JJames okay I think we're gonna raise this higher. Creating new DBs being broken is one...

Matt Silverlock•11/24/23, 10:44 PM

@rozenmd

andy__yo•11/24/23, 10:47 PM

Is there any starter repo for nextjs with CloudFlare D1 on Drizzle? I am new to the CloudFlare environment, I tried with bindings as per doc couldn't get that up running.

Aandy__yo Is there any starter repo for nextjs with CloudFlare D1 on Drizzle? I am new to...

Matt Silverlock•11/24/23, 10:48 PM

See https://developers.cloudflare.com/pages/framework-guides/deploy-a-nextjs-site/#use-bindings-in-your-nextjs-application

Deploy a Next.js site · Cloudflare Pages docs

Next.js is an open-source React framework for creating websites and applications. In this guide, you will create a new Next.js application and deploy …

Matt Silverlock•11/24/23, 10:51 PM

Hi folks - we’re having some API issues specific to D1 and expect to remediate shortly.

Matt Silverlock•11/24/23, 11:13 PM

@D1 Database Issues with the D1 API (including the dashboard) should now be resolved. We attempted to roll out an important fix, but introduced an authentication issue. We've rolled this back until we can re-test the fix.

MMatt Silverlock @D1 Database Issues with the D1 API (including the dashboard) should now be reso...

James•11/24/23, 11:16 PM

Thanks Matt. It might be good to get a retro status page up for this, just in case folks had hit errors in their CI and can correlate the time it happened to the status report.

Constantin•11/24/23, 11:18 PM

is there a cloudflare features roadmap? i'm curious in an auth/user management feature

MMatt Silverlock @D1 Database Issues with the D1 API (including the dashboard) should now be reso...

Rob M.•11/24/23, 11:22 PM

Still can't access the D1 Dashboard blobhaj

CConstantin is there a cloudflare features roadmap? i'm curious in an auth/user management f...

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

Non I’m aware of. They do typically announce stuff with blog posts so can follow those

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

it's the last missing part of the puzzle imho

. it feels bad to use a 3rd party provider (hosted in a different region/cloud infra) and wait for users to login. i'm implementing my own, however users has been allways a hard thing to reason about

I did, here's an example: ``` CREATE TABLE sensors ( id INTEGER PRIMARY KEY, name TEXT NOT N

Similar Threads

Similar Threads

Similar Threads