It appears that triggers are supported (applied several to my tables) but wanted to confirm and see

Bbenjamin It appears that triggers are supported (applied several to my tables) but wanted...

M

Matt Silverlock•8/16/23, 4:48 PM

No intent to remove support. We just haven’t documented them yet (note: this is for the new backend)

S

Stronk•8/16/23, 5:48 PM

Are there docs for D1 further than just how the bindings work?

SStronk Are there docs for D1 further than just how the bindings work?

M

Matt Silverlock•8/16/23, 6:12 PM

What are you specifically after?

SStronk Are there docs for D1 further than just how the bindings work?

M

Matt Silverlock•8/16/23, 6:13 PM

Get started: https://developers.cloudflare.com/d1/get-started/
The client API: https://developers.cloudflare.com/d1/platform/client-api/
Indexes, JSON, local dev, debugging, time travel, etc, etc:https://developers.cloudflare.com/d1/learning/

R

Rabid•8/17/23, 3:15 AM

I am super excited about D1, do you envision it basically being a drop in replacement for something like RDS? I know there are drawbacks right now to max DB size and what not, but do you see this evolving to be as powerful as something like RDS or Aurora eventually?

E

Elliot Hesp•8/17/23, 3:47 AM

Just came across this blog post. Are procedures a thing or not? Don't see any reference to them in the docs

E

Elliot Hesp•8/17/23, 3:52 AM

Dang

having constant issues with prismas data proxy, would love to use d1 but without transaction support it seems like a no go

E

Elliot Hesp•8/17/23, 3:54 AM

Yeah that's our usecase unfortunately

E

Elliot Hesp•8/17/23, 4:27 AM

For context, I came across d1 support in drizzle orm, which has a transaction api. However after a search I discovered it'll throw an error if attempting to do that

M

mukhtharcm•8/17/23, 10:22 AM

any one experiencing this?

when I try to run any deletion or table creation command, I'm getting a foreign key constraints failed error

M

mukhtharcm•8/17/23, 10:22 AM

the same command works on local sqlite db

M

mukhtharcm•8/17/23, 10:23 AM

I tried on cli by providing a schema.sql and also from the cf dashbaord

E

erasmodanarni•8/17/23, 10:50 AM

hello! is LIKE supported? I have:

E

erasmodanarni•8/17/23, 10:51 AM

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

app.get("/api/customers/:name", async (c) => {
        const { name } = c.req.param();
        const { results } = await c.env.DB.prepare(
            // This doesn't work 
            // "SELECT * FROM Customers WHERE ContactName LIKE '?1%'"
            // This works...
            "SELECT * FROM Customers WHERE ContactName LIKE 'Maria%'"
        )
            .bind(name)
            .all();
        return c.json(results);
})

E

erasmodanarni•8/17/23, 11:07 AM

Sorry for having asked too quickly: I just found this in the docs... https://developers.cloudflare.com/d1/platform/client-api/#searching-with-like

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

Mmukhtharcm any one experiencing this? when I try to run any deletion or table creation com...

M

Matt Silverlock•8/17/23, 11:59 AM

Can you provide more details, such as the query / DDL you are trying to execute? If you are defining FK relationships that aren’t valid you will get that error.

MMatt Silverlock Can you provide more details, such as the query / DDL you are trying to execute?...

M

mukhtharcm•8/17/23, 12:36 PM

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

CREATE TABLE user_key (
    id VARCHAR(255) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    hashed_password VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

CREATE TABLE user_session (
    id VARCHAR(127) NOT NULL PRIMARY KEY,
    user_id VARCHAR(15) NOT NULL,
    active_expires BIGINT NOT NULL,
    idle_expires BIGINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

-- basic info table. no need to add fields which exist in user table
DROP TABLE IF EXISTS basic_info;
CREATE TABLE basic_info (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    dob VARCHAR(10) NOT NULL,
    aadhar VARCHAR(12) NOT NULL,
    phone VARCHAR(10) NOT NULL,
    houseName VARCHAR(50) NOT NULL,
    postOffice VARCHAR(50) NOT NULL,
    place VARCHAR(50) NOT NULL,
    permanentAddress VARCHAR(50) NOT NULL,
    residentialAddress VARCHAR(50) NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    district VARCHAR(50) NOT NULL,
    state VARCHAR(50) NOT NULL,
    motherTongue VARCHAR(50) NOT NULL,
    bloodGroup VARCHAR(3) NOT NULL,
    idMark1 VARCHAR(50) NOT NULL,
    idMark2 VARCHAR(50) NOT NULL,
    height VARCHAR(5) NOT NULL,
    weight VARCHAR(5) NOT NULL,
    chronicDisease VARCHAR(50),
    familyType VARCHAR(50) NOT NULL,
    birthPosition INT NOT NULL,
    studentEmail VARCHAR(50) NOT NULL,
    FOREIGN KEY (id) REFERENCES user(id)
);

This is the query I was trying to run.

M

mukhtharcm•8/17/23, 12:37 PM

I even tried just running only this much

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS user_key;
DROP TABLE IF EXISTS user_session;

CREATE TABLE user (
    id VARCHAR(15) NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL,
    name VARCHAR(50) NOT NULL,
    userStatus VARCHAR(10) NOT NULL -- student or alumni or faculty or drop-out
);

M

mukhtharcm•8/17/23, 12:38 PM

still getting the same error
@Matt

Mmukhtharcm I even tried just running only this much ```sql DROP TABLE IF EXISTS user; DROP...

M

Matt Silverlock•8/17/23, 12:38 PM

If those tables exist you can’t drop them if there is a foreign key dependency between them, depending on how you defined the constraint

Mmukhtharcm still getting the same error @Matt

M

Matt Silverlock•8/17/23, 12:39 PM

https://www.sqlite.org/foreignkeys.html

M

mukhtharcm•8/17/23, 12:39 PM

Oh!

I think I've ran into this before.

Lemme try reordering the drop commands

Mmukhtharcm Oh! I think I've ran into this before. Lemme try reordering the drop commands

M

Matt Silverlock•8/17/23, 12:39 PM

You likely want to reverse them

MMatt Silverlock You likely want to reverse them

M

mukhtharcm•8/17/23, 12:40 PM

That was it!

M

mukhtharcm•8/17/23, 12:40 PM

Thank you :)

U

user•8/17/23, 3:32 PM

Heyo, I'm trying to make just a basic 'lil "view count" thing on D1, but I've never really worked with D1 before (or raw sql commands at all for that matter...) and I'd like to know if there's any real documentation for the TS api on workers, because all I could really find was a guide.

Uuser Heyo, I'm trying to make just a basic 'lil "view count" thing on D1, but I've ne...

H

Hello, I’m Allie!•8/17/23, 3:42 PM

D1 API Docs

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

U

user•8/17/23, 3:43 PM

Thank you

Ggeelen but I think there are a lot of users of RDS/Aurora who could definitely be using...

R

Rabid•8/17/23, 6:18 PM

Thank you for the reply. Just want to temper my expectations and not commit to something that won’t be able to scale to a reasonable level. I definitely don’t need supermassive, but setting expectations of max limits on columns rows, etc would be nice.

RRabid Thank you for the reply. Just want to temper my expectations and not commit to s...

M

Matt Silverlock•8/17/23, 10:44 PM

There’s no practical limit on columns or rows (especially rows). We allow 100 cols per table, and there is no limit on rows (outside of storage). 50M rows per table? Works. You’ll want a good index and good queries but row count is rarely a limiting factor.

MMatt Silverlock There’s no practical limit on columns or rows (especially rows). We allow 100 co...

R

Rabid•8/17/23, 11:01 PM

This is great. Thank you

MMatt Silverlock There’s no practical limit on columns or rows (especially rows). We allow 100 co...

S

Stronk•8/18/23, 1:27 AM

is there a querry time limit?

M

marko•8/18/23, 8:41 AM

I created a bunch of tables using wrangler in the preview db (using

db-preview

db-preview

db-preview

db-preview name) and I verified they exist with PRAGMA. However, when I use wranger dev, env.DB doesn't have any tables. If I use wrangler dev --remote to connect to production db, it works fine. When I start wranger dev, it shows the correct id in D1 Preview that it gives for

db-preview

db-preview

db-preview

db-preview, so where is the disconnect happening here?

K

kian•8/18/23, 8:58 AM

dev

dev

w/o

--remote

--remote

is entirely local

Kkian `dev` w/o `--remote` is entirely local

M

marko•8/18/23, 9:51 AM

There's no local D1 yet? I'm a bit noobie with wrangler, so it was my perception that --remote uses the production.

Mmarko There's no local D1 yet? I'm a bit noobie with wrangler, so it was my perception...

H

Hello, I’m Allie!•8/18/23, 9:54 AM

Yes, there is. By default, dev uses D1 locally

H

Hello, I’m Allie!•8/18/23, 9:54 AM

It’s only if you use the —remote flag that it runs directly on Cloudflare

HHello, I’m Allie!Yes, there is. By default, dev uses D1 locally

M

marko•8/18/23, 9:56 AM

Ok, so how do I create the tables in the local D1 if wrangler d1 execute db-preview doesn't do it?

K

kian•8/18/23, 9:57 AM

Add

--local

--local

to

d1 execute

d1 execute

d1 execute

d1 execute too

M

marko•8/18/23, 9:59 AM

aaah, so I didn't realize to check wrangler d1 execute db --help

M

marko•8/18/23, 10:00 AM

every day you learn something new

M

Matt Silverlock•8/18/23, 1:54 PM

D1 now returns

rows_written

rows_written

rows_written

rows_written and

rows_read

rows_read

rows_read

rows_read in the metadata for every query. Documentation landing soon. e.g.

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

➜  wrangler d1 execute db-enam --command "INSERT INTO users(email_address,created_at,deleted,settings) VALUES ('me@example.com,', 1687904983, null, '{}')" --json
[
  {
    "results": [],
    "success": true,
    "meta": {
      "duration": 0.17432300001382828,
      "size_after": 45137920,
      "rows_read": 0,
      "rows_written": 2 // Two rows: one to the table, and one to the index as there's an index on created_at for this table
    }
  }
]

MMatt Silverlock D1 now returns `rows_written` and `rows_read` in the metadata for every query. D...

M

Matt Silverlock•8/18/23, 2:38 PM

Our intent is to move to row-based pricing (noting that we will NOT be enabling billing just yet) — charging on rows read (scanned) and written, and returning both read/write counts for every query so you can optimize/measure, as well as dashboard analytics (+ GraphQL API) for longer-term / higher-level analysis.

We think rows are MUCH easier to reason about (vs. bytes) and incentivize folks to use indexes for both performance and cost benefits, which is win-win.

K

kian•8/18/23, 2:49 PM

MMatt Silverlock Our intent is to move to row-based pricing (noting that we will NOT be enabling ...

U

Unsmart•8/18/23, 2:57 PM

Does this mean that each unit is now going to be priced higher? Or are y'all going to basically eat the cost difference if someone wouldve used multiple units on a single row?

UUnsmart Does this mean that each unit is now going to be priced higher? Or are y'all goi...

M

Matt Silverlock•8/18/23, 3:07 PM

Right now, no intent to change $/unit cost. Just the unit itself is changing. In balance, "95%" of users are reading small rows and writing small rows. Some fraction of users writing 100M 1MB rows every day isn't going to change the dynamics.

It appears that triggers are supported (applied several to my tables) but wanted to confirm and see

Similar Threads