AMD, Ahmedabad, India

AAdi Question, I see with the latest workerd release on github the bugfix for row wri...

M

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

workerd releases tend to go live within a week or so

ⱤⱤØ₥₳₦AMD, Ahmedabad, India

M

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

so your nearest db is unfortunately 200ms away - the queries themselves are fast (if you run

npx wrangler d1 insights <dbname>

npx wrangler d1 insights <dbname>

npx wrangler d1 insights <dbname>

npx wrangler d1 insights <dbname> you can see query timing data)

MMax (@rozenmd)so your nearest db is unfortunately 200ms away - the queries themselves are fast...

Ɽ

ⱤØ₥₳₦OP•2/9/24, 9:18 AM

It is actually city i live in

MMax (@rozenmd)so your nearest db is unfortunately 200ms away - the queries themselves are fast...

Ɽ

ⱤØ₥₳₦OP•2/9/24, 9:25 AM

Lol, insanely fast, but not with worker

ⱤⱤØ₥₳₦Lol, insanely fast, but not with worker

M

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

like I said, your nearest DB is 200ms away

MMax (@rozenmd)like I said, your nearest DB is 200ms away

Ɽ

ⱤØ₥₳₦OP•2/9/24, 9:28 AM

How? DB is less than 10 km far

ⱤⱤØ₥₳₦How? DB is less than 10 km far

M

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

a worker in AMD created a durable object in Singapore

M

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

your worker is less than 10km away, but the DB is in SG

MMax (@rozenmd)your worker is less than 10km away, but the DB is in SG

Ɽ

ⱤØ₥₳₦OP•2/9/24, 9:30 AM

ohh damn, I got it now

M

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

if you add caching to your Worker so it doesn't have to wait for the request from singapore to come back each time, it'll be insanely fast, see: https://developers.cloudflare.com/workers/examples/cache-api/

MMax (@rozenmd)if you add caching to your Worker so it doesn't have to wait for the request fro...

Ɽ

ⱤØ₥₳₦OP•2/9/24, 9:43 AM

If in the worker there are multiple blocks of code using DB again and again then what will happen?

Each block of code using DB will add Amd to Sin round trip latency to the response time?

ⱤⱤØ₥₳₦If in the worker there are multiple blocks of code using DB again and again then...

M

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

If you enable Smart Placement in the dashboard for your Worker, the Worker will move itself to Sin, so only the first hop is slow, and the round-trips will be fast

AAdi Question, I see with the latest workerd release on github the bugfix for row wri...

M

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

forgot to mention - once this fix is live, it'll only be applied to new queries, so

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights for historical data (like 31 days) will be inaccurate for a bit

A

Arturo•2/9/24, 12:22 PM

I'm trying to understand this metric, on the selected chart I have "Rows Metrics" selected, as you can see there was some spikes very weird on selected dates, I don't think what I'm doing requires that many rows read, like the max I probably have is like 50K rows, is this an indication of a compromised db? How can I dig deeper?

AArturo I'm trying to understand this metric, on the selected chart I have "Rows Metrics...

M

Max (@rozenmd)•2/9/24, 12:23 PM

run

npx wrangler@latest d1 insights <dbname> --sortBy reads --timePeriod 7d

npx wrangler@latest d1 insights <dbname> --sortBy reads --timePeriod 7d

npx wrangler@latest d1 insights <dbname> --sortBy reads --timePeriod 7d

npx wrangler@latest d1 insights <dbname> --sortBy reads --timePeriod 7d, will tell you the queries doing this

M

Max (@rozenmd)•2/9/24, 12:25 PM

looking at your tables list counts as a read on every row in every table, so it could be as simple as that (we plan to address this)

A

Arturo•2/9/24, 12:27 PM

the query looks good, the top one is something I'm doing but it looks just too odd

A

Arturo•2/9/24, 12:28 PM

is that counting towards my quota or it's just the queries / write?

A

Arturo•2/9/24, 12:29 PM

I got this metrics from the wrangler:

 "avgRowsRead": 92284,
    "totalRowsRead": 7659605,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 26.3779313253012,
    "totalDurationMs": 2189.3682999999996,
    "numberOfTimesRun": 83

 "avgRowsRead": 92284,
    "totalRowsRead": 7659605,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 26.3779313253012,
    "totalDurationMs": 2189.3682999999996,
    "numberOfTimesRun": 83

 "avgRowsRead": 92284,
    "totalRowsRead": 7659605,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 26.3779313253012,
    "totalDurationMs": 2189.3682999999996,
    "numberOfTimesRun": 83

 "avgRowsRead": 92284,
    "totalRowsRead": 7659605,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 26.3779313253012,
    "totalDurationMs": 2189.3682999999996,
    "numberOfTimesRun": 83

For the top read spike

AArturo is that counting towards my quota or it's just the queries / write?

M

Max (@rozenmd)•2/9/24, 12:53 PM

it counts towards your quota, but bare in mind you get 25 billion per month with the Workers $5 plan, so you could repeat that week 1470 times before you start getting billed for additional usage

S

Soham•2/9/24, 3:51 PM

Is it possible to access and write data into d1 from python?

S

Soham•2/9/24, 3:51 PM

Is there a direct method or do I have to go through a worker?

M

Max (@rozenmd)•2/9/24, 3:52 PM

going through a worker is the most performant way: https://github.com/elithrar/http-api-d1-example

M

Max (@rozenmd)•2/9/24, 3:53 PM

otherwise there's the API, but it's not running from Workers, and will be slow: https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

S

Soham•2/9/24, 4:07 PM

Ah nice, I’m not too worried about speed as it’s not a big project

Original message was deleted

M

Max (@rozenmd)•2/10/24, 9:00 AM

I need to improve that error, but D1's beta databases don't allow you to "create" or download your backups yet, you'd need to use a 3rd party tool like https://github.com/nora-soderlund/cloudflare-d1-backups, see also: https://developers.cloudflare.com/d1/reference/time-travel/

Billing data is available via

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights, as well as the Cloudflare dashboard (see the Metrics tab for your D1 database). You can use that data to figure out what D1 will cost you.

V

Vignesh•2/10/24, 11:40 AM

Will exposing database-id expose my database? How to secure D1?

Original message was deleted

V

Vignesh•2/10/24, 11:49 AM

Do I need to implement any security rules? Like how does D1 knows that I am the real owner?

Original message was deleted

V

Vignesh•2/10/24, 11:58 AM

I mean in custom application. Like I just did binding to DB in SvelteKi, so is it enough?

T

Titan•2/10/24, 2:39 PM

Sorry if this gets asked a lot, I just can’t get my head around it. Are you able to connect directly the a D1 db outside of wrangler or code or the dashboard. Eg in a normal desktop db application/client?

Y

Yanni•2/10/24, 3:28 PM

Is there a way to insert many data during a worker run. I'm thinking about 2k-3k of rows. I only know how to insert one row per execution which is really inefficient. Is there a better way?

Y

Yanni•2/10/24, 3:33 PM

There is currently a limit of just 1000 queries which is like nothing

Y

Yanni•2/10/24, 3:33 PM

the job should detect increments which means that It hast to first query by id and the insert new datasets

Y

Yanni•2/10/24, 3:34 PM

and this ~2'000 - 3'000 times. I would also be fine by deleting everything first and then inserting it which would work if there is a option to insert multiple rows at the same time

something like this

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

INSERT INTO db VALUES (1), (2)

Y

Yanni•2/10/24, 3:37 PM

this job should only run all 24h or maybe only every week the data doesn't really change over time but I'm to lazy to do it manually via cli or a local python script.

YYanni Is there a way to insert many data during a worker run. I'm thinking about 2k-3k...

M

Max (@rozenmd)•2/10/24, 7:23 PM

db.batch

T

Tonic•2/10/24, 9:09 PM

A

aunyks•2/11/24, 3:08 AM

Hey y’all! Has the ability to export a D1 DB to a sqlite3 file or

.sql

.sql

.sql

.sql file been added yet? The ability to back up / download the DB locally, ideally from the Dashboard, is what’s keeping me from using this over Turso / sqld

Aaunyks Hey y’all! Has the ability to export a D1 DB to a sqlite3 file or `.sql` file be...

C

Cyb3r-Jak3•2/11/24, 3:14 AM

It has not been added yet. Still need to use community tools

G

GuillaumeLakano•2/11/24, 9:45 AM

Hi! I got this error while I'm trying to execute an import of a SQL file, is it an important problem ? My content seems to be correctly imported

npx wrangler --persist-to ../../../.wrangler d1 execute content --local --file sql/content.articles.sql
wrangler 3.28.1
-------------------
Mapping SQL input into an array of statements
Executing on local database content (00000000-0000-0000-0007-000000000000) from ../../../.wrangler/v3/d1:
workerd/jsg/setup.c++:493: error: CODE_MOVED for unknown code block?
workerd/jsg/setup.c++:493: error: CODE_MOVED for unknown code block?

D

Denver James Duran•2/11/24, 5:10 PM

Hi! Can I ask for assistance.. so I am using D1 as my database I am required to store dates, so what I did was to first convert the date into numbers with this function

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

const MS_PER_DAY = 24 * 60 * 60 * 1000;
const EPOCH_1900 = new Date('1899-12-30T00:00:00.000Z').getTime();
export function dateToExcelNumberWithTime(date: Date): number {
    const gmtOffset = date.getTimezoneOffset();
    const adjustment = (0 - gmtOffset) * 60 * 1000;
    const finalDate = (date.getTime() - EPOCH_1900 + adjustment) / MS_PER_DAY;

    return finalDate;
}

the thing is, no matter what I try, I really find it hard to synchronize time.. it just wont behave as what I am expecting.. my question is, can D1 support the actual DATE/ Timestamp to be saved on rows instead of me converting it to number then converting it back to date I think it complicates things (do you have any pointers on this or any tips?)

Original message was deleted

D

Denver James Duran•2/11/24, 5:50 PM

what do you mean?

Original message was deleted

D

Denver James Duran•2/11/24, 5:52 PM

So that I can have something like this

45332.058900462966

45332.058900462966

45332.058900462966

45332.058900462966 which I thought was easier to query. the numeric part is the number of days while the decimal is the time.. if I am querying a BETWEEN statement, the process will be straightforward..

Original message was deleted

D

Denver James Duran•2/11/24, 8:02 PM

OMG I am so dumb I just realized, what you meant, the getTime() already gives me a reference, I didn't need to create my own epoch etc, it's already a timestamp thanks! took me so long

K

Kaique Nunes•2/11/24, 9:13 PM

Will Cloudflare still charging us before the write bug has been fixed ? I received an email informing the charge will start on Feb 12th

KKaique Nunes Will Cloudflare still charging us before the write bug has been fixed ? I receiv...

M

Max (@rozenmd)•2/11/24, 9:38 PM

No, you will not be charged

MMax (@rozenmd)No, you will not be charged

Ɽ

ⱤØ₥₳₦OP•2/12/24, 1:18 AM

Btw how to cache worker's response that is using D1? In previous messages you said I've got to use fetch api?

ⱤⱤØ₥₳₦Btw how to cache worker's response that is using D1? In previous messages you sa...