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

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%"]

I get this error:

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

works but

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();

D

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

how to insert multi in d1

DDisciple of Caoximeihao ```const insertedOptionValues = await c.env.DB.prepare(`INSERT INTO optionValues...

H

Hard@Work•6/24/24, 8:50 AM

I would probably do

const ps = c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?)`);
const queries = [];
for(const row of rows) {
  queries.push(ps.bind(...row));
}
await c.env.DB.batch(queries);

const ps = c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?)`);
const queries = [];
for(const row of rows) {
  queries.push(ps.bind(...row));
}
await c.env.DB.batch(queries);

const ps = c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?)`);
const queries = [];
for(const row of rows) {
  queries.push(ps.bind(...row));
}
await c.env.DB.batch(queries);

const ps = c.env.DB.prepare(`INSERT INTO optionValues (optionId, valueName) VALUES (?, ?)`);
const queries = [];
for(const row of rows) {
  queries.push(ps.bind(...row));
}
await c.env.DB.batch(queries);

HHard@Work I would probably do ```js const ps = c.env.DB.prepare(`INSERT INTO optionValues ...

D

Disciple of Caoximeihao•6/24/24, 9:05 AM

working, but sql create 3 query, how to 1 query done

DDisciple of Caoximeihao working, but sql create 3 query, how to 1 query done

H

Hard@Work•6/24/24, 9:05 AM

I'm not sure I understand the question?

HHello, I’m Allie!:soontm:

M

Miloš•6/24/24, 5:10 PM

next to Dynamic Bindings, are there plans for sharding as well perhaps?