DEV Community: Prisma

Extend your Platformatic API with Prisma Client

Ruheni Alex — Fri, 16 Dec 2022 11:22:17 +0000

In this part, you will learn how to extend the auto-generated GraphQL and REST API by Platformatic using Prisma Client.

You will create a GraphQL mutation and a REST API endpoint to increment the views on a post record.

If you're interested in learning how to use Prisma to model your database schema and auto-generate database migrations when working with Platformatic, refer to part 2 of this series.

Note: It is recommended you type out the changes over copying and pasting the code.

Prerequisites

Your dev toolbox

To follow along, ensure you have the following installed:

Node.js v18 or later
npm v7 or later
Prisma VSCode Extension (optional)
Thunder Client VSCode Extension (optional)
Docker (optional)

Note: If you don't have Docker installed, you can set up a free hosted database on Railway or install PostgreSQL.

Assumed knowledge

This guide will require you have basic familiarity in the following technologies:

JavaScript
GraphQL APIs
REST APIs

Set up your Platformatic API

The starting point of this tutorial is part 2 of this series. If you're following along from the previous part, you can jump to Extend the functionality of your API with a plugin section of this guide.

In this tutorial, you'll use the following starter repository. It contains the setup files for a new Platformatic project.

To get started, clone the repository and checkout to the automated-migrations branch.

Clone the repository:

git clone -b extend-plugin https://github.com/ruheni/prisma-platformatic.git

Now, perform the following actions to get started:

Navigate to the cloned directory:
```
cd prisma-platformatic
```
Install dependencies:
```
npm install
```
Create a .env file based-off of the .env.example file:
```
cp .env.example .env
```
Start the PostgreSQL database with docker:
```
docker-compose up -d
```
Note: If you already have an existing database server running locally, update the value of the DATABASE_URL in the .env file with your database's user and password values:
```
DATABASE_URL="postgres://<USER>:<PASSWORD>@localhost:5432/blog"
```
Connect to your database instance using psql or your preferred SQL client. Copy and run the following SQL to create a database:
```
CREATE DATABASE blog;
```

Generate an up and down migration:
```
npx db-diff
```
Apply the up migration to your database:
```
npx platformatic db migrations apply
```
Populate the database with sample data:
```
npx platformatic db seed seed.js
```

Note: If you're following along from the previous part, you can skip this step.

Generate Prisma Client:

```
npx prisma generate
```

Project structure and files

The project has the following structure:
The important files in the directory are:

.env: Contains the database connection string for your PostgreSQL database.
docker-compose.yml: Defines the Docker image and configuration for your PostgreSQL database.
package.json: Defines your application dependencies. platformatic is currently the only dependency in the project.
platformatic.db.json: Defines Platformatic's configuration such as the server's hostname and port, migration directory, and your database's connection string.
seed.js: Contains a script for populating the database with seed data.
schema.prisma: Defines the structure of your database schema.

The schema.prisma file contains two models: User and Post, with a one-to-many relation.

Extend the functionality of your API with plugins

Define the `plugin` property in your `platformatic.db.json` file

The first step is defining the plugin configuration in your platformatic.db.json file. Paste the following snippet to in your configuration file right after the migrations property:

  "plugin": {
    "path": "./plugin.js"
  }

Expand to view the entire file

{
  "server": {
    "logger":{
      "level": "info"
    },
    "hostname": "127.0.0.1",
    "port": "3042"
  },
  "core": {
    "connectionString": "{DATABASE_URL}"
  },
  "migrations": {
    "dir": "./migrations"
  },
  "plugin": {
    "path": "plugin.js"
  }
}

The snippet defines the path to your plugin. The plugin will be registered when your server starts, exposing the custom logic you've defined.

Create a plugin

At the root of your directory, create a new file called plugin.js. Paste the following snippet to your plugin file:

// ./plugin.js
module.exports = async (app) => {
  app.log.info('plugin loaded')
}

The file exports an async function with an app parameter that's a FastifyInstance.

When you you start up Platformatic with npx platformatic db start, Platformatic will register the plugin. "plugin loaded" will also be logged to your console.

With everything set up, let's take a look at how you can extend your API using Prisma Client.

Extend the GraphQL API

You'll extend the GraphQL API by defining a mutation called incrementPostViewCount. The mutation will have one argument, id that is an ID type. The mutation response type will be a Post object type.

To implement the GraphQL mutation, make the following changes to your plugin.js file:

// ./plugin.js

// 1. 
const { PrismaClient } = require("@prisma/client")

// 2
const prisma = new PrismaClient()

module.exports = async (app) => {
  app.log.info('plugin loaded')

  //3.
  app.graphql.extendSchema(`
    extend type Mutation {
      incrementPostViewCount(id: ID): Post
    }
  `)

  //4.
  app.graphql.defineResolvers({
    Mutation: {
      incrementPostViewCount: async (_, { id }) => {
        // 5. 
        const post = await prisma.post.update({
          where: {
            id: Number(id)
          },
          data: {
            viewCount: {
              increment: 1
            }
          }
        })

        // 6.
        if (!post) throw new Error(`Post with id:${id} was not found`)
        // 7.
        return post
      }
    }
  })
}

The above snippet does the following:

Imports Prisma Client.
Creates a new instance of Prisma Client.
Defines the incrementPostViewCount mutation type definition. It also defines the corresponding argument and the return type.
Defines the resolver for your mutation.
Makes a query to the database on the Post model to increment a post's view count.
Adds error handling in the event a record with the matching id was not found.
Returns the updated post on success.

As you're defining the incrementPostMutation resolver, you will notice Prisma Client provides auto-completion. On hovering on the incrementPostViewCount resolver and post variable, VS Code infers the types for you.

You can head over to http://localhost:3042/graphiql to try out the mutation you just created.

mutation INCREMENT_POST_VIEWS {
  incrementPostViewCount(id: 1){
    id 
    title
    viewCount
  }
}

Expand to view the response

{
  "data": {
    "incrementPostViewCount": {
      "id": "1",
      "title": "Prisma 💚 Platformatic",
      "viewCount": 1
    }
  }
}

Extend the REST API

Next, you'll extend the REST API by defining an endpoint that will accept PUT requests to increment the views of a post.

To implement the REST endpoint, update ./plugin.js file with the following snippet:

// ./plugin.js
const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient()

module.exports = async (app) => {
  app.log.info('plugin loaded')

  // ...GraphQL API

  // 1. 
  app.put('/post/:id/views', async (req, reply) => {
    // 2. 
    const { id } = req.params
    // 3. 
    const post = await prisma.post.update({
      where: {
        id: Number(id)
      },
      data: {
        viewCount: {
          increment: 1
        }
      }
    })

    // 4.
    if (!post) reply.code(404).send({ error: `Post with id:${id} was not found` })
     // 5.
    return reply.send(post)
  })
}

The above code does the following:

Defines the route for incrementing a post's views.
Retrieves the id from the request parameters.
Makes a query to the database on the Post model to increment the post's view count.
Adds error handling in the event a record with the matching id was not found.
Returns the updated post on success.

Expand to see the diff

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

const prisma = new PrismaClient()

module.exports = async (app) => {
  app.log.info('plugin loaded')

  app.graphql.extendSchema(`
    extend type Mutation {
      incrementPostViewCount(id: ID): Post
    }
  `)

  //4.
  app.graphql.defineResolvers({
    Mutation: {
      incrementPostViewCount: async (_, { id }) => {
        // 5. 
        const post = await prisma.post.update({
          where: {
            id: Number(id)
          },
          data: {
            viewCount: {
              increment: 1
            }
          }
        })

        // 6.
        if (!post) throw new Error(`Post with id:${id} was not found`)
        // 7.
        return post
      }
    }
  })

+  // 1. 
+  app.put('/post/:id/views', async (req, reply) => {
+    // 2.
+    const { id } = req.params
+    // 3.
+    const post = await prisma.post.update({
+      where: {
+        id: Number(id)
+      },
+      data: {
+        viewCount: {
+          increment: 1
+        }
+      }
+    })
+    // 4. 
+    if (!post) reply.code(404).send({ error: `Post with id:${id} was not found` })
+    // 5. 
+    return reply.send(post)
+  })
}

You can head over to http://localhost:3042/documentation to view the Swagger docs. You should see the endpoint you just created.

To try out the endpoint you just created, you can make a request to your endpoint using curl:

curl -X PUT \
  'localhost:3042/post/2/views' \
  --header 'Accept: */*' \
  --header 'Content-Type: application/json'

You can also use Thunder Client on VS Code to run the request. To make a request:

Open up Thunder Client on VS Code.
Create a new request by clicking New Request button on the side bar.
Select the PUT method.
Enter it to the following URL: http://localhost:3042/post/1/views.
Click Send.

You should see the following output on your editor:

Recap and next steps

Congratulations! 🎉
Using Prisma Client, you've successfully extended the GraphQL and REST APIs generated with Platformatic API.

In case you missed it, check out part 2 to learn how you can use Prisma to:

Model your database schema
Auto-generate fully customizable SQL migrations

You can find the source code for this project on GitHub. If you run into any challenges, feel free to create a GitHub issue or contribute to the repository. You can also reach out to me directly on Twitter

Friendly data modeling & auto-generated, editable migrations for Platformatic with Prisma

Ruheni Alex — Thu, 15 Dec 2022 09:38:54 +0000

A wise man once said...

... that was me.

But all jokes and memes aside, automation helps cut down the amount of time spent on tedious and repetitive tasks.

This guide will teach you how to model your database schema and auto-generate customizable SQL migrations using Prisma when working with Platformatic.

Prerequisites

Your dev toolbox

To follow along, ensure you have the following installed:

Node.js v16.17.0
npm v7 or later
Prisma VSCode Extension (optional)
Thunder Client VSCode Extension (optional)
Docker (optional)

Note: If you don't have Docker installed, you can set up a free hosted database on Railway or install PostgreSQL.

Assumed knowledge

This guide will require you to have basic familiarity with the following technologies:

JavaScript
GraphQL APIs
REST APIs

Set up your Platformatic app

In this tutorial, you'll use the following starter repository. It contains the setup files for a new Platformatic project.

To get started, clone the repository and checkout to the automated-migrations branch.

Clone the repository:



git clone -b automated-migrations https://github.com/ruheni/prisma-platformatic.git

Now, perform the following actions to get started:

Navigate to the cloned directory:
```
cd prisma-platformatic
```


1. Install dependencies:

    ```bash


    npm install

Create a .env file based-off of the .env.example file:
```
cp .env.example .env
```


1. Start the PostgreSQL database with docker:

    ```


    docker-compose up -d

Note: If you already have an existing database server running locally, update the value of the DATABASE_URL in your .env file with your database's user and password values:
# .env
DATABASE_URL="postgres://<USER>:<PASSWORD>@localhost:5432/blog"
Connect to your database instance using psql or your preferred SQL client. Copy and run the following SQL to create a database:
CREATE DATABASE blog;

Project structure and files

The project has the following structure:



prisma-platformatic
  ├── .env.example
  ├── .env
  ├── .gitignore
  ├── README.md
  ├── docker-compose.yml
  ├── package-lock.json
  ├── package.json
  └── platformatic.db.json

The noteworthy files in the directory are:

.env: Contains the database connection string for your PostgreSQL database.
docker-compose.yml: Defines the Docker image and configuration for your PostgreSQL database.
package.json: Defines your application dependencies. platformatic is currently the only dependency in the project.
platformatic.db.json: Defines Platformatic's configuration such as the server's hostname and port, migration directory, and your database's connection string.

Data modeling and automated migrations

Now that you've set up your application, it's time to get your hands dirty with Prisma!

Set Prisma in your project

To get started, first install the Prisma CLI as a development dependency in your project:



npm install prisma --save-dev

The Prisma CLI provides the tools that allow you to evolve your database schema in your project.

You can now initialize Prisma in your project with the following command:



npx prisma init

The command creates a prisma folder at the root containing a schema.prisma file. The schema.prisma file serves as a source of truth for your database schema.

When you open up the schema.prisma file, you should see the following:



// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

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

The Prisma schema uses an intuitive and human-readable language called the Prisma Schema language.

The schema file is composed of three main components:

Data source: Defines your database connection details such as the provider and database's connection string.
Generator: Defines the assets generated when specific Prisma commands are invoked. In this case, Prisma Client, a type-safe query builder for your database, will be generated.
Data model: Defines the entities of your application that map to your database's tables (for relational databases) or collections (MongoDB). The schema doesn't have any yet, but models are denoted with the model keyword, followed by the entity name.

Model your database schema

For this guide, you will create a Post model with the following fields in your schema.prisma file:



// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

The snippet above defines a model called Post with the following fields and properties:

id: An auto-incrementing integer that will be the primary key for the model.
title: A non-null String field.
content: A nullable String field.
published: A Boolean field with a default value of false.
viewCount: An Int field with a default value of 0.
createdAt: A DateTime field with a timestamp of when the value is created as its default value.

Refer to the Prisma documentation for further details on how to model your data using Prisma.

Generate a migration with `migrate diff`

With the schema defined, you will now auto-generate a database migration using prisma migrate diff.

prisma migrate diff compares (or "diffs") two schemas, the current, and the anticipated version. The current version is the from state, and the anticipated version is the to state. The command generates a SQL script describing the changes.

Fun fact: If you've used the prisma migrate dev command before, it runs prisma migrate diff under the hood.

The command, prisma migrate diff accepts the following schema sources for comparison:

A live database
A migration history
Schema data model (defined in the Prisma schema)
An empty schema

The prisma migrate diff command will use the following arguments to generate a migration:

--from-schema-datasource: Uses the URL defined in the datasource block.
--to-schema-datamodel: Uses the data model defined in the Prisma schema for the diff.
--script (optional): Outputs a SQL script.

The --from-schema-datasource and --to-schema-datamodel also require a path to your Prisma schema file.

Create the migrations directory that you will use to store a history of migrations:



mkdir migrations

The migrations directory is used by Platformatic to store and track the history of applied migrations.

Next, open up a terminal within your project directory run the following command to auto-generate your first migration:



npx prisma migrate diff \
--from-schema-datasource ./prisma/schema.prisma \
--to-schema-datamodel ./prisma/schema.prisma \
--script > migrations/001.do.sql \
--exit-code

Notes:

Update the output filename for any future migrations to prevent overwriting the contents of 001.do.sql

You can jump to the Side quest section to learn how you can automate versioning and generating migrations with the @ruheni/db-diff utility library

If you omit the --script argument, the command will generate a human-readable summary that looks something like this: ```

[+] Added tables

Post

The command creates a file called 001.do.sql inside the migrations directory with the following contents:



-- migrations/001.do.sql
-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "viewCount" INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

You'll notice that the command generated the SQL that describes the changes you defined in the Prisma schema file.

Start your API server

In your project directory, apply the migrations to your database using the Platformatic CLI:



npx platformatic db migrations apply

Next, start up your API server:



npx platformatic db start

The command will:

Start the Platformatic API server
Auto-generate a REST and GraphQL API from your SQL database

Explore and interact with your API

You can now explore your GraphQL API on http://localhost:3042/graphiql or your REST API on http://localhost:3042/documentation.

Run the following mutation on GraphiQL to insert a record in your database:



mutation INSERT_POST {
  insertPost(
    inputs: {
      title: "Prisma 💚 Platformatic"
      content: "Learn how you can auto-generate your database migrations using Prisma for Platformatic"
    }
  ) {
    id
    title
    content
    createdAt
    published
  }
}

You should see the following output with a different createdAt value:



{
  "data": {
    "insertPost": [
      {
        "id": "1",
        "title": "Prisma 💚 Platformatic",
        "content": "Learn how you can auto-generate your database migrations using Prisma for Platformatic",
        "createdAt": "2022-10-08T14:26:08.101Z",
        "published": false
      }
    ]
  }
}

Congratulations! 🎉

Introspect your database for the `versions` model

Under the hood, Platformatic uses Postgrator to run migrations. Postgrator creates a table in the database called versions to track the applied migrations.

The versions table is not yet captured in the Prisma schema. When auto-generating future migrations, Prisma might prompt you to drop the versions table, which is not ideal.

To prevent this, you can run prisma db pull to introspect the database and populate the Prisma schema with the missing model:



npx prisma db pull

Your Prisma schema should now contain a version model:



// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

+model versions {
+  version BigInt    @id
+  name    String?
+  md5     String?
+  run_at  DateTime? @db.Timestamptz(6)
+}

Add the @@ignore attribute function to the model to exclude it from the Prisma Client API:



// ./prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
  createdAt DateTime @default(now())
}

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)
+
+  @@ignore
}

Side quest 🧙🏽: Automate versioning and generation of your database migrations

The approach for generating migrations in the previous section generally works fine. The only caveat is that you have to manually specify the version of the migration file with every migration, i.e., 001.do.sql, 002.do.sql, and so forth.

Another friction point is that the command is very long, tedious and there is a possibility of making an error.

To get around these friction points, I built a utility library called @ruheni/db-diff. The tool wraps around the prisma migrate diff command. It can generate an up and a down migration. @ruheni/db-diff also versions the generated migration file and are Postgrator-compatible. On top of that, you can generate an up and down migration for every schema change.

Alternatively, you can also use platformatic-prisma by Kishan Gajera

Install the helper utility

To get started, you can install @ruheni/db-diff as a development dependency in your project:



npm install --save-dev @ruheni/db-diff

Update your schema

Next, update your Prisma schema by creating a User model with the following fields:

id: the primary key with an auto-incrementing integer
email: a string value with a @unique constraint
name: a string value (nullable/ not-required)
posts: a one-to-many relationship between the Post and User models, respectively

Your Prisma schema should resemble the schema in the code block below:



// ./prisma/schema.prisma
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

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

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

Expand here to see the schema diff



// ./prisma/schema.prisma
+model User {
+  id    Int     @id @default(autoincrement())
+  email String  @unique
+  name  String?
+  posts Post[]
+}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String?
  published Boolean  @default(false)
  viewCount Int      @default(0)
+  author    User?    @relation(fields: [authorId], references: [id])
+  authorId  Int?
}

model versions {
  version BigInt    @id
  name    String?
  md5     String?
  run_at  DateTime? @db.Timestamptz(6)

  @@ignore
}

Auto-generate an `up` migration using `@ruheni/db-diff`

Next, use @ruheni/db-diff to auto-generate an up migration:



npx db-diff --up

The command should generate a new file called 002.do.sql with the following contents:



-- migrations/002.do.sql
-- AlterTable
ALTER TABLE "Post" ADD COLUMN     "authorId" INTEGER;

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

You can specify the type of migration you would like to generate by passing either --up for only the up migration or --down for the down migration.

@ruheni/db-diff utility library will auto-generate an up and a down migration if you don't provide either the --up or --down flags. If you maintain down migrations, ensure the migration version name is at par with the up migration.

Apply the generated migration using Platformatic CLI:



npx platformatic db migrations apply

Restart and interact with your API using Platformatic

Restart the API server:



npx platformatic db start

Platformatic will regenerate the GraphQL and REST APIs.

Open up GraphiQL on http://localhost:3042/graphiql and run the following mutation to create a user record in your database:



mutation INSERT_USER {
  insertUser(inputs: { name: "Alex", email: "alex@prisma.io" }) {
    id
    name
  }
}

Expand to view the response



{
  "data": {
    "insertUser": [
      {
        "id": "1",
        "name": "Alex"
      }
    ]
  }
}

Run another query to link the user record with the existing post record you created in a previous step:



mutation SAVE_POST {
  savePost(input: { id: 1, authorId: 1 }) {
    id
    title
    content
    author {
      name
    }
  }
}

Expand to view the response



{
  "data": {
    "savePost": {
      "id": "1",
      "title": "Prisma 💚 Platformatic",
      "content": "Learn how you can auto-generate your database migrations using Prisma for Platformatic",
      "user": {
        "name": "Alex"
      }
    }
  }
}

And you're all done! 🎉

Wrapping up

To recap what was covered in this part, you:

Modeled your database schema using Prisma
Used the prisma migrate diff to auto-generate your SQL migrations
Created a GraphQL and REST API using Platformatic
Used the @ruheni/db-diff utility to auto-generate and version your SQL migrations

The next article will cover how you can extend the generated GraphQL and REST API using Prisma Client.

Feel free to refer to prisma migrate diff reference docs to learn how you can use it to automate your database migration workflows. If you build something cool you would like to share with the rest of the world, feel free to share it in this GitHub discussion thread.

In the event you run into any issues working with @ruheni/db-diff, feel free to create a GitHub issue or contribute to the library.

Happy hacking! 🚀

Why Prisma and Platformatic are a great match

Ruheni Alex — Wed, 14 Dec 2022 11:27:35 +0000

Platformatic is a new tool that helps Node.js developers ship APIs faster. In this 3-part series, we’ll explore why Prisma and Platformatic are such a great fit and how Prisma can help improve your data modeling, migrations, and querying workflows when using Platformatic.

What is Platformatic DB

Platformatic DB is an open-source tool that auto-generates CRUD APIs for your database. It supports both GraphQL and REST (with an OpenAPI schema) APIs.

It also allows you to extend the API using plugins and add authentication as well as authorization for your APIs. Platformatic DB supports SQLite, MySQL, MariaDB, and PostgreSQL databases.

What is Prisma

Prisma is an open-source ORM (Object Relational Mapper). It provides a declarative way to define the structure of your database using the Prisma schema language. It also provides tools for evolving your database schema by auto-generating fully customizable SQL migrations.

Prisma provides a type-safe and intuitive client for interacting with your database called Prisma Client.

How Prisma can improve your development workflow with Platformatic DB

While Platformatic speeds up building REST and GraphQL APIs from your database, Prisma can complement your development workflow in several ways.

In the following sections, we'll explore three concrete ways Prisma makes working with Platformatic DB easier.

Intuitive data modeling language

Prisma provides a Prisma schema — an intuitive, human-readable declarative definition of your database schema. The Prisma schema serves as the source of truth for your database schema by describing its desired end state.

One of the benefits of using the Prisma schema is the auto-completion, with the help of the Prisma extension, and type-checking when modeling your database schema, allowing you to catch errors early.

The following schema represents:

User and Post models
A one-to-many relationship between the User and Post models

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  name      String?
  email     String   @unique

  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  title     String
  content   String
  published Boolean  @default(false)

  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

Refer to the Prisma docs to learn more about data modeling.

Auto-generated and customizable SQL migrations

To generate your API with Platformatic, you must first define your database schema. The database schema is defined using SQL in a set of migrations files that are written manually. However, maintaining SQL migrations by hand can be tedious, brittle, and error-prone.

// Detect dark theme var iframe = document.getElementById('tweet-1575443725047812098-340'); if (document.body.className.includes('dark-theme')) { iframe.src = "https://platform.twitter.com/embed/Tweet.html?id=1575443725047812098&theme=dark" }

I also think writing your own migrations introduces risk for user error like forgetting to create a foreign key.

Kishan Gajera from Generate Up and Down Migrations for Platformatic DB using Prisma

Editors typically don't provide auto-completion and type-checking when working with SQL. This might make it challenging to spot errors such as missing constraints like foreign keys.

Prisma can help automate this process by generating a fully-customizable migration based on the defined schema using the CLI.

The following SQL migration will be generated based on the schema in the previous section:

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "email" TEXT NOT NULL,
    "name" TEXT,

    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" VARCHAR(255) NOT NULL,
    "content" TEXT NOT NULL,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "authorId" INTEGER,

    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

Type-safety and auto-completion for database queries

Platformatic provides plugins for extending your API. It also provides a SQL mapper for querying the database.

Besides the out-of-the-box SQL mapper, you can alternatively extend your Platformatic API using Prisma Client to query your database. Prisma Client provides fully type-safe queries with rich type definitions based on your Prisma schema.

Platformatic also provides TypeScript support. You can use the CLI to generate TypeScript types for your entities from your database.

If you're working with the VS Code editor, VS Code's intellisense and suggestions will pop up as you define your queries.

Additionally, VS Code can also run your JavaScript file, which uses Prisma Client through the TypeScript compiler and throws errors when a type is violated.

Learn more how you can write type-safe JavaScript with JsDoc in this article

Wrapping up

Platformatic speeds up the development of REST and GraphQL APIs. Prisma pushes the development experience even further by providing intuitive data modeling, auto-generating customizable migrations, type-safety, and auto-completion.

Look out for parts 2 and 3 — modeling, auto-generating database migrations, and extending your API using a plugin with Prisma Client for Platformatic. 🤙🏾

How Prisma Introspects a Schema from a MongoDB Database

Jan Piotrowski — Tue, 03 May 2022 10:40:26 +0000

Prisma ORM recently released support for MongoDB. This represents the first time Prisma has supported a database outside of the SQL world. Prisma has been known for supporting many relational databases, but how did it end up being able to support the quite different MongoDB?

🕴️ I work as the Engineering Manager of Prisma’s Schema team. We are responsible for the schema management parts of Prisma, which most prominently include our migrations and introspection tools, as well as the Prisma schema language and file (and our awesome Prisma VS Code extension!).

The other big team working on Prisma object relational-mapper (ORM) is the Client team that builds Prisma Client and the Query Engine. These let users interact with their database to read and manipulate data.

In this blog post, I summarize how our team got Prisma’s schema introspection feature to work for the new MongoDB connector and the interesting challenges we solved along the way.

Prisma

Prisma is a Node.js database ORM built around the Prisma schema language and the Prisma schema file containing an abstract representation of a user’s database. When you have tables with columns of certain data types in your database, those are represented as models with fields of a type in your Prisma schema.

Prisma uses that information to generate a fully type-safe TypeScript/JavaScript Prisma Client that makes it easy to interact with the data in your database (meaning it will complain if you try to write a String into a Datetime field, and make sure you, for example, include information for all the non-nullable columns without a default and similar).

Prisma Migrate uses your changes to the Prisma schema to automatically generate the SQL required to migrate your database to reflect that new schema. You don’t need to think about the changes necessary. You just write what you want to achieve, and Prisma then intelligently generates the SQL DDL (Data Definition Language) for that.

For users who want to start using Prisma with their existing database, Prisma has a feature called Introspection. You call the CLI command prisma db pull to “pull in” the existing database schema, and Prisma then can create the Prisma schema for you automatically, so your existing database can be used with Prisma in seconds.

This works the same for PostgreSQL, MySQL, MariaDB, SQL Server, CockroachDB, and even SQLite and relies on relational databases being pretty similar, having tables and columns, understanding some dialect of SQL, having foreign keys, and concepts like referential integrity.

Prisma + MongoDB

One of our most requested features was support for Prisma with MongoDB. The feature request issue on GitHub for MongoDB support from January 2020 was for a long time by far the most popular one, having gained more than a total of 800 reactions.

MongoDB is known for its flexible schema and the document model, where you can store JSON-like documents. MongoDB takes a different paradigm from relational databases when modeling data – there are no tables, no columns, schemas, or foreign keys to represent relations between tables. Data is often stored grouped in the same document with related data or “denormalized,” which is different from what you would see in a relational database.

So, how could these very different worlds be brought together?

Prisma and MongoDB: Schema team edition

For our team, this meant figuring out:

How to represent a MongoDB structure and its documents in a Prisma schema.
How to migrate said data structures.
How to let people introspect their existing MongoDB database to easily be able to start using Prisma.

Fortunately solving 1 and 2 was relatively simple:

Where relational databases have tables, columns, and foreign keys that are mapped to Prisma’s models, with their fields and relations, MongoDB has equivalent collections, fields, and references that could be mapped the same way. Prisma Client can use that information to provide the same type safety and functionality on the Client side.

Relational database Prisma MongoDB

Table → Model ← Collection

Column → Field ← Field

Foreign Key → Relation ← Reference
With no database-side schema to migrate, creating and updating indexes and constraints was all that was needed for evolving a MongoDB database schema. As there is no SQL to modify the database structure (which is not written down or defined anywhere), Prisma also did not have to create migration files with Data Definition Language (DDL) statements and could just scope it down to allowing prisma db push to directly bring the database to the desired end state.

Relational database	Prisma	MongoDB
Table →	Model	← Collection
Column →	Field	← Field
Foreign Key →	Relation	← Reference

A bigger challenge turned out to be the Introspection feature.

Introspecting a schema with MongoDB

With relational databases with a schema, there is always a way to inquire for the schema. In PostgreSQL, for example, you can query multiple views in a information_schema schema to figure out all the details about the structure of the database—to, for example, generate the DDL SQL required to recreate a database, or abstract it into a Prisma schema.

Because MongoDB has a flexible schema (unless schemas are enforced through the schema validation feature), no such information store exists that could be easily queried. That, of course, poses a problem for how to implement introspection for MongoDB in Prisma.

Research

As any good engineering team would, we started by ... Googling a bit. No need to reinvent the wheel, if someone else solved the problem in the past before. Searches for “MongoDB introspection,” “MongoDB schema reverse engineering,” and (as we learned the native term) “MongoDB infer schema” fortunately brought some interesting and worthwhile results.

MongoDB Compass

MongoDB’s own database GUI Compass has a “Schema” tab in a collection that can analyze a collection to “provide an overview of the data type and shape of the fields in a particular collection.”

It works by sampling 1000 documents from a collection that has at least 1000 documents in it, analyzing the individual fields and then presenting them to the user.

`mongodb-schema`

Another resource we found was Lucas Hrabovsky’s mongodb-infer repository from 2014. Later that year, it seemed to have merged/been replaced by mongodb-schema, which is updated to this day.

It’s a CLI and library version of the same idea—and indeed, when checking the source code of MongoDB Compass, you see a dependency for mongodb-schema that is used under the hood.

Implementing introspection for MongoDB in Prisma

Usually, finding an open source library with an Apache 2.0 license means you just saved the engineering team a lot of time, and the team can just become a user of the library. But in this case, we wanted to implement our introspection in the same introspection engine we also use for the SQL databases—and that is written in Rust. As there is no mongodb-schema for Rust yet, we had to implement this ourselves. Knowing how mongodb-schema works, this turned out to be straightforward:

We start by simply getting all collections in a database. The MongoDB Rust driver provides a handy db.list_collection_names() that we can call to get all collections—and each collection is turned into a model for Prisma schema. 🥂

To fill in the fields with their type, we get a sample of up to 1000 random records from each collection and loop through them. For each entry, we note which fields exist, and what data type they have. We map the BSON type to our Prisma scalar types (and native type, if necessary). Optimally, all entries have the same fields with the same data type, which is easily and cleanly mappable—and we are done!

Often, not all entries in a collection are that uniform. Missing fields, for example, are expected and equivalent to NULL values in a relational database.

How to present fields with different types

But different types (for example, String and Datetime) pose a problem: Which type should we put into the Prisma schema?

🎓 Learning 1: Just choosing the most common data type is not a good idea.

In an early iteration of MongoDB introspection, we defaulted to the most common type, and left a comment with the percentage of the occurrences in the Prisma schema. The idea was that this should work most of the time and give the developer the best development experience—the better the types in your Prisma schema, the more Prisma can help you.

But we quickly figured out when testing this that there was a slight (but logical) problem: Any time the Prisma Client encounters a type that does not match what it has been told via the Prisma schema, it has to throw an error and abort the query. Otherwise, it would return data that does not adhere to its own generated types for that data.

While we were aware this would happen, it was not intuitive to us how often that would cause the Prisma Client to fail. We quickly learned about this when using such a Prisma schema with conflicting types in the underlying database with Prisma Studio, the built-in database GUI that comes with Prisma CLI (just run npx prisma studio). By default, it loads 100 entries of a model you view—and when there were ~5% entries with a different type in a database of 1000 entries, it was very common to hit that on the first page already. Prisma Studio (and also an app using these schemas) was essentially unusable for these data sets this way.

Fortunately, everything in MongoDB is a Document, which maps to a Json type field in Prisma. So, when a field has different data types, we use Json instead, output a warning in Prisma CLI, and put a comment above the field in the Prisma schema that we render, which includes information about the data types we found and how common they were.

Output of Prisma CLI on conflicting data types

Resulting Prisma schema with statistics on conflicting data types

How to iterate on the data to get to a cleaner schema

Using Json instead of a specific data type, of course, substantially lowers the benefit you get from Prisma and effectively enables you to write any JSON into the field (making the data even less uniform and harder to handle over time!). But at least you can read all existing data in Prisma Studio or in your app and interact with it.

The preferred way to fix conflicting data types is to read and update them manually with a script, and then run prisma db pull again. The new Prisma schema should then show only the one type still present in the collection.

🎓 Learning 2: Output Prisma types in Prisma schema, not MongoDB types.

Originally, we outputted the raw type information we got from the MongoDB Rust driver, the BSON types, into our CLI warnings and Prisma schema comments to help our users iterate on their data and fix the type. It turned out that while this was technically correct and told the user what type the data was in, using the BSON type names was confusing in a Prisma context. We switched to output the Prisma type names instead and this now feels much more natural to users.

While Prisma recommends everyone to clean up their data and minimize the amount of conflicting types that fall back to Json, that is, of course, also a valid choice.

How to enrich the introspected Prisma schema with relations

By adding relation information to your introspected Prisma schema, you can tell Prisma to handle a specific column like a foreign key and create a relation with the data in it. user User @relation(fields: [userId], references: [id]) creates a relation to the User model via the local userId field. So, if you are using MongoDB references to model relations, add @relation to them for Prisma to be able to access those in Prisma Client, emulate referential actions, and help with referential integrity to keep data clean.

Right now, Prisma does not offer a way to detect or confirm the potential relations between different collections. We want to learn first how MongoDB users actually use relations, and then help them the optimal way.

Summary

Implementing a good introspection story for MongoDB was a fun challenge for our team. In the beginning, it felt like two very different worlds were clashing together, but in the end, it was straightforward to find the correct tradeoffs and solutions to get the optimal outcome for our users. We are confident we found a great combination that combines the best of MongoDB with what people want from Prisma.

Try out Prisma and MongoDB with an existing MongoDB database, or start from scratch and create one along the way.

Using Prisma to Address Connection Pooling Issues in Serverless Environments

Oleksii Klochai — Mon, 06 Dec 2021 14:57:32 +0000

This article discusses what “serverless” environments are, why developers choose to deploy their apps to them, and how the Prisma Data Platform solves the problems that can arise regarding database connection pooling.

Choosing which tools you will use to build your application, and which platform you will be deploying to in production, are early and vital decisions that you will have to make when building a product.

Using an ORM like Prisma speeds up the development process, while serverless hosting reduces the ongoing cost and resource overhead of ensuring your application’s availability to your customers.

What is Prisma?

Prisma is a next-generation ORM (Object-Relational Mapping) library for Node.js and TypeScript.

Prisma allows you to build your app in JavaScript or TypeScript and work with your data as objects rather than SQL or NoSQL queries. These objects are mapped to a database using a schema that generates all the types and conversions required to accurately store your objects in your database platform.

The Prisma toolset includes developer tools for generating database migrations so that your database is always in-sync with your data mode as defined in the Prisma schema. In addition, the cloud-based Prisma Data Platform provides tools like user/role management and a data browser that allow you and your team to collaboratively manage your data online.

Using Prisma in your projects means spending less time worrying about your database, and more time to focus on building your app.

In addition to being a great aid to development, the Prisma Data Platform discussed in this article makes Prisma a flexible and efficient way to manage database connections for your application with minimal fuss.

What is “serverless” and why do developers “go serverless”?

Serverless hosting is a means of delivering your app to your user without having to build and maintain a server to deploy it to.

It may sound strange, but it is a cost effective way to deploy applications. Serverless cloud hosting providers like AWS Lambda and Vercel take care of all of the infrastructure required to run your application.

Here are some of the top reasons why developers are going serverless.

Reduced infrastructure requirements

The hosting provider administers all of the infrastructure required to host your app—maintaining it, keeping it up-to-date, and most importantly, keeping it secure. The provider deals with the day-to-day burdens of hosting.

Streamlined deployment

With serverless hosting you are provided tools to readily upload, run, and monitor your code. When a request comes in, your code is launched in a container and executed. The serverless environment discards the container when it is no longer needed.

Reduced costs

In addition to the time and resources saved by not having to maintain your own server infrastructure, you also save on the cost of running a full server 24/7. Many serverless hosting providers like AWS and Azure will only bill you for the resources you use.

Scalability

Serverless hosting offerings like AWS Lambda and Azure Functions can support sustained high load thanks to the cloud providers’ globally distributed infrastructure. In addition, such systems are well-suited for handling bursts of high load amid periods of lower use due to their automatic scaling functionality.

The issues with “connection pooling” in serverless environments

Any database, serverless or not, has a limit on the number of open connections that it can handle at one time. For each open connection, the database system like PostgreSQL or MySQL reserves network, memory, and compute resources so that it’s able to handle any incoming requests through each connection.

In a serverless environment your code does not run constantly. A user makes a request (whether it’s viewing a webpage or requesting an API resource), and a tiny containerized executing environment (called an instance) running only the code required to serve the request is launched. The request is served, and the instance is destroyed.

This is efficient, but there is one problem. In traditional hosting environments with a single server that runs your application, a single instance of the application can service multiple user requests.

In a traditional hosting environment a single instance of the application will be managing a single connection pool to your database. The connection pool ensures that any database queries are within the connection and timeout limits that you have configured. It coordinates the re-use of database connections and prevents the database server from being overloaded.

In a serverless environment, a new instance of your code is launched for every user request. Each instance will be unaware of the others, resulting in multiple connection pools. As these separate connection pools are not aware of one another, they cannot coordinate to ensure the database server is not overloaded.

In a serverless environment, there is no shared connection pool. This creates the risk that a small number of user requests exhaust the database connection limit causing the application to stop working.

Methods of dealing with with connection pooling issues

There are three key ways in which the modern developer can deal with connection pooling issues in a serverless environment.

External connection poolers

One method is to use an external connection pooler. By moving the task of coordinating database connections outside of your serverless app, an external connection pooler solves the issue of the separate execution instances not being able to coordinate with each other.
Not all database servers have an external connection pooler. If this path you choose to mitigate the issue, you should carefully select which tools and platforms will be used in your project early in the development process.

The Prisma Data Proxy is an example of an external connection pooler approach.

Adjusting your code

Another method to overcome connection pooling issues is to ensure that your code is written in a way that accounts for the serverless environment in which it will run. Some cloud providers do reuse resources between requests, so structuring your code so that connections can be maintained between requests is a way to reduce database load.

However, this approach is limited and will only help alleviate the problem to some degree.

Fine tuning your database server

An important step in deploying to a production environment is to monitor and profile your application. Knowing how your application will be used and how busy it will be allows you to determine what resources to allocate. With this information, you can optimize your database connection to reduce the chance of overloading it. For example, you can reduce the connection timeout for inactive connections so that your database is less likely to be spending resources on a connection that’s not going to be used. Another option is to increase the connection limit on the database, but handling more connections will likely require more compute resources.

In addition to supporting these traditional methods of dealing with connection pooling issues, the Prisma Data Platform introduces a reliable and readily configurable database proxy that solves connection pooling issues—regardless of the database server or database host in use.

Solving connection pooling issues with Prisma Data Proxy

Prisma has introduced a new service that effectively solves the connection pooling issue when deploying to serverless environments like AWS Lambda and Vercel.

The Prisma Data Proxy acts as an intermediary between your application code and the database, effectively acting as an external connection pooler for any database supported by Prisma.

Flexible ORM and proxy with external connection pooling

Using the Prisma Data Proxy solves the connection pooling issue without you having to drastically alter your code or spend extra time tweaking your database connection configuration to account for the serverless environment.

The Prisma Data Proxy also reduces the size of the data bundle that you must upload to your serverless hosting environment. Many of the database functions are outsourced to the highly performant Prisma Engine binary hosted by the Prisma Data Proxy, so they do not need to be included with your deployment bundle.

Part of the Prisma Data Platform

The Prisma Data Proxy is part of the Prisma Data Platform, a cloud based platform that helps manage and collaborate on application data.

The Prisma Data Platform includes a powerful databas tools to assist you and your team during the development process. This includes the Prisma Query Console, which enables you to view and manage your live data through a simple interface. It allows you to gain insight into how your application is used and to quickly fix any arising data issues.

The benefits of serverless hosting and Prisma

Serverless hosting in combination with the Prisma Data Platform provides a set of collaborative development and deployment tools for developers who simply want to get on with building their product.
Building and running serverless applications has become a very attractive option for developers due to the reduced resources and costs, and an easier scaling approach.

However, when developing your application you will have to ensure its suitability to be run in a serverless environment. There is nothing inherently wrong with the serverless concept, but there are differences to traditional hosting configurations that must be considered when coding to deploy in such an environment.

The Prisma Data Platform is being actively developed to address the challenges of deploying data-intensive apps to modern serverless platforms while allowing you to use the database and database host you are already comfortable with.

To learn more about databases in the Serverless era and a sneak peak into Prisma's vision for the Prisma Data Platform, check out the talks from the Prisma Serverless Conference.

Get started with the Prisma Data Platform

The Prisma Data Platform, including the Prisma Data Proxy, is currently available in Early Access.

Online documentation is available to get you started, so that you are ready to implement the Prisma Data Platform in your project when it is released.

See the Prisma Data Proxy in action or learn more about how to easily integrate the Prisma Data Platform into your project.

And if you are ready to try Prisma Data Platform out, get started today!

Database Migrations for .NET and Entity Framework with Prisma

Ruheni Alex — Thu, 04 Nov 2021 12:51:58 +0000

Introduction

In this article, you will learn how to model your data using Prisma, run a migration with Prisma Migrate and then introspect your database with .NET to generate your Entity Framework models. This article will use Azure SQL/ SQL Server.

This article only offers an alternative data modelling workflow that is succinct and offer a better development experience. Besides .NET and the Entity framework, you can also use Prisma with your tool/ frameworks of choice such as Django.

Prisma auto-generates migration files in SQL that you can modify before applying them on your database. The workflow following this approach would be:

Model your data
Run a migration using Prisma Migrate.
Use the Entity Framework to reverse engineer/ scaffold/ introspect your database and generate models and your database context.

What is Prisma?

Prisma is an object-relational mapper (ORM) that provides a declarative way to define your database models that are easy to read and comprehend. Prisma also provides Prisma Client – an intuitive and type-safe query builder based off your schema.

At the core of Prisma is the Prisma Schema – aka schema file. The schema contains 3 parts: the data sources, generators and your data model definition.

Prerequisites

To follow along, ensure you have the following:

Familiarity with .NET and the Entity framework
Installed Node.js v 12.6.x or higher
Installed .NET locally
A SQL Server or Azure SQL database
Prisma Extension for VS Code users

Note: If you're not familiar with setting up SQL Server, check out Setting up a local SQL Server database.

Step 1: Create a new console application

The first step is to create a new .NET console app in your working directory. This guide uses a console application to keep it simple. However, feel free to create any other type of application.



dotnet new console --output *__app-name__*

Once the command is done, navigate to the project and confirm that your project is running.



cd *__app-name__*
dotnet run

If the output on the terminal is Hello World!, everything seems to be running just fine! 🎉

Step 2: Add Prisma to your project

On a separate terminal window but still within the same project run the following commands.



mkdir prisma-migrate
cd prisma-migrate
npm init -y

The commands create a new directory, navigate to the folder and initializes it as a Node.js project. The Prisma CLI is the only dependency for this project.



npm install --save-dev prisma

Initialize Prisma

Once the install is done, run the following command.



npx prisma init --database-provider sqlserver

The command:

Creates a .env file at the root of the project for storing environment variables
Creates prisma folder containing a schema.prisma file.
Specifies the database provider as sqlserver in schema.prisma file and provides a dummy sqlserver connection string in .env.

The schema.prisma file defines the database connection and the Prisma Client generator. This is where you will also define your database models.



/// prisma/schema.prisma
datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

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

For this guide, you can delete generator client as it is relevant in a TypeScript/ JavaScript project. The generator client is used to generate the TypeScript definitions for the Prisma Client.

Update .env with your database connection string. Refer to connection details for information on how to configure the connection string.



# .env
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"

If you're using a remote database during development like Azure SQL, open this to learn more about how to configure the shadow database.
Create a new database which will be the shadow database on Azure. Update your .env file to include the SHADOW_DATABASE_URL as well. The shadow database will be used in the data modelling step



# .env
DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"
SHADOW_DATABASE_URL="sqlserver://HOST:PORT;database=DATABASE;user=USER;password=PASSWORD;"

Update your schema.prisma file with the connection string for the shadow database.



/// prisma/schema.prisma
datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

A shadow database is a second, temporary database for use in the local development workflow. When running a migration, the secondary database is created and deleted automatically unless you're using a cloud-hosted database.

Here's an illustration of how the shadow database works:

Data modelling

This example will define 2 models: Post and User.The relationship between User and Post is a one-to-many relationship.

The models defined in schema.prisma will resemble a GraphQL syntax . The models also map to a table in your database.



/// prisma/schema.prisma
model Post {
  id        Int      @id @default(autoincrement())
  content   String?
  title     String
  createdAt DateTime @default(now())
  author    User?    @relation(fields: [authorId], references: [id])
  authorId  Int?
}

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

Refer to the Prisma Schema Reference for further information on database field types, attributes, conventions and functions.

Create a database migration

After modelling your data, it's time to apply the changes to your database. On your terminal window, run npx prisma migrate dev.



npx prisma migrate dev --name init

If no database exists, a new database with the database name specified in your .env file will be created. The command also creates a new database migration that is located in ./prisma/migrations directory. The migration is in SQL and is applied against your database. init refers to the migration name which you can change to something more semantic.

If you wish to only create a migration but not apply it to your database, pass the --create-only option to Prisma Migrate. This will generate the SQL and allow you to customize it before applying it against your database.

You can confirm that the changes have been applied, run:



npx prisma studio

Select the Model you would like to view/ edit data.

Click Save 1 change to apply the changes.

Navigate to the User model, create a new user, link the post created in the previous step and save the change.

Step 3: Introspect your database aka Reverse Engineering

Entity framework allows you to introspect your database schema and scaffold entity type classes and the database context based off an existing database.

Navigate out of prisma-migrate directory to the root of your project.



cd ../

Install Entity Framework tools you will require to work with SQL Server in your project.



dotnet add package Microsoft.EntityFrameworkCore.SqlServer 
dotnet add package Microsoft.EntityFrameworkCore.Tools 
dotnet add package Microsoft.EntityFrameworkCore.Design

Scaffold entity type classes based on your database schema to your project.



dotnet ef dbcontext scaffold "Server=**HOST**;Database=**DATABASE_NAME**;User Id=**USER**;Password=**PASSWORD**" Microsoft.EntityFrameworkCore.SqlServer --context-dir DbContext --context BlogExampleContext --output-dir Models

Microsoft.EntityFrameworkCore.SqlServer refers to the package to be used to introspect the database.
--context-dir and --output-dir refer to the location of the context and the entity type classes after introspection
--context refers to the name of your database context

On a successful run 2 folders with 4 files will be created in your project.



*app-name*/
┣ DbContext/
┃ ┗ BlogExampleContext.cs
┣ Models/
┃ ┣ Post.cs
┃ ┗ PrismaMigration.cs
┃ ┗ User.cs
┣ ...

As you will notice, PrismaMigration.cs was not defined in schema.prisma. This is a table created by Prisma to track the migrations that have been created and applied against your database.

To exclude the PrismaMigrations table, and re-run the previous command but specify the tables you would like using the --table and —force options to overwrite the existing models.



dotnet ef dbcontext scaffold "Server=**HOST**;Database=**DATABASE_NAME**;User Id=**USER**;Password=**PASSWORD**" Microsoft.EntityFrameworkCore.SqlServer --context-dir DbContext --context BlogExampleContext --output-dir Models --force --table User --table Post

This time, 3 files are generated and the PrismaMigrations.cs file is excluded when scaffolding.

If you're interested in migrating to Prisma with an existing database, the Prisma CLI provides db pull for introspecting the database and populating schema.prisma with your database models.

Conclusion

Congratulations! You have successfully modelled your entities, run a migration with Prisma and scaffolded your models and context with Entity Framework.

Learn how you can set up and deploy a Prisma based Node.js REST API to Azure Functions together with Azure SQL in Deploying to Azure Functions.

If you would like to learn more about Prisma, check out the documentation and feel free to create an issue or start a discussion on GitHub.

Upcoming Serverless Events in 2021 and 2022

Etel Sverdlov — Wed, 03 Nov 2021 16:00:12 +0000

Between the online, hybrid, and in person conferences happening around the globe, the world's conference schedule is in full swing.

Since 2014, there has been a huge increase in interest about Serverless, at least according to Google Trends, and lots of events to match.

To complement the rise of interest in Serverless, we've quickly compiled some upcoming relevant conferences on the subject.

November 18th - Prisma's Serverless Conference

This is our own, first Serverless event. A short mini-conference chock full with top speakers from Netlify, Cloudflare, MongoDB, Vercel, CockroachDB, Planetscale, and of course Prisma. Prisma's Serverless Conference focuses on the challenges developers face when trying to adopt serverless databases.

PS. Keep a look out for the new Prisma tooling that will also be highlighted in the keynote! 🤩

February 24th - ServerlessDays NASHVILLE

An in-person event in Tennessee, Serverless Days NASHVILLE is a continuation of a number of Serverless Days events. Sign ups for this event are currently open as are the CFPs! ServerlessDays are a family of events around the world that's focused on fostering a community around serverless technologies.

March 22, 2022 - Serverless Architecture Conference

A packed two day hybrid event online or at The Hague, the event covers Serverless infrastructure, especially as how it relates to Cloud native architecture, and Kubernetes. This is a very large conferences with talks, speakers and workshops. Later in the year, the same production company will reprise a Serverless architecture conference once more in Berlin.

April 4-6, 2022- QCon London

Qcon is a behemoth of a conference and this QCon is no exception. Among its many tracks is one dedicated to Serverless: Orchestration vs Choreography. With top-of-the-line speakers and an enormous list of talks to choose from, QCOn is an in person option to learn more about Serverless in the Springtime.

These are some of the Serverless events we've spotted. Are there others that you are especially looking forward to? Share more ideas in the comments! 👇

Set up a free PostgreSQL database on Supabase to use with Prisma

Mahmoud Abdelwahab — Fri, 11 Jun 2021 20:13:04 +0000

Supabase is a backend-as-a-service built on top of open source technologies. It gives you a database, authentication, a REST API, real-time subscriptions, and storage.

It offers a free plan which includes a hosted PostgreSQL database. This can be useful if you're getting started with a new project.

This guide explains how to quickly connect the Postgres database provided by Supabase to a Prisma project. There are other services out there that provide hosted PostgreSQL databases like Heroku or Digital Ocean.

Prisma is an open source next-generation ORM. It consists of the following parts:

Prisma Client: Auto-generated and type-safe query builder for Node.js & TypeScript.
Prisma Migrate: Migration system.
Prisma Studio: GUI to view and edit data in your database.

Step 1: Log into Supabase

Navigate your browser to https://app.supabase.com and log in with GitHub.

Step 2: Create a new project

You'll find that an organization has been created for you under the same GitHub username you used when logging in.

Go ahead and create a new project by clicking on "New project" and then pick the organization.

You'll then need to provide a name and set a password for your database (we'll need it later). Finally, click on "create new project".

After creating the project, you'll need to wait for ~2 minutes for Supabase to finish creating the database.

Step 3: Get the connection string from the project settings

Go to the settings page from the sidebar and navigate to the Database tab. You'll find the database's connection string with a placeholder for the password you provided when creating the project.

Step 4: Testing the connection

To make sure that everything works correctly, let's try the connection string in a Prisma project.

If you already have one, all you need to do is set the DATABASE_URL to the connection string (including the password) in your .env file, and you're good to go.

In case you don't have a Prisma project or this is your first time working with Prisma, you're going to use with the repo from the quickstart guide.

Cloning the starter project

Navigate into a directory of your choice and run the following command in your terminal if you're on a Windows machine:



curl https://pris.ly/quickstart -L -o quickstart-master.tar.gz && tar -zxvf quickstart-master.tar.gz quickstart-master/typescript/starter && move quickstart-master\typescript\starter starter && rmdir /S /Q quickstart-master && del /Q quickstart-master.tar.gz

And if you're using Mac OS or Linux, run the following command:



curl -L https://pris.ly/quickstart | tar -xz --strip=2 quickstart-master/typescript/starter

You can now navigate into the directory and install the project's dependencies:



cd starter && npm install

A look at the project's structure

This project comes with TypeScript configured and has the following structure.

A prisma directory which contains:
- A dev.db file: This is a SQLite database.
- A schema.prisma file: Where we define the different database models and relations between them.
A .env file: Contains the DATABASE_URL variable, which Prisma will use.
A script.ts file: where we will run some queries using Prisma Client.

This starter also comes with the following packages installed:

@prisma/client: An auto-generated and type-safe query builder that's tailored to your data.
prisma: Prisma's command-line interface (CLI). It allows you to initialize new project assets, generate Prisma Client, and analyze existing database structures through introspection to automatically create your application models.

Note: Prisma works with both JavaScript and TypeScript. However, to get the best possible development experience, using TypeScript is highly recommended.

Configuring the project to use PostgreSQL

Go ahead and delete the prisma/dev.db file because we will be switching to PostgreSQL.

Next, inside the prisma/.env file, update the value of the DATABASE_URL variable to the connection string you got in step 3. The URL might look as follows:



# prisma/.env

postgres://postgres:[YOUR-PASSWORD]@db.vdbnhqozmlzdsaejdxwr.supabase.co:5432/postgres

Finally, inside your schema.prisma file, change the provider from "sqlite" to "postgresql".

This is what your schema.prisma file should look like:



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

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

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

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

To test that everything works correctly, run the following command to create a migration:



prisma migrate dev --name init

You can optionally give your migration a name, depending on the changes you made. Since this is the project's first migration, you're setting the --name flag to "init".

If everything works correctly, you should get the following message in your terminal:



Your database is now in sync with your schema.

✔ Generated Prisma Client (2.x.x) to ./node_modules/@prisma/client in 111ms

This will create a prisma/migrations folder inside your prisma directory and synchronize your Prisma schema with your database schema.

Note: if you want to skip the process of creating a migration history, you can use the db push command instead of migrate dev.

If you go to your Supabase project, in the table editor, you should see that two tables have been created, a Post and a User table.

That's it! You have now successfully connected a Prisma project to a PostgreSQL database hosted on Supabase and ran your first migration.

Connection pooling with Supabase

If you're working in a serverless environment (for example Node.js functions hosted on AWS Lambda, Vercel or Netlify Functions), you need to set up connection pooling using a tool like PgBouncer. That's because every function invocation may result in a new connection to the database.

Fortunately, Supabase projects support connection management using PgBouncer and are enabled by default.

Go to the Database page from the sidebar and navigate to connection pool settings

When running migrations you need to use the non pooled connection URL (like the one we used in step 4). However, when deploying your app, you'll use the pooled connection URL. and add the ?pgbouncer=true flag to the PostgreSQL connection URL. The URL might look as follows:



# prisma/.env

postgres://postgres:[YOUR-PASSWORD]@db.vdbnhqozmlzdsaejdxwr.supabase.co:6543/postgres?pgbouncer=true

Prisma Migrate uses database transactions to check out the current state of the database and the migrations table. However, the Migration Engine is designed to use a single connection to the database, and does not support connection pooling with PgBouncer. If you attempt to run Prisma Migrate commands in any environment that uses PgBouncer for connection pooling, you might see the following error:



Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists

This is a known issue and it is being worked on, you can follow the progress on this GitHub issue.

If you want to learn more about Supabase, check out the docs.

If you want to learn more about Prisma, check out the docs. Also in case you have any questions or run into any issue, feel free to start a discussion in the repo's discussions section.

Deploying Prisma to Azure Functions with Azure SQL

Daniel Norman — Mon, 22 Mar 2021 12:24:28 +0000

Overview

In this guide, you will set up and deploy a Prisma based Node.js REST API to Azure Functions together with Azure SQL as the database. The application will expose a REST API and use Prisma Client to handle fetching, creating, and deleting records from a database.

Azure Functions is a serverless deployment platform that allows you to deploy code without having to maintain infrastructure. Azure SQL Database is a relational database service built for the cloud with automatic scaling.

In this guide, you will create the necessary resources in Azure, create the database schema using Prisma Migrate and deploy a Node.js REST API with resource endpoints that use Prisma Client to handle database operations against the Azure SQL database.

This guide's focus is showing how Prisma can be used in the Azure cloud focusing on Azure Functions and Azure SQL. The starting point is the Prisma Azure Functions example – a REST API for a simple blog with two models: User and Post (1:n). The example contains REST endpoints preconfigured as serverless functions.

Note that Azure SQL support in Prisma is in Preview.

With Azure Functions, the fundamental building block is a Function App. A function app provides an execution context in Azure in which your functions run. It is comprised of one or more individual functions that are managed, deployed, and scaled together. That way, you can organize and collectively manage multiple functions as a single logical unit.

Throughout the guide, you'll find various checkpoints that enable you to validate whether you performed the steps correctly.

Prerequisites

Azure account.
Git installed
Azure CLI installed.
Node.js installed.

Prisma workflow

At the core of Prisma is the Prisma schema – a declarative configuration where you define your data model and other Prisma-related configuration. The Prisma schema is also a single source of truth for both Prisma Client and Prisma Migrate.

In this guide, you will use Prisma Migrate to create the database schema. Prisma Migrate is based on the Prisma schema and works by generating .sql migration files that are executed against the database.

Migrate comes with two primary workflows:

Creating migrations and applying during local development with prisma migrate dev
Applying generated migration to production with prisma migrate deploy

For brevity, the guide does not cover how migrations are created with prisma migrate dev. Rather, it focuses on the production workflow with prisma migrate deploy and uses the Prisma schema and SQL migration that are included in the example code.

To learn more about how migrations are created with Prisma Migrate, check out the start from scratch guide

Required Azure resources

Resource group
Azure SQL Database server
Database
Firewall rule
Storage account
Function App

1. Download the example and install dependencies

Open your terminal and navigate to a location of your choice.

Create the directory for the application code and download the example code:

mkdir prisma-azure
cd prisma-azure
curl https://codeload.github.com/prisma/prisma-examples/tar.gz/latest | tar -xz --strip=3 prisma-examples-latest/deployment-platforms/azure-functions/

Checkpoint: Run the following command to list the contents of the folder:

ls -1

You should see the following files

CreatePost/
CreateUser/
DeletePost/
FilterPosts/
GetFeed/
GetPost/
PublishPost/
host.json
lib/
node_modules/
package.json
prisma/
proxies.json

Install the dependencies:

npm install

2. Sign in to Azure using the Azure CLI

Begin by signing in using the following command in your terminal:

az login

3. Create the Resource Group on Azure

In Azure, a resource group is a way to group together different cloud resources. Whenever you create a resource, e.g., an Azure Function, you need to assign it a resource group.

Since the REST API will use both Azure Functions and an Azure SQL database, you will first create the resource group with the following command:

az group create --location germanywestcentral --name prisma-azure-example

Note: The command above creates the resource group in the germanywestcentral region. You should create the resource group in the region closest to your users. For the full list of Azure regions, run the az account list-locations command.

Checkpoint: The command should output information about the created resource group:

{
  "id": "/subscriptions/SUBSCRIPTION_ID/resourceGroups/prisma-azure-example",
  "location": "germanywestcentral",
  "managedBy": null,
  "name": "prisma-azure-example",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null,
  "type": "Microsoft.Resources/resourceGroups"
}

4. Create the Azure SQL database server

To create the Azure SQL database server, copy the command below into your terminal:

az sql server create -l germanywestcentral -g prisma-azure-example --name UNIQUE_DB_SERVER_NAME --admin-user prisma --admin-password CHOOSE_A_PASSWORD --enable-public-network true

Before running the command, replace a unique name for the database in place of UNIQUE_DB_SERVER_NAME, set a password in place of CHOOSE_A_PASSWORD, and note it down.

The command does the following:

Creates the database server in the germanywestcentral region.
Associates it with the prisma-azure-example resource group created in the previous step.
Sets a unique name for the Azure SQL server with UNIQUE_DB_SERVER_NAME.
Sets the admin user to prisma.
Sets the admin password.
Enables public network access so you can create the database schema from your machine.

In the next step, you will create the database that Prisma will use in the REST API.

5. Create the database

In this step, you will create a database in the server you created in the previous step.

Run the following command in the terminal, replace UNIQUE_DB_SERVER_NAME with the database name you chose in the previous step:

az sql db create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name prisma-azure-example --service-objective Basic

Here's a breakdown of the command's parameters:

--resource-group adds the database to the resource group created in step 3
--server sets the Azure SQL database server to create it in
--name sets the name of the database
--service-objective sets the database's service tier that determines the cost.

6. Create a firewall rule to allow local access to the database

In this step, you will add two firewall rules:

Allow remote access from your local computer's public IP to the Azure SQL database. This is necessary so you can create the database schema and use the database for testing locally.
Allow access to the Azure SQL database from Azure Functions

Allow access from your local computer

Begin by determining your public IP with the following command:

curl ifconfig.me

Copy the IP from the output and run the following command, replacing YOUR_PUBLIC_IP with the IP address and UNIQUE_DB_SERVER_NAME with the name from step 4:

az sql server firewall-rule create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name allow-local-acccess --start-ip-address YOUR_PUBLIC_IP --end-ip-address YOUR_PUBLIC_IP

Checkpoint: After creating the firewall rule, the command should output the following:

{
  "endIpAddress": "YOUR_PUBLIC_IP",
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Sql/servers/prisma-db/firewallRules/allow-local-acccess",
  "kind": "v12.0",
  "location": "Germany West Central",
  "name": "allow-local-acccess",
  "resourceGroup": "prisma-azure-example",
  "startIpAddress": "YOUR_PUBLIC_IP",
  "type": "Microsoft.Sql/servers/firewallRules"
}

Allow access from Azure Functions

To allow applications hosted inside Azure to connect to your SQL server, Azure connections must be enabled. To enable Azure connections, there must be a firewall rule with starting and ending IP addresses set to 0.0.0.0.

Create the rule with the following command:

az sql server firewall-rule create --resource-group prisma-azure-example --server UNIQUE_DB_SERVER_NAME --name allow-function-acccess --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

7. Create a storage account

In this step, you will create a storage account used to maintain state and other information about your functions.

Run the following command to create the storage account, replacing UNIQUE_STORAGE_ACCOUNT_NAME with a name for the stroage account:

az storage account create --name UNIQUE_STORAGE_ACCOUNT_NAME --location germanywestcentral --resource-group prisma-azure-example --sku Standard_LRS

Checkpoint: If the command succeeds, it will output a large json object. Verify that provisioningState is Succeeded:

{
  "id": "/subscriptions/YOUR_SUBSCRIPTION_ID/resourceGroups/prisma-azure-example/providers/Microsoft.Storage/storageAccounts/UNIQUE_STORAGE_ACCOUNT_NAME",
  "provisioningState": "Succeeded",
  "resourceGroup": "prisma-azure-example",
  "type": "Microsoft.Storage/storageAccounts"
}

8. Create the function app

In this step, you will create the function app, which provides the environment for executing your function code. A function app maps to your local function project and lets you group functions as a logical unit for easier management, deployment, and sharing of resources.

Copy the following command and replace FUNCTION_APP_NAME with a unique name for your function app, and STORAGE_ACCOUNT_NAME with the name you chose in the previous step:

az functionapp create --resource-group prisma-azure-example --consumption-plan-location germanywestcentral --runtime node --runtime-version 14 --functions-version 3 --name FUNCTION_APP_NAME --storage-account STORAGE_ACCOUNT_NAME --os-type Linux

Note: The Function App name must be globally unique as it will determine the subdomain of the deployed function.

Checkpoint: If the command succeeds, you will see a large JSON object in the terminal. Verify that that the state key in the object is set to Running.

9. Set the DATABASE_URL environment variable locally

In this step, you will define the DATABASE_URL environment variable locally to create the database schema and test the functions locally.

To construct the connection string, copy the following connection string template:

sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_ADMIN_USER@DB_SERVER_NAME;password={DB_ADMIN_PASSWORD};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30

Replace the following parts:

DB_SERVER_NAME with the database server name defined in step 4
DB_NAME with the database name defined in step 5
DB_ADMIN_USER with the database admin user set in step 4 to prisma
DB_ADMIN_PASSWORD with the database admin password set in step 4

After setting all the values, set it as a local environment variable:

export DATABASE_URL="sqlserver://DB_SERVER_NAME.database.windows.net:1433;database=DB_NAME;user=DB_ADMIN_USER@DB_SERVER_NAME;password={DB_ADMIN_PASSWORD};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

10. Create the Azure Functions local configuration

In this step, you will create the local configuration file for Azure Functions. The file is used to define local configuration such as environment variables for the functions and the runtime – in this case Node.js.

Create a file named local.settings.json in the root of the project with the following command:

touch local.settings.json

And add the following contents to it:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node"
  }
}

11. Create the database schema

With the DATABASE_URL environment variable set, you will create the database schema using the prisma migrate deploy command.

Note: The prisma migrate deploy command will run migration files from the prisma/migrations folder. The initial migration is already included in the example. To learn more about how to create migrations, check out the Prisma Migrate docs.

Run the following command to create the database schema:

npx prisma migrate deploy

Checkpoint: The prisma migrate deploy should show the following:

1 migration found in prisma/migrations

The following migration have been applied:

migrations/
  └─ 20210322111219_init/
    └─ migration.sql

All migrations have been successfully applied.

12. Expose the DATABASE_URL environment variable to the functions

In this step, you will expose the DATABASE_URL environment variable to the functions so that Prisma can connect to the database. In Azure Functions, environment variables are set using app settings.

Run the following command, after replacing FUNCTION_APP_NAME_FROM_STEP_8 with the name of the Function App created in step 8:

az functionapp config appsettings set --name FUNCTION_APP_NAME_FROM_STEP_8 --resource-group prisma-azure-example --settings DATABASE_URL=$DATABASE_URL

The command will set the DATABASE_URL app setting using the locally defined DATABASE_URL environment variable set in step 9.

Note: By default, app settings are not encrypted. Since the DATABASE_URL contains sensitive information, it can be stored more securely using Azure's Key Vault

Congratulations! You have created all the necessary resources and configuration, which means your API is ready to be deployed.

13. Deploy the functions

In this step, you will generate Prisma Client and deploy the functions.

From the project folder, run the following command:

npx prisma generate

The command will generate Prisma Client into the node_modules folder.

To deploy the functions, run the following command:

npx func azure functionapp publish FUNCTION_APP_NAME

Checkpoint: If the functions have been successfully deployed you should see the following output:

Getting site publishing info...
Uploading package...
Uploading 67.24 MB [##############################################################################]
Upload completed successfully.
Deployment completed successfully.
Syncing triggers...
Functions in FUNCTION_APP_NAME:
    CreatePost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post
    CreateUser - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/user
    DeletePost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post/{postid}
    FilterPosts - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/filterposts
    GetFeed - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/feed
    GetPost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/post/{postid}
    PublishPost - [httpTrigger]
        Invoke url: https://FUNCTION_APP_NAME.azurewebsites.net/api/publish/{postid}

Congratulations 🎊! If you've made it this far, you've successfully deployed a Prisma based REST API to Azure Functions which uses Azure SQL as the database.

In the next step, you'll test the functions and take a closer look at how the functions are implemented.

14. Test the deployed functions

In this step, you will test the API's different endpoints using the URLs from the previous step.

Begin by making a POST HTTP request to the CreateUser endpoint with curl:

curl --request POST --data '{"email":"alice@prisma.io","name":"Alice"}' https://FUNCTION_APP_NAME.azurewebsites.net/api/user

Note: Replace the FUNCTION_APP_NAME in the URL with the app name you chose in step 8.

If the request succeeds, you should see the created user object returned:

{
  "createdAt": "2021-03-02T14:48:15.746Z",
  "email": "alice@prisma.io",
  "id": 1,
  "name": "Alice"
}

The files associated with the function can be found in the CreateUser folder, which contains two files:

function.json: Function configuration, e.g. HTTP method, path, and return value
index.js: The function handler where Prisma Client is used to create the user in the Azure SQL database

Now, try creating a post associated with the user you just created with the following comand:

curl --request POST --data '{"title":"Prisma with Azure","content":"","authorEmail":"alice@prisma.io"}' https://FUNCTION_APP_NAME.azurewebsites.net/api/post

If the request succeeds, you should see the created post object returned:

{
  "id": 1,
  "createdAt": "2021-03-02T17:09:53.160Z",
  "updatedAt": "2021-03-02T17:09:53.161Z",
  "title": "Prisma with Azure",
  "content": "",
  "published": false,
  "authorId": 1
}

To update the published field of the post, make the following request:

curl --request PUT https://FUNCTION_APP_NAME.azurewebsites.net/api/publish/1

If the request succeeds, you should see the updated post object:

{
  "authorId": 1,
  "content": "",
  "createdAt": "2021-03-02T17:09:53.160Z",
  "id": 1,
  "published": true,
  "title": "Prisma with Azure",
  "updatedAt": "2021-03-03T10:07:11.047Z"
}

Finally, to test the feed endpoint, make the following request:

curl https://FUNCTION_APP_NAME.azurewebsites.net/api/feed

If the request succeeds, you should see the post you created and the related author:

[
  {
    "author": {
      "createdAt": "2021-03-02T14:48:15.746Z",
      "email": "alice@prisma.io",
      "id": 1,
      "name": "Alice"
    },
    "authorId": 1,
    "content": "",
    "createdAt": "2021-03-02T17:09:53.160Z",
    "id": 1,
    "published": true,
    "title": "Prisma with Azure",
    "updatedAt": "2021-03-03T10:07:11.047Z"
  }
]

Developing and debugging the functions locally

When implementing Azure Functions, you can also start a local development environment using the Azure Functions Core tools' function runtime. That way, you can test and debug the implementation of the functions locally.

To launch the functions runtime, run the following command:

npx func start

The command starts a local server and allows you to call any of the functions in the project.

You can inject environment variables into the functions by adding them to the Values object in the local.settings.json file at the root of the project.

Setting up a local database for development

When developing locally, you should consider running a local Microsoft SQL Server instance. While Microsoft SQL Server is not the same as Azure SQL, the two have high compatibility with each other..

The quickest way to set up a local Microsoft SQL Server is with Docker. Check out the Microsoft SQL Server example for more information on how to set it up.

Bootstrapping a new function

When you want to create a new function, you can use the following command to bootstrap a new function:

npx func function new --language JavaScript --template "HTTP trigger" --name FUNCTION_NAME

The command creates a folder with the index.js and function.json files.

Summary

Congratulations! You have successfully deployed the REST API to Azure Functions and used Prisma Client to handle database queries to the Azure SQL database.

For more insight into Prisma Client's API, explore the function handlers and check out the Prisma Client API Reference

It's worth noting that while this guide used the Azure CLI to create all the resources, this can also be achieved via the Azure Portal UI or the VSCode extension, which supports deployments directly from VSCode.

As a next step, you could look into implementing a continuous delivery pipeline using GitHub Actions to automate the deployment process from a GitHub repository.

Bundling Prisma with the CDK using aws-lambda-nodejs

Ryan Dsouza — Thu, 11 Mar 2021 13:56:39 +0000

Introduction

In this post, we shall see how we can bundle Prisma using aws-lambda-nodejs with the CDK.

Prerequisites

This is a walkthrough of the steps required to bundle Prisma with CDK and so cloning the repo below and following along would be ideal.

ryands17 / prisma-lambda

Integrating Prisma with `aws-lambda-nodejs` to run Prisma on Lambda

Constructs

In this project, we have a simple Lambda function that uses Prisma to query data from a User table and logs to the console. The only resource we create here is the Lambda function so let's look at that.

I'm skipping the imports in these snippets to make it smaller but if you want to view those you can check those out in the file mentioned in the comments.

// lib/prisma-lambda-stack.ts

new ln.NodejsFunction(this, 'prisma', {
  runtime: lambda.Runtime.NODEJS_14_X,
  handler: 'handler',
  entry: lambdaFn,
  timeout: cdk.Duration.seconds(10),
  environment: {
    DB_URL: process.env.DB_URL || '',
  },
  bundling: {
    nodeModules: ['@prisma/client', 'prisma'],
    commandHooks: {
      beforeBundling(inputDir: string, outputDir: string): string[] {
        return []
      },
      beforeInstall(inputDir: string, outputDir: string) {
        return [`cp -R ../prisma ${outputDir}/`]
      },
      afterBundling(inputDir: string, outputDir: string): string[] {
        return [
          `cd ${outputDir}`,
          `yarn prisma generate`,
          `rm -rf node_modules/@prisma/engines`,
        ]
      },
    },
  },
})

Here, we create a simple Node.js Lambda function and the parameters like runtime, handler, entry, timeout and environment are the basic Lambda parameters that we pass.

aws-lambda-nodejs uses esbuild under the hood so your function is bundled using esbuild. There are a couple of important parameters that we need to set for the bundling process.

The first is nodeModules. This will instruct esbuild to not include @prisma/client and prisma into our function file and treat them as node_modules.
The other section to be configured is commandHooks. The main hooks we need here are beforeInstall and afterBundling.
- In beforeInstall, we run the command cp -R ../prisma ${outputDir}/. This makes sure we have schema.prisma present in the bundle. You can also directly copy schema.prisma instead of the entire directory if you have the migrations folder which is not required in the bundle.
- The other hook we use is afterBundling. This is to generate PrismaClient and to remove the engines folder to reduce function size.

The rest is a simple Prisma setup with a schema.prisma and a User model.

Now when we run cdk synth, the function will be bundled and ready for deployment.

Conclusion

So this is how we can package Prisma with aws-lambda-nodejs.

Here is the repo again for those who haven't checked it out:

ryands17 / prisma-lambda

Integrating Prisma with `aws-lambda-nodejs` to run Prisma on Lambda

Building a GraphQL CRUD API for your Database with TypeGraphQL & Prisma

Ruheni Alex — Thu, 25 Feb 2021 13:21:34 +0000

Building CRUD APIs can be a tedious chore. The time spent writing glue code, plumbing layers together and doing repetitive work is often better invested into tasks that actually add value and solve interesting problems.

In this article, you'll explore how you can prototype an e-commerce GraphQL CRUD API using TypeGraphQL, Apollo Server and Prisma for database access. SQLite will be the database of choice in this tutorial because of its ease of setup. Feel free to use your choice database - Prisma currently supports PostgreSQL, MySQL, SQL Server, SQLite, MongoDB, and CockroachDB.

The complete example used in this tutorial is available on GitHub.

What is TypeGraphQL?

TypeGraphQL is a framework that follows a code-first and object-oriented approach towards building GraphQL APIs. It leverages TypeScript by using classes and decorators.

Note: If you're not familiar with TypeScript yet, check out Learn TypeScript: A Pocketguide Tutorial.

What is Prisma?

Prisma is an ORM - Object-relational mapper. It provides a declarative way to define your database models that are easy to read and understand. It also offers a type-safe database client from your database schema that is intuitive and fun. 🙂

Prerequisites

Before getting started, ensure you have the following:

Installed Node.js.
Basic understanding of JavaScript/TypeScript.
Familiarity with GraphQL APIs.

Note: To get started on learning the basics of GraphQL, How To GraphQL will give you a great foundation.

Step 1: Initialize Your Project

The first step will be creating your working directory on your computer and initialize it by running npm init -y. This command will generate a package.json file with prepopulated values.

mkdir crud-api
cd crud-api
npm init -y

Set Up Dependencies

Install the following dependencies in your project:

npm install apollo-server type-graphql reflect-metadata class-validator

reflect-metadata allows you to do runtime reflection on types.

class-validator is a dependency used by TypeGraphQL to validate inputs and arguments using decorators.

Your project will be dependent on the following development dependencies:

npm install --save-dev typescript ts-node-dev ts-node @types/node @types/ws

The typescript and ts-node-dev dependencies are required to transpile your TypeScript files and restart the app server when a change is made to a file.

A Little More 🔧

Create a tsconfig.json file in your project:

touch tsconfig.json

tsconfig.json allows you to define options that will let the TypeScript compiler take advantage of when transpiling your code to JavaScript.

Paste in the following code to your tsconfig.json file:

// tsconfig.json
{
  "compilerOptions": {
    "target": "es2018",
    "module": "commonjs",
    "lib": [
      "es2018",
      "esnext.asynciterable"
    ],
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  }
}

To take advantage of ts-node-dev's live reloading, modify scripts in your package.json to this script:

// package.json
"scripts": {
  "dev": "ts-node-dev --no-notify --respawn --transpile-only src/index.ts"
},

Next, create your src directory - this will house the bulk of your application - and create an index.ts file as well:

mkdir src
touch src/index.ts

The following code will be your initial setup of your GraphQL server:

// index.ts
import 'reflect-metadata'
import * as tq from 'type-graphql'
import { ApolloServer } from 'apollo-server'

const app = async () => {
  const schema = await tq.buildSchema({})

  new ApolloServer({ schema }).listen({ port: 4000 }, () =>
    console.log('🚀 Server ready at: <http://localhost:4000>')
  )
}

app()

VS Code's TypeScript compiler will throw an error about the buildSchema missing the resolver argument. Not to worry, this will be covered in Step 3: Set up typegraphql-prisma. 🙂

Step 2: Set Up Prisma

Now that that's out of the way let's get to the fun part, data modeling.

Initialize Prisma

You'll need to setup Prisma in your project by running the following command:

npx prisma init

The Prisma CLI creates a .env file in your project's root and a new prisma directory. The folder created will contain a schema.prisma file that defines your database connection, the Prisma Client generator and this is where you will define your database models.

The default database source is PostgreSQL. However, you can switch this to your preferred provider - sqlite, mysql, sqlserver, cockroachdb or mongodb - and modify the url pointing to your database. For ease of setup, this tutorial will use SQLite.

Navigate to schema.prisma file and change the datasource provider and datasource url string values to the following:

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

// change the code below 👇
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

Alternative database connection string setup

In your .env file, add this line:

#.env file
DATABASE_URL="file:./dev.db"

Then modify the datasource url to point to DATABASE_URL variable in your .env file.

// schema.prisma
generator client {
  provider = "prisma-client-js"
}


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

The database doesn’t currently exist. Prisma will create the database in prisma/dev.db when running your first migration.

Database Models

For the e-commerce API, you will define 4 models: Product, Category, Order, and User. The relationships between these entities will be as follows:

one-to-many relationship between User and Order.
many-to-many relationship between Product and Category.
many-to-many relationship between Order and Product.

Here is a visual diagram representing the relationships between the different database models:

Your Prisma Schema

With the relationships defined, your final database models should be similar to the one below:

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Product {
  id          String     @id @default(uuid())
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  name        String
  sku         String     @unique
  description String?
  quantity    Int
  categories  Category[]
  orders      Order[]
}

model Category {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  name      String
  products  Product[]
}

model Order {
  id        String    @id @default(uuid())
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  userId    Int?
  customer  User?     @relation(fields: [userId], references: [id])
  products  Product[]
}

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  email     String   @unique
  firstName String?
  lastName  String?
  address   String?
  orders    Order[]
}

Feel free to modify the fields provided to your preference.

Your First Migration

Next, you'll sync your schema with your database with the following command:

npx prisma migrate dev --name init

The above command creates a database migration and executes it against your database. A database migration refers to incremental, reversible changes that are made to a database schema over time. In that sense, you can think of migrations as a tool enabling "version control" for your database.

The command creates a migration called init. Once the migration is complete, a new /prisma/migrations directory is created. Since you're working with SQLite, Prisma CLI will create your database and apply the changes against your database.

After the first migration is created, the Prisma CLI installs the @prisma/client package. In subsequent database migrations, the Prisma CLI will generate your Prisma Client. Prisma Client is an auto-generated database client that allows you to interact with your database in a type-safe way.

Pretty neat, right? 😜

Step 3: Modify Your GraphQL Server

Create an instance of PrismaClient and add it to the context of your GraphQL Server as follows:

//index.ts
import 'reflect-metadata'
import * as tq from 'type-graphql'
import { ApolloServer } from 'apollo-server'
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient()

const app = async () => {
  const schema = await tq.buildSchema({})

  const context = {
    prisma
  }

  new ApolloServer({ schema, context }).listen({ port: 4000 }, () =>
    console.log('🚀 Server ready at: <http://localhost:4000>')
  )
}

app()

Adding prisma to your context makes sure that PrismaClient is made available to your GraphQL operations.

Step 4: Set Up `typegraphql-prisma`

TypeGraphQL provides an integration with Prisma using the [typegraphql-prisma](https://www.npmjs.com/package/typegraphql-prisma) package. This package will auto-generate types, enums and even CRUD resolvers based on your Prisma schema, which you otherwise would need to write manually.

Install typegraphql-prisma as a dev dependency with the following command:

npm install --save-dev typegraphql-prisma

typegraphql-prisma is dependent on several packages for it to work properly.

npm install graphql-type-json graphql-fields
npm install --save-dev @types/graphql-fields

Once the dependencies are successfully installed, add a new generator in your schema.prisma file below the client generator as follows:

// schema.prisma
generator client {
  provider = "prisma-client-js"
}

generator typegraphql {
  provider = "typegraphql-prisma"
}

Run npx prisma generate to generate TypeGraphQL classes and CRUD resolvers based on your Prisma schema.

npx prisma generate

typegraphql-prisma emits the generated classes, enums and resolvers to node_modules/@generated/typegraphql-prisma.

It should be noted that in your subsequent migrations, the classes and resolvers TypeGraphQL generates will be automatically updated on running npx prisma migrate dev.

The final step is updating our resolvers in our Apollo Server:

// index.ts
import 'reflect-metadata';
import { PrismaClient } from '@prisma/client';
import { ApolloServer } from 'apollo-server';
import { resolvers } from "@generated/type-graphql";
import * as tq from 'type-graphql';

const prisma = new PrismaClient()

const app = async () => {
  const schema = await tq.buildSchema({ resolvers })

  const context = () => {
    return {
      prisma
    }
  }

  new ApolloServer({ schema, context }).listen({ port: 4000 }, () =>
    console.log('🚀 Server ready at: <http://localhost:4000>')
  )
}

app()

Step 5: Test Your New GraphQL API

Start your GraphQL server with the following command:

npm run dev

Open localhost:4000 to interact with the GraphQL playground...and congratulations. 🎉

You've successfully automated building a CRUD API. Just to be sure explore Docs section of the playground and try out some queries and mutations:

Create a New `Category`:

mutation {
  createCategory(data: { name: "electronics" }) {
    id
    name
  }
}

Create a New `Product`:

mutation {
  createProduct(
    data: {
      name: "Google Pixel"
      sku: "90456123098"
      quantity: 1000
      categories: { connect: [{ id: 1 }] }
    }
  ) {
    id
    name
  }
}

Query all Products:

{
  products {
    id
    name
    quantity
    sku
    categories{
      name
    }
    orders{
      id
    }
  }
}

Everything is running smoothly. Go ahead and pat yourself on the back for getting this far.

Feel free to explore other mutations and queries on the playground. 🙂

Side Quest: Some `typegraphql-prisma` Wizardry 🧙🏾‍♂️

Alternatively, you can make specific CRUD operations, specific actions, modify generated functions that can be performed against your Prisma schema. This gives you more control of the operations exposed on your API. This approach also allows you to create your custom resolvers and add them to your GraphQL schema.

Expose Specific Prisma CRUD operations

typegraphql-prisma allows you to expose selected CRUD operations on your API on your Prisma models.

import {
  ProductCrudResolver,
  CategoryCrudResolver
} from "@generated/type-graphql";

const schema = await tq.buildSchema({
  resolvers: [
    ProductCrudResolver,
    CategoryCrudResolver
  ]
})

Expose Specific Prisma Actions

To get more control over the GraphQL operations, you can expose, typegraphql-prisma allows you to expose specific Prisma operations from the generated types.

import {
  CreateProductResolver,
  UpdateProductResolver
} from "@generated/type-graphql";

const schema = await tq.buildSchema({
  resolvers: [
    CreateProductResolver,
    UpdateProductResolver
  ]
})

TypeGraphQL allows you to add custom queries and mutations to your schema using the generated Prisma Client.

Applying Custom Resolvers

The beauty of TypeGraphQL is that you can create custom resolvers such as authorization by creating its decorators.

typegraphql-prisma generates the applyResolversEnhanceMap function and ResolversEnhanceMap to aid in the creation of a config object that allows you to add decorator functions.

import {
  ResolversEnhanceMap,
  applyResolversEnhanceMap,
} from "@generated/type-graphql";
import { Authorized } from "type-graphql";

const resolversEnhanceMap: ResolversEnhanceMap = {
  Category: {
    createCategory: [Authorized(Role.ADMIN)],
  },
};

applyResolversEnhanceMap(resolversEnhanceMap);

Learn more about other advanced operations you can apply to your GraphQL resolvers, such as custom resolvers, authorization, middleware and additional decorators to your Prisma schema and models here.

Generating the SDL

To enable viewing the SDL of your GraphQL API, make the following modification in your application:

  const schema = await tq.buildSchema({
    resolvers,
    emitSchemaFile: true
  })

This will automatically create a schema.gql file with your GraphQL schema definitions in your working directory.

You could also specify the exact path for your schema.gql file. This modification will add it in src/snapshots/schema folder:

import path = require('path');

const schema = await tq.buildSchema({
    resolvers,
        emitSchemaFile: path.resolve(__dirname, "snapshots/schema", "schema.gql"),
 })

For the application you just built, here's a preview of the type User's queries and mutations that is generated:

type Query {
  aggregateUser(cursor: UserWhereUniqueInput, orderBy: [UserOrderByInput!], skip: Int, take: Int, where: UserWhereInput): AggregateUser!
  findFirstUser(cursor: UserWhereUniqueInput, distinct: [UserScalarFieldEnum!], orderBy: [UserOrderByInput!], skip: Int, take: Int, where: UserWhereInput): User
  user(where: UserWhereUniqueInput!): User
  users(cursor: UserWhereUniqueInput, distinct: [UserScalarFieldEnum!], orderBy: [UserOrderByInput!], skip: Int, take: Int, where: UserWhereInput): [User!]!
}

type Mutation {
  createUser(data: UserCreateInput!): User!
  deleteManyUser(where: UserWhereInput): AffectedRowsOutput!
  deleteUser(where: UserWhereUniqueInput!): User
  updateManyUser(data: UserUpdateManyMutationInput!, where: UserWhereInput): AffectedRowsOutput!
  updateUser(data: UserUpdateInput!, where: UserWhereUniqueInput!): User
  upsertUser(create: UserCreateInput!, update: UserUpdateInput!, where: UserWhereUniqueInput!): User!
}

You can learn more about this feature here.

Conclusion

You have successfully built a CRUD GraphQL API by barely writing any code. Simple and fast. I hope you have enjoyed this tutorial. Feel free to share your thoughts, and share what kind of tutorials or examples you would like to see more.

Explore the prisma-examples to see how Prisma can fit in your stack. If you feel an example is missing, create an issue. 😊

Happy hacking! 👩🏾‍💻👨🏾‍💻

Adding an API and database to your Nuxt App with Prisma

Ruheni Alex — Thu, 18 Feb 2021 12:02:09 +0000

Note: The application is built using Nuxt version 2 at the time it was written. Prisma runs on the server-side and you can use Nuxt version 3's server routes to add database access to your application without using Express.

If you would like to try out Prisma with Nuxt 3, check out sidebase. You can quickly set up a project by running npm create sidebase@latest and select Prisma in the modules prompt.

Building fullstack applications has never been easier using Nuxt. It provides a Vue framework for building blazing fast server-side rendered and static applications. Prisma is an ORM that offers developers a type-safe database client (currently supports PostgreSQL, MySQL, SQLite, SQL Server, CockroachDB and MongoDB). This is useful when building APIs and is intuitive for both beginners and experienced developers.

Nuxt presents two options for building fullstack applications:

Using Nuxt programmatically - setting up your own server with your middleware and API.
serverMiddleware - this is an extension of your Nuxt app that allows you to create additional routes without setting up an external server. The middleware is registered on application start.

In this article, you’ll learn how to add an API in your Nuxt application using Prisma for your database access and Nuxt’s serverMiddleware property.

Prerequisites

Before you start, ensure you have Node.js installed. If you are using Visual Studio Code, you can supercharge your development experience by installing the Prisma extension for auto-completion, formatting, and syntax highlighting.

The completed project is available on GitHub.

Step 0: Initialize your Nuxt application

The first step is initializing your Nuxt application.



npx create-nuxt-app awesome-nuxt-app

You’ll be asked a couple of questions such as the name of the projects, linter, testing framework, etc. Keep the app simple and go with the default options provided. To learn more about the options, head over to Create Nuxt App.



cd awesome-nuxt-app
npm run dev

Congratulations! Your application is now running on http://localhost:3000 🥳.

Step 1: Add Prisma to your app

Now that your Nuxt application is running, the next step is to set up Prisma. You’ll first install the Prisma CLI as a dev dependency by running the following command:



npm install --save-dev prisma

Initialize Prisma

Once the installation is complete, run the following command:



npx prisma init

The command above creates a folder called prisma at the root of your project which contains a file called schema.prisma and a .env file at the root of the project. The schema.prisma defines your database connection and Prisma Client generator. For this example, you’ll use SQLite for ease of setting up. If you’d like to use another database provider, switching is as simple as renaming the provider from sqlite to your provider of choice without any additional setup and updating the connection URL.

For our example, you’ll define two tables: Post and User with a one-to-many relationship between User and Post. Update your schema.prisma file to resemble this one:



// schema.prisma
datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

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

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

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

Create your first database migration

To sync your data model to your database schema, you’ll need to use prisma migrate CLI.



npx prisma migrate dev --name init

The above command will create a migration called init located in the /prisma/migrations directory. The migrations folder is used to keep track of schema changes for our database. Think of it as version control, but for your database schema. The Prisma CLI also creates your dev.db database.

After prisma migrate is done creating a migration, the Prisma CLI installs @prisma/client package and generates Prisma Client.

Prisma Client is an auto-generated and type-safe query builder tailored to your database schema. prisma migrate will update your Prisma Client every time you run a migration.

Note: The above features are now in General Availability and your feedback will be highly appreciated and will help us keep improving them. 🙂

Step 2: Add your `serverMiddleware` endpoints

For your API, you will use Express inside Nuxt to create our API. Sounds crazy, right?

Well, Express is used to allow your API endpoints to access the request and response objects.

Go ahead and install Express:



npm install express

Create an api folder and an index.js file that will contain your API handlers:



mkdir api
touch api/index.js

After creating your /api/index.js file, paste in the following code in index.js:



// index.js
import express from 'express'
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()
const app = express()

app.use(express.json())

/** 
* logic for our api will go here
*/
export default {
  path: '/api',
  handler: app
}

The above code initializes Express and Prisma and exports two properties, path and handler, which will be registered in nuxt.config.js in Step 3. The path property specifies the route the middleware will be accessible, and handler specifies the function executed when invoked. For the rest of this step, you’ll be working in index.js setting up the endpoints and their respective handlers.

Create a `User`

The first feature you’ll be implementing is creating a user/ author. The database will be expecting an email and an optional name. It’s implementation is as follows:



// index.js
app.post(`/user`, async (req, res) => {
  const result = await prisma.user.create({
    data: {
      email: req.body.email,
      name: req.body.name,
    },
  })
  res.json(result)
})

Creating a `Post`

Next, you’ll add the create post endpoint. The request body will expect a title, content and authorEmail. If an author doesn’t exist in the database, their user record will be created.



// index.js
app.post('/post', async (req, res) => {
  const { title, content, authorEmail } = req.body
  const post = await prisma.post.create({
    data: {
      title,
      content,
      author: {
        connectOrCreate: {
          email: authorEmail
        }
      }
    }
  })
  res.status(200).json(post)
})

Get drafts

Once that is done, you’ll need to be able to view all unpublished posts. Prisma lets you specify all relations you’d like to be returned in the response with the include property. This is where you’ll add the author relation query to view the respective posts as well as their authors.



// index.js
app.get('/drafts', async (req, res) => {
  const posts = await prisma.post.findMany({
    where: { published: false },
    include: { author: true }
  })
  res.json(posts)
})

Get `Post` by `Id`

You can get a post by it’s id using findUnique as follows:



// index.js
app.get('/post/:id', async (req, res) => {
  const { id } = req.params
  const post = await prisma.post.findUnique({
    where: {
      id: Number(id),
    },
    include: { author: true }
  })
  res.json(post)
})

Publish a `Post`

When a Post is ready to go live update the published field:



// index.js
app.put('/publish/:id', async (req, res) => {
  const { id } = req.params
  const post = await prisma.post.update({
    where: {
      id: Number(id),
    },
    data: { published: true },
  })
  res.json(post)
})

Get Feed

All your published posts can be made available on the /feed endpoint, filtering them by checking that the published property is set to true.



// index.js
app.get('/feed', async (req, res) => {
  const posts = await prisma.post.findMany({
    where: { published: true },
    include: { author: true },
  })
  res.json(posts)
})

Deleting a `Post`

The last CRUD feature is deleting a Post record in your database:



// index.js
app.delete(`/post/:id`, async (req, res) => {
  const { id } = req.params
  const post = await prisma.post.delete({
    where: {
      id: parseInt(id),
    },
  })
  res.json(post)
})

The final feature in your application is filtering posts, checking if the searchString is found in either the title or content of your Posts.

Search for a `Post`



// index.js
app.get('/filterPosts', async (req, res) => {
  const { searchString } = req.query
  const draftPosts = await prisma.post.findMany({
    where: {
      OR: [
        {
          title: {
            contains: searchString,
          },
        },
        {
          content: {
            contains: searchString,
          },
        },
      ],
    },
  })
  res.send(draftPosts)
})

Step 3: Modify `nuxt.config.js`

The last step is registering your serverMiddleware in nuxt.config.js which is as simple as this.



// nuxt.config.js
  serverMiddleware: [
    '~/api/index.js'
  ]

Step 4: Take your API for a spin

Once you’ve modified nuxt.config.js, make sure to restart your Nuxt app. You can use Prisma Studio to create your database records. Alternatively, you can use your favorite API testing tool - for example Postman,Insomnia or REST Client - to test your API by making HTTP requests against your API.

In a new terminal window, use the Prisma CLI to startup Prisma Studio.



npx prisma studio

The command opens Prisma studio on localhost:5555.

Create a couple of User and Post records on Prisma Studio and save your changes.

Since the post isn’t published yet, fetch a list of the drafted posts using the GET api/drafts endpoint.

Congratulations! Your app is up and running! 🎉

Conclusion

Nuxt is a great tool for building fullstack applications using Vue. Pair it up with Prisma and working with databases while building a fullstack app becomes less of a hassle. This enables a developer to focus on what matters, shipping features!

Using Nuxt to build fullstack applications is great for prototyping. However, if you’d like to build bigger applications, it’s recommended to separate your frontend from the backend.

I hope you liked this tutorial and learned something useful. The completed project is available on GitHub.

Do you have any suggestions of any libraries/frameworks you’d like to see paired with Prisma? Feel free to let us know in the discussion or create an issue on GitHub.

Happy hacking!

DEV Community: Prisma

Extend your Platformatic API with Prisma Client

Prerequisites

Your dev toolbox

Assumed knowledge

Set up your Platformatic API

Project structure and files

Extend the functionality of your API with plugins

Define the plugin property in your platformatic.db.json file

Create a plugin

Extend the GraphQL API

Extend the REST API

Recap and next steps

Friendly data modeling & auto-generated, editable migrations for Platformatic with Prisma

Prerequisites

Your dev toolbox

Assumed knowledge

Set up your Platformatic app

Project structure and files

Data modeling and automated migrations

Set Prisma in your project

Model your database schema

Generate a migration with migrate diff

Start your API server

Explore and interact with your API

Introspect your database for the versions model

Side quest 🧙🏽: Automate versioning and generation of your database migrations

Install the helper utility

Update your schema

Auto-generate an up migration using @ruheni/db-diff

Restart and interact with your API using Platformatic

Wrapping up

Why Prisma and Platformatic are a great match

What is Platformatic DB

What is Prisma

How Prisma can improve your development workflow with Platformatic DB

Intuitive data modeling language

Auto-generated and customizable SQL migrations

Type-safety and auto-completion for database queries

Wrapping up

How Prisma Introspects a Schema from a MongoDB Database

Prisma

Prisma + MongoDB

Prisma and MongoDB: Schema team edition

Introspecting a schema with MongoDB

Research

MongoDB Compass

mongodb-schema

Implementing introspection for MongoDB in Prisma

How to present fields with different types

How to iterate on the data to get to a cleaner schema

How to enrich the introspected Prisma schema with relations

Summary

Using Prisma to Address Connection Pooling Issues in Serverless Environments

What is Prisma?

What is “serverless” and why do developers “go serverless”?

Reduced infrastructure requirements

Streamlined deployment

Reduced costs

Scalability

The issues with “connection pooling” in serverless environments

Methods of dealing with with connection pooling issues

External connection poolers

Adjusting your code

Fine tuning your database server

Solving connection pooling issues with Prisma Data Proxy

Flexible ORM and proxy with external connection pooling

Part of the Prisma Data Platform

The benefits of serverless hosting and Prisma

Get started with the Prisma Data Platform

Database Migrations for .NET and Entity Framework with Prisma

Introduction

What is Prisma?

Prerequisites

Step 1: Create a new console application

Step 2: Add Prisma to your project

Initialize Prisma

Data modelling

Create a database migration

Step 3: Introspect your database aka Reverse Engineering

Define the `plugin` property in your `platformatic.db.json` file

Generate a migration with `migrate diff`

Introspect your database for the `versions` model

Auto-generate an `up` migration using `@ruheni/db-diff`

`mongodb-schema`

Step 4: Set Up `typegraphql-prisma`

Create a New `Category`:

Create a New `Product`:

Side Quest: Some `typegraphql-prisma` Wizardry 🧙🏾‍♂️