https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433https://gist.github.com/WcaleN

W

WcaleNieWolnyOP•1/3/24, 2:14 PM

Essentially I read data from postgrs -> convert it into an insert statement and then I try to execute this in d1

W

WcaleNieWolnyOP•1/3/24, 2:15 PM

the problem is that I get rate limited - it was suggested I "Pass multiple statements in one API call, separated by a semicolon" - that did not work as I use a prepared statement

WWcaleNieWolny https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433https://gi...

I

Isaac McFadyen•1/3/24, 2:15 PM

We're not going to be able to debug with that unfortunately, that won't show us the actual SQL being executed.

W

WcaleNieWolnyOP•1/3/24, 2:16 PM

W

WcaleNieWolnyOP•1/3/24, 2:16 PM

here is the sql being executed

I

Isaac McFadyen•1/3/24, 2:16 PM

So the first thing is that D1 doesn't support BEGIN TRANSACTION

I

Isaac McFadyen•1/3/24, 2:16 PM

Any statements sent as part of the batch API are automatically executed in a transaction, but otherwise transactions are handled by D1 internally.

W

WcaleNieWolnyOP•1/3/24, 2:17 PM

right - I only have https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database available - will this work as batch there?

Cloudflare API Documentation

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

W

WcaleNieWolnyOP•1/3/24, 2:18 PM

how would you like me to send a batch statement using this http api?

W

WcaleNieWolnyOP•1/3/24, 2:22 PM

right - so it also does not support the batch api

WWcaleNieWolny right - so it also does not support the batch api

M

Matt Silverlock•1/3/24, 2:54 PM

Sorry, I interpreted that you had a Worker, or were using wrangler to execute a file export. Row by row in the HTTP API has this limitation (for now)

T

twilson90•1/3/24, 3:06 PM

Trying to setup a d1 database on the remote worker with the following command:

npx wrangler d1 create test

npx wrangler d1 create test

npx wrangler d1 create test

npx wrangler d1 create test

results in

[ERROR] Cannot read properties of undefined (reading 'map')

[ERROR] Cannot read properties of undefined (reading 'map')

[ERROR] Cannot read properties of undefined (reading 'map')

[ERROR] Cannot read properties of undefined (reading 'map')

Using the --local flag works however.

In my wrangler.tml I have:

[[d1_databases]]
binding = "DB"
database_name = "name-of-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "name-of-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "name-of-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "name-of-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

What am I doing wrong?

EDIT:
Oops. Turned out I'd been signed out of cloudflare and I needed to run

npx wrangler login

npx wrangler login

npx wrangler login

npx wrangler login again. Odd because it was still letting me deploy. A better error message would have been helpful.

Ttwilson90 Trying to setup a d1 database on the remote worker with the following command: ...

F

Freeze•1/3/24, 8:19 PM

OMG I have spend 3 hours on this

R

Rabid•1/3/24, 9:02 PM

It would be a big QOL change to handle pagination of D1 databases in the UI better. So when you click on a db, and go back, you don’t have to navigate to the page you were on again. Right now the URL is unchanged when paging through DBs or DB rows.

A

ALECKS•1/4/24, 1:25 AM

Is there any guidance on how to run migrations against a local, development, D1 instance?

AALECKS Is there any guidance on how to run migrations against a local, development, D1 ...

C

Cyb3r-Jak3•1/4/24, 1:30 AM

You just run the migration command with the

--local

--local

--local

--local flag.

CCyb3r-Jak3 You just run the migration command with the `--local` flag.

A

ALECKS•1/4/24, 1:31 AM

thank you!

A

ALECKS•1/4/24, 1:32 AM

What tools are people using to interact with the loca dev db? just the cli

wrangler d1 execute [queries]

wrangler d1 execute [queries]

wrangler d1 execute [queries]

wrangler d1 execute [queries] or is there some other goto, maybe gui, client that is more friendly?

R

Rubi•1/4/24, 2:43 AM

will I be allowed to access D1 from outside of cloudflare?

R

Rubi•1/4/24, 2:43 AM

on GA

U

Unsmart•1/4/24, 2:45 AM

I'd assume it will stay as it is now. If you want to do that you can either use the API, or better: create a worker proxy.

D

dragon•1/4/24, 4:26 AM

just me once again saying im super impressed with d1 thus far.
12mb for 15K rows....
This is the db behind https://dstif.io, which runs completely on cf workers, pages, and d1.

Hope this year brings some ux improvements - like search and filter and improved pagination

Ddragon just me once again saying im super impressed with d1 thus far. 12mb for 15K rows...

R

Rubi•1/4/24, 4:54 AM

does the perf & latency good?

RRubi does the perf & latency good?

D

dragon•1/4/24, 4:56 AM

I havent had any issues with either perf or latency

T

TigersWay•1/4/24, 4:57 AM

I do regularly (little bit less than once a day) get these errors:

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost.

D1_ERROR: Network connection lost. or

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset. with 1k to 2.5k write request per day.
What would be the best way to deal with these? Should I simply try with the obvious "retry on error" logic, or am I not seeing something else?

D

dragon•1/4/24, 5:03 AM

have you considered using batch statements (aka sql transactions) ?

D

dragon•1/4/24, 5:05 AM

i know the error is a connection error - but the transaction would avoid partial queries from being completed

R

Rubi•1/4/24, 5:07 AM

uh, sqlite haven't support geointersect, I always willing to rewrite my Mongo app to use D1

AAaronO Depending on your use-case, you could consider using something like geohashes (h...

R

Rubi•1/4/24, 5:26 AM

I'm good, gotta stay with MongoDB, all-in-one solution, until cloudflare support the same functionality, then I will definitely move to cloudflare solution

R

Rubi•1/4/24, 5:34 AM

I think it will be burden to the memory

AAaronO You mean storage overhead ? Per row ?

R

Rubi•1/4/24, 5:40 AM

explicitly computing the geohash isn't this mean: loading the geojson to the memory then doing a geospatial processing on it

R

Rubi•1/4/24, 5:51 AM

basically what I'm doing is https://www.mongodb.com/docs/manual/reference/operator/query/geoIntersects/ and https://www.mongodb.com/docs/manual/reference/operator/query/near/ on >25GB Geojson dataset

R

Rubi•1/4/24, 5:52 AM

I never specifically learn about geohash, but sure I will take a look

R

Rubi•1/4/24, 5:52 AM

sorry if this mongodb discussion kind out of topic

S

sty•1/4/24, 10:29 AM

how do i

CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS

CREATE TABLE IF NOT EXISTS using the D1 client api?

try{
const stmt = await env.soldb.prepare('CREATE TABLE IF NOT EXISTS Testing (blockId INTEGER PRIMARY KEY, blockTs INTEGER)').run()
                    console.log(stmt)
}
catch(err){
console.log(err)
}

try{
const stmt = await env.soldb.prepare('CREATE TABLE IF NOT EXISTS Testing (blockId INTEGER PRIMARY KEY, blockTs INTEGER)').run()
                    console.log(stmt)
}
catch(err){
console.log(err)
}

try{
const stmt = await env.soldb.prepare('CREATE TABLE IF NOT EXISTS Testing (blockId INTEGER PRIMARY KEY, blockTs INTEGER)').run()
                    console.log(stmt)
}
catch(err){
console.log(err)
}

try{
const stmt = await env.soldb.prepare('CREATE TABLE IF NOT EXISTS Testing (blockId INTEGER PRIMARY KEY, blockTs INTEGER)').run()
                    console.log(stmt)
}
catch(err){
console.log(err)
}

this is returning

{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    duration: 0,
    changes: 0,
    last_row_id: 0,
    changed_db: false,
    size_after: 3854336
  },
  results: []
}

{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    duration: 0,
    changes: 0,
    last_row_id: 0,
    changed_db: false,
    size_after: 3854336
  },
  results: []
}

{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    duration: 0,
    changes: 0,
    last_row_id: 0,
    changed_db: false,
    size_after: 3854336
  },
  results: []
}

{
  success: true,
  meta: {
    served_by: 'miniflare.db',
    duration: 0,
    changes: 0,
    last_row_id: 0,
    changed_db: false,
    size_after: 3854336
  },
  results: []
}

no table is created
edit: i figured out no table was created because i was trying to insert into the production db from a dev worker.

K

kian•1/4/24, 10:31 AM

How are you checking if the table is created, do you have a later query that attempts to insert into it?

S

sty•1/4/24, 11:04 AM

the cloudflare dashboard

S

sty•1/4/24, 11:04 AM

actually maybe it is because im trying to insert into production db from a dev worker

S

sty•1/4/24, 11:08 AM

const createTable = await env.db.prepare(`
  CREATE TABLE IF NOT EXISTS ${tableName} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    blockTs INTEGER,
    value TEXT,
    INDEX idx_blockTs (blockTs)
  )
`).run();

const createTable = await env.db.prepare(`
  CREATE TABLE IF NOT EXISTS ${tableName} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    blockTs INTEGER,
    value TEXT,
    INDEX idx_blockTs (blockTs)
  )
`).run();

const createTable = await env.db.prepare(`
  CREATE TABLE IF NOT EXISTS ${tableName} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    blockTs INTEGER,
    value TEXT,
    INDEX idx_blockTs (blockTs)
  )
`).run();

const createTable = await env.db.prepare(`
  CREATE TABLE IF NOT EXISTS ${tableName} (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    blockTs INTEGER,
    value TEXT,
    INDEX idx_blockTs (blockTs)
  )
`).run();

is this correct way to index blockTs? my primary unique key is

id

id

id

id
I could have duplicate blockTs values.
but I want to put index on blockTs because I frequently query data using

timeFrom

timeFrom

timeFrom

timeFrom

```
timeTo
```
timeTo
```
timeTo
```
timeTo

B

Bram•1/4/24, 11:23 AM

Hi, I am using D1 and queues, after an insert statement, I want to place an event on the queue, but if this event publishing fails, I want to rollback the transaction that did the insert. Is this possible? Does D1 already rollback if somewhere within the request scope an unhandled error is thrown?

https://gist.github.com/WcaleNieWolny/3510515be7145411d3b3cc93ccd0c433https://gist.github.com/WcaleN

Similar Threads