Hello The d1 pricing is based on rows read and write. What if I read something but it does not res

M

maj•9/14/25, 11:16 AM

How are the SQL

lower()

lower()

lower()

lower() and

upper()

upper()

upper()

upper() supposed to work with non latin or accented characters?

select lower('Édith Piaf');

select lower('Édith Piaf');

select lower('Édith Piaf');

select lower('Édith Piaf'); returns "Édith piaf" - which obviously isn't lower case. Therefor many of my exact searches fail.

SSb Hello The d1 pricing is based on rows read and write. What if I read something...

I

Isaac McFadyen•9/14/25, 1:21 PM

What if I read something but it does not result in any row (no row exist for that query in the db)

Rows are still read to search for that row, so yes it will count.

I

Isaac McFadyen•9/14/25, 1:22 PM

Either a full table scan (which you should try to avoid as it reads every row and is also slow) or an index read (which is billed like a row read)

IIsaac McFadyen Either a full table scan (which you should try to avoid as it reads every row an...

S

SbOP•9/14/25, 2:04 PM

Can you please explain with some example (using numbers) ?

1. I queried, but no matching row found. What will be the rows read value in it ??
2. I queried, 5 matched out of 1M, what will happen ?

SSb Can you please explain with some example (using numbers) ? 1. I queried, but no...

I

Isaac McFadyen•9/14/25, 2:10 PM

It's going to heavily depend on your queries and DB schema so we won't be able to give a concrete answer, you'll have to make the queries and check the metrics D1 returns.

R

Roman•9/14/25, 3:27 PM

i need some help with understanding how to access a d1 db binding in astro

R

Roman•9/14/25, 3:57 PM

okay i think i got it

SSb Can you please explain with some example (using numbers) ? 1. I queried, but no...

J

James•9/14/25, 4:34 PM

Rows retrieved has nothing to do with rows read.

JJames Rows retrieved has nothing to do with rows read.

S

SbOP•9/14/25, 5:43 PM

So, doesn't it make the system little negative?

Because, to find the rows to be retrieved.
It must scan all the rows (read all the rows to see if they are valid for the query )

And it will exponentially grow the billing as the db grows.

Am I correct?

SSb So, doesn't it make the system little negative? Because, to find the rows to be...

I

Isaac McFadyen•9/14/25, 5:47 PM

It does not scan all the rows if you have an index on the rows.

I

Isaac McFadyen•9/14/25, 5:47 PM

In that case only the index will be searched (much fewer rows read)

R

Roman•9/14/25, 7:06 PM

why cant better-auth just work with d1

RRoman why cant better-auth just work with d1 😭

G

gruntlord6•9/15/25, 12:40 AM

it works with sqlite and drizzle so I imagine you can get it going

G

gruntlord6•9/15/25, 12:40 AM

I always used clerk though

SSb So, doesn't it make the system little negative? Because, to find the rows to be...

L

lambrospetrou•9/15/25, 8:00 AM

I suggest you do a read of this guide: https://developers.cloudflare.com/d1/best-practices/use-indexes/
D1 bills for rows read and written, not returned.
You can significantly decrease the amount of rows read for each query by using indexes and optimizing your queries to avoid scans of entire tables.
Using

EXPLAIN <yourSQLquery>

EXPLAIN <yourSQLquery>

EXPLAIN <yourSQLquery>

EXPLAIN <yourSQLquery> also helps you figure out what SQLite will do and see if you miss indexes.

BTW, most LLMs nowadays are really good with SQL and can tell you how to optimize your queries based on your schemas.

Cloudflare Docs

Use indexes

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) the database has to scan when running a query.

M

maj•9/16/25, 9:35 AM

How accurate are the numbers in the "Queries" section of the "Overview"? I have a query which always shows zero rows read. Yet I know from testing (and logging) that the query is successful and would return data. It must have read some rows. Yet it's always zero. Is this just an analytics issue, rounding error (I think the query would read two rows when successful)?

G

greenface•9/16/25, 9:41 AM

#d1-database

Ggruntlord6 it works with sqlite and drizzle so I imagine you can get it going

R

Roman•9/16/25, 12:42 PM

Will try with drizzle again

RRoman why cant better-auth just work with d1 😭

T

toadilywasted•9/16/25, 2:08 PM

works for me!

T

toadilywasted•9/16/25, 2:08 PM

LMK if you have any specific issues

T

toadilywasted•9/16/25, 2:10 PM

(fwiw this was one of the main things I had to do for compatibility on d1/drizzle, then replace in my better auth drizzle schema all the date [e.g. createdAt] column types)

import { customType } from "drizzle-orm/sqlite-core";

// Custom date type for D1 compatibility - converts Date objects to/from Unix timestamps
// Required for better-auth to work with Cloudflare D1 which doesn't support Date objects
export const D1DateTime = customType<{
  data: Date | null;
  driverData: number | null;
}>({
  dataType() {
    return "integer";
  },
  fromDriver(value: number | null): Date | null {
    return value ? new Date(value * 1000) : null; // Convert from Unix seconds to Date
  },
  toDriver(value: Date | null): number | null {
    return value ? Math.floor(value.getTime() / 1000) : null; // Convert Date to Unix seconds
  },
});

import { customType } from "drizzle-orm/sqlite-core";

// Custom date type for D1 compatibility - converts Date objects to/from Unix timestamps
// Required for better-auth to work with Cloudflare D1 which doesn't support Date objects
export const D1DateTime = customType<{
  data: Date | null;
  driverData: number | null;
}>({
  dataType() {
    return "integer";
  },
  fromDriver(value: number | null): Date | null {
    return value ? new Date(value * 1000) : null; // Convert from Unix seconds to Date
  },
  toDriver(value: Date | null): number | null {
    return value ? Math.floor(value.getTime() / 1000) : null; // Convert Date to Unix seconds
  },
});

import { customType } from "drizzle-orm/sqlite-core";

// Custom date type for D1 compatibility - converts Date objects to/from Unix timestamps
// Required for better-auth to work with Cloudflare D1 which doesn't support Date objects
export const D1DateTime = customType<{
  data: Date | null;
  driverData: number | null;
}>({
  dataType() {
    return "integer";
  },
  fromDriver(value: number | null): Date | null {
    return value ? new Date(value * 1000) : null; // Convert from Unix seconds to Date
  },
  toDriver(value: Date | null): number | null {
    return value ? Math.floor(value.getTime() / 1000) : null; // Convert Date to Unix seconds
  },
});

import { customType } from "drizzle-orm/sqlite-core";

// Custom date type for D1 compatibility - converts Date objects to/from Unix timestamps
// Required for better-auth to work with Cloudflare D1 which doesn't support Date objects
export const D1DateTime = customType<{
  data: Date | null;
  driverData: number | null;
}>({
  dataType() {
    return "integer";
  },
  fromDriver(value: number | null): Date | null {
    return value ? new Date(value * 1000) : null; // Convert from Unix seconds to Date
  },
  toDriver(value: Date | null): number | null {
    return value ? Math.floor(value.getTime() / 1000) : null; // Convert Date to Unix seconds
  },
});

Ttoadilywasted LMK if you have any specific issues

R

Roman•9/16/25, 2:41 PM

Migrations, because better auth wants an exported auth object, which is not possible with how bindings with d1 work

Ttoadilywasted (fwiw this was one of the main things I had to do for compatibility on d1/drizzl...

J

James•9/16/25, 4:42 PM

You’re storing seconds (Math.floor(ms / 1000)) instead of milliseconds. That’s fine, but make sure it matches the rest of your system. Some Drizzle examples with SQLite use milliseconds since Date.now() is in ms. Seconds is more compact but can trip you up.

Ttoadilywasted (fwiw this was one of the main things I had to do for compatibility on d1/drizzl...

J

James•9/16/25, 4:44 PM

Also in your D1 schema if you specify integer in your drizzle schema with the mode of timestamp_ms, you woudln't need this hack

JJames Also in your D1 schema if you specify integer in your drizzle schema with the mo...

T

toadilywasted•9/16/25, 4:46 PM

really? I couldnt get it to work before, maybe fixed now, will try again. would be helpful. back when I added this I ran into some issue deep in drizzle that the Date was not properly being constructed if I did it another way

Llambrospetrou Hey folks, for those that don't read changelogs, we documented the automatic ret...

T

Tony Leung•9/17/25, 9:11 AM

I knew it, sometimes D1 hiccuped for a second and back to normal with some cryptic SQLITE ERROR. So i can now confirm that it's not my sh*tty code that make that problem. One less thing to think about, 999999 more

TTony Leung I knew it, sometimes D1 hiccuped for a second and back to normal with some crypt...

L

lambrospetrou•9/17/25, 9:25 AM

SQLITE_ERROR

SQLITE_ERROR

SQLITE_ERROR

SQLITE_ERROR errors are coming from SQLite itself, so those are probably not fixable by retries. Retries improve issues like networking hiccups, or servers going down, or other transient things.
Retries don't fix broken queries. If you have specific SQLITE_ERRORs you get, then we can see what they mean.

Llambrospetrou `SQLITE_ERROR` errors are coming from SQLite itself, so those are probably not f...

T

Tony Leung•9/17/25, 9:28 AM

sorry for making an unfunny joke... we haven't seen much cryptic things like that since the post-alpha D1. If we will record more logs and report back to you as soon as I saw one

T

Tony Leung•9/17/25, 9:30 AM

btw is there any progress on this: https://github.com/cloudflare/workers-sdk/issues/5438

GitHub

🚀 Feature Request: Allow turning off foreign key check while mig...

Some table altering tasks in SQLite cannot be achieved without replacing it with a new table (by dropping - recreating the table) (like adding foreign keys, changing primary keys, updating column t...

T

Tony Leung•9/17/25, 9:32 AM

i guess this is not a trivial problem to fix

TTony Leung btw is there any progress on this: https://github.com/cloudflare/workers-sdk/iss...

L

lambrospetrou•9/17/25, 9:33 AM

Import/export/migrations is a big bucket of things we want to start tackling soon.

Llambrospetrou Import/export/migrations is a big bucket of things we want to start tackling soo...

L

Larry•9/17/25, 11:44 AM

What I really want is as simple and flexible as disconnect a replica and save the .sqlite file in R2.

LLarry What I really want is as simple and flexible as disconnect a replica and save th...

L

lambrospetrou•9/17/25, 11:59 AM

Yeah, this flow is among the options we discuss. It's a matter of resourcing

L

Larry•9/17/25, 12:52 PM

I also want whatever you do to come to DOs also

G

gruntlord6•9/17/25, 1:43 PM

I just want dynamic bindings, I don't always want the full complexity of a do in every case that I want accessible storage

Ggruntlord6 I just want dynamic bindings, I don't always want the full complexity of a do in...

L

lambrospetrou•9/17/25, 2:00 PM

Have you checked the https://github.com/cloudflare/actors library? If you need to be able to create as many DBs as you want, then this gets you quite far with little code. You don't need to write a lot of code.

You lose the import/export/wrangler/D1 UI tooling, though.

Llambrospetrou Have you checked the https://github.com/cloudflare/actors library? If you need t...

G

gruntlord6•9/17/25, 4:43 PM

theres still a lot of edge cases you have with durable objects, the tooling being one of them. I can make tooling per project but D1 has much more along the lines of batteries included tools. Being able to dynamically bind solves a lot more issues

Ggruntlord6 theres still a lot of edge cases you have with durable objects, the tooling bein...

L

lambrospetrou•9/17/25, 4:50 PM

Yeah the batteries included things are what gradually I would like us (personal opinion) to also make available for DOs, and then the choice is simpler. If D1 got dynamic bindings then it would be 90% identical to DOs, and duplicated effort from all fronts. But, yeah, everything you say is a common internal debate we are having.

Y

YYS•9/18/25, 1:53 PM

I'm using D1 for an app that has 5 tables, 4 of them have a foreign key reference to table1. Now I'd like to delete a few columns in table1, but since D1 does not support ALTER TABLE DROP COLUMN, the only way to accomplish that is to create a new table, copy the data and then replace the original table. However, since the table is referenced by foreign key and D1 does not support PRAGMA foreign_keys=OFF it fails to delete and recreate it. Can you please advise what is the path forward for this scenario?

E

Ehbraheem•9/18/25, 7:19 PM

Hi @lambrospetrou

Does D1 has SQLite vector extension support

EEhbraheem Hi @lambrospetrou Does D1 has SQLite vector extension support

H

Hard@Work•9/18/25, 9:42 PM

?pings

S

SuperHelpflare•9/18/25, 9:42 PM

Please do not ping community members for non-moderation reasons. Doing so will not solve your issue faster and will make people less likely to want to help you.

HHard@Work ?pings

E

Ehbraheem•9/18/25, 9:46 PM

I’m asking if D1 supports the SQLite vector type and it’s corresponding HNSW index for vector similarity searches

EEhbraheem I’m asking if D1 supports the SQLite vector type and it’s corresponding HNSW ind...

C

Chaika•9/18/25, 10:05 PM

yea, and he was saying not to ping people

C

Chaika•9/18/25, 10:05 PM

there's docs about the supported extensions here: https://developers.cloudflare.com/d1/sql-api/sql-statements/#supported-sqlite-extensions (no vector support)
Cloudflare has a dedicated vector storage db: https://developers.cloudflare.com/vectorize/

CChaika there's docs about the supported extensions here: https://developers.cloudflare....

E

Ehbraheem•9/18/25, 10:07 PM

Oh I missed that. Thanks for the link.

Vectorize as a funny pricing option for our use case and we are already using direct SQLite embedding with great performance

N

Need_Not•9/19/25, 9:44 PM

occasionally i'll get the error

cause: Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1031

cause: Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1031

cause: Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1031

cause: Error: D1_ERROR: Failed to parse body as JSON, got: error code: 1031 and i have no idea what causes it. I restart my server an it stops doing it

N

Need_Not•9/19/25, 9:46 PM

sometimes it's the session table, this case it was the folders table. I don't even know if its' something i'm doing or related to the table.

NNeed_Not occasionally i'll get the error `cause: Error: D1_ERROR: Failed to parse body as...

L

lambrospetrou•9/19/25, 11:08 PM

Do you get this through the REST API or through the Worker API?
The error

Failed to parse body as JSON

Failed to parse body as JSON

Failed to parse body as JSON

Failed to parse body as JSON is thrown when the request body is not a valid JSON, before even validating the actual contents.

N

Need_Not•9/19/25, 11:09 PM

Worker... I'm using drizzle just

db = drizzle(env.DB)

db = drizzle(env.DB)

db = drizzle(env.DB)

db = drizzle(env.DB)

Hello The d1 pricing is based on rows read and write. What if I read something but it does not res

Similar Threads

Similar Threads

Similar Threads