I do regularly (little bit less than once a day) get these errors: `D1_ERROR: Network connection los

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

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

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

timeFrom

timeFrom

```
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?

B

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

The whole purpose of transactions is consistency and data integrity, if you cannot guarantee that, I would still have to setup a background task to cleanup in the (rare) case that a rollback failed...

Ssty ```js const createTable = await env.db.prepare(` CREATE TABLE IF NOT EXISTS ${...

M

Max (@rozenmd)•1/4/24, 12:19 PM

using JS template literals exposes you to SQL injection btw

MMax (@rozenmd)using JS template literals exposes you to SQL injection btw

S

sty•1/4/24, 12:28 PM

Data feed is from a trusted source and clients won't have access to database at all. My plan is to make Durable object coordinate client db queries. I can set a strict filter for allowed queries. I should be safe right?

Original message was deleted

B

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

hmm, fair point...

I now went for tracking the status in the database and set it to

queud

queud

after a successfully publishing the event, then run a background process that can retry in case of failures.

Ssty Data feed is from a trusted source and clients won't have access to database at ...

M

Max (@rozenmd)•1/4/24, 12:48 PM

if its just you developing it's probably fine, but i tend to avoid leaving vulns in my code, never know what people will use it for in the future

MMax (@rozenmd)if its just you developing it's probably fine, but i tend to avoid leaving vulns...

S

sty•1/4/24, 12:55 PM

Even if I make db accessible to clients, putting strict logic inside the worker that is bound to the db should be ok right?

For example, if

timeFrom

timeFrom

or

timeTo

timeTo

value is not a number, then ignore the client request on the worker side

M

Max (@rozenmd)•1/4/24, 1:04 PM

i'm just saying this isn't safe:

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();

using

.bind()

.bind()

makes it safe:

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

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

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

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

P

PolBaladas (⨍)•1/4/24, 1:40 PM

Hi guys is datetime supported? or only text/intger/real/blob

K

kian•1/4/24, 1:41 PM

Pretty much those - TEXT/REAL/INTEGERs are supported by the Date & Time functions though.

K

kian•1/4/24, 1:41 PM

https://www.sqlite.org/datatype3.html

I do regularly (little bit less than once a day) get these errors: `D1_ERROR: Network connection los

Similar Threads

Similar Threads

Similar Threads