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

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

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

styOP•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...

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

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

insert single row
publish event on queue

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

if 2. fails, I want 1. to rollback

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...

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...

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

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

Original message was deleted

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?

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 ${...

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

styOP•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

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 ...

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...

styOP•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

timeTo

timeTo

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

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

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

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

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

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

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

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

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

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

thanks

TTigersWay I do regularly (little bit less than once a day) get these errors: `D1_ERROR: Ne...

Vy•1/4/24, 3:36 PM

@TigersWay Retry is definitely recommended but I'd like to understand more about your workload when you see these errors? Are these long-running queries?

styOP•1/4/24, 7:49 PM

Maximum database size    2 GB (Workers Paid) beta / 500 MB (Free)
Maximum storage per account    50 GB (Workers Paid) beta / 5 GB (Free)

Maximum database size    2 GB (Workers Paid) beta / 500 MB (Free)
Maximum storage per account    50 GB (Workers Paid) beta / 5 GB (Free)

what is the difference between database and storage?

is it maximum 2gb per table in a db?

kian•1/4/24, 7:49 PM

Your databases can be a maximum of 2 GB, with all of your databases collectively needing to be under 50 GB (on Paid plans).

kian•1/4/24, 7:49 PM

Meaning you could have 25 2 GB databases, 50 1 GB databases, etc

styOP•1/4/24, 7:51 PM

oof

styOP•1/4/24, 7:53 PM

is there a way to use d1 api to create new db inside a worker dynamically?

without having to pre-bind in the toml file like this:

[[d1_databases]]
binding = "db" # i.e. available in your Worker on env.DB
database_name = "d1"
database_id = "#####################"

[[d1_databases]]
binding = "db" # i.e. available in your Worker on env.DB
database_name = "d1"
database_id = "#####################"

kian•1/4/24, 7:53 PM

Not currently.

Tudders•1/5/24, 2:22 AM

Hey all - is there a shortcut for a timestamp like now()?

Jumping Back•1/5/24, 3:52 AM

I have lots of transactions (stripe) and what not is it worth going with d1 or normal postgres

Ravi kiran•1/5/24, 5:24 AM

Hey guys, im trying to create a record having datetimes as fields. The fields are strings but im storing input datetimes as iso-strings. i get a D1 error when trying to get back data with another query. can some one look at my code and tell me how it can be cleared. attaching code and error

Screenshot_2024-01-05_at_10.55.35_AM.png

Screenshot_2024-01-05_at_10.55.53_AM.png

Kkarimfromjordan `unixepoch()` for `INTEGER` and `datetime()` for `TEXT`.

Tudders•1/5/24, 6:13 AM

thanks Karim

RRavi kiran Hey guys, im trying to create a record having datetimes as fields. The fields ar...

Ravi kiran•1/5/24, 8:00 AM

guys can someone help me with this please.

maguayo•1/5/24, 8:36 AM

Any estimation on the limits per databse after the "beta"? Now it's 2GB, it will increate to like 3GB or way more?

Mmaguayo Any estimation on the limits per databse after the "beta"? Now it's 2GB, it will...

Jumping Back•1/5/24, 9:42 AM

https://developers.cloudflare.com/d1/platform/pricing/

Jumping Back•1/5/24, 9:43 AM

maybe feb 2024? cause its say they gonna be start charging from that point onwards but its just a guess

Jumping Back•1/5/24, 10:05 AM

quick quest: does prisma migrate works with d1 or will i have to swap out with drizzle

RRavi kiran Hey guys, im trying to create a record having datetimes as fields. The fields ar...

Cyb3r-Jak3•1/5/24, 2:39 PM

My guess is that it’s due to string to use

to compare strings so it wants a number. You should store date time stuff as integers so you can use

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

Similar Threads

Similar Threads

Similar Threads