Introduction#

GitHub this note shows

  • Integrate existing progresql and prisma to a NextJS project
  • Setup primsa and test
  • Integrate with NextJS
  • Explore postgres and pg drivers

Setup Prisma#

  • Setup prisma for NextJS
  • Integrate with an existing database

Let create a new NextJS project

npx create-next-app@latest

Then install prisma

npm install prisma --save-dev

Then install prisma client

npm install @prisma/client

Let integrate (instropect) and existing progresql database. This below command will create .env file

npx prisma init

Update .env file with information for connecting to the database

DATABASE_URL="postgresql://demo:Mike@865525@localhost:5432/dvdrental?schema=public"

Now instropect the existing database to generate schema. This will create schema.prisma in prisma directory

npx prisma generate

Next steps, create PrismaClient and develope applications

Prisma Test#

Run a ts script as a test in NextJS project

npx ts-node --skip-project test/test.ts

Here is the test.ts content

import { PrismaClient } from '@prisma/client'


const prisma = new PrismaClient()


;(BigInt.prototype as any).toJSON = function () {
  return this.toString()
}


const getBooks = async () => {
  try {
    const books = await prisma.book.findMany()
    console.log(books)


    console.log(JSON.stringify({ books }), null, 2)
  } catch (error) {
    console.log(error)
  }
}


getBooks()

Book Page#

Let create a simple book page by querying the book table.

import { PrismaClient } from '@prisma/client'


const prisma = new PrismaClient()


const Home = async () => {
  let books: any[]


  try {
    books = await prisma.book.findMany()
  } catch (error) {
    console.log(error)
    books = []
  }


  return (
    <main className="flex min-h-screen flex-col items-center justify-between">
      <div className="max-w-3xl mx-auto bg-orange-50">
        <div className="grid grid-cols-1 gap-5 md:grid-cols-2">
          {books.map((book: any) => {
            return (
              <div key={book.id} className="bg-slate-200 px-2 py-2">
                <h4>{book.title}</h4>
                <h4>{book.author}</h4>
                <img
                  src="programming-rust.jpg"
                  alt="test"
                  className="w-[200px] h-auto float-left mr-2"
                ></img>
                <p>
                  Lorem ipsum dolor sit amet, consectetur adipisicing elit. Sunt
                  nemo ipsam dolore hic eligendi aut quam quae odio optio
                  laudantium non perferendis consectetur minima maiores
                  consequuntur similique beatae, doloribus quas. Lorem ipsum
                  dolor sit, amet consectetur adipisicing elit. Enim doloremque
                  possimus quis non, numquam alias repudiandae, totam quas
                  dolorem ipsa quisquam mollitia sequi dignissimos, dolores quo
                  necessitatibus nostrum facilis distinctio? Lorem ipsum dolor
                  sit, amet consectetur adipisicing elit. Blanditiis, commodi,
                  eveniet nulla mollitia, voluptatibus veniam libero illo autem
                  totam recusandae debitis ex. Ut animi dignissimos, rerum
                  voluptate nulla sint impedit?
                </p>
              </div>
            )
          })}
        </div>
      </div>
    </main>
  )
}


export default Home

Setup Project#

Let init a new TS project

npm init

Then install

npm install --save-dev typescript tslint @types/node

Project structure

|--node_moduels
|--src
   |--index.ts
   |--test-pg.ts
|--tsconfig.json
|--package.json

Content of tsconfig.json

{
  "compilerOptions": {
    "lib": ["ES2015"],
    "module": "CommonJS",
    "outDir": "dist",
    "sourceMap": true,
    "strict": true,
    "target": "ES2015"
  },
  "include": ["src"]
}

Postgresql Driver#

Let install it HERE

$ npm install postgres

Let create a connection, run a query, and end the connection.

import * as postgres from 'postgres'


const sql = postgres({
  host: 'localhost',
  port: 5432,
  database: 'dvdrental',
  user: 'demo',
  pass: 'Mike@865525'
})


const getBooks = async () => {
  const books = await sql`
        select 
        author, 
        title, 
        book
        from book
    `
  // close connection
  sql.end()
  return books
}


const main = async () => {
  const books = await getBooks()
  console.log(books)
  console.log('Hello')
}


main()

Pg Driver#

Let install the pg driver HERE

npm i pg
npm i --save-dev @type/pg

Let create a client, run a query and close connection

import { Client } from 'pg'


const client = new Client({
  host: 'localhost',
  port: 5432,
  user: 'demo',
  password: 'Mike@865525',
  database: 'dvdrental'
})


const getBooks = async () => {
  await client.connect()


  console.log('query book table ...')
  try {
    const books = await client.query('SELECT author, title from book;')
    console.log(books)
  } catch (error) {
    console.log(error)
  }


  await client.end()
}


const main = async () => {
  await getBooks()
}


main()