SQLite supports renaming a table, adding a new column and renaming an existing column - so that subs

V

Vikash•12/1/23, 5:34 AM

It seems the JSON parse function doesn't work in query - I have

value

value

value

value column to store some JSON data, but when I query with

json(value)

json(value)

json(value)

json(value) it return the string instead the parsed JSON.

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

 const sqlQuery = ctx.env.DB.prepare(
    `
      SELECT key, json(value) as value, created_at, updated_at, expire_at 
      FROM Example
      Where account_id = ?
      LIMIT ?, ?`
  ).bind(authUser.account_id, offset, limit);
  const { results } = await sqlQuery.all();

Result

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

[
    {
        "key": "rMDrWHc",
        "value": "{\"url\":\"https://agenty.com\",\"params\":{},\"key\":\"rMDrWHc\"}",

        "created_at": "2023-12-01 05:27:45",
        "updated_at": null,
        "expire_at": null
    }
]

I was expecting the JSON to be parsed using

json(value)

json(value)

json(value)

json(value) function given in docs - https://developers.cloudflare.com/d1/learning/querying-json/#supported-functions

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

K

kianOP•12/1/23, 6:12 AM

The json(X) function verifies that its argument X is a valid JSON string and returns a minified version of that JSON string

K

kianOP•12/1/23, 6:12 AM

It doesn't turn it into a JS object, you need to do that yourself.

K

kianOP•12/1/23, 6:12 AM

It's a SQLite built-in function rather than something D1 specific.

Kkian It doesn't turn it into a JS object, you need to do that yourself.

V

Vikash•12/1/23, 6:58 AM

you mean the returned value can't be used in query to print in output?

K

ken•12/1/23, 12:55 PM

Hey

Does anyone know the limit of the number of statements for Batch Statements (

db.batch()

db.batch()

db.batch()

db.batch()) on D1?

https://developers.cloudflare.com/d1/platform/client-api/#dbbatch|

I can see that the Maximum SQL statement length is

100,000 bytes (100 KB)

100,000 bytes (100 KB)

100,000 bytes (100 KB)

100,000 bytes (100 KB) and it should apply. But is there any other limits as well?

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

Kken Hey 👋 Does anyone know the limit of the number of statements for Batch Statemen...

M

Max (@rozenmd)•12/1/23, 1:02 PM

should be the same as a regular db.run/all, except the limits apply individually to the queries

M

Max (@rozenmd)•12/1/23, 1:02 PM

all .batch does is save you the round trips of sending your sql+params for several queries

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

H

Hello, I’m Allie!•12/1/23, 1:10 PM

Isn’t that it though? If limits are applied to each query individually, how many queries can you make?

HHello, I’m Allie!Isn’t that it though? If limits are applied to each query individually, how many...

M

Max (@rozenmd)•12/1/23, 1:15 PM

I'm not sure we've tested it tbh, the answer is probably "however many queries it takes to crash the DO"

M

Max (@rozenmd)•12/1/23, 1:15 PM

each query is run sequentially though so it's probably a lot

MMax (@rozenmd)all .batch does is save you the round trips of sending your sql+params for sever...

K

ken•12/1/23, 1:44 PM

that makes sense, my query is throttling anyway so should be fine ,but wanted to see the upper limit. Thank you!

Original message was deleted

M

Max (@rozenmd)•12/1/23, 2:01 PM

that's an alpha db btw

M

Max (@rozenmd)•12/1/23, 2:30 PM

thats awesome

V

Village (Josef)•12/2/23, 4:49 PM

What's the fastest way to wipe a database and remigrate it all

V

Village (Josef)•12/2/23, 4:49 PM

I'd like to avoid having to manually delete 17 tables

S

senojsitruc•12/2/23, 4:53 PM

I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d42) yesterday between 8 - 9pm (ET). I created a support ticket (#3047705). The dashboard times out trying to list the database and says, "Failed to reach database. Please try again later." I had been very successfully testing it with a limited load and then a couple days ago I started trying additional load. And then it died. That could be coincidental, of course. I restored the data to a new database instance and got back up-and-running. If I've done something incorrect with D1, I'd like to know more details so that I can prevent this from happening again. Thanks! (D1 is great, btw.)

V

Village (Josef)•12/2/23, 5:21 PM

Just wipe and go

V

Village (Josef)•12/2/23, 5:21 PM

I ended up finding the --file for wrangler and just wrote a bunch of drop table statements

MMax (@rozenmd)should be the same as a regular db.run/all, except the limits apply individually...

F

Fun Miles•12/2/23, 6:15 PM

Is one batch query a single API call? I am hitting a maximum API call rate limit for a single worker in a CRON worker. Would batching save me?
Is it possible to batch

SELECT

SELECT

statements?

Ssenojsitruc I think I managed to blow up my D1 database (4871ae1f-6808-465d-a8ea-8b404d0c4d4...

M

Matt Silverlock•12/2/23, 6:25 PM

Well take a look. There’s nothing you should be able to do that results in your database being “stuck” - we consider those cases bugs

F

Fun Miles•12/2/23, 6:42 PM

I can confirm I am able to have an array of 'SELECT' in there. But you cannot do

DB.prepare(statment).bind(arguments...).first()

DB.prepare(statment).bind(arguments...).first()

DB.prepare(statment).bind(arguments...).first()

DB.prepare(statment).bind(arguments...).first(). So the result will be an array.

F

Fun Miles•12/2/23, 6:44 PM

But how about the number of API calls it is counted as from the point of view of not getting an error of Too many API calls from a single worker ?

F

Fun Miles•12/2/23, 6:45 PM

I know if will count as as many row retrieval as I have SELECT statement (1 result per SELECT), but that's not an issue.

Original message was deleted

F

Fun Miles•12/2/23, 6:46 PM

You were able to do 100k batched queries, so it sounds like such a batch counts as one API call and not 100k, otherwise you'd have hit the limit

F

Fun Miles•12/2/23, 7:03 PM

My re-factored query worker is now rewritten and it is not hitting the API limit anymore. But some of the queries exceed CPU time. This is a problem I've hit before. The docs say a query worker or a CRON with more than 1h between execution should get 15 minutes of CPU, but clearly it is not the case. I am being killed after 32,000ms. Is this a bug in the new billing?

F

Fun Miles•12/2/23, 7:08 PM

@Matt Any idea? I should be able to work around this for a while by reducing the queue batching size but that means I can hardly trust that it won't happen again.

F

Fun Miles•12/2/23, 9:23 PM

It doesn't look like D1 side limit to me. The message from tail said:

Queue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PM

Queue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PM

Queue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PM

Queue schedule-retrieval (7 messages) - Exceeded CPU Limit @ 12/2/2023, 12:03:45 PM

FFun Miles It doesn't look like D1 side limit to me. The message from tail said: `Queue sch...

M

Matt Silverlock•12/2/23, 10:41 PM

This is related to #queues-beta - not D1.

MMatt Silverlock This is related to #queues-beta - not D1.

F

Fun Miles•12/2/23, 10:43 PM

Or CRON workers without Queues, but nobody has answered my questions there. Thought you might have better knowledge.

FFun Miles Or CRON workers without Queues, but nobody has answered my questions there. Thou...

M

Matt Silverlock•12/2/23, 10:49 PM

The documented limits are 15 minutes of wall clock time (total duration), not true CPU time, if you use Unbound, for Cron triggers & Queue consumers. Recommend keeping the discussion in the relevant channels.

F

Fun Miles•12/2/23, 10:50 PM

It does say of CPU time per invocation. Not wall clock time.

F

Fun Miles•12/2/23, 10:52 PM

What channel do I discuss Cron trigger issue?

FFun Miles What channel do I discuss Cron trigger issue?

M

Matt Silverlock•12/2/23, 11:16 PM

If this is specific to a Cron trigger, then #workers-help - best to keep your question in one place. None of this is related to D1.

H

Hearse•12/3/23, 5:05 AM

I have a couple questions:

can i query d1 from outside of pages/workers, for example (discord bot)
can i tell wrangler d1 for my local database to copy all entries from my production database

S

Shripad Krishna•12/3/23, 6:42 AM

I have an issue with D1 binding in my solid-start app. While accessing the binding from within the app, it always connects to in-memory sqlite database instead of the local persisted DB. I am using Wrangler 3. Any idea why this is happening? There is no --persist in Wrangler 3 (like in 2) so I can't tell it to look for local D1 stored database either (in .wrangler/state/v3/d1). Is there any config that I need to change/set to make the binding look for the local state instead of check in-memory?

S

Shripad Krishna•12/3/23, 6:43 AM

When I log the binding details to the console this is what I get:

    console.log(env.__D1_BETA__DB)

    console.log(env.__D1_BETA__DB)

    console.log(env.__D1_BETA__DB)

    console.log(env.__D1_BETA__DB)

D1Database {
  binding: D1DatabaseAPI {
    db: Database {
      name: ':memory:',
      open: true,
      inTransaction: false,
      readonly: false,
      memory: true
    }
  }
}

D1Database {
  binding: D1DatabaseAPI {
    db: Database {
      name: ':memory:',
      open: true,
      inTransaction: false,
      readonly: false,
      memory: true
    }
  }
}

D1Database {
  binding: D1DatabaseAPI {
    db: Database {
      name: ':memory:',
      open: true,
      inTransaction: false,
      readonly: false,
      memory: true
    }
  }
}

D1Database {
  binding: D1DatabaseAPI {
    db: Database {
      name: ':memory:',
      open: true,
      inTransaction: false,
      readonly: false,
      memory: true
    }
  }
}

Original message was deleted

H

Hearse•12/3/23, 1:35 PM

Ah okay, is it possible for my local db to copy everything from prod

Original message was deleted

H

Hearse•12/3/23, 1:38 PM

Nice, i ll do that. Currently i am using mongodb free tier but since i am transitioning to cloudflare workers, it would be best to transition to d1 and would be more performative

B

BbIsaac•12/3/23, 1:50 PM

I am have successfully implemented an FTS5 virtual table.
However, I am facing an issue with the fts5vocab module, which is resulting in an authorization error. I would appreciate your assistance in resolving this matter. Below are the details of the queries I have executed:

1.Successful Query:
Command:

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5 USING fts5( title, text, tokenize='trigram');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5 USING fts5( title, text, tokenize='trigram');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5 USING fts5( title, text, tokenize='trigram');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5 USING fts5( title, text, tokenize='trigram');"

2.Query Resulting in Error:
Command:

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5vocab USING fts5vocab('trigram_fts5', 'row');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5vocab USING fts5vocab('trigram_fts5', 'row');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5vocab USING fts5vocab('trigram_fts5', 'row');"

npx wrangler d1 execute staging-db-web --local --command="CREATE VIRTUAL TABLE trigram_fts5vocab USING fts5vocab('trigram_fts5', 'row');"

Error Message:

[ERROR] not authorized

[ERROR] not authorized

[ERROR] not authorized

[ERROR] not authorized

I am aware that FTS5 is supported in Cloudflare D1. However, I am uncertain about the support for the fts5vocab module.
Could you please confirm if this module is supported, or if it is yet to be implemented? Any guidance or suggestions to address this issue would be greatly appreciated.

D

dev.itsarun•12/3/23, 2:29 PM

Hai guys ,How to create REST API Key For D1 Read & Write ?

SShripad Krishna I have an issue with D1 binding in my solid-start app. While accessing the bindi...

M

Matt Silverlock•12/3/23, 3:32 PM

Upgrade to the latest wrangler - at least 3.5.0 or greater: https://developers.cloudflare.com/d1/changelog/#bind-d1-from-the-cloudflare-dashboard

Changelog · Cloudflare D1 docs

Developers using D1 on a Workers Paid plan can now create up to 50,000 databases as part of ongoing increases to D1’s limits.

BBbIsaac I am have successfully implemented an FTS5 virtual table. However, I am facing a...

M

Matt Silverlock•12/3/23, 3:32 PM

Please open an issue on GitHub with the details: https://github.com/cloudflare/workers-sdk/issues/new/choose

GitHub

Build software better, together

GitHub is where people build software. More than 100 million people use GitHub to discover, fork, and contribute to over 420 million projects.

D

dev.itsarun•12/3/23, 4:15 PM

i got this msg when i query a d1 db

 {"success":false,"errors":[{"code":10000,"message":"Authentication error"}]}  https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query

 {"success":false,"errors":[{"code":10000,"message":"Authentication error"}]}  https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query

 {"success":false,"errors":[{"code":10000,"message":"Authentication error"}]}  https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query

 {"success":false,"errors":[{"code":10000,"message":"Authentication error"}]}  https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query

but when call list api

 https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database

 https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database

 https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database

 https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database

the api was successfull .
i dont know what am i doing wrong , help me solve this with steps to create a perfect API Token for d1 read query access.

Ddev.itsarun i got this msg when i query a d1 db ``` {"success":false,"errors":[{"code":10000...

C

Cyb3r-Jak3•12/3/23, 4:34 PM

I believe you need write access for the D1 API token in order to be able to query. There isn't a read only query

D

dev.itsarun•12/3/23, 4:51 PM

@Cyb3r-Jok3 am i doing correct ?

Ddev.itsarun @Cyb3r-Jok3 am i doing correct ?

C

Cyb3r-Jak3•12/3/23, 4:53 PM

Needs edit to be able to query

D

dev.itsarun•12/3/23, 4:54 PM

@Cyb3r-Jok3 oh ok thankyou

W

WcaleNieWolny•12/3/23, 4:55 PM

I would like to access

https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query

https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query

https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query

https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query from localhost. I am trying to use D1 as a backup db that will be synced from postgress (postgress -> D1) and I would like to use HTTP for this. However I was not able to find any documentation on replicating this endpoint localy

W

WcaleNieWolny•12/3/23, 4:57 PM

I could try to write a worker with the intention of mocking this endpoint and just hope it behaves exactly as this api. I saw something here tho no idea how to access it