for the love of god dont use next-auth its very bad

F

Fabian•4/7/24, 10:00 PM

hey guys, anyone has an idea how to bind dates in a query? I basically have a

DATETIME

DATETIME

column, and when I run a query using

WHERE xyz BETWEEN ? AND ?

WHERE xyz BETWEEN ? AND ?

and passing in a date it always results in

Type 'object' not supported for value 'Thu Apr 04 2024 23:54:22 GMT+0200 (Central European Summer Time)'

Type 'object' not supported for value 'Thu Apr 04 2024 23:54:22 GMT+0200 (Central European Summer Time)'

which obviously means that the D1 client apparently cannot handle date objects. It works when passing in an ISO string, but it's really annoying when working with ORMs and schema libraries. So is there a known workaround or am I somehow using an outdated version?

B

bunOP•4/7/24, 10:09 PM

there are no date types in sqlite, so the issue solely depends on what ORM you're using

F

Fabian•4/7/24, 10:10 PM

Well I mean the column is defined as date, so the D1 client could convert the string to a native date. But I guess I'll have to switch ORM then

F

Fabian•4/7/24, 10:10 PM

or vice-versa

Rrenzor this worked for me ``` export const UsersTable = sqliteTable("users", { userna...

B

bunOP•4/7/24, 10:11 PM

if you are using drizzle:
i personally use unix timestamp for dates like:

createdAt: int("created_at", { mode: "timestamp" })
      .default(sql`(unixepoch())`)
      .notNull(),

createdAt: int("created_at", { mode: "timestamp" })
      .default(sql`(unixepoch())`)
      .notNull(),

but renzor also pointed out he works with

text

text

dates

Original message was deleted

C

Cloud Kung Fu•4/7/24, 10:12 PM

Thnx

B

bunOP•4/7/24, 10:12 PM

upon looking up docs for sqlite it says it only works with

ISO8601

ISO8601

dates

Bbun for the love of god dont use next-auth its very bad

C

Cloud Kung Fu•4/7/24, 10:12 PM

What makes you say that?

B

bunOP•4/7/24, 10:12 PM

limiting/confusing/terrible docs

B

bunOP•4/7/24, 10:12 PM

i would use lucia if i were to use a ready solution for auth

Bbun i would use lucia if i were to use a ready solution for auth

C

Cloud Kung Fu•4/7/24, 10:13 PM

I see what you mean, appreciate the recc, will definitely check that out

Original message was deleted

C

Cloud Kung Fu•4/7/24, 10:17 PM

I wish I saw this earlier!

Bbun upon looking up docs for sqlite it says it only works with `ISO8601` dates

F

Fabian•4/7/24, 10:19 PM

Yeah I see. But I still think that the D1 client could, instead of throwing an exception when passing in a Date object, automatically convert it to a ISO date. I don't see a problem doing that.
And, while we are at it, automatically convert ISO timestamps to a native date object. This would be a breaking change tho.
They do the same for other data-types, like boolean to int conversion and vice-versa. This is just a developer quality-of-life feature in my opinion, and not a limitation of SQLite.

C

Chihwei•4/7/24, 10:21 PM

Is there any handy ORM for workers-rs with D1?

R

Rubi•4/7/24, 10:40 PM

hi, I'm using SvelteKit

I got this error while trying to execute a query:

Error: D1_ERROR: no such table: countries

Error: D1_ERROR: no such table: countries

I wonder what's wrong

J

James•4/7/24, 10:43 PM

If you're developing locally, keep in mind that the data remotely (what you see in the cf dash) likely isn't what you have locally.

JJames If you're developing locally, keep in mind that the data remotely (what you see ...

R

Rubi•4/7/24, 10:50 PM

scrolling through the chat, but

wrangler pages dev .svelte-kit/cloudflare --remote

wrangler pages dev .svelte-kit/cloudflare --remote

no support for

--remote

--remote

args

B

bunOP•4/7/24, 11:00 PM

yeah you can't use remote db on local

R

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

is there an easy way to "clone" entire database from prod to local wrangler d1 ?

B

bunOP•4/7/24, 11:02 PM

wrangler d1 export

wrangler d1 export

i believe

B

bunOP•4/7/24, 11:05 PM

wrangler d1 export YOUR_DB_NAME --output db.sql --remote && wrangler d1 execute YOUR_DB_BINDING_NAME --local --file db.sql

wrangler d1 export YOUR_DB_NAME --output db.sql --remote && wrangler d1 execute YOUR_DB_BINDING_NAME --local --file db.sql

something like this would work if your local db is empty

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' })

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"}

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:

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.

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

for the love of god dont use next-auth its very bad

Similar Threads

Similar Threads

Similar Threads