Version: 1.36.2

Movie Quotes App Tutorial

This tutorial will help you learn how to build a full stack application on top of Platformatic DB. We're going to build an application that allows us to save our favourite movie quotes. We'll also be building in custom API functionality that allows for some neat user interaction on our frontend.

You can find the complete code for the application that we're going to build on GitHub.

note

We'll be building the frontend of our application with the Astro framework, but the GraphQL API integration steps that we're going to cover can be applied with most frontend frameworks.

What we're going to cover

In this tutorial we'll learn how to:

Create a Platformatic API
Apply database migrations
Create relationships between our API entities
Populate our database tables
Build a frontend application that integrates with our GraphQL API
Extend our API with custom functionality
Enable CORS on our Platformatic API

Prerequisites

To follow along with this tutorial you'll need to have these things installed:

Node.js >= v18.8.0 or >= v20.6.0
npm v7 or later
A code editor, for example Visual Studio Code

You'll also need to have some experience with JavaScript, and be comfortable with running commands in a terminal.

Build the backend

Create a Platformatic API

First, let's create our project directory:

mkdir -p tutorial-movie-quotes-app/apps/movie-quotes-api/

cd tutorial-movie-quotes-app/apps/movie-quotes-api/

Run this command in your terminal to start the Platformatic creator wizard:

npm
yarn
pnpm

npm create platformatic@latest

yarn create platformatic

pnpm create platformatic@latest

This interactive command-line tool will ask you some questions about how you'd like to set up your new Platformatic project. For this guide, select these options:

- What kind of project do you want to create?   => Application
- Where would you like to create your project?  => quick-start
- Which kind of project do you want to create?  => DB
- What is the name of the service?              => (generated-randomly), e.g. legal-soup
- What is the connection string?                => sqlite://./db.sqlite
- Do you want to create default migrations?     => Yes
- Do you want to create another service?        => No
- Do you want to use TypeScript?                => No
- What port do you want to use?                 => 3042
- Do you want to init the git repository?       => No

Once the wizard is complete, you'll have a Platformatic app project in the folder quick-start, with example migration files, plugin script, routes, and tests inside your service directory under services/

info

Make sure you run the npm/yarn/pnpm command install command manually if you don't ask the wizard to do it for you.

Define the database schema

Let's create a new directory to store our migration files:

mkdir migrations

Then we'll create a migration file named 001.do.sql in the migrations directory:

CREATE TABLE quotes (
  id INTEGER PRIMARY KEY,
  quote TEXT NOT NULL,
  said_by VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Now let's setup migrations in our Platformatic configuration file, platformatic.db.json:

{
  "$schema": "https://platformatic.dev/schemas/v0.23.2/db",
  "server": {
    "hostname": "{PLT_SERVER_HOSTNAME}",
    "port": "{PORT}",
    "logger": {
      "level": "{PLT_SERVER_LOGGER_LEVEL}"
    }
  },
  "db": {
    "connectionString": "{DATABASE_URL}",
    "graphql": true,
    "openapi": true
  },
  "plugins": {
    "paths": [
      "plugin.js"
    ]
  },
  "types": {
    "autogenerate": true
  },
  "migrations": {
    "dir": "migrations",
    "autoApply": true
  }
}

info

Take a look at the Configuration reference to see all the supported configuration settings.

Now we can start the Platformatic DB server:

npm run start

Our Platformatic DB server should start, and we'll see messages like these:

[11:26:48.772] INFO (15235): running 001.do.sql
[11:26:48.864] INFO (15235): server listening
    url: "http://127.0.0.1:3042"

Let's open a new terminal and make a request to our server's REST API that creates a new quote:

curl --request POST --header "Content-Type: application/json" \
  -d "{ \"quote\": \"Toto, I've got a feeling we're not in Kansas anymore.\", \"saidBy\": \"Dorothy Gale\" }" \
  http://localhost:3042/quotes

We should receive a response like this from the API:

{"id":1,"quote":"Toto, I've got a feeling we're not in Kansas anymore.","saidBy":"Dorothy Gale","createdAt":"1684167422600"}

Create an entity relationship

Now let's create a migration file named 002.do.sql in the migrations directory:

CREATE TABLE movies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

ALTER TABLE quotes ADD COLUMN movie_id INTEGER REFERENCES movies(id);

This SQL will create a new movies database table and also add a movie_id column to the quotes table. This will allow us to store movie data in the movies table and then reference them by ID in our quotes table.

Let's stop the Platformatic DB server with Ctrl + C, and then start it again:

npm run start

The new migration should be automatically applied and we'll see the log message running 002.do.sql.

GraphQL
Rest API

Our Platformatic DB server also provides a GraphQL API. Let's open up the GraphiQL application in our web browser:

http://localhost:3042/graphiql

Now let's run this query with GraphiQL to add the movie for the quote that we added earlier:

mutation {
  saveMovie(input: { name: "The Wizard of Oz" }) {
    id
  }
}

We should receive a response like this from the API:

{
  "data": {
    "saveMovie": {
      "id": "1"
    }
  }
}

Now we can update our quote to reference the movie:

mutation {
  saveQuote(input: { id: 1, movieId: 1 }) {
    id
    quote
    saidBy
    createdAt
    movie {
      id
      name
    }
  }
}

We should receive a response like this from the API:

{
  "data": {
    "saveQuote": {
      "id": "1",
      "quote": "Toto, I've got a feeling we're not in Kansas anymore.",
      "saidBy": "Dorothy Gale",
      "movie": {
        "id": "1",
        "name": "The Wizard of Oz"
      }
    }
  }
}

Our Platformatic DB server has automatically identified the relationship between our quotes and movies database tables. This allows us to make GraphQL queries that retrieve quotes and their associated movies at the same time. For example, to retrieve all quotes from our database we can run:

query {
  quotes {
    id
    quote
    saidBy
    createdAt
    movie {
      id
      name
    }
  }
}

To view the GraphQL schema that's generated for our API by Platformatic DB, we can run this command in our terminal:

npx platformatic db schema graphql

The GraphQL schema shows all of the queries and mutations that we can run against our GraphQL API, as well as the types of data that it expects as input.

Populate the database

Our movie quotes database is looking a little empty! We're going to create a "seed" script to populate it with some data.

Let's create a new file named seed.js and copy and paste in this code:

'use strict'

const quotes = [
  {
    quote: "Toto, I've got a feeling we're not in Kansas anymore.",
    saidBy: 'Dorothy Gale',
    movie: 'The Wizard of Oz'
  },
  {
    quote: "You're gonna need a bigger boat.",
    saidBy: 'Martin Brody',
    movie: 'Jaws'
  },
  {
    quote: 'May the Force be with you.',
    saidBy: 'Han Solo',
    movie: 'Star Wars'
  },
  {
    quote: 'I have always depended on the kindness of strangers.',
    saidBy: 'Blanche DuBois',
    movie: 'A Streetcar Named Desire'
  }
]

module.exports = async function ({ entities, db, sql }) {
  for (const values of quotes) {
    const movie = await entities.movie.save({ input: { name: values.movie } })

    console.log('Created movie:', movie)

    const quote = {
      quote: values.quote,
      saidBy: values.saidBy,
      movieId: movie.id
    }

    await entities.quote.save({ input: quote })

    console.log('Created quote:', quote)
  }
}

info

Take a look at the Seed a Database guide to learn more about how database seeding works with Platformatic DB.

Let's stop our Platformatic DB server running and remove our SQLite database:

rm db.sqlite

Now let's create a fresh SQLite database by running our migrations:

npx platformatic db migrations apply

And then let's populate the quotes and movies tables with data using our seed script:

npx platformatic db seed seed.js

Our database is full of data, but we don't have anywhere to display it. It's time to start building our frontend!

Build the frontend

We're now going to use Astro to build our frontend application. If you've not used it before, you might find it helpful to read this overview on how Astro components are structured.

tip

Astro provide some extensions and tools to help improve your Editor Setup when building an Astro application.

Create an Astro application

In the root tutorial-movie-quotes-app of our project, let's create a new directory for our frontent application:

mkdir -p apps/movie-quotes-frontend/

cd apps/movie-quotes-frontend/

And then we'll create a new Astro project:

npm create astro@latest -- --template basics

It will ask you some questions about how you'd like to set up your new Astro project. For this guide, select these options:

Where should we create your new project?

   .
◼  tmpl Using basics as project template
✔  Template copied

Install dependencies? (it's buggy, we'll do it afterwards)

   No
◼  No problem! Remember to install dependencies after setup.

Do you plan to write TypeScript?

   No
◼  No worries! TypeScript is supported in Astro by default, but you are free to continue writing JavaScript instead.

Initialize a new git repository?

   No
◼  Sounds good! You can always run git init manually.

Liftoff confirmed. Explore your project!
Run npm dev to start the dev server. CTRL+C to stop.
Add frameworks like react or tailwind using astro add.

Now we'll edit our Astro configuration file, astro.config.mjs and copy and paste in this code:

import { defineConfig } from 'astro/config'

// https://astro.build/config
export default defineConfig({
  output: 'server'
})

And we'll also edit our tsconfig.json file and add in this configuration:

{
  "extends": "astro/tsconfigs/base",
  "compilerOptions": {
    "types": ["astro/client"]
  }
}

Now we can start up the Astro development server with:

npm run dev

And then load up the frontend in our browser at http://localhost:3000

Now that everything is working, we'll remove all default *.astro files from the src/ directory, but we'll keep the directory structure. You can delete them now, or override them later.

Create a layout

In the src/layouts directory, let's create a new file named Layout.astro:

---
export interface Props {
  title: string;
  page?: string;
}
const { title, page } = Astro.props;
---

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width" />
    <title>{title}</title>
  </head>
  <body>
    <header>
      <h1>🎬 Movie Quotes</h1>
    </header>
    <nav>
      <a href="/">All quotes</a>
    </nav>
    <section>
      <slot />
    </section>
  </body>
</html>

The code between the --- is known as the component script, and the code after that is the component template. The component script will only run on the server side when a web browser makes a request. The component template is rendered server side and sent back as an HTML response to the web browser.

Now we'll update src/pages/index.astro to use this Layout component. Let's replace the contents of src/pages/index.astro with this code:

---
import Layout from '../layouts/Layout.astro';
---

<Layout title="All quotes" page="listing">
  <main>
    <p>We'll list all the movie quotes here.</p>
  </main>
</Layout>

Integrate the urql GraphQL client

We're now going to integrate the URQL GraphQL client into our frontend application. This will allow us to run queries and mutations against our Platformatic GraphQL API.

Let's first install @urql/core and graphql as project dependencies:

npm install @urql/core graphql

Then let's create a new .env file and add this configuration:

PUBLIC_GRAPHQL_API_ENDPOINT=http://127.0.0.1:3042/graphql

Now we'll create a new directory:

mkdir src/lib

And then create a new file named src/lib/quotes-api.js. In that file we'll create a new URQL client:

// src/lib/quotes-api.js

import { createClient, cacheExchange, fetchExchange } from '@urql/core';

const graphqlClient = createClient({
  url: import.meta.env.PUBLIC_GRAPHQL_API_ENDPOINT,
  requestPolicy: "network-only",
  exchanges: [cacheExchange, fetchExchange]
});

We'll also add a thin wrapper around the client that does some basic error handling for us:

// src/lib/quotes-api.js

async function graphqlClientWrapper(method, gqlQuery, queryVariables = {}) {
  const queryResult = await graphqlClient[method](
    gqlQuery,
    queryVariables
  ).toPromise();

  if (queryResult.error) {
    console.error("GraphQL error:", queryResult.error);
  }

  return {
    data: queryResult.data,
    error: queryResult.error,
  };
}

export const quotesApi = {
	async query(gqlQuery, queryVariables = {}) {
		return await graphqlClientWrapper("query", gqlQuery, queryVariables);
	},
	async mutation(gqlQuery, queryVariables = {}) {
		return await graphqlClientWrapper("mutation", gqlQuery, queryVariables);
	}
}

And lastly, we'll export gql from the @urql/core package, to make it simpler for us to write GraphQL queries in our pages:

// src/lib/quotes-api.js

export { gql } from "@urql/core";

Stop the Astro dev server and then start it again so it picks up the .env file:

npm run dev

Display all quotes

Let's display all the movie quotes in src/pages/index.astro.

First, we'll update the component script at the top and add in a query to our GraphQL API for quotes:

---
import Layout from '../layouts/Layout.astro';
import { quotesApi, gql } from '../lib/quotes-api';

const { data } = await quotesApi.query(gql`
  query {
    quotes {
      id
      quote
      saidBy
      createdAt
      movie {
        id
        name
      }
    }
  }
`);

const quotes = data?.quotes || [];
---

Then we'll update the component template to display the quotes:

<Layout title="All quotes" page="listing">
  <main>
    {quotes.length > 0 ? quotes.map((quote) => (
      <div>
        <blockquote>
          <p>{quote.quote}</p>
        </blockquote>
        <p>
          — {quote.saidBy}, {quote.movie?.name}
        </p>
        <div>
          <span>Added {new Date(Number(quote.createdAt)).toUTCString()}</span>
        </div>
      </div>
    )) : (
      <p>No movie quotes have been added.</p>
    )}
  </main>
</Layout>

And just like that, we have all the movie quotes displaying on the page!

Integrate Tailwind for styling

Automatically add the @astrojs/tailwind integration:

npx astro add tailwind --yes

Add the Tailwind CSS Typography and Forms plugins:

npm install --save-dev @tailwindcss/typography @tailwindcss/forms

Import the plugins in our Tailwind configuration file:

// tailwind.config.cjs

/** @type {import('tailwindcss').Config} */
module.exports = {
  content: ['./src/**/*.{astro,html,js,jsx,md,mdx,svelte,ts,tsx,vue}'],
  theme: {
    extend: {}
  },
  plugins: [
    require('@tailwindcss/forms'),
    require('@tailwindcss/typography')
  ]
}

Stop the Astro dev server and then start it again so it picks up all the configuration changes:

npm run dev

Style the listing page

To style our listing page, let's add CSS classes to the component template in src/layouts/Layout.astro:

---
export interface Props {
	title: string;
	page?: string;
}

const { title, page } = Astro.props;

const navActiveClasses = "font-bold bg-yellow-400 no-underline";
---

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width" />
    <title>{title}</title>
  </head>
  <body class="py-8">
    <header class="prose mx-auto mb-6">
      <h1>🎬 Movie Quotes</h1>
    </header>
    <nav class="prose mx-auto mb-6 border-y border-gray-200 flex">
      <a href="/" class={`p-3 ${page === "listing" && navActiveClasses}`}>All quotes</a>
    </nav>
    <section class="prose mx-auto">
      <slot />
    </section>
  </body>
</html>

Then let's add CSS classes to the component template in src/pages/index.astro:

<Layout title="All quotes">
  <main>
    {quotes.length > 0 ? quotes.map((quote) => (
      <div class="border-b mb-6">
        <blockquote class="text-2xl mb-0">
          <p class="mb-4">{quote.quote}</p>
        </blockquote>
        <p class="text-xl mt-0 mb-8 text-gray-400">
          — {quote.saidBy}, {quote.movie?.name}
        </p>
        <div class="flex flex-col mb-6 text-gray-400">
          <span class="text-gray-400 italic">Added {new Date(Number(quote.createdAt)).toUTCString()}</span>
        </div>
      </div>
    )) : (
      <p>No movie quotes have been added.</p>
    )}
  </main>
</Layout>

Our listing page is now looking much more user friendly!

Create an add quote page

We're going to create a form component that we can use for adding and editing quotes.

First let's create a new component file, src/components/QuoteForm.astro:

---
export interface QuoteFormData {
  id?: number;
  quote?: string;
  saidBy?: string;
  movie?: string;
}

export interface Props {
  action: string;
  values?: QuoteFormData;
  saveError?: boolean;
  loadError?: boolean;
  submitLabel: string;
}

const { action, values = {}, saveError, loadError, submitLabel } = Astro.props;
---

{saveError && <p class="text-lg bg-red-200 p-4">There was an error saving the quote. Please try again.</p>}
{loadError && <p class="text-lg bg-red-200 p-4">There was an error loading the quote. Please try again.</p>}

<form method="post" action={action} class="grid grid-cols-1 gap-6">
  <label for="quote" class="block">
    <span>Quote</span>
    <textarea id="quote" name="quote" required="required" class="mt-1 w-full">{values.quote}</textarea>
  </label>
  <label for="said-by" class="block">
    <span>Said by</span>
    <input type="text" id="said-by" name="saidBy" required="required" value={values.saidBy} class="mt-1 w-full">
  </label>
  <label for="movie" class="block">
    <span>Movie</span>
    <input type="text" id="movie" name="movie" required="required" autocomplete="off" value={values.movie} class="form-input mt-1 w-full">
  </label>
  <input type="submit" value={submitLabel} disabled={loadError && "disabled"} class="bg-yellow-400 hover:bg-yellow-500 text-gray-900 round p-3" />
</form>

Create a new page file, src/pages/add.astro:

---
import Layout from '../layouts/Layout.astro';
import QuoteForm from '../components/QuoteForm.astro';
import type { QuoteFormData } from '../components/QuoteForm.astro';

let formData: QuoteFormData = {};
let saveError = false;
---

<Layout title="Add a movie quote" page="add">
  <main>
    <h2>Add a quote</h2>
    <QuoteForm action="/add" values={formData} saveError={saveError} submitLabel="Add quote" />
  </main>
</Layout>

And now let's add a link to this page in the layout navigation in src/layouts/Layout.astro:

<nav class="prose mx-auto mb-6 border-y border-gray-200 flex">
  <a href="/" class={`p-3 ${page === "listing" && navActiveClasses}`}>All quotes</a>
  <a href="/add" class={`p-3 ${page === "add" && navActiveClasses}`}>Add a quote</a>
</nav>

Send form data to the API

When a user submits the add quote form we want to send the form data to our API so it can then save it to our database. Let's wire that up now.

First we're going to create a new file, src/lib/request-utils.js:

export function isPostRequest (request) {
  return request.method === 'POST'
}

export async function getFormData (request) {
  const formData = await request.formData()

  return Object.fromEntries(formData.entries())
}

Then let's update the component script in src/pages/add.astro to use these new request utility functions:

---
import Layout from '../layouts/Layout.astro';
import QuoteForm from '../components/QuoteForm.astro';
import type { QuoteFormData } from '../components/QuoteForm.astro';

import { isPostRequest, getFormData } from '../lib/request-utils';

let formData: QuoteFormData = {};
let saveError = false;

if (isPostRequest(Astro.request)) {
  formData = await getFormData(Astro.request);
}
---

When we create a new quote entity record via our API, we need to include a movieId field that references a movie entity record. This means that when a user submits the add quote form we need to:

Check if a movie entity record already exists with that movie name
Return the movie id if it does exist
If it doesn't exist, create a new movie entity record and return the movie ID

Let's update the import statement at the top of src/lib/quotes-api.js

-import { createClient } from '@urql/core'
+import { createClient, gql } from '@urql/core'

And then add a new method that will return a movie ID for us:

async function getMovieId (movieName) {
  movieName = movieName.trim()

  let movieId = null

  // Check if a movie already exists with the provided name.
  const queryMoviesResult = await quotesApi.query(
    gql`
      query ($movieName: String!) {
        movies(where: { name: { eq: $movieName } }) {
          id
        }
      }
    `,
    { movieName }
  )

  if (queryMoviesResult.error) {
    return null
  }

  const movieExists = queryMoviesResult.data?.movies.length === 1
  if (movieExists) {
    movieId = queryMoviesResult.data.movies[0].id
  } else {
    // Create a new movie entity record.
    const saveMovieResult = await quotesApi.mutation(
      gql`
        mutation ($movieName: String!) {
          saveMovie(input: { name: $movieName }) {
            id
          }
        }
      `,
      { movieName }
    )

    if (saveMovieResult.error) {
      return null
    }

    movieId = saveMovieResult.data?.saveMovie.id
  }

  return movieId
}

And let's export it too:

export const quotesApi = {
  async query (gqlQuery, queryVariables = {}) {
    return await graphqlClientWrapper('query', gqlQuery, queryVariables)
  },
  async mutation (gqlQuery, queryVariables = {}) {
    return await graphqlClientWrapper('mutation', gqlQuery, queryVariables)
  },
  getMovieId
}

Now we can wire up the last parts in the src/pages/add.astro component script:

---
import Layout from '../layouts/Layout.astro';
import QuoteForm from '../components/QuoteForm.astro';
import type { QuoteFormData } from '../components/QuoteForm.astro';

import { quotesApi, gql } from '../lib/quotes-api';
import { isPostRequest, getFormData } from '../lib/request-utils';

let formData: QuoteFormData = {};
let saveError = false;

if (isPostRequest(Astro.request)) {
  formData = await getFormData(Astro.request);

  const movieId = await quotesApi.getMovieId(formData.movie);

  if (movieId) {
    const quote = {
      quote: formData.quote,
      saidBy: formData.saidBy,
      movieId,
    };

    const { error } = await quotesApi.mutation(gql`
      mutation($quote: QuoteInput!) {
        saveQuote(input: $quote) {
          id
        }
      }
    `, { quote });

    if (!error) {
      return Astro.redirect('/');
    } else {
      saveError = true;
    }
  } else {
    saveError = true;
  }
}

Add autosuggest for movies

We can create a better experience for our users by autosuggesting the movie name when they're adding a new quote.

Let's open up src/components/QuoteForm.astro and import our API helper methods in the component script:

import { quotesApi, gql } from '../lib/quotes-api.js';

Then let's add in a query to our GraphQL API for all movies:

const { data } = await quotesApi.query(gql`
  query {
    movies {
      name
    }
  }
`);

const movies = data?.movies || [];

Now lets update the Movie field in the component template to use the array of movies that we've retrieved from the API:

<label for="movie" class="block">
  <span>Movie</span>
  <input list="movies" id="movie" name="movie" required="required" autocomplete="off" value={values.movie} class="form-input mt-1 w-full">
  <datalist id="movies">
    {movies.map(({ name }) => (
      <option>{name}</option>
    ))}
  </datalist>
</label>

Create an edit quote page

Let's create a new directory, src/pages/edit/:

mkdir src/pages/edit/

And inside of it, let's create a new page, [id].astro:

---
import Layout from '../../layouts/Layout.astro';
import QuoteForm, { QuoteFormData } from '../../components/QuoteForm.astro';

const id = Number(Astro.params.id);

let formValues: QuoteFormData = {};
let loadError = false;
let saveError = false;
---

<Layout title="Edit movie quote">
  <main>
    <h2>Edit quote</h2>
    <QuoteForm action={`/edit/${id}`} values={formValues} saveError={saveError} loadError={loadError} submitLabel="Update quote" />
  </main>
</Layout>

You'll see that we're using the same QuoteForm component that our add quote page uses. Now we're going to wire up our edit page so that it can load an existing quote from our API and save changes back to the API when the form is submitted.

In the [id.astro] component script, let's add some code to take care of these tasks:

---
import Layout from '../../layouts/Layout.astro';
import QuoteForm, { QuoteFormData } from '../../components/QuoteForm.astro';

import { quotesApi, gql } from '../../lib/quotes-api';
import { isPostRequest, getFormData } from '../../lib/request-utils';

const id = Number(Astro.params.id);

let formValues: QuoteFormData = {};
let loadError = false;
let saveError = false;

if (isPostRequest(Astro.request)) {
  const formData = await getFormData(Astro.request);
  formValues = formData;

  const movieId = await quotesApi.getMovieId(formData.movie);

  if (movieId) {
    const quote = {
      id,
      quote: formData.quote,
      saidBy: formData.saidBy,
      movieId,
    };

    const { error } = await quotesApi.mutation(gql`
      mutation($quote: QuoteInput!) {
        saveQuote(input: $quote) {
          id
        }
      }
    `, { quote });

    if (!error) {
      return Astro.redirect('/');
    } else {
      saveError = true;
    }
  } else {
    saveError = true;
  }
} else {
  const { data } = await quotesApi.query(gql`
    query($id: ID!) {
      getQuoteById(id: $id) {
        id
        quote
        saidBy
        movie {
          id
          name
        }
      }
    }
  `, { id });

  if (data?.getQuoteById) {
    formValues = {
      ...data.getQuoteById,
      movie: data.getQuoteById.movie.name
    };
  } else {
    loadError = true;
  }
}
---

Load up http://localhost:3000/edit/1 in your browser to test out the edit quote page.

Now we're going to add edit links to the quotes listing page. Let's start by creating a new component src/components/QuoteActionEdit.astro:

---
export interface Props {
  id: number;
}

const { id } = Astro.props;
---
<a href={`/edit/${id}`} class="flex items-center mr-5 text-gray-400 hover:text-yellow-600 underline decoration-yellow-600 decoration-2 underline-offset-4">
  <svg class="w-6 h-6 mr-1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor" class="w-6 h-6">
    <path d="M21.731 2.269a2.625 2.625 0 00-3.712 0l-1.157 1.157 3.712 3.712 1.157-1.157a2.625 2.625 0 000-3.712zM19.513 8.199l-3.712-3.712-8.4 8.4a5.25 5.25 0 00-1.32 2.214l-.8 2.685a.75.75 0 00.933.933l2.685-.8a5.25 5.25 0 002.214-1.32l8.4-8.4z" />
    <path d="M5.25 5.25a3 3 0 00-3 3v10.5a3 3 0 003 3h10.5a3 3 0 003-3V13.5a.75.75 0 00-1.5 0v5.25a1.5 1.5 0 01-1.5 1.5H5.25a1.5 1.5 0 01-1.5-1.5V8.25a1.5 1.5 0 011.5-1.5h5.25a.75.75 0 000-1.5H5.25z" />
  </svg>
  <span class="hover:underline hover:decoration-yellow-600">Edit</span>
</a>

Then let's import this component and use it in our listing page, src/pages/index.astro:

---
import Layout from '../layouts/Layout.astro';
import QuoteActionEdit from '../components/QuoteActionEdit.astro';
import { quotesApi, gql } from '../lib/quotes-api';

// ...
---

<Layout title="All quotes" page="listing">
  <main>
    {quotes.length > 0 ? quotes.map((quote) => (
      <div class="border-b mb-6">
        ...
        <div class="flex flex-col mb-6 text-gray-400">
          <span class="flex items-center">
            <QuoteActionEdit id={quote.id} />
          </span>
          <span class="mt-4 text-gray-400 italic">Added {new Date(Number(quote.createdAt)).toUTCString()}</span>
        </div>
      </div>
    )) : (
      <p>No movie quotes have been added.</p>
    )}
  </main>
</Layout>

Add delete quote functionality

Our Movie Quotes app can create, retrieve and update quotes. Now we're going to implement the D in CRUD — delete!

First let's create a new component, src/components/QuoteActionDelete.astro:

---
export interface Props {
  id: number;
}

const { id } = Astro.props;
---
<form method="POST" action={`/delete/${id}`} class="form-delete-quote m-0">
  <button type="submit" class="flex items-center text-gray-400 hover:text-red-700 underline decoration-red-700 decoration-2 underline-offset-4">
    <svg class="w-6 h-6 mr-1" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="currentColor" class="w-6 h-6">
      <path fill-rule="evenodd" d="M12 2.25c-5.385 0-9.75 4.365-9.75 9.75s4.365 9.75 9.75 9.75 9.75-4.365 9.75-9.75S17.385 2.25 12 2.25zm-1.72 6.97a.75.75 0 10-1.06 1.06L10.94 12l-1.72 1.72a.75.75 0 101.06 1.06L12 13.06l1.72 1.72a.75.75 0 101.06-1.06L13.06 12l1.72-1.72a.75.75 0 10-1.06-1.06L12 10.94l-1.72-1.72z" clip-rule="evenodd" />
    </svg>
    <span>Delete</span>
  </button>
</form>

And then we'll drop it into our listing page, src/pages/index.astro:

---
import Layout from '../layouts/Layout.astro';
import QuoteActionEdit from '../components/QuoteActionEdit.astro';
import QuoteActionDelete from '../components/QuoteActionDelete.astro';
import { quotesApi, gql } from '../lib/quotes-api';

// ...
---

<Layout title="All quotes" page="listing">
  <main>
    {quotes.length > 0 ? quotes.map((quote) => (
      <div class="border-b mb-6">
        ...
        <div class="flex flex-col mb-6 text-gray-400">
          <span class="flex items-center">
            <QuoteActionEdit id={quote.id} />
            <QuoteActionDelete id={quote.id} />
          </span>
          <span class="mt-4 text-gray-400 italic">Added {new Date(Number(quote.createdAt)).toUTCString()}</span>
        </div>
      </div>
...

At the moment when a delete form is submitted from our listing page, we get an Astro 404 page. Let's fix this by creating a new directory, src/pages/delete/:

mkdir src/pages/delete/

And inside of it, let's create a new page, [id].astro:

---
import Layout from '../../layouts/Layout.astro';

import { quotesApi, gql } from '../../lib/quotes-api';
import { isPostRequest } from '../../lib/request-utils';

if (isPostRequest(Astro.request)) {
  const id = Number(Astro.params.id);

  const { error } = await quotesApi.mutation(gql`
    mutation($id: ID!) {
      deleteQuotes(where: { id: { eq: $id }}) {
        id
      }
    }
  `, { id });

  if (!error) {
    return Astro.redirect('/');
  }
}
---
<Layout title="Delete movie quote">
  <main>
    <h2>Delete quote</h2>
    <p class="text-lg bg-red-200 p-4">There was an error deleting the quote. Please try again.</p>
  </main>
</Layout>

Now if we click on a delete quote button on our listings page, it should call our GraphQL API to delete the quote. To make this a little more user friendly, let's add in a confirmation dialog so that users don't delete a quote by accident.

Let's create a new directory, src/scripts/:

mkdir src/scripts/

And inside of that directory let's create a new file, quote-actions.js:

// src/scripts/quote-actions.js

export function confirmDeleteQuote (form) {
  if (confirm('Are you sure want to delete this quote?')) {
    form.submit()
  }
}

Then we can pull it in as client side JavaScript on our listing page, src/pages/index.astro:

<Layout>
  ...
</Layout>

<script>
  import { confirmDeleteQuote } from '../scripts/quote-actions.js'

  addEventListener('DOMContentLoaded', () => {
    document.querySelectorAll('.form-delete-quote').forEach((deleteForm) => {
      deleteForm.addEventListener('submit', (event) => {
        event.preventDefault()
        confirmDeleteQuote(event.currentTarget)
      })
    })
  })
</script>

Build a "like" quote feature

We've built all the basic CRUD (Create, Retrieve, Update & Delete) features into our application. Now let's build a feature so that users can interact and "like" their favourite movie quotes.

To build this feature we're going to add custom functionality to our API and then add a new component, along with some client side JavaScript, to our frontend.

Create an API migration

We're now going to work on the code for API, under the apps/movie-quotes-api directory.

First let's create a migration that adds a likes column to our quotes database table. We'll create a new migration file, migrations/003.do.sql:

ALTER TABLE quotes ADD COLUMN likes INTEGER default 0;

This migration will automatically be applied when we next start our Platformatic API.

Create an API plugin

To add custom functionality to our Platformatic API, we need to create a Fastify plugin and update our API configuration to use it.

Let's create a new file, plugin.js, and inside it we'll add the skeleton structure for our plugin:

// plugin.js

'use strict'

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

Now let's register our plugin in our API configuration file, platformatic.db.json:

{
  ...
  "migrations": {
    "dir": "./migrations"
  },
  "plugins": {
    "paths": ["./plugin.js"]
  }
}

And then we'll start up our Platformatic API:

npm run dev

We should see log messages that tell us that our new migration has been applied and our plugin has been loaded:

[10:09:20.052] INFO (146270): running 003.do.sql
[10:09:20.129] INFO (146270): plugin loaded
[10:09:20.209] INFO (146270): server listening
    url: "http://127.0.0.1:3042"

Now it's time to start adding some custom functionality inside our plugin.

Add a REST API route

We're going to add a REST route to our API that increments the count of likes for a specific quote: /quotes/:id/like

First let's add fluent-json-schema as a dependency for our API:

npm install fluent-json-schema

We'll use fluent-json-schema to help us generate a JSON Schema. We can then use this schema to validate the request path parameters for our route (id).

tip

You can use fastify-type-provider-typebox or typebox if you want to convert your JSON Schema into a Typescript type. See this GitHub thread to have a better overview about it. Look at the example below to have a better overview.

Here you can see in practice of to leverage typebox combined with fastify-type-provider-typebox:

import { FastifyInstance } from "fastify";
import { Static, Type } from "@sinclair/typebox";
import { TypeBoxTypeProvider } from "@fastify/type-provider-typebox";

/**
 * Creation of the JSON schema needed to validate the params passed to the route
 */
const schemaParams = Type.Object({
  num1: Type.Number(),
  num2: Type.Number(),
});

/**
 * We convert the JSON schema to the TypeScript type, in this case:
 * {
    num1: number;
    num2: number;
  }
 */
type Params = Static<typeof schemaParams>;

/**
 * Here we can pass the type previously created to our syncronous unit function
 */
const multiplication = ({ num1, num2 }: Params) => num1 * num2;

export default async function (app: FastifyInstance) {
  app.withTypeProvider<TypeBoxTypeProvider>().get(
    "/multiplication/:num1/:num2",
    { schema: { params: schemaParams } },
    /**
     * Since we leverage `withTypeProvider<TypeBoxTypeProvider>()`,
     * we no longer need to explicitly define the `params`.
     * The will be automatically inferred as:
     *  {
          num1: number;
          num2: number;
        }
     */
    ({ params }) => multiplication(params)
  );
}

Now let's add our REST API route in plugin.js:

'use strict'

const S = require('fluent-json-schema')

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

  // This JSON Schema will validate the request path parameters.
  // It reuses part of the schema that Platormatic DB has
  // automatically generated for our Quote entity.
  const schema = {
    params: S.object().prop('id', app.getSchema('Quote').properties.id)
  }

  app.post('/quotes/:id/like', { schema }, async function (request, response) {
    return {}
  })
}

We can now make a POST request to our new API route:

curl --request POST http://localhost:3042/quotes/1/like

info

Learn more about how validation works in the Fastify validation documentation.

Our API route is currently returning an empty object ({}). Let's wire things up so that it increments the number of likes for the quote with the specified ID. To do this we'll add a new function inside of our plugin:

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

  async function incrementQuoteLikes (id) {
    const { db, sql } = app.platformatic

    const result = await db.query(sql`
      UPDATE quotes SET likes = likes + 1 WHERE id=${id} RETURNING likes
    `)

    return result[0]?.likes
  }

  // ...
}

And then we'll call that function in our route handler function:

app.post('/quotes/:id/like', { schema }, async function (request, response) {
  return { likes: await incrementQuoteLikes(request.params.id) }
})

Now when we make a POST request to our API route:

curl --request POST http://localhost:3042/quotes/1/like

We should see that the likes value for the quote is incremented every time we make a request to the route.

{"likes":1}

Add a GraphQL API mutation

We can add a likeQuote mutation to our GraphQL API by reusing the incrementQuoteLikes function that we just created.

Let's add this code at the end of our plugin, inside plugin.js:

module.exports = async function plugin (app) {
  // ...

  app.graphql.extendSchema(`
    extend type Mutation {
      likeQuote(id: ID!): Int
    }
  `)

  app.graphql.defineResolvers({
    Mutation: {
      likeQuote: async (_, { id }) => await incrementQuoteLikes(id)
    }
  })
}

The code we've just added extends our API's GraphQL schema and defines a corresponding resolver for the likeQuote mutation.

We can now load up GraphiQL in our web browser and try out our new likeQuote mutation with this GraphQL query:

mutation {
  likeQuote(id: 1)
}

info

Learn more about how to extend the GraphQL schema and define resolvers in the Mercurius API documentation.

Enable CORS on the API

When we build "like" functionality into our frontend, we'll be making a client side HTTP request to our GraphQL API. Our backend API and our frontend are running on different origins, so we need to configure our API to allow requests from the frontend. This is known as Cross-Origin Resource Sharing (CORS).

To enable CORS on our API, let's open up our API's .env file and add in a new setting:

PLT_SERVER_CORS_ORIGIN=http://localhost:3000

The value of PLT_SERVER_CORS_ORIGIN is our frontend application's origin.

Now we can add a cors configuration object in our API's configuration file, platformatic.db.json:

{
  "server": {
    "logger": {
      "level": "{PLT_SERVER_LOGGER_LEVEL}"
    },
    "hostname": "{PLT_SERVER_HOSTNAME}",
    "port": "{PORT}",
    "cors": {
      "origin": "{PLT_SERVER_CORS_ORIGIN}"
    }
  },
  ...
}

The HTTP responses from all endpoints on our API will now include the header:

access-control-allow-origin: http://localhost:3000

This will allow JavaScript running on web pages under the http://localhost:3000 origin to make requests to our API.

Add like quote functionality

Now that our API supports "liking" a quote, let's integrate it as a feature in our frontend.

First we'll create a new component, src/components/QuoteActionLike.astro:

---
export interface Props {
  id: number;
  likes: number;
}

const { id, likes } = Astro.props;
---
<span data-quote-id={id} class="like-quote cursor-pointer mr-5 flex items-center">
  <svg class="like-icon w-6 h-6 mr-2 text-red-600" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5" stroke="currentColor" class="w-6 h-6">
    <path stroke-linecap="round" stroke-linejoin="round" d="M21 8.25c0-2.485-2.099-4.5-4.688-4.5-1.935 0-3.597 1.126-4.312 2.733-.715-1.607-2.377-2.733-4.313-2.733C5.1 3.75 3 5.765 3 8.25c0 7.22 9 12 9 12s9-4.78 9-12z" />
  </svg>
  <span class="likes-count w-8">{likes}</span>
</span>

<style>
  .like-quote:hover .like-icon,
  .like-quote.liked .like-icon {
    fill: currentColor;
  }
</style>

And in our listing page, src/pages/index.astro, let's import our new component and add it into the interface:

---
import Layout from '../layouts/Layout.astro';
import QuoteActionEdit from '../components/QuoteActionEdit.astro';
import QuoteActionDelete from '../components/QuoteActionDelete.astro';
import QuoteActionLike from '../components/QuoteActionLike.astro';
import { quotesApi, gql } from '../lib/quotes-api';

// ...
---

<Layout title="All quotes" page="listing">
  <main>
    {quotes.length > 0 ? quotes.map((quote) => (
      <div class="border-b mb-6">
        ...
        <div class="flex flex-col mb-6 text-gray-400">
          <span class="flex items-center">
            <QuoteActionLike id={quote.id} likes={quote.likes} />
            <QuoteActionEdit id={quote.id} />
            <QuoteActionDelete id={quote.id} />
          </span>
          <span class="mt-4 text-gray-400 italic">Added {new Date(Number(quote.createdAt)).toUTCString()}</span>
        </div>
      </div>
...

Then let's update the GraphQL query in this component's script to retrieve the likes field for all quotes:

const { data } = await quotesApi.query(gql`
  query {
    quotes {
      id
      quote
      saidBy
      likes
      createdAt
      movie {
        id
        name
      }
    }
  }
`);

Now we have the likes showing for each quote, let's wire things up so that clicking on the like component for a quote will call our API and add a like.

Let's open up src/scripts/quote-actions.js and add a new function that makes a request to our GraphQL API:

import { quotesApi, gql } from '../lib/quotes-api.js'

export function confirmDeleteQuote (form) {
  if (confirm('Are you sure want to delete this quote?')) {
    form.submit()
  }
}

export async function likeQuote (likeQuote) {
  likeQuote.classList.add('liked')
  likeQuote.classList.remove('cursor-pointer')

  const id = Number(likeQuote.dataset.quoteId)

  const { data } = await quotesApi.mutation(gql`
    mutation($id: ID!) {
      likeQuote(id: $id)
    }
  `, { id })

  if (data?.likeQuote) {
    likeQuote.querySelector('.likes-count').innerText = data.likeQuote
  }
}

And then let's attach the likeQuote function to the click event for each like quote component on our listing page. We can do this by adding a little extra code inside the <script> block in src/pages/index.astro:

<script>
  import { confirmDeleteQuote, likeQuote } from '../scripts/quote-actions.js'

  addEventListener('DOMContentLoaded', () => {
    document.querySelectorAll('.form-delete-quote').forEach((deleteForm) => {
      deleteForm.addEventListener('submit', (event) => {
        event.preventDefault()
        confirmDeleteQuote(event.currentTarget)
      })
    })

    document.querySelectorAll('.like-quote').forEach((container) => {
      container.addEventListener('click', (event) => likeQuote(event.currentTarget), { once: true })
    })
  })
</script>

Sort the listing by top quotes

Now that users can like their favourite quotes, as a final step, we'll allow for sorting quotes on the listing page by the number of likes they have.

Let's update src/pages/index.astro to read a sort query string parameter and use it the GraphQL query that we make to our API:

---
// ...

const allowedSortFields = ["createdAt", "likes"];
const searchParamSort = new URL(Astro.request.url).searchParams.get("sort");
const sort = allowedSortFields.includes(searchParamSort) ? searchParamSort : "createdAt";

const { data } = await quotesApi.query(gql`
  query {
    quotes(orderBy: {field: ${sort}, direction: DESC}) {
      id
      quote
      saidBy
      likes
      createdAt
      movie {
        id
        name
      }
    }
  }
`);

const quotes = data?.quotes || [];
---
<Layout title="All quotes" page={`listing-${sort}`}>
...

Then let's replace the 'All quotes' link in the <nav> in src/layouts/Layout.astro with two new links:

<nav class="prose mx-auto mb-6 border-y border-gray-200 flex">
  <a href="/?sort=createdAt" class={`p-3 ${page === "listing-createdAt" && navActiveClasses}`}>Latest quotes</a>
  <a href="/?sort=likes" class={`p-3 ${page === "listing-likes" && navActiveClasses}`}>Top quotes</a>
  <a href="/add" class={`p-3 ${page === "add" && navActiveClasses}`}>Add a quote</a>
</nav>

With these few extra lines of code, our users can now sort quotes by when they were created or by the number of likes that they have. Neat!

Wrapping up

And we're done — you now have the knowledge you need to build a full stack application on top of Platformatic DB.

We can't wait to see what you'll build next!

Movie Quotes App Tutorial

What we're going to cover​

Prerequisites​

Build the backend​

Create a Platformatic API​

Define the database schema​

Create an entity relationship​

Populate the database​

Build the frontend​

Create an Astro application​

Create a layout​

Integrate the urql GraphQL client​

Display all quotes​

Integrate Tailwind for styling​

Style the listing page​

Create an add quote page​

Send form data to the API​

Add autosuggest for movies​

Create an edit quote page​

Add delete quote functionality​

Build a "like" quote feature​

Create an API migration​

Create an API plugin​

Add a REST API route​

Add a GraphQL API mutation​

Enable CORS on the API​

Add like quote functionality​

Sort the listing by top quotes​

Wrapping up​

What we're going to cover

Prerequisites

Build the backend

Create a Platformatic API

Define the database schema

Create an entity relationship

Populate the database

Build the frontend

Create an Astro application

Create a layout

Integrate the urql GraphQL client

Display all quotes

Integrate Tailwind for styling

Style the listing page

Create an add quote page

Send form data to the API

Add autosuggest for movies

Create an edit quote page

Add delete quote functionality

Build a "like" quote feature

Create an API migration

Create an API plugin

Add a REST API route

Add a GraphQL API mutation

Enable CORS on the API

Add like quote functionality

Sort the listing by top quotes

Wrapping up