wrangler v3 moved all of the local persistence to a new directory (not specific to D1). You have to

wrangler v3 moved all of the local persistence to a new directory (not specific to D1). You have to assume a clean slate - e.g. apply all migrations again.

MMatt Silverlock wrangler v3 moved all of the local persistence to a new directory (not specific ...

hanpolo•6/3/23, 9:44 PM

This did not address the issue: the issue is that in the new setup you cannot possibly match location from d1 migration tool with location from pages dev

(P.S. at this point well aware of the new locations & have started with clean slate, but the issue I have mentioned remains)

Hhanpolo This did not address the issue: the issue is that in the new setup you cannot po...

Matt SilverlockOP•6/3/23, 9:47 PM

Can you open an issue against https://github.com/cloudflare/workers-sdk/issues/new?assignees=&labels=bug&projects=&template=bug-template.yaml&title=%F0%9F%90%9B+BUG%3A

lay out what you expect to happen (hard to follow your thread on the weekend here)

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 330 million projects.

Hhanpolo This did not address the issue: the issue is that in the new setup you cannot po...

Matt SilverlockOP•6/3/23, 9:49 PM

(The fix is likely that pages devpages dev should use the UUID of the DB, but it doesn’t know it…)

MMatt Silverlock (The fix is likely that `pages dev` should use the UUID of the DB, but it doesn’...

Matt SilverlockOP•6/3/23, 9:50 PM

The binding name isn’t a stable ID, which would be the “easy” hack but break any time you renamed a binding

hanpolo•6/3/23, 9:53 PM

Created issue: https://github.com/cloudflare/workers-sdk/issues/3400

GitHub

🐛 BUG: location from d1 migration cannot match location from pages ...

Which Cloudflare product(s) does this pertain to? D1, Pages, Wrangler What version of Wrangler are you using? 3.0.1 What operating system are you using? Mac Describe the Bug We have a problem for l...

hanpolo•6/3/23, 9:53 PM

Yep .. would be happy if both worked off of UUID, but pages dev does not have a way of identifying UUID, unless it was able to read wrangler.toml

hanpolo•6/3/23, 10:02 PM

@elithrar Thanks for your help on the weekend.

Original message was deleted

Matt SilverlockOP•6/3/23, 11:26 PM

That’s fine for wrangler d1wrangler d1 but not pages devpages dev

Original message was deleted

Matt SilverlockOP•6/4/23, 1:00 AM

Yeah, but folks change bindings, so it’s likely to be a surprise when you change a binding name -> all of your local state is “gone”.

kian•6/4/23, 1:25 AM

The irony is that if you use wrangler.tomlwrangler.toml which pages devpages dev picks up as a mistake then it sounds like it'd work properly meowlul

hanpolo•6/4/23, 4:08 AM

Honestly, I would much rather use wrangler.toml for pages dev. It would simplify things a lot and allow me not to have all my config on the command line. If there were some sort of switch on pages dev that said get your config from wrangler.toml that would then accommodate people who want to do it the old way.

Ssumé ሱሜ I have created a d1 database locally ``` --incidents.sql CREATE TABLE incidents...

itsmatteomanf•6/4/23, 12:17 PM

It's returned as text, you need to parse it again.

itsmatteomanf•6/4/23, 12:18 PM

You can also just get the single column, given your data structure, and parse it just once.

itsmatteomanf•6/4/23, 12:19 PM

But also, you might just put those in separate columns, they make more sense. Why you chose to put a single JSON string?

itsmatteomanf•6/4/23, 12:21 PM

As long as it's known columns, just parse it out...?

itsmatteomanf•6/4/23, 12:22 PM

It's so much easier to filter and get things. You'll get an actually valid JSON structure automatically first, without double parsing.

itsmatteomanf•6/4/23, 12:22 PM

When you get the JSON. Put the citycity in the citycity column, datedate in the date column, etc.

itsmatteomanf•6/4/23, 12:23 PM

Yeah, like dbs are supposed to be used

itsmatteomanf•6/4/23, 12:24 PM

JSON structures are helpful for unstructured data, but if the fields are static it's the best course of action.

Ssumé ሱሜ Haha ok, that shouldn't be too hard. And why I query it?

itsmatteomanf•6/4/23, 12:24 PM

"why"...? I'm not following the question.

itsmatteomanf•6/4/23, 12:25 PM

Oh, I see

itsmatteomanf•6/4/23, 12:26 PM

Normal SQL

env.mydb.prepare('SELECT * FROM incidents').all()

env.mydb.prepare('SELECT * FROM incidents').all()

itsmatteomanf•6/4/23, 12:27 PM

This returns a JSON array of rows, just iterate over them and get each object.

itsmatteomanf•6/4/23, 12:28 PM

Of course, if you wanted only incidents from 2017, you could filter them before getting the results

env.mydb.prepare("SELECT * FROM incidents WHERE year = '2017'").all()

env.mydb.prepare("SELECT * FROM incidents WHERE year = '2017'").all()

itsmatteomanf•6/4/23, 12:29 PM

But also, you might not even need to store years/months/days separately. There are datetime()datetime() functions which allow you to filter if needed

itsmatteomanf•6/4/23, 12:29 PM

And I'd store them as numbers, regardless so you can do ranges easily

Ssumé ሱሜ Ohh nice. I'll try it out, thanks!

Matt SilverlockOP•6/4/23, 3:29 PM

You can also mix and match - https://developers.cloudflare.com/d1/learning/querying-json/

parse the JSON within D1 before returning it to your client.

Query JSON · Cloudflare D1 docs

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

Matt SilverlockOP•6/4/23, 3:31 PM

SELECT
  json_extract(incidents, '$.incident.city') as city
FROM
   …
WHERE
  city = "CHI"

SELECT
  json_extract(incidents, '$.incident.city') as city
FROM
   …
WHERE
  city = "CHI"

Also see ->-> and ->>->> as shorthand - SELECT incidents ->> '$.incident.year' FROM …SELECT incidents ->> '$.incident.year' FROM … returns plain SQL instead of JSON.

Matt SilverlockOP•6/4/23, 3:46 PM

And indexes on fields within your JSON objects: https://antonz.org/json-virtual-columns/

JSON and virtual columns in SQLite

Faster JSON handling in a relational database.

JJason Hostetter Are there any plans to increase the maximum expression depth of 20? I am refacto...

Matt SilverlockOP•6/4/23, 6:12 PM

Yes, but not > 100 at this stage. I’d like to get a better understanding of your actual query’s depth (which is non-trivial - the query planner doesn’t output it)