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

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

thank you!

A

ALECKSOP•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?

Original message was deleted

S

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

youre right it doesnt work. your method works

AAaronO Yeah sounds like it's planned, but not available yet (ref https://discord.com/ch...

B

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

yea, looks like they are not working on them yet, so the JS transaction API could take a while to become available, unfortunately this doesn't make D1 suitable for more complex use cases...

hopefully it will come soon

B

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

insert single row
publish event on queue

B

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

if 2. fails, I want 1. to rollback

B

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

But that is not really a rollback, deleting that single record could fail as well, right?

AAaronO You could also update the status and then have a background task cleanup those r...

B

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

Indeed, bit cumbersome but that would work

AAaronO If you're worried about race conditions you could have a durable object arbitrat...

B

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

Not in my current use case, but thanks for the suggestion : )

Original message was deleted

B

Bram•1/4/24, 12:01 PM

in that case the transaction will be blocking other writes to the database and eventually the transaction gets aborted on the database side, no?

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

Similar Threads

Similar Threads

Similar Threads