I have tried to use $[-1] , but it doesn't work 😅

W

WillOP•1/9/24, 6:59 AM

solved it

W

WillOP•1/9/24, 6:59 AM

UPDATE daily SET schedule = json_insert(schedule,'$[' || json_array_length(schedule) || ']','16.00') WHERE day = 1

UPDATE daily SET schedule = json_insert(schedule,'$[' || json_array_length(schedule) || ']','16.00') WHERE day = 1

UPDATE daily SET schedule = json_insert(schedule,'$[' || json_array_length(schedule) || ']','16.00') WHERE day = 1

UPDATE daily SET schedule = json_insert(schedule,'$[' || json_array_length(schedule) || ']','16.00') WHERE day = 1

Kkian Hovers around 40-50ms personally, sometimes as low as 20ms.

G

Gunnar•1/9/24, 11:16 AM

Hi, when using D1, would it make sense to use Cache-Aside pattern with KV?
Cache-Aside pattern:
"Load data on demand into a cache from a data store. This can improve performance and also helps to maintain consistency between data held in the cache and data in the underlying data store."
https://learn.microsoft.com/en-us/azure/architecture/patterns/cache-aside

Cache-Aside pattern - Azure Architecture Center

Learn how to load data on demand into a cache, which can improve performance and maintain consistency between cached data and the underlying data store.

G

Gunnar•1/9/24, 11:56 AM

Are there any D1 Rust examples out there? I only see examples in TS:
https://developers.cloudflare.com/d1/examples/d1-and-remix/

Query D1 from Remix · Cloudflare D1 docs

Remix is a full-stack web framework that operates on both client and server. You can query your D1 database(s) from Remix using Remix’s data loading …

GGunnar Are there any D1 Rust examples out there? I only see examples in TS: https://dev...

M

Max (@rozenmd)•1/9/24, 1:11 PM

https://github.com/cloudflare/workers-rs#d1-databases

S

Sam•1/9/24, 2:47 PM

Does D1 have any implementation for Levenshtein? (like postgres does with fuzzystrmatch). Thanks

WWill I have tried to use $[-1] , but it doesn't work 😅

J

Jason Hostetter•1/9/24, 4:51 PM

You can append to an array using ‘$[#]’ to denote the insert position

J

Jason Hostetter•1/9/24, 4:51 PM

https://www.sqlite.org/json1.html

J

Jason Hostetter•1/9/24, 4:51 PM

See section 3.2

SSam Does D1 have any implementation for Levenshtein? (like postgres does with fuzzys...

M

Max (@rozenmd)•1/9/24, 5:51 PM

not exposed as a function, but I think FTS5 uses it under the hood: https://www.sqlite.org/fts5.html

M

Matt Silverlock•1/9/24, 7:09 PM

Sharing here too: a neat TV show by season rating viewer built on D1 - https://discord.com/channels/595317990191398933/783765338692386886/1194047275681452123

code at https://github.com/docyx/imdb-table

GitHub

GitHub - docyx/imdb-table: IMDb rating tables and watch times for T...

IMDb rating tables and watch times for TV shows. Contribute to docyx/imdb-table development by creating an account on GitHub.

G

Gagan Suie•1/9/24, 8:53 PM

if i have an array of primary keys, how do i search using IN?
will this work? or do i need to format the array to a comma delimited string?

            const { results: usersFromUserIds } = await this.env.DB.prepare(

            const { results: usersFromUserIds } = await this.env.DB.prepare(

            const { results: usersFromUserIds } = await this.env.DB.prepare(

            const { results: usersFromUserIds } = await this.env.DB.prepare(

SELECT *
FROM user
WHERE _id IN (?1)


            )
                .bind(uniqueUserIds)
                .all()


            )
                .bind(uniqueUserIds)
                .all()


            )
                .bind(uniqueUserIds)
                .all()


            )
                .bind(uniqueUserIds)
                .all()

A

ALECKS•1/9/24, 9:03 PM

Trying to understand how local D1 runs a bit and I'm a bit confused. To run D1 locally it seems you have two options:

as part of
```
wranger dev
```
wranger dev
```
wranger dev
```
wranger dev
using miniflare

I'm trying to find a way to have my local D1 instance run it, but be able to query it from a separate process. I thought I could start it using #1, and connect to it from another process using the D1 Client via miniflare, but this doesn't seem to work.

How can I achieve this?

K

kian•1/9/24, 9:12 PM

D1 is just SQLite built into the Workers runtime, and that's what

wrangler dev

wrangler dev

is - running a Worker.

K

kian•1/9/24, 9:12 PM

Miniflare uses the runtime as well.

A

ALECKS•1/9/24, 9:34 PM

Yeah i just wasn't sure how to connect to a running D1 instance from another node process. But I figured it out. You can specific the DB1 database details in the miniflare config object. This worked for me, reference if others need it:

  const mf = new Miniflare({
    modules: true,
    script: "",
    d1Persist: "PATH_TO_PROJECT/.wrangler/state/v3/d1",
    d1Databases: { DB: "DB_ID_HERE" },
  });
  const db = await mf.getD1Database("DB");
  const out = await db.prepare("SELECT name FROM sqlite_schema").raw();

  const mf = new Miniflare({
    modules: true,
    script: "",
    d1Persist: "PATH_TO_PROJECT/.wrangler/state/v3/d1",
    d1Databases: { DB: "DB_ID_HERE" },
  });
  const db = await mf.getD1Database("DB");
  const out = await db.prepare("SELECT name FROM sqlite_schema").raw();

  const mf = new Miniflare({
    modules: true,
    script: "",
    d1Persist: "PATH_TO_PROJECT/.wrangler/state/v3/d1",
    d1Databases: { DB: "DB_ID_HERE" },
  });
  const db = await mf.getD1Database("DB");
  const out = await db.prepare("SELECT name FROM sqlite_schema").raw();

  const mf = new Miniflare({
    modules: true,
    script: "",
    d1Persist: "PATH_TO_PROJECT/.wrangler/state/v3/d1",
    d1Databases: { DB: "DB_ID_HERE" },
  });
  const db = await mf.getD1Database("DB");
  const out = await db.prepare("SELECT name FROM sqlite_schema").raw();

A

ALECKS•1/9/24, 9:44 PM

What's the difference between the local db and the preview db (

preview_database_id

preview_database_id

preview_database_id

preview_database_id in wrangler.toml)?

C

Chaika•1/9/24, 9:47 PM

preview_database_id is used when wrangler dev --remote is used, and is a real D1 DB -- just only used in dev

CChaika preview_database_id is used when wrangler dev --remote is used, and is a real D...

A

ALECKS•1/9/24, 9:49 PM

I see, so sharing state with other devs would be a usecase

MMatt Silverlock Keep in mind that each Dynamo replica adds to your storage costs + adds addition...

N

Negative•1/10/24, 2:24 AM

Sorry to bring up an old convo... but I'm curious if there will be options to disable replication? Say I only want my data to stay in the EU? I know there is an entire data privacy suite dedicated to this stuff, but we're trying to start off a bit smaller than enterprise if possible.

K

K•1/10/24, 5:13 AM

There are location hints but there is no mentioning about what happens when replication will be turned on.

NNegative Sorry to bring up an old convo... but I'm curious if there will be options to di...

K

K•1/10/24, 5:13 AM

https://developers.cloudflare.com/d1/learning/data-location/

Data location · Cloudflare D1 docs

Learn how the location of data stored in D1 is determined, including where the leader is placed and how you optimize that location based on your …

NNegative Sorry to bring up an old convo... but I'm curious if there will be options to di...

M

Max (@rozenmd)•1/10/24, 7:52 AM

It'll be possible to limit replication within an area (like the EU for GDPR)

W

WillOP•1/10/24, 9:17 AM

I tried this query to check if it's using index or not

EXPLAIN QUERY PLAN select * from professional_list where business_id = "9984587b-8505-4737-8b83-309c7b1642ab" AND professional_id = "a67a2dfd-3128-4667-8605-6b1fbc2f768b"

EXPLAIN QUERY PLAN select * from professional_list where business_id = "9984587b-8505-4737-8b83-309c7b1642ab" AND professional_id = "a67a2dfd-3128-4667-8605-6b1fbc2f768b"

EXPLAIN QUERY PLAN select * from professional_list where business_id = "9984587b-8505-4737-8b83-309c7b1642ab" AND professional_id = "a67a2dfd-3128-4667-8605-6b1fbc2f768b"

EXPLAIN QUERY PLAN select * from professional_list where business_id = "9984587b-8505-4737-8b83-309c7b1642ab" AND professional_id = "a67a2dfd-3128-4667-8605-6b1fbc2f768b"

W

WillOP•1/10/24, 9:17 AM

but I only get this as the result

W

WillOP•1/10/24, 9:17 AM

implying only professional_id is used as the index

W

WillOP•1/10/24, 9:17 AM

while business_id is indexed too

W

WillOP•1/10/24, 9:18 AM

and it's the left most column

W

WillOP•1/10/24, 9:18 AM

is this an expected behavior?

K

kian•1/10/24, 9:21 AM

https://www.sqlite.org/queryplanner.html

K

kian•1/10/24, 9:21 AM

Take a look at the Multiple AND-Connected WHERE-Clause Terms heading

K

kian•1/10/24, 9:22 AM

The sections below that are also relevant.

W

WillOP•1/10/24, 9:35 AM

Thanks!

W

WillOP•1/10/24, 9:35 AM

I get where I was wrong now

W

WillOP•1/10/24, 9:38 AM

I created an index for each column

W

WillOP•1/10/24, 9:38 AM

while the correct way should be making one index but covering two column

S

SumitK0069•1/10/24, 9:55 AM

hello everyone i am new here i just to know can anyone help me setup my react project with D1Database

P

Ping for toast•1/10/24, 1:13 PM

Is there any way to search a D1 database?

P

Ping for toast•1/10/24, 1:14 PM

Ah https://www.sqlite.org/fts5.html

P

Ping for toast•1/10/24, 1:17 PM

Also what is the recommended use case for D1? Because my databases are usually much larger than 2gb

P

Ping for toast•1/10/24, 1:23 PM

The 50,000 database limit is interesting, some of my products are largely single player so this could work, but ideally there is some first class support for this (a “group”, and again the 50,000 is a scary limit, even though im not close to hitting this)

P

Ping for toast•1/10/24, 1:23 PM

What I’m thinking is that I’m not the target customer, but I’m curious who that is

PPing for toast Also what is the recommended use case for D1? Because my databases are usually m...

K

kian•1/10/24, 1:27 PM

This is a beta limitation, but there’s no estimate on what the cap will be in GA.

Kkian This is a beta limitation, but there’s no estimate on what the cap will be in GA...

P

Ping for toast•1/10/24, 1:35 PM

obviously it's safe to assume there will be a cap, so the question is just what sort of applications would a customer not be worried about hitting the cap in?

PPing for toast obviously it's safe to assume there *will* be a cap, so the question is just wha...

M

Max (@rozenmd)•1/10/24, 2:17 PM

the underlying tech (DOs) is currently being used by services doing real-time stuff with 200 million monthly active users, it scales really well if you can figure out the right way to split up your schema

Original message was deleted

M

Max (@rozenmd)•1/10/24, 2:18 PM

basically this

Original message was deleted

M

Max (@rozenmd)•1/10/24, 4:19 PM

not yet but is on our radar

K

kian•1/10/24, 5:09 PM

I'd say I wonder if it's just increased load from scheduled Workers kicking off at that time as well but that wouldn't have been a 'since Monday' thing

I have tried to use $[-1] , but it doesn't work 😅

Similar Threads

Similar Threads

Similar Threads