Hey there, is there any database manager or viewer for D1 ? Something similar to a DBeaver, I can't

M

MartialOP•5/6/24, 5:17 PM

Thanks!

M

MartialOP•5/6/24, 5:24 PM

Ah it's compatible with SQLite so opening .wrangler/state/.../miniflare-D1DatabaseObject/<mydb>.sqlite with any sqlite viewier will do !

MMartial Hey there, is there any database manager or viewer for D1 ? Something similar to...

I

IEatBeans•5/6/24, 6:31 PM

I use drizzle studio (though it’s kind of hacky to get it to work locally and I’m not able to get it to work in production.) I use a pages project template called d1 manager for production

IIEatBeans I use drizzle studio (though it’s kind of hacky to get it to work locally and I’...

M

MartialOP•5/6/24, 6:47 PM

Hacky is the term, had the same experience making Prisma to work with D1. Btw I couldn’t find a way to generate migration diff with drizzle (so switched to Prisma - prisma studio’s not working for me) how did you ?

MMartial Hacky is the term, had the same experience making Prisma to work with D1. Btw I ...

I

IEatBeans•5/6/24, 7:33 PM

There’s a command for the drizzle kit cli that does that. I believe it uses metadata that it itself sets in the drizzle folder and not the actual live schema from the db

R

roftt•5/7/24, 5:53 AM

Hi! Will binding thousands of databases to a single worker slow down the execution of the worker?

IIEatBeans You can set the —persist-to flag to specify a directory that both workers can ac...

S

scruffy•5/7/24, 6:19 AM

I ended up leaning on Miniflare's d1Persist as I'm already using Miniflare for handling local dev of bound workers. Didn't know about wrangler's --persist-to though, so thanks for sharing

K

kalaivanan•5/7/24, 7:53 AM

drizzle nextjs

K

kalaivanan•5/7/24, 7:54 AM

I'm trying to user D1 with drizzle orm in next js, currently using next auth 5, and using drizzle adapter for that.. How to get the DB instance during build?

K

kalaivanan•5/7/24, 7:56 AM

export const {
  handlers: { GET, POST },
  auth,
  signIn,
  signOut,
} = NextAuth({
  adapter: DrizzleAdapter(getDBInstance()),
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_CLIENT_ID ?? "",
      clientSecret: process.env.GOOGLE_CLIENT_SECRET ?? "",
    }),
  ],
});

export const {
  handlers: { GET, POST },
  auth,
  signIn,
  signOut,
} = NextAuth({
  adapter: DrizzleAdapter(getDBInstance()),
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_CLIENT_ID ?? "",
      clientSecret: process.env.GOOGLE_CLIENT_SECRET ?? "",
    }),
  ],
});

export const {
  handlers: { GET, POST },
  auth,
  signIn,
  signOut,
} = NextAuth({
  adapter: DrizzleAdapter(getDBInstance()),
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_CLIENT_ID ?? "",
      clientSecret: process.env.GOOGLE_CLIENT_SECRET ?? "",
    }),
  ],
});

export const {
  handlers: { GET, POST },
  auth,
  signIn,
  signOut,
} = NextAuth({
  adapter: DrizzleAdapter(getDBInstance()),
  providers: [
    GoogleProvider({
      clientId: process.env.GOOGLE_CLIENT_ID ?? "",
      clientSecret: process.env.GOOGLE_CLIENT_SECRET ?? "",
    }),
  ],
});

This is method to get db instance

export const getDBInstance = () => {
  console.log("getRequestContext", getRequestContext);

  const DB = getRequestContext().env.AI_FORM_GENERATOR_DB;

  return drizzle(DB, { schema });
};

export const getDBInstance = () => {
  console.log("getRequestContext", getRequestContext);

  const DB = getRequestContext().env.AI_FORM_GENERATOR_DB;

  return drizzle(DB, { schema });
};

export const getDBInstance = () => {
  console.log("getRequestContext", getRequestContext);

  const DB = getRequestContext().env.AI_FORM_GENERATOR_DB;

  return drizzle(DB, { schema });
};

export const getDBInstance = () => {
  console.log("getRequestContext", getRequestContext);

  const DB = getRequestContext().env.AI_FORM_GENERATOR_DB;

  return drizzle(DB, { schema });
};

How to get access to Binding variable AI_FORM_GENERATOR_DB during build process?

K

kalaivanan•5/7/24, 8:12 AM

Have anyone used D1 adaptor with authjs?

V

Vikash•5/7/24, 9:34 AM

D1 doesn't support

IN

IN

IN

IN operator?

const userIds = [1, 2, 3]
const sqlQuery= env.DB.prepare(
    `
      SELECT * FROM Users
      WHERE user_id in (?)`
  ).bind(userIds);
const { results } = await sqlQuery.all();

const userIds = [1, 2, 3]
const sqlQuery= env.DB.prepare(
    `
      SELECT * FROM Users
      WHERE user_id in (?)`
  ).bind(userIds);
const { results } = await sqlQuery.all();

const userIds = [1, 2, 3]
const sqlQuery= env.DB.prepare(
    `
      SELECT * FROM Users
      WHERE user_id in (?)`
  ).bind(userIds);
const { results } = await sqlQuery.all();

const userIds = [1, 2, 3]
const sqlQuery= env.DB.prepare(
    `
      SELECT * FROM Users
      WHERE user_id in (?)`
  ).bind(userIds);
const { results } = await sqlQuery.all();

VVikash D1 doesn't support `IN` operator? ``` const userIds = [1, 2, 3] const sqlQuery= ...

B

Beny•5/7/24, 9:37 AM

You need to add multiple

?

? in the WHERE clause.
Check this example: https://discord.com/channels/595317990191398933/992060581832032316/1226795894280687636

BBeny You need to add multiple `?` in the WHERE clause. Check this example: https://di...

V

Vikash•5/7/24, 9:45 AM

worked, thanks.

K

kian•5/7/24, 11:41 AM

The existing rows will be indexed, and changed/news rows will be indexed too.

R

roftt•5/7/24, 12:10 PM

is binding 1000s of databases to a single worker a good idea? Will it slow down the execution of the worker?

Rroftt is binding 1000s of databases to a single worker a good idea? Will it slow down ...

K

kian•5/7/24, 12:29 PM

It'll make uploads take like 30+ seconds, but the Worker itself shouldn't really be affected.

H

Hello, I’m Allie!•5/7/24, 1:59 PM

Anyone remember if there is a D1-compatible tool that shows recommended indexes?

Kkian `wrangler d1 export`

S

Softbear•5/7/24, 2:07 PM

Is there a limit on the size of the database we can export ? I've been trying to run the command on a 600mb database and it times-out

SSoftbear Is there a limit on the size of the database we can export ? I've been trying to...

S

Softbear•5/7/24, 3:39 PM

The error on export was fixed by updating wrangler to the latest version.

But now I have a 504 error on import.
There is around 28k rows in a table so it breaks it down in 3 batches, the 3rd one does not go trough (around 18k rows are imported).

SSoftbear The error on export was fixed by updating wrangler to the latest version. But ...

R

roftt•5/7/24, 3:58 PM

The same happened to me and my database is 5 MB only. Does your rows contain lots of text? Most likely this bug: https://github.com/cloudflare/workers-sdk/issues/4510

S

Softbear•5/7/24, 4:00 PM

No pretty small rows

INSERT INTO tokens VALUES('XXX',2024-01-27 08:56:38,2024-01-27 08:56:38,'XXX','XXX','ACCESS',NULL,NULL);

INSERT INTO tokens VALUES('XXX',2024-01-27 08:56:38,2024-01-27 08:56:38,'XXX','XXX','ACCESS',NULL,NULL);

INSERT INTO tokens VALUES('XXX',2024-01-27 08:56:38,2024-01-27 08:56:38,'XXX','XXX','ACCESS',NULL,NULL);

INSERT INTO tokens VALUES('XXX',2024-01-27 08:56:38,2024-01-27 08:56:38,'XXX','XXX','ACCESS',NULL,NULL);

SSoftbear No pretty small rows `INSERT INTO tokens VALUES('XXX',2024-01-27 08:56:38,2024...

R

roftt•5/7/24, 4:02 PM

Have no idea then. Ended up importing each table separately

S

Sam•5/7/24, 7:00 PM

Hey all, is something up with

wrangler d1 info

wrangler d1 info

wrangler d1 info

wrangler d1 info +

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights

wrangler d1 insights?

info

info

info

info is reporting 0 read queries in the past 24h (not the case unless it's somehow using some local version? The ID is the same as in my CF dash)

insights

insights

insights

insights is returning an empty array regardless of configuration

S

Softbear•5/7/24, 8:10 PM

I'm getting timeout errors when trying to modify the schema of a table. " Error: D1_ERROR: D1 exceeded its CPU time limit and was reset."

The users table has 18k rows and 6 tables have foreign key references on it so that's probably why its taking a lot of time. But because of this I'm kind of stuck with not being able to modify my users table.

D

di_uthred•5/8/24, 12:28 AM

Having an issue with this D1 insert. The DB binding is fine (another function reads from the DB OK), but this insert just will not execute. It gets to the "Preparing to insert.." line OK and then black hole - not even an exception error. Simplifying by stubbing in values to avoid type mismatch issues didn't help either.

const insertSql =


        INSERT INTO company_profile 
        (symbol, companyName, website, fullTimeEmployees, industry, overallRisk, description) 
        VALUES (?, ?, ?, ?, ?, ?, ?);


        INSERT INTO company_profile 
        (symbol, companyName, website, fullTimeEmployees, industry, overallRisk, description) 
        VALUES (?, ?, ?, ?, ?, ?, ?);


        INSERT INTO company_profile 
        (symbol, companyName, website, fullTimeEmployees, industry, overallRisk, description) 
        VALUES (?, ?, ?, ?, ?, ?, ?);


        INSERT INTO company_profile 
        (symbol, companyName, website, fullTimeEmployees, industry, overallRisk, description) 
        VALUES (?, ?, ?, ?, ?, ?, ?);

;

for (const entry of data) {
console.log(

Preparing to insert/update data for symbol: ${entry.symbol}

Preparing to insert/update data for symbol: ${entry.symbol}

Preparing to insert/update data for symbol: ${entry.symbol}

Preparing to insert/update data for symbol: ${entry.symbol});
try {
const stmt = await DB.prepare(insertSql);
await stmt.bind(entry.symbol, entry.companyName, entry.website, entry.fullTimeEmployees, entry.industry, entry.overallRisk, entry.description);
const result = await stmt.run();

if (result) {
console.log(

Data successfully inserted for symbol: ${entry.symbol}

Data successfully inserted for symbol: ${entry.symbol}

Data successfully inserted for symbol: ${entry.symbol}

Data successfully inserted for symbol: ${entry.symbol}, result);
} else {
console.log(

Insert failed for symbol: ${entry.symbol}

Insert failed for symbol: ${entry.symbol}

Insert failed for symbol: ${entry.symbol}

Insert failed for symbol: ${entry.symbol}, result);
}
} catch (error) {
console.error(

Failed to insert data for symbol: ${entry.symbol}

Failed to insert data for symbol: ${entry.symbol}

Failed to insert data for symbol: ${entry.symbol}

Failed to insert data for symbol: ${entry.symbol}, error);
}
}

Ddi_uthred Having an issue with this D1 insert. The DB binding is fine (another function r...

R

roftt•5/8/24, 6:24 AM

should be VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)

SSan possible bug: I noticed that when an account member has "Administrator Read Only...

U

Utkarsh Saxena•5/8/24, 7:08 AM

Any update on this?

N

Natalie•5/8/24, 9:51 AM

Hi there. I'm building an realtime 3d texturing application and am planning on utilising cloudflare D1 as the backing store. My problem is that given the limit of 50k databases each with a max size of 10GB, I'm unsure what would be the best architecture to adopt. My initial instinct would be to create a DB per organization, but that means I could only have up to 50k orgs. Given a pareto assumption of organization, probably most of these databases would be under-utilized. So an alternative would be to shard the data somehow, but that could quickly becomes awkward if remote joins would are needed. So I'm a bit confused about how D1 fits into the SaaS use case

N

Natalie•5/8/24, 9:54 AM

I would also be using durable objects as well. But for non-realtime data, would still need a traditional database

NNatalie Hi there. I'm building an realtime 3d texturing application and am planning on u...

H

Hello, I’m Allie!•5/8/24, 9:55 AM

IIRC the 50k DB limit should be able to be raised on request

N

Natalie•5/8/24, 9:58 AM

That also begs the question of if it is possible to have dynamic db bindings? As 50k DBs would far exceed the max number of bindings per worker

NNatalie That also begs the question of if it is possible to have dynamic db bindings? As...

H

Hello, I’m Allie!•5/8/24, 10:00 AM

Not at the moment, though iirc it is something the team is working on

N

Natalie•5/8/24, 10:01 AM

Would that use the rest API?

H

Hello, I’m Allie!•5/8/24, 10:01 AM

It may be ok with WfP? IIRC you should be able to bind to an individual script

NNatalie Would that use the rest API?

H

Hello, I’m Allie!•5/8/24, 10:04 AM

No, I believe it would be like a Durable Object, where you pass an ID in, and get a binding out

H

Hello, I’m Allie!•5/8/24, 10:05 AM

Though I'm not on the team, so that is just a guess on my part

N

Natalie•5/8/24, 10:05 AM

Ah. Makes sense

N

Natalie•5/8/24, 10:05 AM

Thanks

K

krofdrakula•5/8/24, 1:38 PM

does anyone know if prepared statements can be bound with arrays? i'm trying to do a query like

db.prepare('SELECT * FROM table WHERE status IN (?)').bind(['active','expired']).all()

db.prepare('SELECT * FROM table WHERE status IN (?)').bind(['active','expired']).all()

db.prepare('SELECT * FROM table WHERE status IN (?)').bind(['active','expired']).all()

db.prepare('SELECT * FROM table WHERE status IN (?)').bind(['active','expired']).all(), would that work?

Kkrofdrakula does anyone know if prepared statements can be bound with arrays? i'm trying to ...

R

roftt•5/8/24, 1:41 PM

https://discord.com/channels/595317990191398933/992060581832032316/1226795894280687636

M

Marcel Overdijk•5/8/24, 3:06 PM

The pricing docs for D1 mention:

Every query returns a meta object that contains a total count of the rows read (rows_read) and rows written (rows_written) by that query. For example, a query that performs a full table scan (for instance, SELECT * FROM users) from a table with 5000 rows would return a rows_read value of 5000:

"meta": {
  "duration": 0.20472300052642825,
  "size_after": 45137920,
  "rows_read": 5000,
  "rows_written": 0
}

These are also included in the D1 Cloudflare dashboard and the analytics API, allowing you to attribute read and write volumes to specific databases, time periods, or both.

Every query returns a meta object that contains a total count of the rows read (rows_read) and rows written (rows_written) by that query. For example, a query that performs a full table scan (for instance, SELECT * FROM users) from a table with 5000 rows would return a rows_read value of 5000:

"meta": {
  "duration": 0.20472300052642825,
  "size_after": 45137920,
  "rows_read": 5000,
  "rows_written": 0
}

These are also included in the D1 Cloudflare dashboard and the analytics API, allowing you to attribute read and write volumes to specific databases, time periods, or both.

Every query returns a meta object that contains a total count of the rows read (rows_read) and rows written (rows_written) by that query. For example, a query that performs a full table scan (for instance, SELECT * FROM users) from a table with 5000 rows would return a rows_read value of 5000:

"meta": {
  "duration": 0.20472300052642825,
  "size_after": 45137920,
  "rows_read": 5000,
  "rows_written": 0
}

These are also included in the D1 Cloudflare dashboard and the analytics API, allowing you to attribute read and write volumes to specific databases, time periods, or both.

Every query returns a meta object that contains a total count of the rows read (rows_read) and rows written (rows_written) by that query. For example, a query that performs a full table scan (for instance, SELECT * FROM users) from a table with 5000 rows would return a rows_read value of 5000:

"meta": {
  "duration": 0.20472300052642825,
  "size_after": 45137920,
  "rows_read": 5000,
  "rows_written": 0
}

These are also included in the D1 Cloudflare dashboard and the analytics API, allowing you to attribute read and write volumes to specific databases, time periods, or both.

But where can I see these rows_read statitiscs in the dashboard?

I'm trying to find out how count queries are calculated...

Imagine I have a

select count(1) from customer where country_id = 'uk'

select count(1) from customer where country_id = 'uk'

select count(1) from customer where country_id = 'uk'

select count(1) from customer where country_id = 'uk' what is the cost for such a query?
Is it equals to the number of rows in the customer table?

R

roftt•5/8/24, 3:24 PM

Try this in your worker:

const stmt = db.prepare('select count(1) from customer where country_id = "uk"').all();
console.log(stmt.meta.rows_read)

const stmt = db.prepare('select count(1) from customer where country_id = "uk"').all();
console.log(stmt.meta.rows_read)

const stmt = db.prepare('select count(1) from customer where country_id = "uk"').all();
console.log(stmt.meta.rows_read)

const stmt = db.prepare('select count(1) from customer where country_id = "uk"').all();
console.log(stmt.meta.rows_read)

Overall statistics for rows_read of the entire database is in Dashboard -> Your DB -> Metrics
And don't forget to use indexes, it reduces rows_read drastically

M

Marcel Overdijk•5/8/24, 5:27 PM

I'm wondering if I can check these query statistic per query executed from the console?

K

kian•5/8/24, 5:31 PM

If you open DevTools, the

meta

meta

meta

meta object will be in the HTTP response.

M

Marcel Overdijk•5/8/24, 5:38 PM

That was indeed useful.
So when a

customer

customer

customer

customer table contains 900 records both queries

select * from customer

select * from customer

select * from customer

select * from customer and

select count(1) from customer

select count(1) from customer

select count(1) from customer

select count(1) from customer
will have

"rows_read": 900,

"rows_read": 900,

"rows_read": 900,

"rows_read": 900,

M

Marcel Overdijk•5/8/24, 5:39 PM

so

count

count

count

count queries are quite expensive in D1

Rroftt should be VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)

D

di_uthred•5/8/24, 6:37 PM

didn't make a difference

MMarcel Overdijk so `count` queries are quite expensive in D1

I

Isaac McFadyen•5/8/24, 7:41 PM

This is not really specific to D1 (but yeah that's accurate)

I

Isaac McFadyen•5/8/24, 7:41 PM

A lot of databases will read every row for "count" because they assume you want an exact count.

Hey there, is there any database manager or viewer for D1 ? Something similar to a DBeaver, I can't

Similar Threads

Similar Threads

Similar Threads