Hi guys, new dev here. I am using hono with Drizzle as ORM and d1 sqlite as database and having trou

D

Dahoom•9/15/23, 7:11 AM

check out the blog here

D

Dahoom•9/15/23, 7:11 AM

https://blog.cloudflare.com/d1-turning-it-up-to-11/

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

D

Dahoom•9/15/23, 7:12 AM

"We’ve also not yet started to optimize our overall write throughput, and so expect D1 to only get faster here."

D

Dahoom•9/15/23, 7:13 AM

go ahead and test it out yourself, now that you can bind d1 to a worker you can easily create a benchmark for write performance in a couple minutes

D

Dahoom•9/15/23, 7:15 AM

heard about it for the first time yesterday but other than seeing the pricing, no I have not tried it

D

Dahoom•9/15/23, 7:17 AM

did you try it?

Ccopper @WalkerSyed581 did you try at drizzle's issues and discussion on gh?

W

walkersyed581OP•9/15/23, 7:18 AM

Ah... No. Sorry. Looked into it now and it has something to do with drizzle transactions. My bad

D

Dahoom•9/15/23, 7:19 AM

hows the w/r speed?

D

Dahoom•9/15/23, 7:24 AM

I'm testing it out, thanks for the recommendation

Wwalkersyed581 Hi guys, new dev here. I am using hono with Drizzle as ORM and d1 sqlite as data...

M

Max (@rozenmd)•9/15/23, 7:59 AM

Remove the transaction - we're already doing that for you

Ccopper reads are very fast b/c of the free rpelication

M

Max (@rozenmd)•9/15/23, 7:59 AM

there's no replication yet

M

Max (@rozenmd)•9/15/23, 8:01 AM

ah

M

Max (@rozenmd)•9/15/23, 8:05 AM

I personally don't know since I mainly work on D1's "frontend", but D1 isn't exactly pitched as a write-heavy db

M

Max (@rozenmd)•9/15/23, 8:06 AM

there's fun stuff we could be doing with queues to smooth-out the load though

E

EvanPiro•9/15/23, 2:36 PM

How would I handle updating a table that exists in D1? The following is failing:

ALTER TABLE transcript ADD COLUMN IF NOT EXISTS date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE transcript ADD COLUMN IF NOT EXISTS date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE transcript ADD COLUMN IF NOT EXISTS date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE transcript ADD COLUMN IF NOT EXISTS date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

D

dave•9/15/23, 4:35 PM

Does D1 optimize duplicate queries that are in the same

.batch(...)

.batch(...)

.batch(...)

.batch(...)?

Original message was deleted

E

EvanPiro•9/15/23, 4:46 PM

I just found

wrangler d1 migrations

wrangler d1 migrations

wrangler d1 migrations

wrangler d1 migrations which is essentially what I was trying to do so I no longer need that query.

Ddave Does D1 optimize duplicate queries that are in the same `.batch(...)`?

M

Matt Silverlock•9/15/23, 8:49 PM

They are all sent in the same network call, so there is a performance win

D

Dahoom•9/16/23, 6:24 AM

Can i get some help with a JSON query? It's been very difficult for me to debug what exactly is wrong with it.
I'm logging click analytics data into a table in 5 minute intervals

CREATE TABLE IF NOT EXISTS interval_clicks (
  id INTEGER PRIMARY KEY,
  url_id INTEGER,
  interval_start TIMESTAMP, -- Rounded to the nearest 5-minute mark
  country TEXT,
  device_type TEXT,
  click_count INTEGER DEFAULT 0,
  FOREIGN KEY(url_id) REFERENCES url(id)
);

CREATE TABLE IF NOT EXISTS interval_clicks (
  id INTEGER PRIMARY KEY,
  url_id INTEGER,
  interval_start TIMESTAMP, -- Rounded to the nearest 5-minute mark
  country TEXT,
  device_type TEXT,
  click_count INTEGER DEFAULT 0,
  FOREIGN KEY(url_id) REFERENCES url(id)
);

CREATE TABLE IF NOT EXISTS interval_clicks (
  id INTEGER PRIMARY KEY,
  url_id INTEGER,
  interval_start TIMESTAMP, -- Rounded to the nearest 5-minute mark
  country TEXT,
  device_type TEXT,
  click_count INTEGER DEFAULT 0,
  FOREIGN KEY(url_id) REFERENCES url(id)
);

CREATE TABLE IF NOT EXISTS interval_clicks (
  id INTEGER PRIMARY KEY,
  url_id INTEGER,
  interval_start TIMESTAMP, -- Rounded to the nearest 5-minute mark
  country TEXT,
  device_type TEXT,
  click_count INTEGER DEFAULT 0,
  FOREIGN KEY(url_id) REFERENCES url(id)
);

For country and device type im going for a format of something like this, for example for country:

{"US":30,"FR":10,"DE":5}

{"US":30,"FR":10,"DE":5}

{"US":30,"FR":10,"DE":5}

{"US":30,"FR":10,"DE":5} with each value being incremented by 1 once someone from that country clicks the url within that same 5 minute interval. This is the script I'm using in a worker

  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

Dahoom•9/16/23, 6:26 AM

right now i get exception:

D1_ERROR: no such column: country

D1_ERROR: no such column: country

D1_ERROR: no such column: country

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

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

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

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

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

D

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

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

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

I dont see anything out of the ordinary here

D

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

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

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

country exists

Hi guys, new dev here. I am using hono with Drizzle as ORM and d1 sqlite as database and having trou

Similar Threads

Similar Threads

Similar Threads