hi! new to D1. do we need to do LIMIT 1 if we're already doing .first()? will it be faster if we do

Kkian https://discord.com/channels/595317990191398933/992060581832032316/1174472811927...

R

Rakhim•1/8/24, 10:18 AM

Thanks! Is there an estimate of the future upper limit? I am working on a product which requires a constant increase in data storage, and was wondering which ceiling I should design for.

GGagan Suie hi! new to D1. do we need to do LIMIT 1 if we're already doing .first()? will it...

M

Max (@rozenmd)•1/8/24, 10:32 AM

.first()

.first()

.first()

.first() returns the first result after fetching the entire query result (https://github.com/cloudflare/workerd/blob/main/src/cloudflare/internal/d1-api.ts#L231-L257), explicitly adding

LIMIT 1

LIMIT 1

LIMIT 1

LIMIT 1 can be significantly more efficient if you know there are lots of results

A

Amr Yasser•1/8/24, 1:55 PM

Can d1 be used outside workers?
from node.js with express for example

Z

zegevlier•1/8/24, 1:56 PM

While you could, it's generally not recommended. It is much slower than if you use it via workers.

Z

zegevlier•1/8/24, 1:57 PM

Although if you just need it for administration stuff, that should be fine to do via the API

A

Amr Yasser•1/8/24, 2:05 PM

I am using it with next-auth so I was wondering how to use it out of workers

AAmr Yasser I am using it with next-auth so I was wondering how to use it out of workers

M

Max (@rozenmd)•1/8/24, 2:22 PM

next-auth doesn't have to be out of workers btw https://github.com/cloudflare/next-on-pages/tree/main/packages/next-on-pages

G

Gunnar•1/8/24, 2:29 PM

hi, what's your typical latency in d1-beta?

K

kian•1/8/24, 2:55 PM

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

RraRaRa Hi everyone. Is it possible to use D1 outside of Workers? E.g. if I run NextJS i...

M

Max (@rozenmd)•1/8/24, 5:00 PM

You can write your own Worker that exposes an API to NextJS: https://github.com/elithrar/http-api-d1-example

M

Max (@rozenmd)•1/8/24, 5:01 PM

using the public API directly also works, but isn't as performant

X

Xazu•1/8/24, 6:28 PM

Guys, can i use data from d1 in getStaticPaths on Astro framework?

W

Will•1/9/24, 6:50 AM

I am trying to add an element to an array

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

but got unrecognized token

W

Will•1/9/24, 6:51 AM

how can I insert the element without knowing the array length?

W

Will•1/9/24, 6:54 AM

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

W

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

solved it

W

Will•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 SuieOP•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

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

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

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

but I only get this as the result

hi! new to D1. do we need to do LIMIT 1 if we're already doing .first()? will it be faster if we do

Similar Threads