Hi guys, I'm querying data with the query above, but it seems like scanning too much data. I've set

P

prabha•2/21/24, 10:30 AM

Hi everyone.. How to create REST API for CRUD operations in D1

S

stefanoros•2/21/24, 10:45 AM

Hello everyone, i'm trying to set up a simple many-to-many join

SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;

SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;

SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;

SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;

i get this error

Error: D1_ERROR: A prepared SQL statement must contain only one statement.

Error: D1_ERROR: A prepared SQL statement must contain only one statement.

Error: D1_ERROR: A prepared SQL statement must contain only one statement.

Error: D1_ERROR: A prepared SQL statement must contain only one statement.

. Can't seem to figure why.

Sstefanoros Hello everyone, i'm trying to set up a simple many-to-many join ```SELECT * ...

M

Max (@rozenmd)•2/21/24, 10:56 AM

can you post the exact code, including JS/TS that you're using?

S

stefanoros•2/21/24, 10:57 AM

sure

    let result = await platform!.env.DB.prepare(
        `SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;
    `
    ).all();

    let result = await platform!.env.DB.prepare(
        `SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;
    `
    ).all();

    let result = await platform!.env.DB.prepare(
        `SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;
    `
    ).all();

    let result = await platform!.env.DB.prepare(
        `SELECT *
    FROM categories
    JOIN images_categories ON categories.id = images_categories.category_id
    JOIN images ON images_categories.image_id = images.id;
    `
    ).all();

if I remove one join statement it works

M

Max (@rozenmd)•2/21/24, 10:59 AM

that's weird - i've only seen that error with the parser failing to understand comments

M

Max (@rozenmd)•2/21/24, 11:00 AM

does it work if you combine the SQL onto a single line? like

`SELECT * FROM categories JOIN images_categories ON categories.id = images_categories.category_id JOIN images ON images_categories.image_id = images.id;`

`SELECT * FROM categories JOIN images_categories ON categories.id = images_categories.category_id JOIN images ON images_categories.image_id = images.id;`

`SELECT * FROM categories JOIN images_categories ON categories.id = images_categories.category_id JOIN images ON images_categories.image_id = images.id;`

`SELECT * FROM categories JOIN images_categories ON categories.id = images_categories.category_id JOIN images ON images_categories.image_id = images.id;`

MMax (@rozenmd)does it work if you combine the SQL onto a single line? like ``` `SELECT * FROM ...

S

stefanoros•2/21/24, 11:25 AM

Yes! It works now, thanks

TTony Leung Hi guys, I'm querying data with the query above, but it seems like scanning too ...

T

Tony LeungOP•2/21/24, 11:35 AM

hi guys, could anyone help me

TTony Leung Hi guys, I'm querying data with the query above, but it seems like scanning too ...

M

Max (@rozenmd)•2/21/24, 11:45 AM

is there a single index covering both

posted

posted

posted

posted and

retry

retry

, or two individual indexes?

T

Tony LeungOP•2/21/24, 11:50 AM

yes, I'm having a composite index like this:

CREATE INDEX post_posted_retry_idx ON post (posted, retry);

CREATE INDEX post_posted_retry_idx ON post (posted, retry);

CREATE INDEX post_posted_retry_idx ON post (posted, retry);

CREATE INDEX post_posted_retry_idx ON post (posted, retry);

T

Tony LeungOP•2/21/24, 11:51 AM

it should cover both

posted

posted

posted

posted and

retry

retry

if I place the condition in the correct order right?

T

Tony LeungOP•2/21/24, 11:54 AM

base on this document: "https://www.sqlite.org/optoverview.html", both

posted

posted

posted

posted and

retry

retry

should be used

D

Denver James Duran•2/21/24, 12:05 PM

Hi guys! Good day to you all. Does D1 really consider 0 as NULL?

I have a NOT NULL constraint on my table.. when saving 0, it returns a

Error: D1_ERROR: FOREIGN KEY constraint failed
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)

Error: D1_ERROR: FOREIGN KEY constraint failed
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)

Error: D1_ERROR: FOREIGN KEY constraint failed
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)

Error: D1_ERROR: FOREIGN KEY constraint failed
      at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)

I have already logged the input, and confirmed, it is indeed 0 and typeof number.. and is absolutely NOT null... but it still results to an error..

If ever, what would be the workaround for this? I definitely would wanna save 0 but do not want a null

TThenerox hello can i connect to D1 using dbeaver or something like that?

P

prabha•2/21/24, 12:07 PM

there is a tool called DATAFLARE https://dataflare.app/ that supports D1

Dataflare

Dataflare - A simple, easy-to-use database manager

Easily connect to your Cloudflare D1, libSQL, MariaDB, MySQL, PostgreSQL, Rqlite, SQLCipher, SQLite, SQL Server databases in Dataflare, manage Table, view Data, write SQL and run Query.

Kkian SQLite supports renaming a table, adding a new column and renaming an existing c...

S

sten•2/21/24, 12:14 PM

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it.

So if I want to alter the datatype of a column, I would first need to drop that column and then add it again with a new datatype?

DDenver James Duran Hi guys! Good day to you all. Does D1 really consider 0 as NULL? I have a NOT ...

M

Max (@rozenmd)•2/21/24, 12:41 PM

you're trying to insert 0 as a foreign key?

Original message was deleted

S

sten•2/21/24, 12:41 PM

There's gotta be a good way to do this though?
I guess best way is to

Rename original table with different name.
Create new table with same name as previous table have with desired column type.
Copy all previous data from old table (renamed table) to newly created table.
Drop renamed table.

MMax (@rozenmd)you're trying to insert 0 as a foreign key?

D

Denver James Duran•2/21/24, 12:43 PM

No, not a foreign key, just a regular value

CREATE TABLE IF NOT EXISTS carts (
    id INTEGER PRIMARY KEY,

    cartId INTEGER NOT NULL,
    branchId INTEGER NOT NULL,
    name TEXT NOT NULL,
    productId INTEGER NOT NULL,
    price REAL NOT NULL,  -- << HERE
    size TEXT NOT NULL,
    quantity TEXT NOT NULL,
    customerNotes TEXT NOT NULL DEFAULT '',
    discount REAL,
    discountName TEXT,
    discountDescription TEXT DEFAULT '',
    discountEndDate REAL,

    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    status INTEGER NOT NULL,
    FOREIGN KEY (branchId) REFERENCES branches(id),
    FOREIGN KEY (cartId) REFERENCES cartIds(id),
    FOREIGN KEY (productId) REFERENCES products(id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS carts (
    id INTEGER PRIMARY KEY,

    cartId INTEGER NOT NULL,
    branchId INTEGER NOT NULL,
    name TEXT NOT NULL,
    productId INTEGER NOT NULL,
    price REAL NOT NULL,  -- << HERE
    size TEXT NOT NULL,
    quantity TEXT NOT NULL,
    customerNotes TEXT NOT NULL DEFAULT '',
    discount REAL,
    discountName TEXT,
    discountDescription TEXT DEFAULT '',
    discountEndDate REAL,

    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    status INTEGER NOT NULL,
    FOREIGN KEY (branchId) REFERENCES branches(id),
    FOREIGN KEY (cartId) REFERENCES cartIds(id),
    FOREIGN KEY (productId) REFERENCES products(id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS carts (
    id INTEGER PRIMARY KEY,

    cartId INTEGER NOT NULL,
    branchId INTEGER NOT NULL,
    name TEXT NOT NULL,
    productId INTEGER NOT NULL,
    price REAL NOT NULL,  -- << HERE
    size TEXT NOT NULL,
    quantity TEXT NOT NULL,
    customerNotes TEXT NOT NULL DEFAULT '',
    discount REAL,
    discountName TEXT,
    discountDescription TEXT DEFAULT '',
    discountEndDate REAL,

    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    status INTEGER NOT NULL,
    FOREIGN KEY (branchId) REFERENCES branches(id),
    FOREIGN KEY (cartId) REFERENCES cartIds(id),
    FOREIGN KEY (productId) REFERENCES products(id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

CREATE TABLE IF NOT EXISTS carts (
    id INTEGER PRIMARY KEY,

    cartId INTEGER NOT NULL,
    branchId INTEGER NOT NULL,
    name TEXT NOT NULL,
    productId INTEGER NOT NULL,
    price REAL NOT NULL,  -- << HERE
    size TEXT NOT NULL,
    quantity TEXT NOT NULL,
    customerNotes TEXT NOT NULL DEFAULT '',
    discount REAL,
    discountName TEXT,
    discountDescription TEXT DEFAULT '',
    discountEndDate REAL,

    createdBy INTEGER NOT NULL,
    createdAt REAL NOT NULL,
    updatedBy INTEGER,
    updatedAt REAL,
    status INTEGER NOT NULL,
    FOREIGN KEY (branchId) REFERENCES branches(id),
    FOREIGN KEY (cartId) REFERENCES cartIds(id),
    FOREIGN KEY (productId) REFERENCES products(id),
    FOREIGN KEY (createdBy) REFERENCES users (id),
    FOREIGN KEY (updatedBy) REFERENCES users (id)
);

DDenver James Duran No, not a foreign key, just a regular value ```sql CREATE TABLE IF NOT EXISTS ...

M

Max (@rozenmd)•2/21/24, 12:47 PM

That's not the error you're getting though:

FOREIGN KEY constraint failed

FOREIGN KEY constraint failed

FOREIGN KEY constraint failed

FOREIGN KEY constraint failed

D

Denver James Duran•2/21/24, 12:49 PM

that's the weird thing, the contstraint error only appears when price is 0... I wonder why..

D

Denver James Duran•2/21/24, 12:49 PM

thanks you made me realize that I may need to look at other aspects of my code

B

Befus•2/21/24, 1:15 PM

I can't seem to get drizzle to work with D1, funnily enough I believe I'm already failing at the configuration does anybody have some docs?
I have looked at the ones from Drizzle itself but I am using cloudflare pages instead of workers/wrangler

BBefus I can't seem to get drizzle to work with D1, funnily enough I believe I'm alread...

D

Denver James Duran•2/21/24, 2:02 PM

I got it to work, it's what I am using.. although I think they have limited support. I tried using it, I get trouble with JOINs.. and other queries, I end up just using the regular

D

Denver James Duran•2/21/24, 2:05 PM

Here's what I did to make it work (I am using Sveltekit deployed via cloudflare pages)

//hooks.server.ts

import { drizzle } from 'drizzle-orm/d1';

const injectD1 = async (event: RequestEvent<Partial<Record<string, string>>, string | null>) => {
    try {
        if (event.platform?.env?.DATABASE) {
            event.locals.DRIZZLE = drizzle(event.platform.env.DATABASE);
            event.locals.D1 = event.platform.env.DATABASE;
        }
        ...
    } catch (error) {
        console.log('🚀 ~ file: hooks.server.ts:27 ~ consthandle:Handle= ~ error:', error);
    }
};

//hooks.server.ts

import { drizzle } from 'drizzle-orm/d1';

const injectD1 = async (event: RequestEvent<Partial<Record<string, string>>, string | null>) => {
    try {
        if (event.platform?.env?.DATABASE) {
            event.locals.DRIZZLE = drizzle(event.platform.env.DATABASE);
            event.locals.D1 = event.platform.env.DATABASE;
        }
        ...
    } catch (error) {
        console.log('🚀 ~ file: hooks.server.ts:27 ~ consthandle:Handle= ~ error:', error);
    }
};

//hooks.server.ts

import { drizzle } from 'drizzle-orm/d1';

const injectD1 = async (event: RequestEvent<Partial<Record<string, string>>, string | null>) => {
    try {
        if (event.platform?.env?.DATABASE) {
            event.locals.DRIZZLE = drizzle(event.platform.env.DATABASE);
            event.locals.D1 = event.platform.env.DATABASE;
        }
        ...
    } catch (error) {
        console.log('🚀 ~ file: hooks.server.ts:27 ~ consthandle:Handle= ~ error:', error);
    }
};

//hooks.server.ts

import { drizzle } from 'drizzle-orm/d1';

const injectD1 = async (event: RequestEvent<Partial<Record<string, string>>, string | null>) => {
    try {
        if (event.platform?.env?.DATABASE) {
            event.locals.DRIZZLE = drizzle(event.platform.env.DATABASE);
            event.locals.D1 = event.platform.env.DATABASE;
        }
        ...
    } catch (error) {
        console.log('🚀 ~ file: hooks.server.ts:27 ~ consthandle:Handle= ~ error:', error);
    }
};

MMax (@rozenmd)That's not the error you're getting though: `FOREIGN KEY constraint failed`

D

Denver James Duran•2/21/24, 2:45 PM

so apparently the product that has 0 price just so happen to have 0 id which does NOT exist on my database... reason why I am getting an error. thanks for this I was able to debug it

I

Ian•2/21/24, 3:22 PM

Does db backup count into the storage quota and charged by it or will it be a different pricing.

IIan Does db backup count into the storage quota and charged by it or will it be a di...

I

itsmatteomanf•2/21/24, 3:24 PM

Backup, as in this (https://developers.cloudflare.com/d1/reference/time-travel/) is included.

Cloudflare Docs

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

C

Chinoman10•2/21/24, 3:25 PM

I'm not sure what's going on here... for one of our tables (the PRD one) the list of tables never loads (and after a while it shows the default "Your database was successfully created!").
For the Dev DB (half the size) it loads just fine...

CChinoman10 I'm not sure what's going on here... for one of our tables (the PRD one) the lis...

I

itsmatteomanf•2/21/24, 3:25 PM

Does the DB load fine in the list of dbs?

TThenerox hello can i connect to D1 using dbeaver or something like that?

C

Chinoman10•2/21/24, 3:27 PM

Supposedly there's a VS Code extension... but I tried and it didn't work.

Iitsmatteomanf Does the DB load fine in the list of dbs?

C

Chinoman10•2/21/24, 3:27 PM

Yes.

C

Chinoman10•2/21/24, 5:13 PM

So... I deployed this https://github.com/Momentum-Free/d1-manager/
And I get the exact same experience... I can load the Dev DB and the PRD one is just 'blank'.

C

Chinoman10•2/21/24, 5:19 PM

Basically it's always returning an internal server error (500)

C

Chinoman10•2/21/24, 5:21 PM

I know Pages & Workers are having issues currently, but this has been going on for at least 3 days now...

CChinoman10 I know Pages & Workers are having issues currently, but this has been going on f...

M

Max (@rozenmd)•2/21/24, 6:04 PM

whats your databaseId?

PPinheiro Is there any limit on server locations when using D1? I'm from Brazil and there'...

K

Kosicki•2/21/24, 10:27 PM

Any news on that? Looking forward to have better Brazil latency

MMax (@rozenmd)whats your databaseId?

C

Chinoman10•2/21/24, 10:50 PM

C

Chinoman10•2/21/24, 11:11 PM

https://media.discordapp.net/attachments/772180657169760276/1209999820924653598/image.png
Found the issue... I downloaded the "entire DB" (it's just 30MB) using another tool, and then I opened it locally... looks like one of the tables is corrupted somehow.

C

Chinoman10•2/21/24, 11:11 PM

happy to send the DB over if you need Max (although I guess you can get it from the ID)

C

Chinoman10•2/21/24, 11:13 PM

I'd also like to understand when/how the backups work... when does it decide that a backup should be done (previously it was doing them several times per day, now sometimes even entire weeks go by without one).

C

Cyb3r-Jak3•2/21/24, 11:19 PM

Is your database an alpha one? The output of

wrangler d1 info <dbname>

wrangler d1 info <dbname>

will tell you

Original message was deleted

K

Kosicki•2/21/24, 11:27 PM

Yes, I saw that, but I meant if there's any news on having this presence in south america

CChinoman10 I'm not sure what's going on here... for one of our tables (the PRD one) the lis...

I

impairedFelon•2/22/24, 2:15 AM

Why does this gentleman have different options to me? For example, he has a backup section and I don't, and I have time travel and metrics and he doesn't.

IimpairedFelon Why does this gentleman have different options to me? For example, he has a back...

C

Cyb3r-Jak3•2/22/24, 2:16 AM

I believe he is using the old database backend

C

Cyb3r-Jak3•2/22/24, 2:17 AM

Which is slower and not really supported anymore

CCyb3r-Jak3 I believe he is using the old database backend

M

Max (@rozenmd)•2/22/24, 6:04 AM

this

RRicardas Hey is anyone else experiencing those errors: D1_ERROR: Cannot resolve D1 due to...

M

Max (@rozenmd)•2/22/24, 9:07 AM

What's the output of

wrangler d1 info <dbname>

wrangler d1 info <dbname>

for that DB?

S

Sej•2/22/24, 9:38 AM

Hi, I need help with my DB. I tried to use the time travel feature to restore some data and now I can't access the DB at all. The status on the dashboard just says:

Failed to reach database. Please try again later.

Failed to reach database. Please try again later.

and when trying to access the url, it says the DB has just been successfully created. Any tips?

Hi guys, I'm querying data with the query above, but it seems like scanning too much data. I've set

Similar Threads