Question: can I get raw results when using D1 batch()? It seems there is no way of avoiding the cost

Question: can I get raw results when using D1 batch()? It seems there is no way of avoiding the cost of turning everything into JSON, if I want the perf benefits of batching queries.

Mmatthew Question: can I get raw results when using D1 batch()? It seems there is no way ...

Hard@Work•9/6/24, 1:08 PM

Not sure I understand what you mean by "raw results"?

HHard@Work Not sure I understand what you mean by "raw results"?

matthewOP•9/6/24, 1:10 PM

Results in array format, e.g. https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#await-stmtraw

Cloudflare Docs

Query D1 | Cloudflare D1 docs

D1 is compatible with most SQLite’s SQL convention since it leverages SQLite’s query engine. D1 client API allows you to interact with a D1 database from within a Worker.

Mmatthew Results in array format, e.g. https://developers.cloudflare.com/d1/build-with-d1...

Hard@Work•9/6/24, 1:41 PM

I mean, I don't think there is actually that much of a difference, but no, afaik there isn't a way to do batch raw queries atm

Original message was deleted

Hard@Work•9/6/24, 1:49 PM

Sorry, I meant specifically for performance, but good point

Rubi•9/7/24, 4:49 PM

hi, I can't export my database

and there is no other way than using wrangler export

Rubi•9/7/24, 4:57 PM

oh

turns out I was having a column name called limitlimit and it's now working after I rename the table

https://github.com/cloudflare/workers-sdk/issues/6599

ac•9/7/24, 6:03 PM

On a related note ^, do you all ever intend to allow exporting D1 as a simple sqlite file, rather than the complicated wrangler export (which seems pretty prone to error)? This would make local development so much easier.

Original message was deleted

Cyb3r-Jak3•9/8/24, 3:10 AM

I mean step one would be building a worker to handle the queries from Grafana. Then you could do it with a Grafana REST plugin

Original message was deleted

Cyb3r-Jak3•9/8/24, 3:11 AM

There is. The only thing is it is rate limited

Cyb3r-Jak3•9/8/24, 3:13 AM

Yeah you can use a REST API connetion in Grafana to query it

JJacob drizzle.config.ts ```ts import { defineConfig } from "drizzle-kit"; import fs fr...

Iberius•9/8/24, 1:27 PM

btw i got D1 and drizzle working if you ened help lmk

Shravan•9/8/24, 5:28 PM

Blobs seem to be returning a number[] instead of arraybuffer in d1. Does anyone know why? there's a few old threads about this

Shravan•9/8/24, 6:36 PM

oh the tdocs seem to say

2024-09-08.0936.Brave_Browser.Query_D1_Cloudflare_D1_docs.png

Shravan•9/8/24, 6:39 PM

ah so, the return types are always js objects? so blob wil be an array of number?

Shravan•9/8/24, 6:51 PM

thanks, i was going i little crazy trying to figure out what was going in

Kyrre•9/9/24, 8:34 AM

Hey! Why does one D1 database consistently have ~ +80 ms query time compared to another, when querying an identical, empty table? Both run in EEUR, and both are the "production" version. None have any load.

Spuckwaffel•9/9/24, 8:59 AM

Hey there, i was wondering why when you create a Table on the CF dashboard, you cannot have multiple columns with a primary key. Is this intentional?
If you create the DB using the Console you can specify something like CREATE TABLE ... (APIKey TEXT, username TEXT, ... , PRIMARY KEY (APIKey, username) );CREATE TABLE ... (APIKey TEXT, username TEXT, ... , PRIMARY KEY (APIKey, username) ); which executes successfully.

Original message was deleted

Kyrre•9/9/24, 9:28 AM

Thanks! Any way to determine this? Or set more fine-grained location?

Original message was deleted

Kyrre•9/9/24, 9:40 AM

So strange, when the main selling point is being close to the user. Guess we have to start migrating away from D1.

IEatBeans•9/10/24, 1:35 AM

Is it possible to create an api token with access to a specific d1 db, instead of just all of them on my account?

IIEatBeans Is it possible to create an api token with access to a specific d1 db, instead o...

Cyb3r-Jak3•9/10/24, 3:09 AM

It is not currently, and I don't believe it is currently on the road map

MMax (@rozenmd)(my canned reply to this is): D1 is built to work to the strengths of Cloudflar...

�

𝓈𝒽𝓎•9/10/24, 5:59 AM

hello @Max (@rozenmd) I'm currently building my startup which would be a POS saas and I thought a database per business would be great, I saw you 5 months ago mentioning a tooling that will help with this process that will be out in couple of weeks/months, idk what to do now, is there a clunky workflow you recommend me to start with for now?

oof2win2•9/10/24, 1:52 PM

wait for birthday week which is in 2 weeks

oof2win2•9/10/24, 1:52 PM

if they have something big like that (would probably be for queues + kv and other products) then theyll probably announce it then

�

𝓈𝒽𝓎•9/10/24, 2:40 PM

maybe but that's why I asked max whether it's coming up or not and what's the alternative they probably know a workflow that can be used for now and won't be a pain to switch to in the future, otherwise what was the point of announcing D1 with Database per user, two weeks for potentially having something is quite long

�𝓈𝒽𝓎 maybe but that's why I asked max whether it's coming up or not and what's the a...

o7•9/10/24, 3:21 PM

how much data are u storing per user

o7•9/10/24, 3:22 PM

with 10GB I can potentially store over 50 million users

�

𝓈𝒽𝓎•9/10/24, 3:24 PM

there is no way that would be possible, imagine a large restaurant chain, very likely it would need a lot of space

�

𝓈𝒽𝓎•9/10/24, 3:24 PM

and having multiple under one db that is limited to 10GB doesn't sound like a good idea

�

𝓈𝒽𝓎•9/10/24, 3:28 PM

besides, I mainly wanted the database per business to easy separation and being able to rollback for that specific business, also give them all their data if they wanted to export it

�𝓈𝒽𝓎 there is no way that would be possible, imagine a large restaurant chain, very l...

o7•9/10/24, 3:33 PM

thats why I asked how much you were storing per user/row

o7•9/10/24, 3:34 PM

i can personally store over 50 million with the basic amount of data im writing per user

o7•9/10/24, 3:34 PM

ive only ever tested to 500,000 rows and did the math with how much space it used

�

𝓈𝒽𝓎•9/10/24, 3:35 PM

are you talking about a single table?

o7•9/10/24, 3:37 PM

yes

�

𝓈𝒽𝓎•9/10/24, 3:37 PM

that's not a realistic test

o7•9/10/24, 3:38 PM

it was meant to be a theoretical test

o7•9/10/24, 3:43 PM

using a DB per user would be awkward to manage IMO. At that point I would use KV/DO.

storing all users in a single table/sharding the table makes more sense as you'd be able to query on every row without having to jump between DBs, especially considering the cold start times

�

𝓈𝒽𝓎•9/10/24, 3:46 PM

if I couldn't find a good solution for this I might consider switching

John Spurlock•9/10/24, 5:41 PM

curious what you mean by this - do you mean exporting it to a local .db file? what's complicated about the one liner wrangler command (other than .sql -> .db step at the end)

JJohn Spurlock curious what you mean by this - do you mean exporting it to a local .db file? w...

ac•9/10/24, 6:47 PM

Yes, I mean exporting it to a simple .db file. The export from the wrangler command just exports queries, which in my experience are prone to issues if the dataset is large enough. It creates a lot of extra work, where a simple sqlite file would be amazing for dev experience

birdfly•9/11/24, 10:34 AM

Hello ,I was charged 1347.30 USD for D1 - Rows Read ,but the 1,342,795,529,750 mentioned in the invoice doesn't match what I saw in cloudflare dashboard ,where I can get help

James•9/11/24, 12:31 PM

Is this an expected number of errors with this number of requests? https://community.cloudflare.com/t/multiple-errors-from-worker-d1-error-network-connection-lost/710299?u=cherryjimbo

nulled•9/11/24, 4:31 PM

Hi All. Hopefully this is the right place to ask this question:

I'm working on a project where a local python app uses the cloudflare D1 REST API (via cloudflare-python) to write data to D1 as the producer.

I then use cloudflare workers to query D1 to consume this data and display it on a web app.

D1 is amazing! Everything "works" however the D1 REST API via the python app seems quite slow, perhaps hitting some type of REST API rate limit? So my next step was to batch my D1 queries in the python app to reduce round trips, same as I do in my workers. But this does not appear to be supported via the rest API? https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

The REST API spec includes "Your SQL query. Supports multiple statements, joined by semicolons, which will be executed as a batch."
Great! But how do I pass in multiple params arrays for each different query? I definitely want to be using prepared statements here for injection safety.

I could write a new worker to make a bespoke API that I send my queries and params to, and then batch them via the worker, but I was hoping to avoid another worker to maintain.

Cloudflare API Documentation

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

nulled•9/11/24, 4:38 PM

I found a similar question from the community forums here: https://community.cloudflare.com/t/d1-batch-mode-in-rest-api/697481 but sadly it is unanswered.

Aac Yes, I mean exporting it to a simple .db file. The export from the wrangler comm...

John Spurlock•9/11/24, 4:49 PM

gotcha - I agree. I'm in the middle of tweaking D1 support in my denoflaredenoflare tool, and it wasn't too hard to add a single one-liner subcommand for exporting a D1 database to a local sqlite DB file: denoflare d1 export-db your-database-name --file /path/to/downloaded.dbdenoflare d1 export-db your-database-name --file /path/to/downloaded.db

details here if you want to try it out: https://gist.github.com/johnspurlock-skymethod/2e3f1a1d9762f8034d622a53ca7aa1a2

Gist

Export a D1 database to a local sqlite3 db file

Export a D1 database to a local sqlite3 db file. GitHub Gist: instantly share code, notes, and snippets.

JJohn Spurlock gotcha - I agree. I'm in the middle of tweaking D1 support in my `denoflare` to...

ac•9/11/24, 4:53 PM

I'll check it out, thanks for sharing!

Nnulled Hi All. Hopefully this is the right place to ask this question: I'm working on...

Dean•9/11/24, 11:53 PM

accessing D1 via the CF API is slow because it has to route to a core location. Create an API with a cloudflare worker and bind the database to the worker and it'll be significantly quicker.

Question: can I get raw results when using D1 batch()? It seems there is no way of avoiding the cost

Similar Threads

Similar Threads

Similar Threads