also whats the storage limit for D1 ? curious if you have people running multiple TB at ease with it

Iiska also whats the storage limit for D1 ? curious if you have people running multipl...

I

10GB per database currently. The goal with D1 is to create many smaller DBs (i.e. one DB per customer, one DB per user, etc) rather than a single monolithic DB.

I

Isaac McFadyen•8/10/24, 7:01 PM

See here for more details: https://canary.discord.com/channels/595317990191398933/992060581832032316/1157239450741526560

I

iskaOP•8/10/24, 7:07 PM

alright that changes thing up a bit but 1DB per customer say you have 50k users because you are a free app but only 10k are paying

you wouldnt want to create a DB for non paying users, a waste
2.having a db per customer makes aggregation queries such as grouped statistics for example a bit complicated
say you run multiples applications, you would hit rather quickly the limit of i think i saw 50k db

I

iskaOP•8/10/24, 7:29 PM

i guess i could shard something like this:

App1:

Free Users:
- app1_free_shard_1 (1GB D1 DB)
- app1_free_shard_2 (1GB D1 DB)
  ...
Paid Users:
- app1_paid_shard_1 (5GB D1 DB)
- app1_paid_shard_2 (5GB D1 DB)
  ...
Enterprise:
- app1_tenant_bigcorp (10GB D1 DB)
- app1_tenant_shared_1 (10GB D1 DB for multiple smaller tenants)
  ...
Analytics:
- app1_analytics (10GB D1 DB)

but that includes a whole lot of infra dev and complex logic

I

iskaOP•8/10/24, 7:30 PM

monolithic db are good because they are easy to start with , then you start sharding later in the road but starting to shard right off the boat given D1 limit seems a no brainer

wonder if someone already created some library to achieve something similar

Ccopper btw, how many secs did the entire delete in those 20 table at the end? btw2, D1 ...

V

Vikash•8/11/24, 2:13 PM

It’s resolved after creating index on all foreign keys

I

iskaOP•8/13/24, 9:40 AM

surely, i wish there was a library that offered the orchestration of sharding D1 dbs, with features like auto resharding, migration sharding, visual interface , would be so valuable to workers D1 eco system

H

hendrixString•8/13/24, 11:49 AM

Hi there, a quick question. Why doesn't the REST-API of D1 enables a batch of parameterized statements (it only allows static statements joined by a semicolon, which is SQL Injection vulnerable). Any plans as to when this will be supported ? The way it is supported with Workers

S

sandulat•8/13/24, 1:31 PM

Hey! I'm running a simple SQL query with D1 (paid plan) and Drizzle every minute in a Worker's CRON job:

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

await db
    .delete(schema.authRequests)
    .where(
      lte(
        schema.authRequests.createdAt,
        dateToUnixepoch(subMinutes(new Date(), 3)),
      ),
    );

The query has been running thousands of times without any issues, but today I encountered this random error:

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

D1_ERROR: D1 storage operation exceeded timeout which caused the object to be reset.

The

authRequests

authRequests

authRequests

authRequests table is empty 99% of the time, or occasionally contains just a few rows (<20). Could this be an issue on Cloudflare's side?

Here's the

createdAt

createdAt

column definition:

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

  createdAt: integer("created_at")
    .default(sql`(unixepoch())`)
    .notNull()

It has no indexes, but I find it hard to believe that's the problem.

A

Abensur•8/13/24, 9:51 PM

Anyone tried the request form to increase "Maximum characters (bytes) in a LIKE or GLOB pattern"?

A

Abensur•8/13/24, 9:52 PM

Maybe I should use eq instead of like

A

Abensur•8/13/24, 9:53 PM

yeah, nvm, eq worked just fine, wrong use case for like

0

0xwei_hup•8/14/24, 5:25 AM

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

--preview boolean optional
Execute commands/files against a preview D1 database (as defined by preview_database_id in Wrangler.toml).

--local boolean(default: true) optional
Execute commands/files against a local database for use with wrangler dev.

hey guys, would like to ask what's the difference for local and preview actually? I only know that local is stored at your local

.wrangler/state/v3/d1/miniflare-d1databaseobject/...

.wrangler/state/v3/d1/miniflare-d1databaseobject/...

directory but what's preview?

I

iskaOP•8/14/24, 6:33 AM

Is D1 going to support auto scaling some day so user doesn’t have to deal manually with sharding ? Like how long can we estimate this feature to be ready approximately less than a year from now or no eta at all ?

00xwei_hup ```--preview boolean optional Execute commands/files against a preview D1 databa...

H

Hello, I’m Allie!•8/14/24, 6:01 PM

Preview runs against a real D1 DB

J

jacob•8/14/24, 6:04 PM

Are D1 errors just presented as a regular "Error"? How should I handle a scenario where I want to check specifically if the error in my catch block is due to a unique constraint failing

J

jacob•8/14/24, 6:06 PM

I'm effectively trying to do something like this ..

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

try {
  await this.LandmarkRepository.addLandmark(label, locations[0]);
} catch (e) {
  if (e instanceof SomeUniqueConstraintError) {
            
  }
}

A

arch•8/14/24, 8:32 PM

How do people prefer to manage their Workers + D1 schemas? So far I've been relying on just SQL files I manually load in. Prefer not using an ORM in most cases :p

J

jdruwe•8/14/24, 9:58 PM

Guys, I have a question regarding pushing a lot of data into D1. Basically I want to insert around 300K records (based on data from a CSV file I am passed). I build a UI using Hono inside a worker but I am running into the following error: "Exceeded CPU Time Limits", so to me it seems like a worker is not the best solution. What other option can you suggest to me? Could I just insert the data using the REST api? I just noticed there is also an option for: https://developers.cloudflare.com/workers/testing/local-development/#develop-using-remote-resources-and-bindings. It also results the same error, isn't that weird as I am executing the logic locally? For now I think something like: $ npx wrangler d1 execute example-db --remote --file=users_export.sql is better

A

Ashish Rawat•8/15/24, 7:15 AM

D1 is not usable without sharding. Just for mini projects it's good. I want to know is there any lib to shard d1 with workers.

AAshish Rawat D1 is not usable without sharding. Just for mini projects it's good. I want to k...

I

iskaOP•8/15/24, 9:24 AM

im using drizzle orm to avoid vendor lock in and will move to aws rds whenever i hit 10 gb limit, dealing with sharding is a hassle , will wait for an auto scale solution from cloudflare

I

iskaOP•8/15/24, 9:24 AM

i m building a product , dont want to deal with infra too much

R

rossanmol•8/15/24, 5:26 PM

Getting this error not authorized: SQLITE_AUTH in Workers when using D1? Anyone knows what it could be?

Rrossanmol Getting this error not authorized: SQLITE_AUTH in Workers when using D1? Anyone ...

I

Isaac McFadyen•8/15/24, 5:27 PM

Are you trying to access internal SQLite tables or use SQLite pragmas?

I

Isaac McFadyen•8/15/24, 5:27 PM

Some are blocked on D1 for security reasons.

I

Isaac McFadyen•8/15/24, 5:28 PM

If you are using a database ORM or explorer then it's possible it's making those queries on your behalf to try and inspect the schema of the database (and getting blocked).

J

jdruwe•8/15/24, 9:49 PM

When importing data into a D1 db using the wrangler command it mentions: "This process may take some time, during which your D1 database will be unavailable to serve queries.". I noticed this also in a test as drizzle showed "7500: Currently processing a long-running import." on a select query. Is there any way to get around this? Lets assume that the DB is already being used in production, I would like for the app to continue working. Any suggestions?

O

oof2win2•8/16/24, 5:38 AM

is there any eta on read replicas with d1?

Y

YuryC•8/16/24, 6:48 PM

Hello all! I need to rename a few columns in the existing table. For some reason, the RENAME statement doesn't work for me. Does d1 support the RENAME statement? Thanks in advance.

YYuryC Hello all! I need to rename a few columns in the existing table. For some reason...

I

Isaac McFadyen•8/16/24, 6:50 PM

D1 is SQLite, so see https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-an-sqlite-database-table

Stack Overflow

How do I rename a column in an SQLite database table?

I need to rename table columns in an SQLite database. This similar question doesn't mention SQLite. From the documentation of ALTER TABLE I gather that it's not a single statement.
What is a generi...

S

Steven•8/16/24, 7:07 PM

Hi all,
I have an issue I don't understand: when i save values to a boolean column they seem to be saved as strings (true, false, or in the case of accidental error in inserting some random other strings).

This is on a local d1 database in development.

I was under the impression that values would be converted to 0 or 1 automatically (or error thrown if not truthy or falsy). Is this not correct?

SSteven Hi all, I have an issue I don't understand: when i save values to a boolean colu...

I

Isaac McFadyen•8/16/24, 7:12 PM

So yes,

true

true

and

false

false

will be cooerced into integers, but SQLite (which D1 uses) also has the concept of "flexible datatypes" that other DB engines don't have: https://sqlite.org/datatype3.html

I

Isaac McFadyen•8/16/24, 7:12 PM

It means you can insert a string into an integer column, or an integer into a string column, and the DB engine will just accept it

I

Isaac McFadyen•8/16/24, 7:13 PM

You can use STRICT tables if you want datatypes enforced: https://sqlite.org/stricttables.html

S

Steven•8/16/24, 7:14 PM

mm, okay. Thanks. Good to know.

But I do have 'true' and 'false' values in my boolean column. Those should be converted to 1 & 0 then right? Even without STRICT

I

Isaac McFadyen•8/16/24, 7:15 PM

I'm not sure if it's case-sensitive or not?

S

Steven•8/16/24, 7:18 PM

mm, i'll do a typecheck. maybe it's getting turned into a string somewhere

Z

ZeddBread🐣•8/17/24, 10:34 AM

Heyyyy

I'm getting started with D1 databases for my app.
I have 3 separate databases and want to put it into my local wrangler.toml

I don't know how to input multiple databases into it, the docs only show how to do one database like so:

[[d1_databases]]
binding = "DB"
database_name = "test-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "test-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "test-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

[[d1_databases]]
binding = "DB"
database_name = "test-db"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

If someone could lead me the right way, you would be madd af ngl

V

veeque•8/17/24, 10:58 AM

I made a SvelteKit project but what I don't understand is how to work with D1 in development. I would ideally like to start the SvelteKit app normally (

pnpm dev

pnpm dev

) to quickly iterate on the UI, but have D1 accessible like I would by having a development docker image of a remote database. Currently I can only have a functioning version of my app when building the ui and then starting

wrangler pages dev .svelte-kit/cloudflare

wrangler pages dev .svelte-kit/cloudflare

(but since it relies on a build, I would have to re-run the command after every change to have a functioning app in development). What am I missing?

Vveeque I made a SvelteKit project but what I don't understand is how to work with D1 in...

H

Hello, I’m Allie!•8/17/24, 11:04 AM

If you are using the Cloudflare Pages Adapter for SvelteKit, then it should be generating bindings for you in

pnpm dev

pnpm dev

if that is what you mean

HHello, I’m Allie!If you are using the Cloudflare Pages Adapter for SvelteKit, then it should be g...

V

veeque•8/17/24, 11:11 AM

In

src/routes/api/test/+server.ts

src/routes/api/test/+server.ts

I have:

import type { RequestHandler } from '@sveltejs/kit';

export const GET: RequestHandler = async ({ request, platform }) => {
  const result = await platform?.env.db.prepare('SELECT * FROM users LIMIT 5').run();

  return new Response(JSON.stringify(result));
};

import type { RequestHandler } from '@sveltejs/kit';

export const GET: RequestHandler = async ({ request, platform }) => {
  const result = await platform?.env.db.prepare('SELECT * FROM users LIMIT 5').run();

  return new Response(JSON.stringify(result));
};

import type { RequestHandler } from '@sveltejs/kit';

export const GET: RequestHandler = async ({ request, platform }) => {
  const result = await platform?.env.db.prepare('SELECT * FROM users LIMIT 5').run();

  return new Response(JSON.stringify(result));
};

import type { RequestHandler } from '@sveltejs/kit';

export const GET: RequestHandler = async ({ request, platform }) => {
  const result = await platform?.env.db.prepare('SELECT * FROM users LIMIT 5').run();

  return new Response(JSON.stringify(result));
};

And in my

src/app.d.ts

src/app.d.ts

:

declare global {
  namespace App {
    interface Platform {
      env: {
        db: D1Database;
      };
    }
  }
}

export {};

declare global {
  namespace App {
    interface Platform {
      env: {
        db: D1Database;
      };
    }
  }
}

export {};

declare global {
  namespace App {
    interface Platform {
      env: {
        db: D1Database;
      };
    }
  }
}

export {};

declare global {
  namespace App {
    interface Platform {
      env: {
        db: D1Database;
      };
    }
  }
}

export {};

(where does the

D1Database

D1Database

type come from?)

but when running with

pnpm dev

pnpm dev

and I navigate to

localhost:5173/api/test

localhost:5173/api/test

I get the following in the console:

TypeError: Cannot read properties of undefined (reading 'prepare')

TypeError: Cannot read properties of undefined (reading 'prepare')

. If I build, then run

wrangler pages dev .svelte-kit/cloudflare --d1 db=[uuid-here]

wrangler pages dev .svelte-kit/cloudflare --d1 db=[uuid-here]

the error is just because I haven't made the table, but would otherwise work.

I don't know about the binding it should be generating, because I don't seem to have any(?), but what also strikes me is that i can't just run

wrangler pages dev

wrangler pages dev

, but I have to specify both the folder and the

--d1

--d1

binding myself, despite this being my

wrangler.toml

wrangler.toml

file:

name = "[name-here]"
compatibility_date = "2024-07-29"
pages_build_output_dir = ".svelte-kit/cloudflare"

[[d1_databases]]
binding = "DB"
database_name = "prod-main"
database_id = "[uuid-here]"

name = "[name-here]"
compatibility_date = "2024-07-29"
pages_build_output_dir = ".svelte-kit/cloudflare"

[[d1_databases]]
binding = "DB"
database_name = "prod-main"
database_id = "[uuid-here]"

name = "[name-here]"
compatibility_date = "2024-07-29"
pages_build_output_dir = ".svelte-kit/cloudflare"

[[d1_databases]]
binding = "DB"
database_name = "prod-main"
database_id = "[uuid-here]"

name = "[name-here]"
compatibility_date = "2024-07-29"
pages_build_output_dir = ".svelte-kit/cloudflare"

[[d1_databases]]
binding = "DB"
database_name = "prod-main"
database_id = "[uuid-here]"

V

veeque•8/17/24, 11:13 AM

nevermind about the

--d1

--d1

thing, it works regardless of it

V

veeque•8/17/24, 11:14 AM

oh, maybe it solved itself after deleting node_modules and retrying

V

veeque•8/17/24, 11:29 AM

ok, the only real issue now is data not persisting between each

pnpm dev

pnpm dev

run. The documentation only tells me how to deal with this with

wrangler dev

wrangler dev

also whats the storage limit for D1 ? curious if you have people running multiple TB at ease with it

Similar Threads