Database problems

I’ve been following your tutorial, and everything was working beautifully until I tried to access the database that I had created. What I got was “Something went wrong” on the page, and a message that
“Cannot read property ‘findMany’ of undefined”. The program referred me to orgs.js, identifying the problem line as:

export const orgs = () => {

return db.org.findMany()

}

My schema.prisma looks like this:

datasource DS {
// optionally set multiple providers
// example: provider = [“sqlite”, “postgresql”]
provider = “sqlite”
url = env(“DATABASE_URL”)
}
generator client {
provider = “prisma-client-js”
binaryTargets = “native”
}

model MembersDB {
id Int @id @default(autoincrement())
email String @unique
phone Int?
name String
street_address String
suburb String
state String
postcode Int
}

model Org {

id Int @id @default(autoincrement())
email String @unique
phone Int?
name String
street_address String
suburb String
state String
postcode Int
}

I had tried to build a model called MembersDB, but however I try to access it, I get “404:not found”. At least with Org I get an error message

Can you help me?

1 Like

Hi @Boadicea45 Welcome to Redwood!

That error most often occurs when the DB either doesn’t exist (local development) or it’s not connected/updated. For local development, after you change the schema.prisma file you need to:

  1. Create a ‘snapshot’ of your data model called a “migration”: yarn rw db save
  2. Then apply the snapshot to your database: yarn rw db up

If this is happening on a production deploy, then that means something went wrong with your deployment, and the DB either did not connect or the migration was not applied correctly during deployment.

1 Like

Another quick thing to try (once database is up and built) is to run

yarn rw db studio

and see if Prisma can connect directly to bring up the UI to browse the tables/data.

Thanks for that, its working fine, although it does not appear to be seeing the CSS!

I have another question. I have two models in the schema, but only Org works. When I try to access the MembersDB one, I get “404-not found” I’ve tried membersDBs, MembersDB, membersdbs, and other combinations, but same result. I also changed the model name to Member (but I have a page named that, and when I enter that, it points me to the page), so I tried Mem, but still I get 404. Can you have multiple models in the schema, or am I doing something wrong?

The prisma studio is seeing both models

Can you share

  • a repo
  • some screenshots (with urls and browser console, dev console messages)
  • the page and route names (actually the entire Routes.js would be helpful)

that you are using such that a 404 not found is seen?

Also, the Router Docs may be helpful as a reference:

Hi, thanks for your help.

Here are the things you asked for.

Repo

Routes.js

I can only include one embedded item in the post, as I am a new contributor, so let me know if you want more

Please let me know if you need more

Regards

@Boadicea45 I believe the issue with the members-dbs path (aka … the route named membersDbs):

import { Router, Route } from '@redwoodjs/router'

const Routes = () => {
  return (
    <Router>
      <Route path="/orgs/new" page={NewOrgPage} name="newOrg" />
      <Route path="/orgs/{id:Int}/edit" page={EditOrgPage} name="editOrg" />
      <Route path="/orgs/{id:Int}" page={OrgPage} name="org" />
      <Route path="/orgs" page={OrgsPage} name="orgs" />
      <Route path="/members-dbs/new" page={NewMembersDBPage} name="newMembersDB" />
      <Route path="/members-dbs/{id:Int}/edit" page={EditMembersDBPage} name="editMembersDB" />
      <Route path="/members-dbs/{id:Int}" page={MembersDBPage} name="membersDb" />
      <Route path="/members-dbs" page={MembersDBSPage} name="membersDbs" />
      <Route path="/members" page={MembersPage} name="members" />
      <Route path="/about" page={AboutPage} name="about" />
      <Route notfound page={NotFoundPage} />
      <Route path="/" page={HomePage} name="home" />
    </Router>
  )
}

export default Routes

Is with your service and graphql query:

Uncaught Error: Error: Cannot read property 'findMany' of undefined
    at withCell.js:106
    at Query (Query.js:7)
    at renderWithHooks (react-dom.development.js:14803)
    at updateFunctionComponent (react-dom.development.js:17034)

which you can see if you view the development tools and console.

The MembersDbPage uses the MembersDbCell which queries:

export const QUERY = gql`
  query FIND_MEMBERS_DB_BY_ID($id: Int!) {
    membersDb: membersDb(id: $id) {
      id
      email
      phone
      name
      street_address
      suburb
      state
      postcode
    }
  }
`

But when you look at the service membersDbs.js:

import { db } from 'src/lib/db'

export const membersDbs = () => {
  return db.membersDb.findMany()
}

export const membersDb = ({ id }) => {
  return db.membersDb.findOne({
    where: { id },
  })
}
...

That does match your schema …

export const schema = gql`
  type MembersDb {
    id: Int!
    email: String!
    phone: Int
    name: String!
    street_address: String!
    suburb: String!
    state: String!
    postcode: Int!
  }

  type Query {
    membersDbs: [MembersDb!]!
    membersDb(id: Int!): MembersDb
  }
...

but that does not match your model:

model Mem {
  id             Int    @id @default(autoincrement())
  email          String @unique
  phone          Int?
  name           String
  street_address String
  suburb         String
  state          String
  postcode       Int
}

.

So things are rather out of sync.

I am going to assume you want a table/model named Mem.

The quickest way to rectify this is to:

yarn rw g scaffold mem --force

And then remove all the other scaffolded/generated sdl, services, pages, etc.

If your issue is that you want Mem to be named “something else” then you need to cleanup the datatase, rename the model in schema, re-gen/scaffold, etc.

Few notes on your model:

model Mem {
  id             Int    @id @default(autoincrement())
  email          String @unique
  phone          Int?
  name           String
  street_address String
  suburb         String
  state          String
  postcode       Int
}

Phone and postcode as Int may not be the best choice. For example, you could not store +1-555-1212 or a post code of 02113 or any other non US values.

Also, since the structure is the same you could have a Mem and and Org with a relation to an Address – or you could add an enum with a EntityType of MEMBER or ORG and use a self-relation: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations#self-relations

Hope this helps!

1 Like

Thanks David,

That worked well!, and now I have two databases. Perhaps I haven’t got to the bit in the tutorial yet, but I am assuming that I can do forms and reports etc that combine both data bases? I want to have a database just with info about members, and then another which records activities.

I’m really enjoying Redwood, and it looks like something I’ll be using quite a bit.

Regards

David

1 Like

Great!

Just to be clear, I think you mean “tables”

Definitely, though you’ll need to have a “relation” between the tables (aka models).

Here’s some info on relations in Prisma: https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema/relations

And also a nice primer on data modeling in general:

and

2 Likes

Thanks for those links DT :slight_smile:

Scanning through them, and found this quote

There are two schools of thought about performing computations in your database: people who think it’s great, and people who are wrong.

So how do we write stored procedures when in the RW ecosystem?

1 Like

There are two schools of thought about performing computations in your database: people who think it’s great, and people who are wrong.

Just. Amazing. :rofl:

I don’t know if I would want to write stored procedures (honestly I haven’t written them since my SQLServer or Oracle days) vs good services.

Prisma can execute and run raw SQL.

In fact I’ve uses some rawQuery top do CTE’s (with / select) and some custom aggregation.

const TAG_SUMMARY_SQL = `
  SELECT
    label,
    "entityTypes",
    count(label) AS "totalCount",
    sum(mentions) AS "totalMentions",
    avg(mentions) AS "avgMentions",
    min(mentions) AS "minMentions",
    max(mentions) AS "maxMentions",
    avg(confidence) AS "avgConfidence",
    min(confidence) AS "minConfidence",
    max(confidence) AS "maxConfidence",
    avg(salience) AS "avgSalience",
    min(salience) AS "minSalience",
    max(salience) AS "maxSalience",
    avg(sentiment) AS "avgSentiment",
    min(sentiment) AS "minSentiment",
    max(sentiment) AS "maxSentiment"
  FROM
    "Tag" t
  WHERE
    NOT('date' = ANY ("entityTypes"))
  GROUP BY
    1,
    2
  ORDER BY
    4 DESC,
    1
`

and for charting:

const BUMP_CHART_SQL = `
WITH t1 AS (
	SELECT
		(date_trunc('day'::text,
				t. "publishedAt"))::date AS tweet_date,
		g.label,
		count(g.label) as total
	FROM
		"TweetPriority" g
		JOIN "Tweet" t ON t.id = g. "tweetId"
	WHERE
		g.label != 'Wine'
	GROUP BY
		1,
		2
),

all_dates as (
  SELECT DISTINCT tweet_date, 0 AS total FROM t1 ORDER BY tweet_date
),

all_priorities AS (
	SELECT DISTINCT
		label
	FROM
		t1
),

all_data AS (
	SELECT
		*
	FROM
		all_dates
	CROSS JOIN all_priorities
),

interpolated_data AS (
  SELECT
			ad.label AS id,
			ad.tweet_date AS x,
			coalesce(t1.total, ad.total) AS y
		FROM
			t1
			full join all_data ad on ad.tweet_date = t1.tweet_date and ad.label = t1.label
			order by 1, 2
)

SELECT
	row_to_json(bump) AS priority_bump_data
FROM (
	SELECT
		d.id,
		to_json(array_agg(to_jsonb (row_to_json(d)) - 'id')) AS data
	FROM (
		SELECT
			id,
			x,
			y
		FROM
			interpolated_data
    ORDER BY id, x
      ) d
	GROUP BY
		1) bump;
`

and you can just

export const bumpChart = async () => {
  const result = await db.$queryRaw(BUMP_CHART_SQL)
  const chartDataSeries = result?.map((chart) => {
    return chart.priority_bump_data
  })
  return { chart: chartDataSeries }
}

and get some nicely formatted data back to render a chart

Note:

1 Like

Cool chart!

PostgreSQL has support for SPs written in js. So the RW way, imo, would be to have somewhere to store js files, that will be sent off to the DB and used as SPs there. And then of course some way to call those SPs using some nice Prisma syntax (Prisma doesn’t have support for SPs yet, so that would have to land first)

1 Like

I didn’t know that – I may have to try that out.

One thing I wish Prisma did support was views and materialized views.

Those would enable normal use of SDL and querying (just no mutations b/c the model would be read only) and save from inlining some complex sql.