Ok so I'm unsure if I'm understanding this documentation correctly, but it seems there are `json` he

Ok so I'm unsure if I'm understanding this documentation correctly, but it seems there are

json

json

helper functions for use with your SQL queries i.e.

SELECT * ...

SELECT * ...

SELECT * ...

SELECT * ... or

db.prepare("SELECT * ...

db.prepare("SELECT * ...

db.prepare("SELECT * ...

db.prepare("SELECT * ...

I'm just trying to understand how to put it into practice -
I have

const data = db.prepare("SELECT * FROM profiles WHERE user_id = ?").bind(id).first()`

const data = db.prepare("SELECT * FROM profiles WHERE user_id = ?").bind(id).first()`

const data = db.prepare("SELECT * FROM profiles WHERE user_id = ?").bind(id).first()`

const data = db.prepare("SELECT * FROM profiles WHERE user_id = ?").bind(id).first()`

This data is already just about what I want, except my

fields_of_study

fields_of_study

fields_of_study

fields_of_study key is the string

"['Major 1', 'Major 2']"

"['Major 1', 'Major 2']"

"['Major 1', 'Major 2']"

"['Major 1', 'Major 2']".

Am I storing the data incorrectly in the column or do I need to restructure my query so that D1 will parse the array into a javascript object?

Sslowpoke Ok so I'm unsure if I'm understanding this documentation correctly, but it seems...

itsmatteomanf•9/8/23, 8:43 PM

I'm sure you already read this, but there are examples here

https://developers.cloudflare.com/d1/learning/querying-json/

itsmatteomanf•9/8/23, 8:43 PM

What's the question, though? The return value?

itsmatteomanf•9/8/23, 8:43 PM

The JSON helper functions help for traversing or fetching specific parts of a JSON object stored in a column.

itsmatteomanf•9/8/23, 8:43 PM

They run at the SQL query level, not in the Worker.

slowpokeOP•9/8/23, 8:44 PM

I just thought though, I think I could always just do JSON.parse() on the key before returning it to the client

itsmatteomanf•9/8/23, 8:44 PM

Yeah, you can lul

slowpokeOP•9/8/23, 8:44 PM

I think I thought that this was used to also parse JSON strings without needing the use of parse. Lmao

slowpokeOP•9/8/23, 8:45 PM

I see what you mean. This will help you traverse your JSON strings in a column without needing to like… do everything that entails to update one object key.

itsmatteomanf•9/8/23, 8:45 PM

Exactly, but also allows you to create virtual columns from a JSON object, avoiding the work of iterating through data or returning more than necessary.

itsmatteomanf•9/8/23, 8:46 PM

Or do sort of multi-query with a single query using the json_each()json_each()

slowpokeOP•9/8/23, 8:47 PM

Yep, tysm! Sorry about me overthinking it. thonk

itsmatteomanf•9/8/23, 8:52 PM

No prob

Original message was deleted

Matt Silverlock•9/9/23, 2:01 AM

Yes, they're required: https://developers.cloudflare.com/d1/get-started/#3-bind-your-worker-to-your-d1-database

Get started · Cloudflare D1 docs

This guide will instruct you through:

shxgreen•9/9/23, 8:57 AM

help
When I execute the command "npx wrangler d1 backup create my-database-name" to backup the D1 database, an error occurs.

# npx wrangler d1 backup create my-database-name 


✘ [ERROR] A request to the Cloudflare API (/accounts/xxxxxxxxxx/d1/database/xxxx-xxxx-xxxx-xxxx-xxxx/backup) failed.

  The request is malformed: Experimental DBs do not support backups yet:
  xxxx-xxxx-xxxx-xxxx-xxxx (v3) [code: 7400]
  
  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

# npx wrangler d1 backup create my-database-name 


✘ [ERROR] A request to the Cloudflare API (/accounts/xxxxxxxxxx/d1/database/xxxx-xxxx-xxxx-xxxx-xxxx/backup) failed.

  The request is malformed: Experimental DBs do not support backups yet:
  xxxx-xxxx-xxxx-xxxx-xxxx (v3) [code: 7400]
  
  If you think this is a bug, please open an issue at:
  https://github.com/cloudflare/workers-sdk/issues/new/choose

Sshxgreen 🙏 help When I execute the command "npx wrangler d1 backup create my-database-na...

itsmatteomanf•9/9/23, 9:26 AM

Backups aren’t supported in the new experimental backend (yet, but no ETA). There is the Time Travel feature which accomplishes a similar goal, with some pros and cons.

itsmatteomanf•9/9/23, 9:27 AM

https://developers.cloudflare.com/d1/learning/time-travel/

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

Southpaw•9/9/23, 7:27 PM

As a Ghost user...why would you want to do that?

Are you wanting to store your mailing lists in D1 or...?

Southpaw•9/9/23, 7:27 PM

Or are you wanting to use D1 as the Ghost database?

mrwind•9/10/23, 2:46 AM

https://community.cloudflare.com/t/cant-access-bound-d1-database-from-cloudflare-pages/552176/5 Hi is wrangler version 3.7.0 fix that bug?

Cloudflare Community

Can't access bound D1 database from Cloudflare Pages

Yes I have: and

Mmrwind https://community.cloudflare.com/t/cant-access-bound-d1-database-from-cloudflare...

Cyb3r-Jak3•9/10/23, 3:09 AM

I don’t believe so. The public issue I can find is https://github.com/cloudflare/workers-sdk/issues/3756

GitHub

🐛 BUG: cannot find bound D1 DB using wrangler 3.5.0 in pages app · ...

Which Cloudflare product(s) does this pertain to? D1, Pages What version(s) of the tool(s) are you using? 3.5.0 Wrangler What version of Node are you using? 18.14.2 on windows, 18.17.0 on github ac...

SSrikanth This. I believe I can run a full fledged blog with their current data storage ca...

Southpaw•9/10/23, 6:39 AM

Given that they only support MySQL and D1 is based off SQLite, I think you’re going to have a very difficult time.

I don’t see the point in decentralising Ghost, but if you really wanted to do that the best way to do it would be to use it as a headless CMS for a site you can host on Cloudflare Pages, not trying to run the entire site in a serverless environment.

Mage•9/10/23, 9:39 AM

I would like to use this in production, any ETA on beta?

digeratidave•9/11/23, 2:22 PM

I am trying to perform a quick POC/Prototype of D1 being called from a worker.
Can it be implemented from the cloudflare dashboard (https://dash.cloudflare.com/) or only locally on a desktop with Wrangler? is there any documentation for implementing in the dashboard, it seems it is all Wrangler docs.

Ddigeratidave I am trying to perform a quick POC/Prototype of D1 being called from a worker. ...

Max (@rozenmd)•9/11/23, 3:02 PM

You used to only be able to use Wrangler, but we recently made it possible to develop via the dashboard actually. Will be documented soon.

Ddigeratidave I am trying to perform a quick POC/Prototype of D1 being called from a worker. ...

Max (@rozenmd)•9/11/23, 3:05 PM

1. Create a Worker
2. Click into the Worker, visit the Settings tab, and click the Variables submenu scroll down to "D1 Database Bindings", and add your D1 database (I called mine DB)
3. Quick edit the Worker, (to test this works, I used this:)

export default {
  async fetch(request, env, ctx) {
    const res = await env.DB.prepare('SELECT 1;').all()
    return new Response(JSON.stringify(res, null, 2));
  },
};

export default {
  async fetch(request, env, ctx) {
    const res = await env.DB.prepare('SELECT 1;').all()
    return new Response(JSON.stringify(res, null, 2));
  },
};

4. Hit Send on the right hand side
5. You should see JSON output similar to:

{
....
"results": [
    {
      "1": 1
    }
  ]
}

{
....
"results": [
    {
      "1": 1
    }
  ]
}

MMax (@rozenmd)1. Create a Worker 2. Click into the Worker, visit the Settings tab, and click t...

digeratidave•9/11/23, 3:46 PM

awesome! Works great! Thank You!

MMax (@rozenmd)1. Create a Worker 2. Click into the Worker, visit the Settings tab, and click t...

digeratidave•9/11/23, 8:54 PM

@rozenmd I would like to use the url in the where clause. how do we reference the url?

Ddigeratidave @rozenmd I would like to use the url in the where clause. how do we reference th...

Matt Silverlock•9/11/23, 9:19 PM

request.urlrequest.url has the URL. I'd suggest reading the MDN docs on how to parse URLs (https://developer.mozilla.org/en-US/docs/Web/API/URL) and/or moving over to #workers-help for non-D1 specific questions

URL - Web APIs | MDN

The URL interface is used to parse, construct, normalize, and encode URLs. It works by providing properties which allow you to easily read and modify the components of a URL.

Matt Silverlock•9/11/23, 10:20 PM

https://x.com/andrii_sherman/status/1701243280321552399?s=46

Andrew Sherman (@andrii_sherman)

Just sitting here, browsing @Cloudflare D1 from @DrizzleORM Studio

Twitter

•

9/11/23, 2:36 PM

justaname•9/11/23, 11:35 PM

Was wondering if connecting to the database via a url is possible?

Jjustaname Was wondering if connecting to the database via a url is possible?

Cyb3r-Jak3•9/11/23, 11:37 PM

You mean as like a connection string or via HTTP?

justaname•9/11/23, 11:38 PM

Yes

Jjustaname Yes

Cyb3r-Jak3•9/11/23, 11:40 PM

Connection string: No
HTTP API: Not officially, you can reverse engineer wrangler but not to be stable and can break

justaname•9/11/23, 11:42 PM

There is a plan for a connection string in the future though correct?

Jjustaname There is a plan for a connection string in the future though correct?

Max (@rozenmd)•9/12/23, 8:33 AM

I don't work on the core, so I don't have the full picture, but I don't see how connection string could work without a custom driver, by default sqlite looks for files on the filesystem to connect to

MMax (@rozenmd)I don't work on the core, so I don't have the full picture, but I don't see how ...

GuillaumeLakano•9/12/23, 3:41 PM

Matt confirm that later this will be possible to have millions of databases, so I suppose you'll support a kind of connection string or similar to permit to reach a specific database not mentioned in the wrangler.toml config

For our use case, our goal to have million of D1 databases is to permit us to create 1 DB per client, to isolate and increase the security (it's what we do currently with Durable Objects). But we can't put million of ID in the wrangler.toml so there will be surely a way to point to the DB of our choice directly in the code. ( BTW it's the DB name is also limited to 32 alphanumeric characters currently in the wrangler.toml )

GGuillaumeLakano Matt confirm that later this will be possible to have millions of databases, so ...

Max (@rozenmd)•9/12/23, 3:47 PM

That particular case should be possible, I assumed the previous commenter was talking about the sqlite library's connection string, not from inside Workers

Unsmart•9/12/23, 3:48 PM

Theres been some talks around dynamic bindings for all R2/D1/Queues before but not something that blocks GA (i.e. might be a while till it happens)

digeratidave•9/12/23, 6:08 PM

Am I understanding correctly that SQLite does not support remote access other than through Workers, Pages, and Wrangler?
I was looking for a tool like TOAD or SQL Developer to remotely access the database to run queries but it sounds like SQLite does not support that. Did I understand correctly?

Ddigeratidave Am I understanding correctly that SQLite does not support remote access other th...

Cyb3r-Jak3•9/12/23, 6:35 PM

There’s an unofficial api that you can use to make queries but nothing like a native connection string

Ddigeratidave Am I understanding correctly that SQLite does not support remote access other th...

Matt Silverlock•9/12/23, 6:51 PM

SQLite itself is file-based. Anyone building on it needs to expose some sort of network API - such as HTTP

dave•9/12/23, 11:46 PM

How can I combine these two in one query?

const d1_lookup_code = await c.env.DB.prepare(`SELECT id, program, details FROM my_codes WHERE code = ?`).bind(code).first();
await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ?`).bind(code).run();

const d1_lookup_code = await c.env.DB.prepare(`SELECT id, program, details FROM my_codes WHERE code = ?`).bind(code).first();
await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ?`).bind(code).run();

Unsmart•9/12/23, 11:51 PM

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).run()

await c.env.DB.prepare(`DELETE FROM my_codes WHERE code = ? RETURNING *`).bind(code).run()

Unsmart•9/12/23, 11:51 PM

dave•9/12/23, 11:52 PM

oh nice

dave•9/12/23, 11:52 PM

where is RETURNING documented?

Ok so I'm unsure if I'm understanding this documentation correctly, but it seems there are `json` he

Similar Threads