right now i get exception: ``D1_ERROR: no such column: country`` despite it being there

DDahoom right now i get exception: ``D1_ERROR: no such column: country`` despite it bei...

M

dunno if this is related, but your table is called

i_clicks

i_clicks

i_clicks

i_clicks while your query is inserting into

interval_clicks

interval_clicks

interval_clicks

interval_clicks?

D

DahoomOP•9/16/23, 8:50 AM

i was going to censor it but gave up after that first line

D

DahoomOP•9/16/23, 8:51 AM

its all interval_clicks, its edited back to normal now

M

Max (@rozenmd)•9/16/23, 9:45 AM

can you add a query that runs

PRAGMA table_xinfo('interval_clicks')

PRAGMA table_xinfo('interval_clicks')

PRAGMA table_xinfo('interval_clicks')

PRAGMA table_xinfo('interval_clicks') prior to your insert, and log the result?

D

DahoomOP•9/16/23, 9:50 AM

  "logs": [
    {
      "message": [
        {
          "success": true,
          "meta": {
            "served_by": "v3-prod",
            "duration": 0.1650210000007064,
            "changes": 0,
            "last_row_id": 0,
            "changed_db": false,
            "size_after": 65536,
            "rows_read": 0,
            "rows_written": 0
          },
          "results": [
            {
              "cid": 0,
              "name": "id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 1,
              "hidden": 0
            },
            {
              "cid": 1,
              "name": "url_id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 2,
              "name": "interval_start",
              "type": "TIMESTAMP",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 3,
              "name": "country",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 4,
              "name": "device_type",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 5,
              "name": "click_count",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": "0",
              "pk": 0,
              "hidden": 0
            }
          ]
        }
      ],
      "level": "log",
      "timestamp": 1694857686407
    }
  ],

  "logs": [
    {
      "message": [
        {
          "success": true,
          "meta": {
            "served_by": "v3-prod",
            "duration": 0.1650210000007064,
            "changes": 0,
            "last_row_id": 0,
            "changed_db": false,
            "size_after": 65536,
            "rows_read": 0,
            "rows_written": 0
          },
          "results": [
            {
              "cid": 0,
              "name": "id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 1,
              "hidden": 0
            },
            {
              "cid": 1,
              "name": "url_id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 2,
              "name": "interval_start",
              "type": "TIMESTAMP",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 3,
              "name": "country",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 4,
              "name": "device_type",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 5,
              "name": "click_count",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": "0",
              "pk": 0,
              "hidden": 0
            }
          ]
        }
      ],
      "level": "log",
      "timestamp": 1694857686407
    }
  ],

  "logs": [
    {
      "message": [
        {
          "success": true,
          "meta": {
            "served_by": "v3-prod",
            "duration": 0.1650210000007064,
            "changes": 0,
            "last_row_id": 0,
            "changed_db": false,
            "size_after": 65536,
            "rows_read": 0,
            "rows_written": 0
          },
          "results": [
            {
              "cid": 0,
              "name": "id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 1,
              "hidden": 0
            },
            {
              "cid": 1,
              "name": "url_id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 2,
              "name": "interval_start",
              "type": "TIMESTAMP",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 3,
              "name": "country",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 4,
              "name": "device_type",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 5,
              "name": "click_count",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": "0",
              "pk": 0,
              "hidden": 0
            }
          ]
        }
      ],
      "level": "log",
      "timestamp": 1694857686407
    }
  ],

  "logs": [
    {
      "message": [
        {
          "success": true,
          "meta": {
            "served_by": "v3-prod",
            "duration": 0.1650210000007064,
            "changes": 0,
            "last_row_id": 0,
            "changed_db": false,
            "size_after": 65536,
            "rows_read": 0,
            "rows_written": 0
          },
          "results": [
            {
              "cid": 0,
              "name": "id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 1,
              "hidden": 0
            },
            {
              "cid": 1,
              "name": "url_id",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 2,
              "name": "interval_start",
              "type": "TIMESTAMP",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 3,
              "name": "country",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 4,
              "name": "device_type",
              "type": "TEXT",
              "notnull": 0,
              "dflt_value": null,
              "pk": 0,
              "hidden": 0
            },
            {
              "cid": 5,
              "name": "click_count",
              "type": "INTEGER",
              "notnull": 0,
              "dflt_value": "0",
              "pk": 0,
              "hidden": 0
            }
          ]
        }
      ],
      "level": "log",
      "timestamp": 1694857686407
    }
  ],

D

DahoomOP•9/16/23, 9:52 AM

I dont see anything out of the ordinary here

D

DahoomOP•9/16/23, 9:52 AM

just confirms that no rows are written and country does exist i guess

M

Max (@rozenmd)•9/16/23, 9:57 AM

yeah that's weird, does writing data directly without the json fns work? like if you hardcode the values

D

DahoomOP•9/16/23, 10:02 AM

OK i had

D1_ERROR: FOREIGN KEY constraint failed

D1_ERROR: FOREIGN KEY constraint failed

D1_ERROR: FOREIGN KEY constraint failed

D1_ERROR: FOREIGN KEY constraint failed, fixed it and now the hardcoded values have been written

D

DahoomOP•9/16/23, 10:04 AM

country exists

D

DahoomOP•9/16/23, 10:06 AM

Isnt the point of JSON query to avoid having to query the data, read it, do what you need to with it in js, then rewrite it again

D

DahoomOP•9/16/23, 10:08 AM

isnt that what IFNULL() is for?

D

DahoomOP•9/16/23, 10:10 AM

lets take a step back, I want it to add a new value within an array within the country column if that value has not been inputted before, if it is then increment that value by 1. Did i do it compeltely wrong in my request?

D

DahoomOP•9/16/23, 10:10 AM

Here it is again so you dont have to scroll:

  const ans = await env.DATABASE.prepare(`INSERT INTO interval_clicks (url_id, interval_start, country, device_type, click_count) VALUES (?1, ?2, json_insert(IFNULL(json_extract(country, '$'), '{}'), '$.?3', IFNULL(json_extract(country, '$.?3'), 0) + 1), json_insert(IFNULL(json_extract(device_type, '$'), '{}'), '$.?4', IFNULL(json_extract(device_type, '$.?4'), 0) + 1), 1) ON CONFLICT(url_id, interval_start) DO UPDATE SET country = json_insert(country, '$.?3', json_extract(country, '$.?3') + 1), device_type = json_insert(device_type, '$.?4', json_extract(device_type, '$.?4') + 1), click_count = click_count + 1; `)
  .bind(url_id, interval_start, country, deviceType).run(); console.log(ans);

  const ans = await env.DATABASE.prepare(`INSERT INTO interval_clicks (url_id, interval_start, country, device_type, click_count) VALUES (?1, ?2, json_insert(IFNULL(json_extract(country, '$'), '{}'), '$.?3', IFNULL(json_extract(country, '$.?3'), 0) + 1), json_insert(IFNULL(json_extract(device_type, '$'), '{}'), '$.?4', IFNULL(json_extract(device_type, '$.?4'), 0) + 1), 1) ON CONFLICT(url_id, interval_start) DO UPDATE SET country = json_insert(country, '$.?3', json_extract(country, '$.?3') + 1), device_type = json_insert(device_type, '$.?4', json_extract(device_type, '$.?4') + 1), click_count = click_count + 1; `)
  .bind(url_id, interval_start, country, deviceType).run(); console.log(ans);

  const ans = await env.DATABASE.prepare(`INSERT INTO interval_clicks (url_id, interval_start, country, device_type, click_count) VALUES (?1, ?2, json_insert(IFNULL(json_extract(country, '$'), '{}'), '$.?3', IFNULL(json_extract(country, '$.?3'), 0) + 1), json_insert(IFNULL(json_extract(device_type, '$'), '{}'), '$.?4', IFNULL(json_extract(device_type, '$.?4'), 0) + 1), 1) ON CONFLICT(url_id, interval_start) DO UPDATE SET country = json_insert(country, '$.?3', json_extract(country, '$.?3') + 1), device_type = json_insert(device_type, '$.?4', json_extract(device_type, '$.?4') + 1), click_count = click_count + 1; `)
  .bind(url_id, interval_start, country, deviceType).run(); console.log(ans);

  const ans = await env.DATABASE.prepare(`INSERT INTO interval_clicks (url_id, interval_start, country, device_type, click_count) VALUES (?1, ?2, json_insert(IFNULL(json_extract(country, '$'), '{}'), '$.?3', IFNULL(json_extract(country, '$.?3'), 0) + 1), json_insert(IFNULL(json_extract(device_type, '$'), '{}'), '$.?4', IFNULL(json_extract(device_type, '$.?4'), 0) + 1), 1) ON CONFLICT(url_id, interval_start) DO UPDATE SET country = json_insert(country, '$.?3', json_extract(country, '$.?3') + 1), device_type = json_insert(device_type, '$.?4', json_extract(device_type, '$.?4') + 1), click_count = click_count + 1; `)
  .bind(url_id, interval_start, country, deviceType).run(); console.log(ans);

D

DahoomOP•9/16/23, 10:20 AM

I think my query is an upsert? it already includes INSERT INTO ... ON CONFLICT ... DO UPDATE SET

Original message was deleted

D

DahoomOP•9/16/23, 10:28 AM

my plan is to have it return an array for the country column, something like: (Country code: clicks)

    {
        "DE": 77,
        "US": 52,
        "CA": 11,
        "AU": 13,
    }

    {
        "DE": 77,
        "US": 52,
        "CA": 11,
        "AU": 13,
    }

    {
        "DE": 77,
        "US": 52,
        "CA": 11,
        "AU": 13,
    }

    {
        "DE": 77,
        "US": 52,
        "CA": 11,
        "AU": 13,
    }

the click_count column is there so I wouldnt have to count up the numbers for each array items + it can be used as a fallback

J

jimmydirt•9/17/23, 5:33 PM

Hi folks, is there any way to develop pages locally against a remote D1 db? It took me quite some time googling error messages and re-reading docs to understand that

wrangler pages dev --d1=X

wrangler pages dev --d1=X

wrangler pages dev --d1=X

wrangler pages dev --d1=X works with a local database that would seem to have a relationship to the "live" one but is really a completely independent DB (correct me if I'm misunderstanding this). This seems unusual to me as a default (and only?) option. I would think that many people would often want to develop against some kind of "live" db without always having to manage a local copy. Am I missing something, is this seen as a bad practice? What is the recommended workflow?

J

jimmydirt•9/17/23, 5:44 PM

It doesn't, I tried, it's not an available option for

wrangler pages dev

wrangler pages dev

wrangler pages dev

wrangler pages dev

Jjimmydirt It doesn't, I tried, it's not an available option for `wrangler pages dev`

H

Hello, I’m Allie!•9/17/23, 5:51 PM

pages dev

pages dev

pages dev

pages dev doesn't support remote anything atm

J

jimmydirt•9/17/23, 6:02 PM

I am using Svelte, this page (https://developers.cloudflare.com/d1/examples/d1-and-sveltekit/) very much makes it sound like you put in your DB name to hit the "live" one (step 4). This page (https://developers.cloudflare.com/d1/learning/local-development/#develop-locally-with-pages) is more clear about the local only part, but it doesn't really describe how to migrate from a remote db. Just trying to understand what the workflow is for developing locally against an up-to-date shared db (e.g. schema changes), just keep creating migrations?

Jjimmydirt I am using Svelte, this page (https://developers.cloudflare.com/d1/examples/d1-a...

H

Hello, I’m Allie!•9/17/23, 6:04 PM

If nothing else, I would store a DB schema local, plus maybe a script to generate seed data for it.

H

Hello, I’m Allie!•9/17/23, 6:05 PM

For a read-only DB, it should be fine to read from production, but otherwise, it probably wouldn’t be a good idea

J

jimmydirt•9/17/23, 6:16 PM

Yeah for sure, in my experience though there would be e.g. Prod, QA, and Dev databases, devs would normally be connected to Dev, which gets migrated from Prod at certain times. They could then see the same data. This is mentioned as a use case for "wrangler dev --persist-to=/path/to/file", which I guess works but I'm just unfamiliar with the concept of devs working against their own local databases (outside of one-off needs), it seems like a lot of extra work and room for error. Is this common, is this a design decision of D1, or just a

--remote

--remote

--remote

--remote functionality that hasn't been added yet?

Jjimmydirt Yeah for sure, in my experience though there would be e.g. Prod, QA, and Dev dat...

H

Hello, I’m Allie!•9/17/23, 6:25 PM

I haven't seen anything about adding it, but that doesn't mean it won't happen. For non-Prod databases, if you deploy your project, you can set it to use a QA/Dev DB instead. And, I would assume that testing a project locally would be at least a little bit faster if the DB was running locally, rather than having to hit the cloud every single time.

J

jimmydirt•9/17/23, 7:00 PM

Yeah, and I can understand the value of having that option, but there's also a lot of value in being able to connect to remote databases (from your dev environment). I feel like there's something I'm missing, because that would seem to me a basic and necessary feature (in line with the concept of the DB as a single source of truth), and some of the docs do make it sound like it's prevented by design. Anyway, thank you, and I will work around it.

CCole Xemi I'm trying to disable foreign keys but the disabling is not working: ``` PRAGMA...

M

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

You can’t disable them - you can

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on to disable temporarily, but you must ensure FK constraints are met. Draft docs: https://silverlock-d1-foreign-keys.cloudflare-docs-7ou.pages.dev/d1/learning/foreign-keys/

Foreign keys · Cloudflare D1 docs

D1 supports defining and enforcing foreign key constraints across tables in a database.

M

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

(Just saw your last message; was out earlier)

I

itstmillertime•9/18/23, 5:07 AM

What's the max db size the team sees D1 achieving when it is out of Alpha?

RRunar This is probably the most frequently asked question here, but sadly a question w...

I

itstmillertime•9/18/23, 6:40 AM

Thank you for your input. 0.5TB is plenty for most apps. What concerns me is other serverless sqlite database providers seem to have a limit at 24gb, and then bump you to the "enterprise" tier

M

Max (@rozenmd)•9/18/23, 7:28 AM

you cannot scale Facebook on D1

If you manage to reach facebook-level success on almost any vendor, that vendor tends to work with you to find a way to scale

Original message was deleted

H

Hello, I’m Allie!•9/18/23, 7:55 AM

A single unified query maybe? If the images are small enough, it might be handy to be able to retrieve everything in one go.

D

dave•9/18/23, 3:02 PM

I'm getting this error when I attempt to run things on a remote database:

  A prepared SQL statement must contain only one statement. [code: 7500]

  A prepared SQL statement must contain only one statement. [code: 7500]

  A prepared SQL statement must contain only one statement. [code: 7500]

  A prepared SQL statement must contain only one statement. [code: 7500]

D

dave•9/18/23, 3:02 PM

but it works locally

D

dave•9/18/23, 3:02 PM

with

--local

--local

--local

--local

MMatt Silverlock Are you using the new backend? Executed as-is for me. ```sh ~/repos/d1 (main) ✔ ...

D

dave•9/18/23, 3:06 PM

How can I check if I'm using the new backend?

┌───────────────────┬──────────────────────────────────────┐
│                   │ removed-removed-removed-removed-     │
├───────────────────┼──────────────────────────────────────┤
│ name              │ removed-worker-d1                    │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-09-09T03:49:34.773Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 6                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 2.59 MB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 533                                  │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 755                                  │
└───────────────────┴──────────────────────────────────────┘

┌───────────────────┬──────────────────────────────────────┐
│                   │ removed-removed-removed-removed-     │
├───────────────────┼──────────────────────────────────────┤
│ name              │ removed-worker-d1                    │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-09-09T03:49:34.773Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 6                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 2.59 MB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 533                                  │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 755                                  │
└───────────────────┴──────────────────────────────────────┘

┌───────────────────┬──────────────────────────────────────┐
│                   │ removed-removed-removed-removed-     │
├───────────────────┼──────────────────────────────────────┤
│ name              │ removed-worker-d1                    │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-09-09T03:49:34.773Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 6                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 2.59 MB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 533                                  │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 755                                  │
└───────────────────┴──────────────────────────────────────┘

┌───────────────────┬──────────────────────────────────────┐
│                   │ removed-removed-removed-removed-     │
├───────────────────┼──────────────────────────────────────┤
│ name              │ removed-worker-d1                    │
├───────────────────┼──────────────────────────────────────┤
│ created_at        │ 2023-09-09T03:49:34.773Z             │
├───────────────────┼──────────────────────────────────────┤
│ version           │ beta                                 │
├───────────────────┼──────────────────────────────────────┤
│ num_tables        │ 6                                    │
├───────────────────┼──────────────────────────────────────┤
│ running_in_region │ ENAM                                 │
├───────────────────┼──────────────────────────────────────┤
│ database_size     │ 2.59 MB                              │
├───────────────────┼──────────────────────────────────────┤
│ read_queries_24h  │ 533                                  │
├───────────────────┼──────────────────────────────────────┤
│ write_queries_24h │ 755                                  │
└───────────────────┴──────────────────────────────────────┘

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

D

dave•9/18/23, 3:06 PM

Did you find a solution for this?

Ddave How can I check if I'm using the new backend? ``` ┌───────────────────┬────────...

I

Isaac McFadyen•9/18/23, 3:23 PM

You are. New is beta and old is alpha.

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

D

dave•9/18/23, 3:31 PM

I found this as a solution: