darkTower Posts - Answer Overflow

darkTower

•Created by darkTower on 6/20/2024 in #help-and-questions

Where is my environment var coming from?

Odd question but I recently changed my SQL provider. In my deployed environment I have an environment var called DATABASE_URL it works fine I updated it and now in my deployed environment I'm pointing to the correct server. In my local host though I have no idea where DATBASE_URL is getting set. I don't have a .env file. I did this 5 months ago but I know I didn't want my database info in my repo. It would make sense that I would create an environment var in my localhost only I don't remember doing so and when I type printenv DATABASE_URL isn't listed. That said when I build my app I get an error saying it can't connect to my old provider so it's getting it from somewhere I have the following in my schema but no idea where I set it - anyone have any ideas?

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

3 replies

PPrisma

•Created by darkTower on 5/17/2024 in #help-and-questions

Should I stick with raw SQL?

I'm migrating an on old PHP app I wrote 11 years ago to React (Nestjs / Nextjs). In the new stack I'm using Prisma for new calls but I have an old SQL statement I know works. Should I stick with sending it as raw SQL or is there a better way with Prisma?

const columns = `e.date_test_completed AS 'CompletedDate', c.name AS 'courtName', c.number AS 'courtId', COUNT(*) AS 'count', SUM(c.court_fee) AS 'Fee'`;
    const query = `SELECT ${columns}
     FROM enrollment AS e, student_citation AS st_citation, court AS c, user AS u, student_registration AS student
     WHERE e.student_registration_id = st_citation.student_registration_id
                AND student.student_registration_id = e.student_registration_id
                AND st_citation.court_id = c.court_id
                AND u.user_id = student.user_id
                AND u.status != 0
                AND student.status != 0 
                AND (e.date_test_completed BETWEEN '${start_date}' AND  '${end_date}')
                AND Name != 'VOL'
                GROUP BY courtName;`;

 return JSON.stringify(
      this.prisma.$queryRaw`${query}`,
      (key, value) => (typeof value === 'bigint' ? value.toString() : value),
    );

const columns = `e.date_test_completed AS 'CompletedDate', c.name AS 'courtName', c.number AS 'courtId', COUNT(*) AS 'count', SUM(c.court_fee) AS 'Fee'`;
    const query = `SELECT ${columns}
     FROM enrollment AS e, student_citation AS st_citation, court AS c, user AS u, student_registration AS student
     WHERE e.student_registration_id = st_citation.student_registration_id
                AND student.student_registration_id = e.student_registration_id
                AND st_citation.court_id = c.court_id
                AND u.user_id = student.user_id
                AND u.status != 0
                AND student.status != 0 
                AND (e.date_test_completed BETWEEN '${start_date}' AND  '${end_date}')
                AND Name != 'VOL'
                GROUP BY courtName;`;

 return JSON.stringify(
      this.prisma.$queryRaw`${query}`,
      (key, value) => (typeof value === 'bigint' ? value.toString() : value),
    );

To be honest I haven't gotten this working yet. Coping and pasting query into my SQL client gives me the results I want but trying to run it though the prisma client I get a BigInt error. But I feel like I should be able to work that out. I have no idea how I would go about joining the tables needed though. I'm completely open to reading docs on how to do so if someone can point me in the right direction.

5 replies

Gaming

Programming