do you get not authorized or an error about it not existing

V

Victor•11/8/23, 12:32 AM

If I have a uuid stored as binary

BLOB

BLOB

BLOB

BLOB (initially generated with

(randomblob(16))

(randomblob(16))

(randomblob(16))

(randomblob(16))), this works:

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

to get the normal looking utf8 uuid

Kkian do you get not authorized or an error about it not existing

V

Victor•11/8/23, 12:32 AM

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

{
  result: [],
  success: false,
  errors: [ { code: 7500, message: 'no such function: unhex' } ],
  messages: []
}

V

Victor•11/8/23, 12:35 AM

I make it

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

import { randomUUID } from 'node:crypto';

export class Helpers {
    public static get generateUuid() {
        const uuid = randomUUID();
        const uuidHex = uuid.replaceAll('-', '');

        return {
            utf8: uuid,
            forBlob: uuidHex,
        };
    }
}

Then I try to insert it with

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

query: {
    params: [Helpers.generateUuid.forBlob],
    sql: `INSERT INTO some_table (uuid) VALUES (unhex(?))`,
}

K

kianOP•11/8/23, 12:37 AM

unhex isn’t in the version of SQLite that D1 currently uses

Kkian unhex isn’t in the version of SQLite that D1 currently uses

V

Victor•11/8/23, 12:37 AM

what is the version of sqlite that d1 uses?

not authorized to use function: sqlite_version

not authorized to use function: sqlite_version

not authorized to use function: sqlite_version

not authorized to use function: sqlite_version

K

kianOP•11/8/23, 12:42 AM

3.40.1

V

Victor•11/8/23, 12:43 AM

nooooo, the version that added it is literally the next one

3.41.0

3.41.0

V

Victor•11/8/23, 12:43 AM

Woah it even adds

base64()

base64()

too...plzzzzzz update to 3.41.0+

V

Victor•11/8/23, 12:45 AM

In that case, how can I format buffer related data through the REST api to output back as a BLOB binary buffer in D1?

VVictor 😭 nooooo, the version that added it is literally the next one `3.41.0`

K

kianOP•11/8/23, 12:50 AM

There’s an open PR to update it to 3.44.0 so if/when that’s merged and released it’ll be available.

Kkian There’s an open PR to update it to 3.44.0 so if/when that’s merged and released ...

V

Victor•11/8/23, 12:53 AM

Can you send me a link to that PR? I'm not seeing one on

cloudflare/workers-sdk

cloudflare/workers-sdk

K

kianOP•11/8/23, 12:54 AM

The runtime is at cloudflare/workerd

K

kianOP•11/8/23, 12:55 AM

https://github.com/cloudflare/workerd/pull/1374

V

Victor•11/8/23, 12:55 AM

lol I just found it literally same second as you sent it

R

Rick•11/8/23, 7:00 AM

Does the sqllite FTS works on D1? Also, are there any options for text search, if my data is distributed amongst all the D1 dbs in my account?

M

MrXO•11/8/23, 7:02 AM

hi

M

MrXO•11/8/23, 7:02 AM

how I can add more column for table?

MMrXO how I can add more column for table?

M

Matthijs•11/8/23, 8:13 AM

you can use migrations to alter existing tables: https://developers.cloudflare.com/d1/platform/migrations/

Migrations · Cloudflare D1 docs

Database migrations are a way of versioning your database. Each migration is stored as an .sql file in your migrations folder. The migrations folder …

R

Rick•11/8/23, 8:58 AM

Thanks! I guess I will have to add an orchestrator in my api which will take the search query, propagate it over the dbs and then merge the results

L

Lila Rest•11/8/23, 10:16 AM

Hey

Any ETA for D1 going out of beta?

LLila Rest Hey 👋 Any ETA for D1 going out of beta?

G

GuillaumeLakano•11/8/23, 10:54 AM

https://blog.cloudflare.com/d1-open-beta-is-here/
« We’re setting a clear goal: we want to make D1 “generally available” (GA) for production use-cases by early next year (Q1 2024). Although you can already use D1 without a waitlist or approval process, we understand that the GA label is an important one for many when it comes to a database (and as do we). »

RRick Does the sqllite FTS works on D1? Also, are there any options for text search, i...

M

Matt Silverlock•11/8/23, 11:21 AM

FTS5 works in D1.

GGuillaumeLakano https://blog.cloudflare.com/d1-open-beta-is-here/ « We’re setting a clear goal: ...

L

Lila Rest•11/8/23, 11:22 AM

Thanks Guillaume for your answer!
In fact I'm relying on Cloudflare Pages so it'd be awesome to be able to use D1 as an edge database.
The main point is not the GA, but the 2GB limit per database. Is that something that can be increased at that point?

K

kianOP•11/8/23, 11:24 AM

This is a beta-only limitation. The maximum storage per-database, storage per-account and number of databases will automatically increase for paid plans during the course of D1’s public beta.

K

kianOP•11/8/23, 11:24 AM

All limits tagged with beta on https://developers.cloudflare.com/d1/platform/limits/ will be raised over time.

Kkian All limits tagged with beta on <https://developers.cloudflare.com/d1/platform/li...

L

Lila Rest•11/8/23, 11:26 AM

Thanks, any ETA on raising the storage per-database limit?
We cannot take the risk to develop on D1 if the limit is not raised once we go in production (late 2023)

K

kianOP•11/8/23, 11:27 AM

I’m not aware of a specific timeline, although Matt may be able to share one. It’s currently a limit that can’t be raised by exception (which is to say it applies to everyone and isn’t a case-by-case basis limit).

Kkian I’m not aware of a specific timeline, although Matt may be able to share one. It...

L

Lila Rest•11/8/23, 11:51 AM

Thanks, so I'll wait for Matt's answer

R

Ravi kiran•11/8/23, 12:19 PM

Hey guys i just started using cloudflare workers for a project and im using cloudflare D1 as the db. My doubt is regarding D1.
i want a field in a table suppose the users table wherein i want the field to be store multiple strings in an array. i.e ["123","456","789"]. i tried it like as shown below

CREATE TABLE IF NOT EXISTS Study(
_id VARCHAR(20) PRIMARY KEY,
services_provided TEXT[]
);
now i want to query the record based on a value in the array i.e services_provided.
Can anyone please give me the query for this? i have tried to use the array field and the json filed but it does not work.

R

Ravi kiran•11/8/23, 12:26 PM

Can anyone answer this please

K

kianOP•11/8/23, 12:39 PM

SQLite doesn't support arrays natively - you probably want to use JSON instead.

K

kianOP•11/8/23, 12:39 PM

Have you tried

json_each

json_each

? https://stackoverflow.com/a/63653966

R

Ravi kiran•11/8/23, 12:40 PM

nope. i will try using this and get back.

R

Ravi kiran•11/8/23, 12:45 PM

Thanks, this works.

LLila Rest Thanks, any ETA on raising the storage per-database limit? We cannot take the ri...

M

Matt Silverlock•11/8/23, 12:46 PM

What per database size are you looking for?

F

forslunds•11/8/23, 12:58 PM

Myself would love either A) 5GB (good standard size and room/time to adjust) or B) keep 2gb but make sure its easy to make one-db-per-user-bindings

R

Rick•11/8/23, 1:19 PM

For something like sessions, should I be using kv or d1? kv sounds like the obvious choice but I wanted to make sure that is much more performant that d1 for simple lookups

RRick For something like sessions, should I be using kv or d1? kv sounds like the obvi...

G

GuillaumeLakano•11/8/23, 1:21 PM

KV keys need to be loaded a lot of time per minute to be « hot » and fast, so I don't think it's a good idea

RRick For something like sessions, should I be using kv or d1? kv sounds like the obvi...

G

GuillaumeLakano•11/8/23, 1:22 PM

https://developers.cloudflare.com/workers/learning/storage-options/ for storage comparaison

MMatt Silverlock What _per database_ size are you looking for?

L

Lila Rest•11/8/23, 1:23 PM

We are building a SaaS, so ideally the DB should be able to scale infinitely with our userbase and the content they create.
But maybe am I not well understanding how D1 should be properly used? Maybe 1 DB per user would be the good way to do it?

L

Lila Rest•11/8/23, 1:25 PM

Anyway, it looks that even 2GB per users could be reached by our oldest and most intensive users.

GGuillaumeLakano KV keys need to be loaded a lot of time per minute to be « hot » and fast, so I ...

R

Rick•11/8/23, 1:27 PM

Not sure I understood. Since each route request will trigger a session check, wouldn't that mean that each session in my kv will be "hot"?

GGuillaumeLakano KV keys need to be loaded a lot of time per minute to be « hot » and fast, so I ...

M

Matt Silverlock•11/8/23, 1:28 PM

That’s not true. “A lot of times per minute” is definitely an exaggeration.

MMatt Silverlock That’s not true. “A lot of times per minute” is definitely an exaggeration.

G

GuillaumeLakano•11/8/23, 1:34 PM

So may be it's changed since february ?

G

GuillaumeLakano•11/8/23, 1:37 PM

From what I understood of this answer of february, is we need to read at least 2 times in 60 seconds a KV key to be considered as « hot » and be fast. May be it's changed or may be I didn't understood correctly

K

kianOP•11/8/23, 1:43 PM

KV is eventually consistent, with a central stores in NA and EU. When you read a value in location A, it will be cached for at least 60 seconds.

This means that if you read it in Colo A, write it to a new value in Colo B, Colo A's reads will still show the old value for up to 60 seconds.

K

kianOP•11/8/23, 1:44 PM

Compared to Durable Objects, R2 and D1 where they are strongly consistent (read-after-write) and all reads/writes go to the central stores. R2 and D1 may introduce their own replication down the line, which allows you to have a trade-off between consistency and latency to suit your use-case.

K

kianOP•11/8/23, 1:46 PM

The How KV works document still has some references to tiers, similar to https://developers.cloudflare.com/cache/how-to/tiered-cache/, which an experimental version of KV used but isn't currently available.

GGuillaumeLakano From what I understood of this answer of february, is we need to read at least 2...

M

Matt Silverlock•11/8/23, 1:46 PM

There's no such metric. Cached values can remain in the cache after just one read: we don't make guarantees that they will stay in cache all the time if they are infrequently accessed.

MMatt Silverlock There's no such metric. Cached values can remain in the cache after just one rea...

G

GuillaumeLakano•11/8/23, 1:46 PM

Ok, good to known, thank you for the precision!

do you get not authorized or an error about it not existing

Similar Threads

Similar Threads

Similar Threads