Setup Your Database for TweetyTag

Before TweetyTag can auto post the users that have registered to be tagged in TwitterSpace reminders, the database must be setup. We will be using Prisma ORM as the tool to connect a Node backend to MySQL with Aiven.

1. Create database with Aiven
2. Prisma Client for database access
3. Read/Write Query
4. Write to database
5. Read from database

1. Create Service in your Aiven Account:

See Getting started with Aiven for MySQL.

Once the database is running you will see the following:

The connection information will become avaiable:

2. Using the Relational databases article from Prisma, Create project setup

Continue by connecting your database. Prisma has the default database provider set to postgresql so make sure to change the provider to mysql.

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

Change to:

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

Include the following for our models.

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  title     String   @db.VarChar(255)
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  user   User    @relation(fields: [userId], references: [id])
  userId Int     @unique
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

Now that the Prisma Client is setup, we'll create a file to read and write to the database.

3. Create a new file named index.js:

This will allow you to read and write from the same file.

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  await prisma.user.create({
    data: {
      name: 'Alice',
      email: 'alice@prisma.io',
      posts: {
        create: { title: 'Hello World' },
      },
      profile: {
        create: { bio: 'I like turtles' },
      },
    },
  })

  const allUsers = await prisma.user.findMany({
    include: {
      posts: true,
      profile: true,
    },
  })
  console.dir(allUsers, { depth: null })
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

To ensure the database can write a query, run the following command in your terminal:

node index.js

4. Create a new file named write.js:

To query the DB seperately, add the following to write.js:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  await prisma.user.create({
    data: {
      name: 'Alice2',
      email: 'alice2@prisma.io',
      posts: {
        create: { title: 'Hello World' },
      },
      profile: {
        create: { bio: 'I like turtles' },
      },
    },
  })
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

To ensure the database can write a query, run the following command in your terminal:

node write.js

5. Create a new file named read.js:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
    const allUsers = await prisma.user.findMany({
      include: {
        posts: true,
        profile: true,
      },
    })
    console.dir(allUsers, { depth: null })
  }

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

To ensure the database can read the query, run node read.js in your terminal.

node read.js

Success of the node read.js command will look like this:

[
  {
    id: 1,
    email: 'alice@prisma.io',
    name: 'Alice',
    posts: [
      {
        id: 1,
        createdAt: 2022-10-24T23:06:12.491Z,
        updatedAt: 2022-10-24T23:06:12.491Z,
        title: 'Hello World',
        content: null,
        published: false,
        authorId: 1
      }
    ],
    profile: { id: 1, bio: 'I like turtles', userId: 1 }
  }
]

In the next article of this blog series, we will review how to setup your Prisma Client to allow front end input into your database. In the final post, we will share how to connect to the Twitter API.

Follow the TweetyTag journey by following Jenn on:

• GitHub
• Twitter
• LinkedIn
• DevTo