Hi. if there are 500 items when executing SELECT COUNT(*) FROM foo on D1, 500 read counts to be char

Original message was deleted

H

hOP•1/30/24, 9:20 AM

Ok, thanks

Hh Hi. if there are 500 items when executing SELECT COUNT(*) FROM foo on D1, 500 re...

M

Matt Silverlock•1/30/24, 9:23 AM

You should consider using indexes to reduce the rows you read (and make your queries faster!) - https://developers.cloudflare.com/d1/how-to/using-indexes/

review the pricing: https://developers.cloudflare.com/d1/platform/pricing/

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) …

Pricing · Cloudflare D1 docs

D1 is currently free to use during the public beta. Billing is expected to be enabled in February 2024 for developers using D1 on the Workers paid …

M

Musk•1/30/24, 11:08 AM

Hi, I would like to ask if one of the 3 million row data table queries in D1 will consume 3 million rows to avoid or 1 row to avoid? Will there be different consumption after increasing the index

V

vakno•1/30/24, 11:43 AM

    for (const { question, answer } of objectsData) {
        // Check if the row with the same question and answer already exists
        const existingRow = await env.faq.prepare(

    for (const { question, answer } of objectsData) {
        // Check if the row with the same question and answer already exists
        const existingRow = await env.faq.prepare(

    for (const { question, answer } of objectsData) {
        // Check if the row with the same question and answer already exists
        const existingRow = await env.faq.prepare(

    for (const { question, answer } of objectsData) {
        // Check if the row with the same question and answer already exists
        const existingRow = await env.faq.prepare(

SELECT id FROM FAQs WHERE question = ? AND answer = ?

).run(question, answer);

        if (!existingRow) {
            // If the row doesn't exist, insert it into the table
            await env.faq.prepare(

).run(question, answer);

        if (!existingRow) {
            // If the row doesn't exist, insert it into the table
            await env.faq.prepare(

).run(question, answer);

        if (!existingRow) {
            // If the row doesn't exist, insert it into the table
            await env.faq.prepare(

).run(question, answer);

        if (!existingRow) {
            // If the row doesn't exist, insert it into the table
            await env.faq.prepare(

INSERT INTO FAQs (question, answer) VALUES (?, ?)

).run(question, answer);
        }
    }

).run(question, answer);
        }
    }

).run(question, answer);
        }
    }

).run(question, answer);
        }
    }

Why am i getting D1_ERROR: Wrong number of parameter bindings for SQL query. There are two ? one for question and one for answer

K

kian•1/30/24, 11:44 AM

.bind(question, answer).run()

V

vakno•1/30/24, 11:49 AM

Thanks!

JJakob Norlin Is there a way to have the sqlite file generated when running wrangler d1 with t...

C

Cyb3r-Jak3•1/30/24, 12:52 PM

This was changed a while ago in how the data was stored. You should be using miniflare to interact with your bindings locally

CCyb3r-Jak3 This was changed a while ago in how the data was stored. You should be using min...

J

Jakob Norlin•1/30/24, 1:45 PM

Is there a good writeup on how to work with D1 in miniflare locally? The docs I can locate seem sparse on details

JJakob Norlin Is there a good writeup on how to work with D1 in miniflare locally? The docs I ...

C

Cyb3r-Jak3•1/30/24, 1:56 PM

The only docs I know are https://miniflare.dev/storage/d1#working-with-d1-databases which are short. Someone may have written more outside of Cloudflare

💾 D1 · Miniflare

Fun, full-featured, fully-local simulator for Cloudflare Workers

JJakob Norlin Is there a good writeup on how to work with D1 in miniflare locally? The docs I ...

M

Matt Silverlock•1/30/24, 7:34 PM

https://developers.cloudflare.com/d1/configuration/local-development/#miniflare

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

R

Rob M.•1/30/24, 7:48 PM

Hi

I have some logic with D1 in a worker. If a "Client Disconnected" error occurs, will the worker finish the job?

R

Rob M.•1/30/24, 7:49 PM

or it will halt the execution?

R

rvg•1/30/24, 7:56 PM

The docs mention that workers bound to a d1 database are always placed in a datacenter near the location of the db (https://developers.cloudflare.com/workers/configuration/smart-placement/#d1). Is this a limitation of the beta that will change once read replicas are a thing?

That same page indicates later that you can split up workers into front/backend workers that communicate using service bindings (https://developers.cloudflare.com/workers/configuration/smart-placement/#best-practices), suggesting one would be placed close to the user and the other close to the backend resource. This seems like it could help today if I could put the d1 bindings in the backend, but I'm a bit confused as to how this would work since the service binding docs seem to indicate that workers using service bindings are on the same machine? (https://developers.cloudflare.com/workers/runtime-apis/service-bindings/ mentions same thread, no internet)

MMatt Silverlock https://developers.cloudflare.com/d1/configuration/local-development/#miniflare

J

Jakob Norlin•1/30/24, 9:08 PM

So is there a way with miniflare to do what I asked about in my original question? I.e. write to an sqlite file with the name of my choosing, instead of a long random string.

JJakob Norlin So is there a way with miniflare to do what I asked about in my original questio...

C

Cyb3r-Jak3•1/30/24, 10:06 PM

No. The human readable part was removed with miniflare v3.20230918.0. See the notes in https://github.com/cloudflare/miniflare/releases/tag/v3.20230918.0

A

Amr Yasser•1/31/24, 12:29 AM

Hello I need help with next-on-pages, I have a remote D1 database, and added it locally too. I did some migration with drizzle and everything worked fine and the data persisted locally to a sqlite file (say X). But when I follow the steps in next-on-pages/next-dev to use bindings inside next.js app, it does not use the file X, instead, it creates new file Y which has no migrations. Any solutions to his behavior? I tried to see if there is a way to make the next.js local binding use file X but I found nothing.

AAmr Yasser Hello I need help with next-on-pages, I have a remote D1 database, and added it ...

A

Amr Yasser•1/31/24, 1:20 AM

I made a workaround using bettersqlite3 for anyone interested but it depends on the generated storage name.

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";

const sqlite = new Database(
  "../.wrangler/state/v3/d1/miniflare-D1DatabaseObject/8c4adb7a51078a272a8336d1d6f606acb37a5564b94ce38b4862ef2734d493a1.sqlite"
);

const db = drizzle(sqlite);

migrate(db, { migrationsFolder: "drizzle" });

sqlite.close();

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";

const sqlite = new Database(
  "../.wrangler/state/v3/d1/miniflare-D1DatabaseObject/8c4adb7a51078a272a8336d1d6f606acb37a5564b94ce38b4862ef2734d493a1.sqlite"
);

const db = drizzle(sqlite);

migrate(db, { migrationsFolder: "drizzle" });

sqlite.close();

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";

const sqlite = new Database(
  "../.wrangler/state/v3/d1/miniflare-D1DatabaseObject/8c4adb7a51078a272a8336d1d6f606acb37a5564b94ce38b4862ef2734d493a1.sqlite"
);

const db = drizzle(sqlite);

migrate(db, { migrationsFolder: "drizzle" });

sqlite.close();

import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";

const sqlite = new Database(
  "../.wrangler/state/v3/d1/miniflare-D1DatabaseObject/8c4adb7a51078a272a8336d1d6f606acb37a5564b94ce38b4862ef2734d493a1.sqlite"
);

const db = drizzle(sqlite);

migrate(db, { migrationsFolder: "drizzle" });

sqlite.close();

T

thedelanyo•1/31/24, 1:40 AM

Hi, am new here. But there's something I need an understanding.

I have a Sveltekit project hosted on Cloudflare Pages. And now I want to add D1 as the database on this project.

I created the database using the D1 dashboard.

I want to follow the D1 docs, but it seems mostly it centered around Wrangler and Workers sort of.

How do I get started with D1 for a Pages project? Please any help or link to any doc? Thank you.

M

Max (@rozenmd)•1/31/24, 7:35 AM

A fix for inflated rows_written counts is coming: https://github.com/cloudflare/workerd/pull/1594

GitHub

sqlite: don't count rows written to temporary (ephemeral) tables by...

SQLite may generate temporary tables while executing a SELECT statement. Rows written to these tables shouldn't count as they're not under control of the user.
This change is essentially
tu...

Tthedelanyo Hi, am new here. But there's something I need an understanding. I have a Svelt...

M

Matt Silverlock•1/31/24, 8:04 AM

Have you looked at https://developers.cloudflare.com/d1/examples/d1-and-sveltekit/ or https://developers.cloudflare.com/pages/framework-guides/deploy-a-svelte-site/

GGagan Suie why am i getting this error?? `"Error: D1_TYPE_ERROR: Type 'boolean' not support...

P

Patrick•1/31/24, 4:29 PM

Did you end up converting your boolean types to integers?

Running into Error: D1_TYPE_ERROR: Type 'boolean' not supported for value 'false'

It looks like the docs under queries mention that it's converted, but this is on an insert where my migration creates the table with boolean types. Was curious if I should just be typing these integer and storing 1/0 instead of messing with true/false.

edit/ seems like yes, i should be using integers. it might be helpful if the migration execution would fail when you incorrectly use boolean

MMatt Silverlock Have you looked at https://developers.cloudflare.com/d1/examples/d1-and-svelteki...

T

thedelanyo•1/31/24, 4:30 PM

Okay. Thank you Sir. Will look it up

KK How many points of presence will read replications have?

K

K•1/31/24, 6:51 PM

Just bumping. CF has 300 pops. D1 has some 6 locations (eu , us etc) for compliance. So what is planned for D1 in terms of PoPs?

KK Just bumping. CF has 300 pops. D1 has some 6 locations (eu , us etc) for complia...

C

Chaika•1/31/24, 6:55 PM

D1 runs on Durable Object (DOs), which are only in specific colos/pops and regions: https://where.durableobjects.live/

Where Durable Objects Live

Tracking where Durable Objects are created, wherever you are in the world.

C

Chaika•1/31/24, 6:55 PM

employees have mentioned a few times that south america support is planned d1-database

CChaika D1 runs on Durable Object (DOs), which are only in specific colos/pops and regio...

K

K•1/31/24, 7:05 PM

Haven't seen this site before. Thank s for pointing it out.

PPatrick Did you end up converting your boolean types to integers? Running into Error: D...

G

Gagan Suie•1/31/24, 7:54 PM

Yes. Convert to int and store in DB as 1/0

DDavid Renshaw I have a fresh database and an initial migration: ``` -- I don't expect there to...

M

Max (@rozenmd)•1/31/24, 8:11 PM

it's a bug in the API, yeah

M

Max (@rozenmd)•2/1/24, 1:40 PM

we should really stop calling them experimental

V

Vignesh•2/1/24, 6:14 PM

Can I store 40gb db on D1?

VVignesh Can I store 40gb db on D1?

Z

zegevlier•2/1/24, 6:41 PM

Not right now, no: https://developers.cloudflare.com/d1/platform/limits/

Z

zegevlier•2/1/24, 6:42 PM

That limit is marked as beta and subject to increase, but if it'll increase >40gb I do not know

Z

zegevlier•2/1/24, 6:42 PM

If you really want to use D1 you might be able to store it as several smaller databases though

B

bun•2/2/24, 12:03 AM

i inserted like 2000 records. tried quering a random record by id. it takes ~130-150 ms. is this expected even after multiple requests?

B

bun•2/2/24, 12:05 AM

import { Hono } from "hono";

const app = new Hono<{
  Bindings: {
    DB: D1Database;
  };
}>();

app.get("/", async (c) => {
  const result = c.env.DB.prepare(`
    select * from users where id = 420;
  `);

  const t = performance.now();

  await result.first();

  return c.text(`Query took ${performance.now() - t}ms`);
});

export default app;

import { Hono } from "hono";

const app = new Hono<{
  Bindings: {
    DB: D1Database;
  };
}>();

app.get("/", async (c) => {
  const result = c.env.DB.prepare(`
    select * from users where id = 420;
  `);

  const t = performance.now();

  await result.first();

  return c.text(`Query took ${performance.now() - t}ms`);
});

export default app;

import { Hono } from "hono";

const app = new Hono<{
  Bindings: {
    DB: D1Database;
  };
}>();

app.get("/", async (c) => {
  const result = c.env.DB.prepare(`
    select * from users where id = 420;
  `);

  const t = performance.now();

  await result.first();

  return c.text(`Query took ${performance.now() - t}ms`);
});

export default app;

import { Hono } from "hono";

const app = new Hono<{
  Bindings: {
    DB: D1Database;
  };
}>();

app.get("/", async (c) => {
  const result = c.env.DB.prepare(`
    select * from users where id = 420;
  `);

  const t = performance.now();

  await result.first();

  return c.text(`Query took ${performance.now() - t}ms`);
});

export default app;

B

bun•2/2/24, 12:39 AM

i get 0.150-0.300 on duration

B

bun•2/2/24, 12:39 AM

is it 150-300 ms ?

B

bun•2/2/24, 12:42 AM

ah so it takes actual 0.1-0.3 miliseconds

B

bun•2/2/24, 12:42 AM

then why is networking such an issue? is it because the worker it not located close to the database?

C

Chaika•2/2/24, 12:43 AM

yea duration is in milliseconds. You can see where your worker is in via `npx wrangler d1 info <db-name>, and look for running_in_region

B

bun•2/2/24, 12:44 AM

you mean worker or db ?

B

bun•2/2/24, 12:45 AM

ok latency is reduced to 10ms after i enabled smart placement

C

Chaika•2/2/24, 12:45 AM

That would show the location of the db. Worker is likely way closer to you. You could look at

https://<worker-host-name>/cdn-cgi/trace

https://<worker-host-name>/cdn-cgi/trace

i.e

https://cloudflare.com/cdn-cgi/trace

https://cloudflare.com/cdn-cgi/trace

and look at colo= for the airport code of where the worker is running

C

Chaika•2/2/24, 12:45 AM

if you have smart placement on then you'd look at the cf-placement response header

C

Chaika•2/2/24, 12:46 AM

also make sure your db version shown in d1 info is beta, not alpha

Hi. if there are 500 items when executing SELECT COUNT(*) FROM foo on D1, 500 read counts to be char

Similar Threads

Similar Threads

Similar Threads