sqlite_schema is unrelated to migrations, it's an internal table SQLite uses for various things.

IIsaac McFadyen sqlite_schema is unrelated to migrations, it's an internal table SQLite uses for...

S

skip•5/3/24, 11:16 PM

OK thanks... would you know how migrations decide what migration point has or has not run yet?

Because ATM my database it totally empty and it thinks none of the migrations need to run

I

Isaac McFadyenOP•5/3/24, 11:17 PM

I'm not sure, sorry - someone else would probably know the answer.

S

stibbs•5/4/24, 4:46 AM

How do I use sqlite extensions with D1? E.g.,

uuid.c

uuid.c

uuid.c

uuid.c (https://sqlite.org/src/file/ext/misc/uuid.c) to generate UUIDs

EDIT: Or should I be creating the UUID's myself prior to interacting with D1?

K

kian•5/4/24, 5:16 AM

If the extension isn't already enabled in D1 then you can't use it - so you'll need to make the UUIDs yourself, i.e with crypto.randomUUID()

B

Bilal B•5/4/24, 6:15 AM

Hey I'm trying to run SQL commands from

console

console

console

console tab in the interface but it says I'm not authorized

B

Bilal B•5/4/24, 6:15 AM

I'm the owner of the account

B

Bilal B•5/4/24, 6:16 AM

never mind, I was using

BEGIN... COMMIT;

BEGIN... COMMIT;

BEGIN... COMMIT;

BEGIN... COMMIT; which I guess is not supported

S

scruffy•5/4/24, 1:57 PM

I have two workers (in

a/

a/

a/

a/ and

b/

b/

b/

b/), both with D1 bindings to

DB

DB

in their

wrangler.toml

wrangler.toml

s. I created the D1 inside

a/

a/

a/

a/, added some data, and can retrieve it using

pnpm wrangler d1 execute DB --local --command="SELECT * FROM users"

pnpm wrangler d1 execute DB --local --command="SELECT * FROM users"

pnpm wrangler d1 execute DB --local --command="SELECT * FROM users"

pnpm wrangler d1 execute DB --local --command="SELECT * FROM users" inside

a/

a/

a/

a/. Ideally, I'd like to run the same command in

b/

b/

b/

b/, but I'm getting an error:

no such table: users

no such table: users

no such table: users

no such table: users. I'm guessing this is because the local D1 instance is inside

a/.wrangler/state/v3/d1/miniflare-D1DatabaseObject

a/.wrangler/state/v3/d1/miniflare-D1DatabaseObject

a/.wrangler/state/v3/d1/miniflare-D1DatabaseObject

a/.wrangler/state/v3/d1/miniflare-D1DatabaseObject ...is there any way to make this (or similar) work?

S

scruffy•5/4/24, 2:00 PM

(am trying to boil down a problem I'm facing in a monorepo)

S

scruffy•5/4/24, 4:25 PM

For anyone running into this, I solved it by using Miniflare for running the workers and setting

mf.options.d1Persist: 'data-worker/.wrangler/state/v3/d1

mf.options.d1Persist: 'data-worker/.wrangler/state/v3/d1

mf.options.d1Persist: 'data-worker/.wrangler/state/v3/d1

mf.options.d1Persist: 'data-worker/.wrangler/state/v3/d1, i.e. the path which contains

miniflare-D1DatabaseObject

miniflare-D1DatabaseObject

miniflare-D1DatabaseObject

miniflare-D1DatabaseObject inside the worker where D1 was created

S

scruffy•5/4/24, 4:25 PM

(in this snippet, the relevant worker is called

data-worker

data-worker

data-worker

data-worker)

F

Fabian•5/4/24, 5:53 PM

I have seen many use Drizzle here - is there any way to prepare queries? I have a rather "complex" query that when running locally it has basically no impact on runtime, but when deployed it takes 10-20ms to compile. Idk if the "CPUs" on Workers are really that trash or if I am doing something wrong. (side note, it's inside a batch, so no, it's not latency related - upon removing it from the batch performance gets instantly better. Also assured it is query compilation by pasting the query multiple times in the code without executing it)

Kkian If the extension isn't already enabled in D1 then you can't use it - so you'll n...

S

skip•5/5/24, 12:06 AM

Is there a list of enabled extensions?

K

kian•5/5/24, 12:07 AM

https://github.com/cloudflare/workerd/blob/bb8c8cd75e6a85dc88a1905553eb1ce4e1e58576/build/BUILD.sqlite3 has all of the defines/build configuration

S

stibbs•5/5/24, 3:55 AM

Is it possible to delete a row while also returning it in the same request?

prepare("DELETE FROM table WHERE id = ?1").bind(bla).first()

prepare("DELETE FROM table WHERE id = ?1").bind(bla).first()

prepare("DELETE FROM table WHERE id = ?1").bind(bla).first()

prepare("DELETE FROM table WHERE id = ?1").bind(bla).first() does not work

I'm retrieving a password reset hash. If it exists, I want to delete it and my code proceeds with the returned value. If it doesn't exist it needs to error out. I can easily do it across two requests but if there's a better way I'd like to hear it

Sstibbs Is it possible to delete a row while also returning it in the same request? `pre...

K

kian•5/5/24, 3:59 AM

Use a returning clause

K

kian•5/5/24, 3:59 AM

i.e add

RETURNING *

RETURNING *

RETURNING *

RETURNING * to the end

K

kian•5/5/24, 3:59 AM

https://www.sqlite.org/syntax/returning-clause.html

S

stibbs•5/5/24, 3:59 AM

Damn I am not operating on all cylinders today lol. Thanks!

R

roftt•5/6/24, 3:12 AM

Hi! Any updates on the issue of importing databases containing large individual rows? https://github.com/cloudflare/workers-sdk/issues/4510

S

San•5/6/24, 10:40 AM

possible bug: I noticed that when an account member has "Administrator Read Only" access, they can not see D1 tables and their data. They can see D1 databases though.

Sskip OK thanks... would you know how migrations decide what migration point has or ha...

K

kingmesal•5/6/24, 11:05 AM

There is a d1_migrations table. It is hidden from the console, but it is there.

Sscruffy I have two workers (in `a/` and `b/`), both with D1 bindings to `DB` in their `...

I

IEatBeans•5/6/24, 4:21 PM

You can set the —persist-to flag to specify a directory that both workers can access, then they will share the same SQLite files (as long as the binding is the same)

M

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

Hey there, is there any database manager or viewer for D1 ? Something similar to a DBeaver, I can't find something to read and manipulate my local D1

M

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

Thanks!

M

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

Martial•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);
}
}

sqlite_schema is unrelated to migrations, it's an internal table SQLite uses for various things.

Similar Threads

Similar Threads

Similar Threads