What I wanted was to codify and standardize the types of things that we were already doing and just remove choice and remove friction and just give people the ability to sit down and say,

"All right, I know these technologies already; I have the prerequisite knowledge to do this."

Tom Preston-Werner - Full Stack Radio

• In part 1 we installed and created our first RedwoodJS application
• In part 2 we created links to our different page routes and a reusable layout for our site.

In this part we'll get our database up and running and learn to create, retrieve, update, and destroy blog posts. So far we've been working in the web folder. In our api folder there is a folder called db for our Prisma schema.

Prisma is an ORM that provides a type-safe API for submitting database queries which return JavaScript objects. It was selected by Tom in the hopes of emulating Active Record's role in Ruby on Rails.

3.1 schema.prisma

The Prisma schema file is the main configuration file for your Prisma setup. It is typically called schema.prisma

// api/db/schema.prisma

datasource DS {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

generator client {
  provider      = "prisma-client-js"
  binaryTargets = "native"
}

model UserExample {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

In order to set up Prisma Client, you need a Prisma schema file with:

• your database connection
• the Prisma Client generator
• at least one model

We'll delete the default UserExample model and make a Post model with an id, title, body, and createdAt time.

// api/db/schema.prisma

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  body      String
  createdAt DateTime @default(now())
}

3.2 seeds.js

seeds.js is used to populate your database with any data that needs to exist for your app to run at all (maybe an admin user or site configuration).

// api/db/seeds.js

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

dotenv.config()
const db = new PrismaClient()

async function main() {
  console.info('No data to seed. See api/db/seeds.js for info.')
}

main()
  .catch((e) => console.error(e))
  .finally(async () => {
    await db.$disconnect()
  })

3.3 redwood db save

Running yarn rw db save generates the folders and files necessary to create a new migration.

yarn rw db save

This will create our database migration.

It is named migration by default.

• Data sources specify the details of the data sources Prisma should connect to such as a PostgreSQL database
• Generators specify what clients should be generated such as the Prisma Client
• Data model definition specifies your application models and their relations

3.4 migrations

A migration defines the steps necessary to update your current schema.

The migrate command creates and manages database migrations. It can be used to create, apply, and rollback database schema updates in a controlled manner.

The README contains a human-readable description of the migration.

It includes metadata like:

• when the migration was created and by who
• a list of the actual migration changes
• a diff of the changes that are made to the schema.prisma file

schema.prisma in the migration folder is the schema that will be created if the migration is applied to the project.

3.5 steps.json

steps.json is an alternative representation of the migration steps that will be applied.

{
  "version": "0.3.14-fixed",
  "steps": [
    {
      "tag": "CreateSource",
      "source": "DS"
    },
    {
      "tag": "CreateArgument",
      "location": {
        "tag": "Source",
        "source": "DS"
      },
      "argument": "provider",
      "value": "\"sqlite\""
    },
    {
      "tag": "CreateArgument",
      "location": {
        "tag": "Source",
        "source": "DS"
      },
      "argument": "url",
      "value": "\"***\""
    },
    {
      "tag": "CreateModel",
      "model": "Post"
    },
    {
      "tag": "CreateField",
      "model": "Post",
      "field": "id",
      "type": "Int",
      "arity": "Required"
    },
    {
      "tag": "CreateDirective",
      "location": {
        "path": {
          "tag": "Field",
          "model": "Post",
          "field": "id"
        },
        "directive": "id"
      }
    },
    {
      "tag": "CreateDirective",
      "location": {
        "path": {
          "tag": "Field",
          "model": "Post",
          "field": "id"
        },
        "directive": "default"
      }
    },
    {
      "tag": "CreateArgument",
      "location": {
        "tag": "Directive",
        "path": {
          "tag": "Field",
          "model": "Post",
          "field": "id"
        },
        "directive": "default"
      },
      "argument": "",
      "value": "autoincrement()"
    },
    {
      "tag": "CreateField",
      "model": "Post",
      "field": "title",
      "type": "String",
      "arity": "Required"
    },
    {
      "tag": "CreateField",
      "model": "Post",
      "field": "body",
      "type": "String",
      "arity": "Required"
    },
    {
      "tag": "CreateField",
      "model": "Post",
      "field": "createdAt",
      "type": "DateTime",
      "arity": "Required"
    },
    {
      "tag": "CreateDirective",
      "location": {
        "path": {
          "tag": "Field",
          "model": "Post",
          "field": "createdAt"
        },
        "directive": "default"
      }
    },
    {
      "tag": "CreateArgument",
      "location": {
        "tag": "Directive",
        "path": {
          "tag": "Field",
          "model": "Post",
          "field": "createdAt"
        },
        "directive": "default"
      },
      "argument": "",
      "value": "now()"
    }
  ]
}

Steps are actions that resolve into zero or more database commands. Steps generically describe models, fields and relationships, so they can be easily translated to datasource-specific migration commands.

3.6 redwood db up

This command generates the Prisma client and applies migrations. The database is migrated up to a specific state.

yarn rw db up

This will run the commands against the database to create the changes we need. This results in a new table called Post with the fields we defined.

The following datasource defines a SQLite database.

After checking for data loss the database actions will be executed. In this instance we just have a single CreateTable statement for our Post model.

The Prisma client will then be generated.

3.7 redwood generate scaffold

A scaffold quickly creates a CRUD interface for a model by generating all the necessary files and corresponding routes.

yarn rw g scaffold post

This will generate pages, SDL's, services, layouts, cells, and components based on a given database schema Model.

Look at all the stuff I'm not doing! Open the browser and enter localhost:8910/posts.

We have a new page called Posts with a button to create a new post. If we click the new post button we are given an input form with fields for title and body.

We were taken to a new route, /posts/new. Let's create a blog post about everyone's favorite dinosaur.

If we click the save button we are brought back to the posts page.

We now have a table with our first post.

When we click the edit button we are taken to a route for the individual post that we want to edit. Each post has a unique id.

The title has now been changed from 01-deno to 01-deno-edit. Now we'll create another post.

If we click the delete button we will be given a warning.

Click ok to confirm and you'll receive a message saying your post has been deleted.

Lets add another blog post:

And one more:

If we make more posts they will start on the id after the deleted item. We already had an id 2. You can't call something else id 2, that would make your database a liar.

In the next part we'll look at the code that powers this functionality and learn about Cells. We'll also set up our frontend to query data from our backend to render a list of our blog posts to the front page.