What are real-world use-cases for Cloudflare D1 and what are use cases when I should not use it? E.

RRobin Lindner What are real-world use-cases for Cloudflare D1 and what are use cases when I sh...

H

harshil1712•6/19/24, 8:25 AM

Hey Robin,
You can always add more DBs to scale your application. D1 comes with Time Travel that does automatic backups for you. You can learn more about it here: https://developers.cloudflare.com/d1/reference/time-travel/

Cloudflare Docs

Time Travel and backups · Cloudflare D1 docs

Time Travel is D1’s approach to backups and point-in-time-recovery, and allows you to restore a database to any minute within the last 30 days.

N

nuno•6/19/24, 5:17 PM

Hey guys, quick question, Raw D1 Database query will support params with multiple statements at any point in time?

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

[
  {
    "code": 7400,
    "message": "The request is malformed: params with multiple statements is not supported"
  }
]

J

JustinNoel•6/20/24, 11:56 AM

There doesn't seem to be a way to access a D1 backup via the API. I'd like to be able to setup a cron worker to get a backup and send it somewhere else for off site (or off Cloudflare) backup.

Is that possible?

M

maj•6/20/24, 12:32 PM

You could use wrangler:

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

npx wrangler d1 export <db-name> --remote --output backup.sql

Mmaj You could use wrangler: `npx wrangler d1 export <db-name> --remote --output back...

J

JustinNoel•6/20/24, 1:48 PM

Right, but you can only do that with a permanently on computer. I'd like to call an API from a cron worker to do this.

JJustinNoel Right, but you can only do that with a permanently on computer. I'd like to cal...

S

Sam•6/20/24, 1:51 PM

What about using the export api used in the Wrangler CLI?
https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/export.ts#L145

SSam What about using the export api used in the Wrangler CLI? https://github.com/clo...

M

Matt Silverlock•6/20/24, 2:37 PM

@Max (@rozenmd) @geelen we should think about documenting this in the public API schema once we're happy with it - it'll then get pulled into the SDKs

A

Aman Singh•6/21/24, 8:09 AM

Hey, how do I add constraints like UNIQUE and NOT NULL to the attributes of the table present in D1??

AAman Singh Hey, how do I add constraints like UNIQUE and NOT NULL to the attributes of the ...

H

harshil1712•6/21/24, 8:37 AM

Do you want to add the constraints in an existing table or you want to do that when creating a new table? Either way, you can use the SQLite statements to do that. Note that SQLite does not support altering existing columns. You will have to use a workround for that

Hharshil1712 Do you want to add the constraints in an existing table or you want to do that w...

A

Aman Singh•6/21/24, 8:49 AM

Thank you for the reply. I wanted to add the constraints while creating a new table.

AAman Singh Thank you for the reply. I wanted to add the constraints while creating a new ta...

H

harshil1712•6/21/24, 8:51 AM

Here's an example:

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER,
  CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE "User" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

Hharshil1712 Here's an example: ``` CREATE TABLE "Post" ( "id" INTEGER NOT NULL PRIMARY KEY...

A

Aman Singh•6/21/24, 8:52 AM

Thank you. I will do as above

AAman Singh Hey, how do I add constraints like UNIQUE and NOT NULL to the attributes of the ...

S

SteffTek•6/21/24, 11:34 AM

my 2 cents: i highly recommend using an ORM, in JS/TS maybe Drizzle or Prisma, both are great, i personally use drizzle* (oof, almost got cancelled there). you don't want to manage migrations etc. yourself, trust me lol

J

Jason Hostetter•6/21/24, 12:42 PM

Is there yet a blessed way to import larger databases? I am trying to import a db just over 1 GB in size, have followed the instructions in the docs to dump to .sql etc, but when trying to import with wrangler, get the error:

Cannot create a string longer than 0x1fffffe8 characters

Cannot create a string longer than 0x1fffffe8 characters

Cannot create a string longer than 0x1fffffe8 characters

Cannot create a string longer than 0x1fffffe8 characters

J

Jason Hostetter•6/21/24, 12:43 PM

I wish I could just create a new DB from a .sqlite file by dropping it in the dashboard or something, that would be awesome

K

kaushal•6/21/24, 12:55 PM

how to make pdf to link ?

K

kaushal•6/21/24, 12:55 PM

make link

Basics_of_Class_10th_03___Class_Notes____Udaan_2.0_2025.pdf1.7MB

S

SteffTek•6/21/24, 12:59 PM

what

NNico Im just curious is it possible increase the maximum database size of 10GB by any...

S

SteffTek•6/21/24, 7:08 PM

possibly have to write support and tell them your business case, get an enterprise / business plan or something like that

S

SteffTek•6/21/24, 7:08 PM

10GB is not much, but should last most free users

S

SteffTek•6/21/24, 7:09 PM

and even some paid

SSteffTek possibly have to write support and tell them your business case, get an enterpri...

C

Chaika•6/21/24, 7:09 PM

they've said it's a hard(er) limit and not something they raise currently, but you could try: d1-database

S

SteffTek•6/21/24, 7:11 PM

ahh i missread, i thaught he meant storage per account

S

SteffTek•6/21/24, 7:11 PM

not per db

S

SteffTek•6/21/24, 7:11 PM

10gb per db is MUCH

SSteffTek ahh i missread, i thaught he meant storage per account

C

Chaika•6/21/24, 7:12 PM

the storage per account is 250 GB (Workers Paid) but yea that's a soft limit that can be raised
https://developers.cloudflare.com/d1/platform/limits/

M

Matt•6/21/24, 7:47 PM

Can anyone spot what I'm doing wrong?

Drizzle:

export async function searchByFontFamilyName(db: DB, search: string) {
  return db
    .select({
      family: table_fonts.family,
    })
    .from(table_fonts)
    .where(
      ilike(table_fonts.family, `%${search}%`), // search: "nato"
    );
}

export async function searchByFontFamilyName(db: DB, search: string) {
  return db
    .select({
      family: table_fonts.family,
    })
    .from(table_fonts)
    .where(
      ilike(table_fonts.family, `%${search}%`), // search: "nato"
    );
}

export async function searchByFontFamilyName(db: DB, search: string) {
  return db
    .select({
      family: table_fonts.family,
    })
    .from(table_fonts)
    .where(
      ilike(table_fonts.family, `%${search}%`), // search: "nato"
    );
}

export async function searchByFontFamilyName(db: DB, search: string) {
  return db
    .select({
      family: table_fonts.family,
    })
    .from(table_fonts)
    .where(
      ilike(table_fonts.family, `%${search}%`), // search: "nato"
    );
}

Logs this output:

Query: select "family" from "fonts" where "fonts"."family" ilike ? -- params: ["%nato%"]

Query: select "family" from "fonts" where "fonts"."family" ilike ? -- params: ["%nato%"]

Query: select "family" from "fonts" where "fonts"."family" ilike ? -- params: ["%nato%"]

Query: select "family" from "fonts" where "fonts"."family" ilike ? -- params: ["%nato%"]

I get this error:

D1_ERROR: near \"ilike\": syntax error at offset 66

D1_ERROR: near \"ilike\": syntax error at offset 66

D1_ERROR: near \"ilike\": syntax error at offset 66

D1_ERROR: near \"ilike\": syntax error at offset 66

M

Matt•6/21/24, 8:08 PM

For some reason,

like

like

like

like works but

ilike

ilike

ilike

ilike doesn't

MMatt For some reason, `like` works but `ilike` doesn't

C

Chaika•6/21/24, 8:11 PM

D1 is just SQlite, does Sqlite support ilike? I don't think so..

C

Chaika•6/21/24, 8:11 PM

looks like like in sqlite is case insensitive by default

M

Matt•6/21/24, 8:12 PM

Ah okay! Thanks. Still getting used to using SQLite.

MMatt Ah okay! Thanks. Still getting used to using SQLite.

S

SteffTek•6/21/24, 9:15 PM

SQLite on the edge was one of the dumbest, but best things to happen to the CF ecosystem

SSoftbear Hello has anyone tested the d1 driver for datagrip ? I can't manage to make pa...

V

vrballer•6/23/24, 12:27 AM

were you able to get this working (or possibly find some working alternative)

G

Gerbuuun•6/23/24, 6:43 PM

How would one access a DB specific to a user with the same pages project? Isn't there a limit on bindings per worker/pages project? I've read the messages around this: d1-database (or this d1-database ) Is this multi-tenancy support already available? I can't find anything about it in the documentation

GGerbuuun How would one access a DB specific to a user with the same pages project? Isn't ...

H

Hello, I’m Allie!•6/23/24, 6:44 PM

:soontm:

G

Gerbuuun•6/23/24, 6:44 PM

Ah the classic.. Looking forward to it!

D

Disciple of Caoximeihao•6/24/24, 8:40 AM

hello

D

Disciple of Caoximeihao•6/24/24, 8:40 AM

i from hono and D1

D

Disciple of Caoximeihao•6/24/24, 8:41 AM

const insertedOptionValues = await c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?), (?, ?), (? ,? )`)
      .bind(optionId, valueName[0], optionId, valueName[1], optionId, valueName[2])
      .run();

const insertedOptionValues = await c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?), (?, ?), (? ,? )`)
      .bind(optionId, valueName[0], optionId, valueName[1], optionId, valueName[2])
      .run();

const insertedOptionValues = await c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?), (?, ?), (? ,? )`)
      .bind(optionId, valueName[0], optionId, valueName[1], optionId, valueName[2])
      .run();

const insertedOptionValues = await c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?), (?, ?), (? ,? )`)
      .bind(optionId, valueName[0], optionId, valueName[1], optionId, valueName[2])
      .run();

What are real-world use-cases for Cloudflare D1 and what are use cases when I should not use it? E.

Similar Threads

Similar Threads

Similar Threads