`wrangler d1 export YOUR_DB_NAME --output db.sql --remote && wrangler d1 execute YOUR_DB_BINDING_NAM

R

Rubi•4/7/24, 11:09 PM

R

Rubi•4/7/24, 11:09 PM

thankyou @bun

Bbun bro how often do you change your db. also i dont think you need to restart dev s...

A

adjlsncm•4/7/24, 11:58 PM

not when i change the db when i make any change to the app, because for the local database to be active we have to run wrangler dev not npm run dev

FFabian Yeah I see. But I still think that the D1 client could, instead of throwing an e...

J

johtso•4/8/24, 12:18 AM

It does do this if the column is defined as timestamp mode

J

johtso•4/8/24, 12:19 AM

I personally like to use human readable ISO strings so had to define a custom type:

const timestamp = customType<
  { data: Date; driverData: string; }
>({
  dataType() {
    return 'text';
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
  toDriver(value: Date): string {
    try {
      return value.toISOString();
    } catch (e) {
      console.log(`failed to covert ${JSON.stringify(value)} to iso string`)
      throw e;
    }
  },
});

const timestamp = customType<
  { data: Date; driverData: string; }
>({
  dataType() {
    return 'text';
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
  toDriver(value: Date): string {
    try {
      return value.toISOString();
    } catch (e) {
      console.log(`failed to covert ${JSON.stringify(value)} to iso string`)
      throw e;
    }
  },
});

const timestamp = customType<
  { data: Date; driverData: string; }
>({
  dataType() {
    return 'text';
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
  toDriver(value: Date): string {
    try {
      return value.toISOString();
    } catch (e) {
      console.log(`failed to covert ${JSON.stringify(value)} to iso string`)
      throw e;
    }
  },
});

const timestamp = customType<
  { data: Date; driverData: string; }
>({
  dataType() {
    return 'text';
  },
  fromDriver(value: string): Date {
    return new Date(value);
  },
  toDriver(value: Date): string {
    try {
      return value.toISOString();
    } catch (e) {
      console.log(`failed to covert ${JSON.stringify(value)} to iso string`)
      throw e;
    }
  },
});

J

johtso•4/8/24, 12:20 AM

integer('id', { mode: 'timestamp' })

integer('id', { mode: 'timestamp' })

integer('id', { mode: 'timestamp' })

integer('id', { mode: 'timestamp' }) behaves in a similar way

Aadjlsncm not when i change the db when i make any change to the app, because for the loca...

A

adjlsncm•4/8/24, 1:19 AM

if i use npm run dev to start local development for fast refresh it seems like i dont have access to my database because it says

{"error":"Unauthorized, missing DB in environment"}

{"error":"Unauthorized, missing DB in environment"}

{"error":"Unauthorized, missing DB in environment"}

{"error":"Unauthorized, missing DB in environment"}

if i use wrangler dev it does work but using wrangler dev means i have to recompile the entire app for every line change

J

jayMakes11•4/8/24, 2:01 AM

Any insight on how to use Drizzle in the context of a multi-tenant (db-per-user) architecture?

J

jayMakes11•4/8/24, 2:03 AM

(specifically in the context of D1...)

J

jayMakes11•4/8/24, 2:03 AM

found https://www.answeroverflow.com/m/1196744708106506241, which is so far insightful

Drizzle and multi-tenancy - Drizzle Team

I've been given a project that requires a degree of multi-tenancy. The system database will contain users, organizations, sub-organizations and all the links between these entities, then all tenant-specific entities would reside in the separate database. The client is asking for a separate database per tenant for two reasons:
1. to prevent data ...

J

jayMakes11•4/8/24, 2:09 AM

and this (super helpful) (it's turso related but the same could probably be applied to D1, plus i assume any pain here will be improved once D1 releases support for multi-tenenacy): https://turso.tech/blog/creating-a-multitenant-saas-service-with-turso-remix-and-drizzle-6205cf47

Creating a multitenant SaaS service with Turso, Remix, and Drizzle

Here's an example of taking advantage of Turso's numerous database offering to create a multitenant SaaS.

J

johtso•4/8/24, 3:48 PM

Any idea what's going on here?

select "id", "mem_type", "name", "email", "tickets_used", "tickets_remaining", "end_date" from "members" where
                    "members"."end_date" < date('now', '+? days')
                AND "members"."end_date" > date('now', '+? days')
                AND NOT EXISTS (
                    SELECT 1
                    FROM "emails"
                    WHERE "members"."id" = "emails"."mem_id"
                        AND "emails"."time_sent" < date('now', '-? days')
                )
             -- params: [7, 1, 7]

select "id", "mem_type", "name", "email", "tickets_used", "tickets_remaining", "end_date" from "members" where
                    "members"."end_date" < date('now', '+? days')
                AND "members"."end_date" > date('now', '+? days')
                AND NOT EXISTS (
                    SELECT 1
                    FROM "emails"
                    WHERE "members"."id" = "emails"."mem_id"
                        AND "emails"."time_sent" < date('now', '-? days')
                )
             -- params: [7, 1, 7]

select "id", "mem_type", "name", "email", "tickets_used", "tickets_remaining", "end_date" from "members" where
                    "members"."end_date" < date('now', '+? days')
                AND "members"."end_date" > date('now', '+? days')
                AND NOT EXISTS (
                    SELECT 1
                    FROM "emails"
                    WHERE "members"."id" = "emails"."mem_id"
                        AND "emails"."time_sent" < date('now', '-? days')
                )
             -- params: [7, 1, 7]

select "id", "mem_type", "name", "email", "tickets_used", "tickets_remaining", "end_date" from "members" where
                    "members"."end_date" < date('now', '+? days')
                AND "members"."end_date" > date('now', '+? days')
                AND NOT EXISTS (
                    SELECT 1
                    FROM "emails"
                    WHERE "members"."id" = "emails"."mem_id"
                        AND "emails"."time_sent" < date('now', '-? days')
                )
             -- params: [7, 1, 7]

Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

Error: D1_ERROR: Wrong number of parameter bindings for SQL query.

J

johtso•4/8/24, 3:48 PM

3 placeholders 3 parameters.. not sure why I'd be getting an error

Jjohtso 3 placeholders 3 parameters.. not sure why I'd be getting an error

K

K•4/8/24, 3:55 PM

Maybe inner query doesn't receive param

J

johtso•4/8/24, 3:56 PM

oh actually.. skill issue.. I'm not allowed to use placeholders like that am I?

J

johtso•4/8/24, 3:57 PM

like inside an argument to

date()

date()

date()

date()

J

johtso•4/8/24, 3:58 PM

aha: https://stackoverflow.com/a/62911303/311220

Stack Overflow

Query Parameter Binding in DateTime Function of Sqlite3

I am using python and attempting to insert into a sqlite3 table as follows:
database.execute("INSERT INTO EXAMPLE VALUES (?, ?, DATETIME('now','+? seconds'), ?);", (id, statement, time_du...

A

adjlsncm•4/8/24, 4:26 PM

what is the prisma migrate diff command to apply changes ive made to the schema to the local database?

A

adjlsncm•4/8/24, 4:31 PM

it returns a summary in the migration file?

K

kindaraffy•4/8/24, 4:32 PM

If you are trying to apply migrations to your database, the diff command might not be what you want. Its been a while since I've used Prisma, so maybe someone here might be able to help you.

K

kindaraffy•4/8/24, 4:32 PM

Are there any (current) future plans to extend D1 past 10Gb? Or will you guys instead focus on implementing some kind of sharding solution as a higher priority?

A

adjlsncm•4/8/24, 4:40 PM

oh i am an idiot

A

adjlsncm•4/8/24, 4:40 PM

i forgot the --script tag

Aadjlsncm if i use npm run dev to start local development for fast refresh it seems like i...

A

adjlsncm•4/8/24, 6:17 PM

still curious about this

TTony!My CF Worker can apparently not see any data in my DB

V

Vy•4/8/24, 6:57 PM

What error are you seeing?

TTony!My CF Worker can apparently not see any data in my DB

M

Max (@rozenmd)•4/8/24, 7:20 PM

are you using

--remote

--remote

--remote

--remote or

--local

--local

--local

--local with a recent version of wrangler?

J

jstn•4/8/24, 9:20 PM

Does anyone have any suggestions or comments about a CMS that leverages D1 (and R2)? I looked at sonicjs and microfeed but they don't seem to have very active development.

P

PhilH•4/8/24, 10:48 PM

Forgive me if this is a stupid error, but does anyone have any idea why on earth prepared statements aren't working for me at all?

router.post('/:brand/:slug', async (r,e,c) => {
    const brand = r.brand;
    const slug = r.slug;
    const db = e.LINK_DB
    const body = await r.json();

    const stmt = db.prepare(`INSERT INTO ? (slug, type, data, clicks) VALUES ('?', "ArtistLanding", '?', 0)`)
    stmt.bind(brand,slug,body.data)
    return await stmt.run();
})

router.post('/:brand/:slug', async (r,e,c) => {
    const brand = r.brand;
    const slug = r.slug;
    const db = e.LINK_DB
    const body = await r.json();

    const stmt = db.prepare(`INSERT INTO ? (slug, type, data, clicks) VALUES ('?', "ArtistLanding", '?', 0)`)
    stmt.bind(brand,slug,body.data)
    return await stmt.run();
})

router.post('/:brand/:slug', async (r,e,c) => {
    const brand = r.brand;
    const slug = r.slug;
    const db = e.LINK_DB
    const body = await r.json();

    const stmt = db.prepare(`INSERT INTO ? (slug, type, data, clicks) VALUES ('?', "ArtistLanding", '?', 0)`)
    stmt.bind(brand,slug,body.data)
    return await stmt.run();
})

router.post('/:brand/:slug', async (r,e,c) => {
    const brand = r.brand;
    const slug = r.slug;
    const db = e.LINK_DB
    const body = await r.json();

    const stmt = db.prepare(`INSERT INTO ? (slug, type, data, clicks) VALUES ('?', "ArtistLanding", '?', 0)`)
    stmt.bind(brand,slug,body.data)
    return await stmt.run();
})

No matter what request I make, I always get -

{
  "status": 500,
  "error": "D1_ERROR: near \"?\": syntax error at offset 12"
}

{
  "status": 500,
  "error": "D1_ERROR: near \"?\": syntax error at offset 12"
}

{
  "status": 500,
  "error": "D1_ERROR: near \"?\": syntax error at offset 12"
}

{
  "status": 500,
  "error": "D1_ERROR: near \"?\": syntax error at offset 12"
}

The offset varies but it seems that wherever my first

?

? is in my statement, there is a syntax error, regardless of where it is in the project
I really hope I'm just being stupid and missing something obvious here!

P

PhilH•4/8/24, 10:49 PM

I can get this to work by using a template string and just inserting the variables directly into the SQL statement, but I'd rather be able to actually use the bind feature

`wrangler d1 export YOUR_DB_NAME --output db.sql --remote && wrangler d1 execute YOUR_DB_BINDING_NAM

Similar Threads

Similar Threads

Similar Threads