I have upgraded to wrangler 3.0.1 and my previously persisted database no longer works. The error I

I have upgraded to wrangler 3.0.1 and my previously persisted database no longer works. The error I get is that tables I created in the persisted database are not present. I ran my migrations again and noticed that two databases were created under .wrangler/state/v3/d1/<some random string> & .wrangler/state/v3/d1/my_d1 with the <some random string> database having the tables and my_d1 being empty. But even after this, when I tried accessing the database I got the same error. I did notice a line when wrangler initialized : "my_d1: local-my_d1 (my_d1)." Any ideas on what I have to do to get back to my database?

MMatt Silverlock https://developers.cloudflare.com/d1/platform/limits/ (Function arguments are ...

yeehow•6/3/23, 6:35 PM

Someone how missed this... Great to hear thank you!

Hhanpolo I have upgraded to wrangler 3.0.1 and my previously persisted database no longer...

Matt Silverlock•6/3/23, 7:48 PM

Wrangler 3 changed the location and format of persisted data - if you search this channel you’ll find a couple of others talking about it. Do you need the local data for some reason?

hanpoloOP•6/3/23, 8:17 PM

Nope, don't need the old data .. just need to be able to access local data ..

hanpoloOP•6/3/23, 8:17 PM

I'll search some more

hanpoloOP•6/3/23, 8:23 PM

My problem is not getting hold of old data. I upgraded to wrangler 3, ran my migrations again, a database was created in the .wrangler/state/v3/d1/<UUID> location, and still can't get hold of any of the tables created by migration scripts

hanpoloOP•6/3/23, 8:27 PM

I just ran a test and created a table from my main "functions" code with local binding. It created the table in .wrangler/state/v3/d1/my_d1 instead of d1/<UUID>. Am I missing some setting? This is cumbersome because now my migrations are creating tables in d1/<UUID> but my functions scripts are looking at d1/my_d1

hanpoloOP•6/3/23, 8:29 PM

my wrangler command is: npx wrangler pages dev --d1=my_d1npx wrangler pages dev --d1=my_d1

hanpoloOP•6/3/23, 8:30 PM

do I need to use --persist-to?

hanpoloOP•6/3/23, 8:33 PM

my migration command is npx wrangler d1 migrations apply -e staging my_d1 --localnpx wrangler d1 migrations apply -e staging my_d1 --local

hanpoloOP•6/3/23, 8:35 PM

And my wrangler.toml has:

[env.staging]
d1_databases = [
  { binding = "my_d1", database_name = "my_d1_preview", database_id = <UUID> }
]

[env.staging]
d1_databases = [
  { binding = "my_d1", database_name = "my_d1_preview", database_id = <UUID> }
]

hanpoloOP•6/3/23, 9:31 PM

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

I have upgraded to wrangler 3.0.1 and my previously persisted database no longer works. The error I

Similar Threads

Similar Threads

Similar Threads