Cloudflare Developers•3y ago

Ok .. in summary we have a problem: 1. --persist-to will not work since it moves the entire persist

Ok .. in summary we have a problem:

--persist-to will not work since it moves the entire persistence directory
the migrate tool goes off wrangler.toml and hence puts migrations to .wrangler/state/v3/d1/<UUID>
but `npx wrangler pages dev --d1=<BINDING> reads the database off of .wrangler/state/v3/d1/<BINDING>.

The only solution I see for myself at this point is to follow a manual procedure on each database migration:

copy database from d1/<BINDING> to d1/<UUID>
run the migration
copy migrated database from d1/<UUID> back to d1/<BINDING>

I wonder if this is intentional or a bug?

Hhanpolo Ok .. in summary we have a problem: 1. --persist-to will not work since it move...

Matt Silverlock•6/3/23, 9:43 PM

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

hanpoloOP•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 Silverlock•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 Silverlock•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 Silverlock•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

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

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

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

@elithrar Thanks for your help on the weekend.

Original message was deleted

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

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

Original message was deleted

Matt Silverlock•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.toml

wrangler.toml

which pages devpages dev picks up as a mistake then it sounds like it'd work properly meowlul

hanpoloOP•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 Silverlock•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 Silverlock•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 Silverlock•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 Silverlock•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)

Murder Chicken•6/4/23, 8:49 PM

Anyone know of any simple ORMs that are compatible with D1?

MMurder Chicken Anyone know of any simple ORMs that are compatible with D1?

meyer•6/4/23, 8:51 PM

documentation: https://developers.cloudflare.com/d1/platform/community-projects/

Community projects · Cloudflare D1 docs

During D1’s alpha period, members of the Cloudflare developer community have made some valuable contributions to the D1 ecosystem. Below are some …

Mmeyer documentation: https://developers.cloudflare.com/d1/platform/community-projects/

Murder Chicken•6/4/23, 8:55 PM

Thank you! Super-helpful!

Ssteida Inconsistent unit of size in Cloudflare dashboard. KB and kB actually have diffe...

Matt Silverlock•6/4/23, 9:55 PM

We should be consistent, but kB and KB are the same. KiB (kibibyte) is the different unit

Ssteida ChatGPT told me it's 1000 vs 1024, but it's well-known liar so... 😅

Leon338•6/4/23, 11:58 PM

It deppends on the capitalized B. A small kb is 1000 Bit and a big kB is 8000 Bit. You can ignore the K

Unsmart•6/5/23, 12:16 AM

Afaik kb and KB are exactly the same its only KiB that is different... KB = 1000 KiB = 1024

Unsmart•6/5/23, 12:18 AM

Ah yup can be confirmed here: https://physics.nist.gov/cuu/Units/binary.html

Ok .. in summary we have a problem: 1. --persist-to will not work since it moves the entire persist

Similar Threads

Similar Threads

Similar Threads