Building a Startup Acquisitions list on Airtable with Next.js, Prisma and Sequin

Airtable is the ultimate back office tool. Teams can work together to organize data around almost anything. Often, companies want to build apps, internal tools, or customer portals on top of their data in Airtable. While there are several low code options (stacker, softr, etc), often you'll want the full customizability and maintainability of full code.

With Sequin, you can replicate Airtable to a Postgres database to easily build a custom app on top of your Airtable data. Pairing the standard Sequin Postgres database with Prisma and Next.js, you can build even faster.

In this tutorial, you'll be using this new stack to build a fun website that lists the biggest startup acquisitions in tech.

Why choose Sequin

With Sequin, you can get a Postgres database containing all your Airtable data in less than 2 minutes. It syncs in real-time with Airtable so the database always contains fresh data.

It intelligently maps Airtable field types to the corresponding Postgres type. Sequin also recognizes indexes so your queries run fast. It does so by keeping track of primary keys and linked records.

Additionally, Sequin provides automatic migrations whenever you perform CRUD (create, read, update, delete) operations on your Airtable fields or whenever you add or remove any new bases to Airtable.

Sequin can host your database in the region of your choice or sync right into your existing database. With all your data in a hosted database, Sequin abstracts you from Airtable's API. It manages quota, errors, pagination and more so you can just build.

The best part about Sequin is it allows you to query Airtable with all the power and familiarity of SQL. Use the SQL syntax you already know to query Airtable.

What you will be building

You'll be building a website that allows users to explore tech startup acquisitions. When completed, you'll be able to filter by acquisition price so you can see which startups trotted into a new company as a unicorn and which got aqua-hired. You'll also be able to sort the startups by name or by price:

You will be using Next.js and Prisma to build your app. Next.js is a React framework that allows you to build a production-level app in an easy way. It's the fastest way to get started with React.

Prisma is a next-generation ORM for Node.js and TypeScript. It helps you to access your Sequin database in a type-safe manner so you make fewer errors and build your applications faster than ever.

Prisma and Next.js combined with Airtable via Sequin is a powerful combination. Let's get started.

Airtable Setup

For this tutorial, you will be using the Startup Acquisitions Airtable template.

This simple base contains one table, Acquisitions, with 7 fields:

Field Name	Airtable Field Type
Parent Company	Single line text
Acquired Startup	Single line text
Price Amount	Number
Currency Code	Single line text
Acquired At	Date
Source URL	URL
Source Description	Single line text

To add this template to your workspace, click the Copy base button in the top right corner:

Then, select your Airtable workspace in the modal that appears:

This will duplicate the entire base into your workspace so you can edit the base and access it through the API key.

Sequin Setup

You have setup your Airtable base. Now set up Sequin to replicate your Airtable base to a Postgres database.

Go to https://app.sequin.io/signup and create an account.

Connect your Airtable base by going through the tutorial or clicking the Add Base button.

You'll be prompted to enter your Airtable API key. After that, select the Startup Acquisitions base and all its tables. Then click Start Syncing.

Sequin will immediately provision you a Postgres database and begin syncing all the data in your Airtable base to it. You'll be provided with credentials for you new database. Keep these handy as you'll use them to connect your Sequin database to your app using Prisma.

Don't worry if you lose the credentials. You can always access them by clicking on Connect.

You now have access to a fully hosted Postgres database that is in sync with the Airtable base.

Why use Prisma

Prisma simplifies database access in the application and removes the complexity of writing queries. Currently, it only supports mySQL, SQLite, and (lucky for you) PostgreSQL.

The Prisma client provides auto-generated, type-safe database access. It has a simple and powerful API for working with relational data and transactions. And as a cherry on top, it allows visual data management with Prisma Studio.

Prisma and Sequin are the perfect combination to query the Airtable base faster without having to write any PostgresSQL queries.

Prerequisites

In this tutorial, you will be using Next.js, Prisma, urql and Tailwind CSS (for styling).

To help you get right into the project, I have created a starter repo. Clone the repo and let's get started!

$ git clone https://github.com/sync-inc-so/startup-acquisitions-starter.git startup-acquisitions

Folder structure

First, take a look at the folder structure:

startup-acquisitions/
  client/
  server/

The client/ folder is bootstrapped from create-next-app while the server/ folder was generated manually to house Prisma.

Go inside of the startup-acquisitions/ folder and cd into it as follows:

$ cd startup-acquisitions

You will first start with the backend (ie Prisma) to query all your Airtable data via Sequin and GraphQL.

Backend (Server-Side)

Navigate into the server/ directory:

$ cd server

Setting up Prisma

Install the following dependencies:

$ npm install prisma --save-dev
$ npm install @prisma/client apollo-server graphql-scalars nexus nexus-plugin-prisma

Here's how you'll be using each of these libraries:

prisma is a Prisma CLI which is used to generate a new Prisma project, introspect an existing database, generate artifacts (i.e, Prisma Client) and much more.
You'll use @prisma/client as an auto-generated query builder that enables type-safe database access and reduces boilerplate.
You'll also use apollo-server which is a open-source GraphQL server that works with pretty much all Node.js HTTP server frameworks.
graphql-scalars provide access to custom GraphQL scalars that are common but not supported by the GraphQL specification yet.
nexus allows you to strongly type your GraphQL schema in a code-first declarative manner.
nexus-plugin-prisma is the glue that makes nexus work with prisma.

Now, set up your Prisma project by creating your Prisma schema file with the following command:

$ npx prisma init

This command creates a new directory called prisma which contains a file called schema.prisma and a .env file in the root of the project. schema.prisma contains the Prisma schema with your database connection and the Prisma Client generator. .env is a dotenv file for defining environment variables (used for your database connection).

Connect your database

To connect your Sequin database to Prisma, you need to set the url field of the datasource block in your Prisma schema to your Sequin database connection URL.

Out of the box, the prisma/schema.prisma file looks like this:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

The url is set via an environment variable which is defined in .env.

Go ahead and edit the .env file so that the DATABASE_URL now points to your Sequin database.

To do so, you can simply copy and past your database url from the Sequin console - just click the black Connect button on your resource and then find the connection URL:

Your .env file will look something like this:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"

Once you've updated the DATABASE_URL, save the .env file.

Introspect the database

Now let's introspect the database by using the prisma introspect command. This command will automatically generates a database schema from your Sequin database inside the prisma/schema.prisma file.

$ npx prisma introspect

After the command executes, the prisma/schema.prisma will changed to:

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

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

model sync_meta {
  id            Int                      @id
  started_at    DateTime?                @db.Timestamptz(6)
  completed_at  DateTime?                @db.Timestamptz(6)
  duration_last Unsupported("interval")?

  @@map("_sync_meta")
}

model acquisitions {
  id                 String    @id
  created_time       DateTime? @db.Timestamptz(6)
  acquired_at        DateTime? @db.Date
  acquired_startup   String?
  currency_code      String?
  parent_company     String?
  price_amount       Decimal?  @db.Decimal
  source_description String?
  source_url         String?
}

You'll see that Prisma generates two models:

An acquisitions model which takes the table by the same name from Airtable. It also maps all the fields and datatypes properly.
A sync_meta model which is a special Sequin table that tracks the performance of the Sequin sync.

Because users do not have write access to the Sequin database, you can edit the model in schema.prisma as you want.

For instance, one edit you need to make here is for price_amount. The price_amount field, which captures the total acquisition price, contains some huge, multi-billion numbers. They cannot fit into Int so you'll be using BigInt.

So go ahead and change the price_amount field to use BigInt in schema.prisma:

model acquisitions {
  .
  price_amount       BigInt?   @db.BigInt
  .
}

Now, that you've assigned price_amount to type BigInt, save the schema.prisma file and define the BigInt scalar.

First, lets take a quick step back. In GraphQL, there are two different kinds of types.

Scalar types represent concrete units of data. The GraphQL spec has five predefined scalars: String, Int, Float, Boolean, and ID. Here, you need to create custom scalars like BigInt and DateTime.
Object types have fields that express the properties of that type and are composable. Here, you'll need to create an object type for Acquisition.

To start defining these custom types, generate the Prisma Client by typing in the following command in the terminal:

$ npx prisma generate

This will create the Prisma Client inside ./node_modules/@prisma/client.

Now, open up the api/graphql/ folder:

You'll see several files in this directory that will define several custom datatypes:

BigInt.ts allows you to support large numbers as GraphQL doesn't support it by default.
DateTime.ts allows support for DateTime as GraphQL doesn't have support for it by default.
Acquisition.ts contains the GraphQL model of your schema. Since you have only one table in your schema, you only need one model. If you had multiple tables, you would need multiple schema files.
Query.ts contains all the GraphQL queries needed for fetching data by the client-side of the app.
index.ts is just for ease of use. It imports everything inside api/graphql folder and re-exports it like a barrel. It acts as a simple aggregator of the other files to make imports easier down the road.

BigInt.ts

As we've noted, GraphQL doesn't have support for BigInt, so you'll need to use GraphQLBigInt from graphql-scalars. It allows you to handle large number like billion in your price_amount field. It converts the large values to string since JavaScript cannot handle very large numbers by default.

Declare your custom scalar type BigInt in the BigInt.ts file.

import { asNexusMethod } from 'nexus'
import { GraphQLBigInt } from 'graphql-scalars'

export const BigInt = asNexusMethod(GraphQLBigInt, 'bigint')

DateTime.ts

DateTime is also not supported by GraphQL so you need to create a custom type which is a nexus wrapper around GraphQLDateTime from graphql-scalars.

Declare a custom scalar type DateTime in the DateTime.ts file.

import { asNexusMethod } from 'nexus'
import { GraphQLDateTime } from 'graphql-scalars'

export const DateTime = asNexusMethod(GraphQLDateTime, 'date')

Acquisition.ts

The most basic components of a GraphQL schema are object types, which represent the kinds of objects you can fetch from your service, and what fields it has. You'll create your Acquisition model here. This should be the same as the model that you already generated in schema.prisma after introspecting your SyncInc database with npx prisma introspect.

Acquisition is a GraphQL Object Type, meaning it's a type with some fields. Most of the types in your schema will be object types. Add the following type definition to the Acquisition.ts file:

import { objectType } from 'nexus'

export const Acquisition = objectType({
  name: 'Acquisition',
  definition(t) {
    t.string('id')
    t.field('created_time', { type: 'DateTime' })
    t.field('acquired_at', { type: 'DateTime' })
    t.string('acquired_startup')
    t.string('currency_code')
    t.string('parent_company')
    t.field('price_amount', { type: 'BigInt' })
    t.string('source_description')
    t.string('source_url')
  },
})

Here, you are importing objectType from nexus and providing each of the fields defined in your model in schema.prisma with their respective types.

Note: Because DateTime and BigInt are custom scalar types. That's why you have to access them from t.field using a type option as a 2nd parameter. You must also pass the custom type to makeSchema as you have down below in schema.ts.

Define your GraphQL query

Now, you'll declare the queries you want your GraphQL server to expose to your client in Query.ts.

Open up the Query.ts file:

Query.ts

As you'll recall, you want the user to be able to filter the list of startup acquisitions by different price thresholds to see which was a unicorn acquisition and which was a bit of a "soft landing."

To do so, you'll first get the query to return the Acquisition details between a minimum price and a maximum price.

import { queryType, nonNull, arg, intArg, booleanArg, stringArg } from 'nexus'

import { Context } from '../context'

export const Query = queryType({ // 1
  definition(t) {
    t.nonNull.list.field('getAcquisitionsByPrice', { // 2
      type: 'Acquisition', // 3
      args: { // 4
        minPrice: nonNull(arg({ type: 'BigInt' })),
        maxPrice: nonNull(arg({ type: 'BigInt' })),
      },
      resolve: async (
        _root,
        { minPrice, maxPrice }, // 5
        ctx: Context
      ) => {
        const acquisitions = await ctx.prisma.acquisitions.findMany({ // 6
          where: { // 7
            AND: [
              {
                price_amount: {
                  gte: minPrice,
                },
              },
              {
                price_amount: {
                  lte: maxPrice,
                },
              },
            ],
          },
        })
        return acquisitions // 8
      },
    })
  },
})

Stepping through this code:

Starting off, you import queryType from nexus. This tells nexus that you are declaring a Query and not a custom type.
Next, you name your query getAcquisitionsByPrice inside the field. The same name should be used when calling it from the GraphQL playground and the client-side. t.nonNull.list.field() tells nexus that the result should be a non-null list of values.
The list should be of type Acquisition as described by the type field inside of it. This is the return type inside of the resolve folder.
The args parameter takes in minPrice and maxPrice. The nonNull function around them makes these required fields. minPrice and maxPrice are of custom scalar types BigInt as you have declared in the BigInt.ts file.
The arguments you passed into the query in the previous step can be accessed in the 2nd parameter of the resolve function.
You then use findMany which returns a list of values of type Acquisition. You have specified this above when you wrote type. It is also why you used t.nonNull.list.field() previously.
The query in where makes sure that the price_amount is greater than or equal to minPrice and price_amount is less than or equal to maxPrice. You use the AND operator in this case as you want both conditions to be true at the same time.
Finally, you return the acquisitions variable which is the result of ctx.prisma.acquisitions.findMany().

Next, you need to deal with any acquisition that was undiscolsed - or in this case a null value. To do so, you need to get the Acquisition details of deals that are undisclosed as well as all the acquisitions whose price amount is known by using a boolean operator:

.
.
.
export const Query = queryType({
  definition(t) {
    t.nonNull.list.field('getAcquisitionsByPrice', {
      type: 'Acquisition',
      args: {
        .
        .
        .
        undisclosed: nonNull(booleanArg()), // 1
      },
      resolve: async (
        _root,
        {
          .
          .
          .
          undisclosed  // 2
        },
        ctx: Context
      ) => {
        const priceAmountZero = {
          price_amount: {
            equals: 0,
          },
        } // 3
        const notUndisclosed = undisclosed
          ? priceAmountZero
          : { NOT: priceAmountZero } // 4

        const acquisitions = await ctx.prisma.acquisitions.findMany({
          where: {
            AND: [
              .
              .
              .
            ],
            ...notUndisclosed, // 5
          },
        })
        return acquisitions
      },
    })
  },
})

Lets step through the query:

The args parameter takes in undisclosed. undisclosed is of type Boolean. It is also a required field.
The arguments you passed into the query can be accessed in the 2nd parameter of the resolve function.
You then declare a variable priceAmountZero which literally lives up to its name. It compares the price_amount variable to the number zero. When the price_amount equals zero, it means that the startup acquisition numbers are undisclosed.
notUndisclosed variable returns all priceAmountZero values if its true or else all values that are not equal to zero when its false. You use the NOT operator to return only the startup acquisitions whose price_amount is known.
You spread your notUndisclosed variable declared inside the where clause.

Now, you'll be sorting your Acquisition details either descending or ascending. You'll sort on two parameters: price and startup name.

.
.
.
export const Query = queryType({
  definition(t) {
    t.nonNull.list.field('getAcquisitionsByPrice', {
      type: 'Acquisition',
      args: {
        .
        .
        .
        sortBy: nonNull(stringArg()), // 1
      },
      resolve: async (
        _root,
        {
          .
          .
          .
          sortBy // 2
        },
        ctx: Context
      ) => {
        const sortByPrice = sortBy.includes('price') // 3
        const sortByDesc = sortBy.includes('desc')
        const orderBy:
          | { price_amount: 'desc' | 'asc' }
          | { acquired_startup: 'desc' | 'asc' } = sortByPrice
          ? { price_amount: sortByDesc ? 'desc' : 'asc' }
          : { acquired_startup: sortByDesc ? 'desc' : 'asc' } // 4

        const acquisitions = await ctx.prisma.acquisitions.findMany({
          where: {
            .
            .
            .
          },
          orderBy, // 5
        })
        return acquisitions
      },
    })
  },
})

Stepping through this code:

The args parameter takes in sortBy. sortBy is of type String. It is also a required field.
The arguments you passed into the query can be accessed in the 2nd parameter of the resolve function.
sortByPrice checks if the sortBy variable includes price while the sortByDesc checks if the sortBy variable includes desc. It returns a boolean value.
You sort it by price descending if it includes both desc and price. If it includes only price, then you sort it ascending via price_amount. If it includes only desc but not price, then you sort by startup name ,i.e, acquired_startup. If it doesn't include both desc and price, then you sort by startup name ascending.
Put orderBy variable inside the where clause.

Finally, you'll implement offset pagination using Prisma's skip and take.

.
.
.
export const Query = queryType({
  definition(t) {
    t.nonNull.list.field('getAcquisitionsByPrice', {
      type: 'Acquisition',
      args: {
        skip: nonNull(intArg()), // 1
        take: nonNull(intArg()),
      },
      resolve: async (
        _root,
        { 
          .
          .
          .
          skip, // 2
          take
        },
        ctx: Context
      ) => {
        const acquisitions = await ctx.prisma.acquisitions.findMany({
          skip, // 3
          take,
          .
          .
          .
        })
        return acquisitions
      },
    })
  },
})

Stepping through this code:

The args parameter takes in skip and take. skip and take are of type Integer.
The arguments you passed into the query can be accessed in the 2nd parameter of the resolve function.
You pass in skip and take which you received as arguments. It allows you to paginate through the data. skip skips a certain number of results and take selects a limited range of results. skip is similar to OFFSET in SQL and take is similar to LIMIT in SQL.

Your final Query.ts file should look like:

import { queryType, nonNull, arg, intArg, booleanArg, stringArg } from 'nexus'

import { Context } from '../context'

export const Query = queryType({
  definition(t) {
    t.nonNull.list.field('getAcquisitionsByPrice', {
      type: 'Acquisition',
      args: {
        minPrice: nonNull(arg({ type: 'BigInt' })),
        maxPrice: nonNull(arg({ type: 'BigInt' })),
        undisclosed: nonNull(booleanArg()),
        sortBy: nonNull(stringArg()),
        skip: nonNull(intArg()),
        take: nonNull(intArg()),
      },
      resolve: async (
        _root,
        { minPrice, maxPrice, undisclosed, sortBy, skip, take },
        ctx: Context
      ) => {
        const priceAmountZero = {
          price_amount: {
            equals: 0,
          },
        }
        const notUndisclosed = undisclosed
          ? priceAmountZero
          : { NOT: priceAmountZero }

        const sortByPrice = sortBy.includes('price')
        const sortByDesc = sortBy.includes('desc')
        const orderBy:
          | { price_amount: 'desc' | 'asc' }
          | { acquired_startup: 'desc' | 'asc' } = sortByPrice
          ? { price_amount: sortByDesc ? 'desc' : 'asc' }
          : { acquired_startup: sortByDesc ? 'desc' : 'asc' }

        const acquisitions = await ctx.prisma.acquisitions.findMany({
          skip,
          take,
          where: {
            AND: [
              {
                price_amount: {
                  gte: minPrice,
                },
              },
              {
                price_amount: {
                  lte: maxPrice,
                },
              },
            ],
            ...notUndisclosed,
          },
          orderBy,
        })
        return acquisitions
      },
    })
  },
})

Standup the GraphQL API

You have now setup your GraphQL query, your model and the scalar data types needed for the app. Now, lets glue them together to the Prisma Client.

context.ts

Go inside the api/ directory and open up context.ts.

The context.ts file allows you to access the typings of your schema in your IDE. Enter the following code:

import { PrismaClient } from '@prisma/client'

export interface Context {
  prisma: PrismaClient
}

const prisma = new PrismaClient()

export const context: Context = {
  prisma,
}

Here, you generate a new instance of PrismaClient and then export the context.

schema.ts

Open up schema.ts and paste the following:

The schema.ts file is responsible for generating nexus-typegen.ts and schema.graphql.

import { join } from 'path'
import { makeSchema } from 'nexus'
import { nexusPrisma } from 'nexus-plugin-prisma'

import * as types from './graphql/index'

export const schema = makeSchema({
  types,
  outputs: {
    typegen: join(__dirname, 'nexus-typegen.ts'),
    schema: join(__dirname, 'schema.graphql'),
  },
  plugins: [nexusPrisma({ experimentalCRUD: true })],
})

You use the makeSchema method from the nexus package to combine the models and add Acquisition, BigInt, DateTime, and Query to the types array.

You also add nexusPrisma to the plugins array which ensures that nexus and prisma work together nicely.

Open up server.ts and paste the following:

server.ts

The server.ts file starts a simple GraphQL server using apollo-server.

import { ApolloServer } from 'apollo-server'
import { schema } from './schema'
import { context } from './context'

const server = new ApolloServer({
  schema,
  context,
})

server.listen().then(({ url }) => {
  console.log(`🚀 Server ready at ${url}`)
})

ApolloServer takes in schema and context variables defined in the previous files so it knows the schema it should generate on the GraphQL Playground and the typings it should provide in your IDE.

Start your server

Now, run the dev command in the terminal by typing:

$ npm run dev

Now you can open up http://localhost:4000 and put in the following query:

query getAcquisitionsByPrice {
  getAcquisitionsByPrice(
    minPrice: 9000000000
    maxPrice: 10000000000
    undisclosed: false
    sortBy: "price (desc)"
    skip: 0
    take: 20
  ) {
    parent_company
    acquired_startup
    price_amount
  }
}

It should return the following result:

Frontend (Client-Side)

Go inside the client/ directory from the root of your project.

$ cd client/

Start your server by typing the following in the terminal:

$ npm install
$ npm run dev

Define the GraphQL query client-side

In the client-side application, you need to get the startup details by querying the GraphQL endpoint you just created in the server. To do so you'll be creating getAcquisitionsByPrice.js inside your graphql/ folder.

Create the graphql/ folder in order to store your GraphQL queries in one location. In this app, it doesn't matter much as you only have one GraphQL query but it does make sense as the app grows large and you need multiple GraphQL queries.

Note: In larger apps, you can also divide graphql/ folder into multiple subfolders depending on their type like queries/, mutations/, fragments/, etc...

$ mkdir graphql
$ cd graphql
$ touch getAcquisitionsByPrice.js

Open up getAcquisitionsByPrice.js and paste the following:

const gql = String.raw

export const GET_ACQUISITIONS_BY_PRICE = gql`
  query getAcquisitionsByPrice(
    $minPrice: BigInt!
    $maxPrice: BigInt!
    $undisclosed: Boolean!
    $sortBy: String!
    $skip: Int!
    $take: Int!
  ) {
    getAcquisitionsByPrice(
      minPrice: $minPrice
      maxPrice: $maxPrice
      undisclosed: $undisclosed
      sortBy: $sortBy
      skip: $skip
      take: $take
    ) {
      id
      parent_company
      acquired_startup
      acquired_at
      price_amount
      source_description
      source_url
    }
  }
`

Note: The gql variable using String.raw simply adds syntax highlighting in VSCode. You can remove it if you want. It has no difference on the code whatsover.

Here, you're setting up the front-end to query the GraphQL endpoint you just built: getAcquisitionsByPrice. You'll see that the query perfectly matches the query you ran in GraphQL Playground while implementing the server side. The only difference is you have used variables instead of values (denoted with $).

The datatypes defined alongside the variables must match to the ones in Prisma. Notice, the ! at the end of each variable. It means that the value is required. If the value is not required, then you can omit the !.

Build the Card component

You'll be creating a Card component to display a card with startup details. It will contain the startup name, the parent company it got acquired by, the price for which it got acquired and much more.

Now, go inside the components/ folder, open up Card.js and paste the following:

import React from 'react'
import millify from 'millify'

import { MoneybagIcon } from './MoneybagIcon'

export const Card = ({ startup, index }) => { // 1
  const parentCompany = startup.parent_company
  const acquiredStartup = startup.acquired_startup
  const isPriceUndisclosed = parseInt(startup.price_amount) === 0 // 2
  const acquiredPrice = isPriceUndisclosed
    ? 'Undisclosed'
    : '$ ' + millify(startup.price_amount)
  const dateOfAcquisition = new Date(startup.acquired_at).toDateString()
  const pressDescription = startup.source_description
  const pressURL = startup.source_url

  let gradient = ''
  switch (index % 6) { // 3
    case 0:
      gradient = 'from-red-700'
      break
    case 1:
      gradient = 'from-indigo-700'
      break
    case 2:
      gradient = 'from-green-600'
      break
    case 3:
      gradient = 'from-pink-700'
      break
    case 4:
      gradient = 'from-cyan-600'
      break
    default:
      gradient = 'from-purple-700'
      break
  }

  return (
    <div
      className={`flex flex-wrap flex-col p-2 m-2 md:p-4 md:m-4 w-48 h-80 md:w-72 md:h-96 text-center rounded-xl bg-gradient-to-br ${gradient} relative`}
    >
      <div
        className={`text-xl md:text-3xl flex absolute transform rotate-[-30deg] top-4 -left-10 px-2 bg-blue-gray-900 text-blue-gray-400 rounded-lg`}
      >
        <MoneybagIcon className="w-8 h-8" />
        {acquiredPrice}
      </div>
      <div className="absolute text-xs md:text-lg top-5 right-5 text-blue-gray-400">
        {dateOfAcquisition}
      </div>
      <div className="mt-16 text-xl break-words md:text-4xl text-blue-gray-200">
        {acquiredStartup}
      </div>
      <div className="mt-4 text-sm md:text-lg text-blue-gray-400">
        Acquired by{' '}
        <span className="px-2 rounded-lg bg-blue-gray-900">
          {parentCompany}
        </span>
      </div>
      {pressDescription !== null && pressDescription.trim() !== '' && (
        <div className="mt-4 text-sm md:text-lg text-blue-gray-400">
          Press mention:{' '}
          <a
            href={pressURL}
            target="_blank"
            className="underline text-blue-gray-400"
          >
            {pressDescription}
          </a>
        </div>
      )}
    </div>
  )
}

This is just standard React. Let's step through it.

You take two props startup and index and render them into cards in your render() function.
Recall that in Airtable price_amount is equal to 0 for any undisclosed acquisition values. Since you are using BigInt for price_amount, the value is first converted into number from string using parseInt.
You are then using some gradients based on the index prop to create six, nice alternate colors.

Query for your data

Now you'll use urql as the lightweight GraphQL client. This allows you to communicate from the client to the server and fetch the startup details.

You will query the GraphQL query getAcquisitionsByPrice that is already set up on the backend to get a list of acquisitions.

Open up AcquisitionList.js and paste the following:

import React from 'react'
import { useQuery } from 'urql'

import { GET_ACQUISITIONS_BY_PRICE } from '../graphql/getAcquisitionsByPrice'

const Text = ({ children, className }) => (
  <p className={`mt-10 text-xl md:text-4xl text-center ${className}`}>
    {children}
  </p>
)

export const AcquisitionList = ({ price, undisclosed, sortBy }) => {
  const [minPrice, maxPrice] = price
  const [skip, setSkip] = React.useState(0)
  const [result, reexecuteQuery] = useQuery({
    query: GET_ACQUISITIONS_BY_PRICE,
    variables: {
      minPrice,
      maxPrice,
      undisclosed,
      sortBy,
      skip,
      take: 20,
    },
  }) // 1

  const { data, fetching, error } = result

  if (fetching && !data) return <Text>Loading...</Text> // 2
  if (error && !data) return <Text>Oh no... {error.message}</Text>

  return (
    .
    .
    .
  )
}

The AcquisitionList component is responsible for calling the GET_ACQUISITION_BY_PRICE query.

First, you also store the skip value in a variable so you can skip a bunch of pages using pagination. You then have a useQuery function from urql which takes in a bunch of variables needed for the GET_ACQUISITION_BY_PRICE query.
You show loading and error messages when there is no data. You display the loading and the error message with a little helper Text component.

You will now check if new data is available if someone clicked Load More... button. You will also clear the old data if someone changes the minPrice or the maxPrice.

.
.
.

export const AcquisitionList = ({ price, undisclosed, sortBy }) => {
  const [acq, setAcq] = React.useState([])
  .
  .
  .

  React.useEffect(() => {
    if (
      data !== undefined &&
      data !== null &&
      data.getAcquisitionsByPrice.length !== 0
    )
      setAcq([...acq, ...data.getAcquisitionsByPrice])
  }, [data]) // 2

  React.useEffect(() => {
    setAcq([])
  }, [minPrice, maxPrice]) // 3

  return (
    .
    .
    .
  )
}

First, you use acq to temporarily store the startup acquisitions data. You don't directly use the data.getAcquisitionsByPrice returned by result since you need to push the old data (see point no.2) when someone clicks the Load More... button.
You listen to the changes made to the data variable. If new data comes in, for example, when someone clicks Load More... button, then you combine it with the old data ,i.e, acq.
You listen to the changes made to the minPrice and maxPrice variable. When someone changes the ranges, you then empty the state by setting acq to []. This way you only display the new data between minPrice and maxPrice.

Now you'll render the list of acquisitions Cards.

.
.
.

import { Card } from './Card'

.
.
.

export const AcquisitionList = ({ price, undisclosed, sortBy }) => {
  .
  .
  .

  return (
    <>
      {(data.getAcquisitionsByPrice.length > 0 || acq.length > 0) && (
        <div className="flex flex-wrap justify-center mt-10">
          {acq
            .filter((startup) => {
              return undisclosed
                ? parseInt(startup.price_amount) === 0
                : parseInt(startup.price_amount) !== 0
            }) // 1
            .sort((a, b) => {
              const sortByNumber = (a, b) => a - b
              const sortByName = (a, b) => {
                const nameA = a.toUpperCase()
                const nameB = b.toUpperCase()
                if (nameA < nameB) return -1
                if (nameA > nameB) return 1
                return 0
              }

              switch (sortBy) {
                case 'name (desc)':
                  return sortByName(b.acquired_startup, a.acquired_startup)
                case 'name (asc)':
                  return sortByName(a.acquired_startup, b.acquired_startup)
                case 'price (asc)':
                  return sortByNumber(a.price_amount, b.price_amount)
                case 'price (desc)':
                default:
                  return sortByNumber(b.price_amount, a.price_amount)
              }
            }) // 2
            .map((startup, i) => {
              return <Card key={i} startup={startup} index={i} />
            })}
        </div>
      )}

      {fetching && <Text className="mt-16">Loading...</Text>} {/* 3 */}
      {error && <Text className="mt-16">Oh no... {error.message}</Text>}

      {data.getAcquisitionsByPrice.length !== 0 && !fetching && (
        <div className="flex justify-center mb-20">
          <button
            className="inline-flex items-center px-4 py-2 md:px-6 md:py-4 mt-16 text-sm md:text-xl font-medium text-white border border-transparent rounded-md shadow-sm select-none transform hover:-translate-y-0.5 transition-all duration-150 bg-gradient-to-br from-indigo-600 hover:bg-gradient-to-br hover:from-indigo-700 focus:ring-indigo-500 focus:outline-none focus:ring-2 focus:ring-offset-2 hover:shadow-lg"
            onClick={() => setSkip(skip + 20)}
          >
            <span className="ml-2">Load More...</span>
          </button>
        </div>
      )} {/* 4 */}
    </>
  )
}

First, filter the startup array by undisclosed variable. This variable shows or hides acquisitions whose numbers were undisclosed.
Sort the result from filter by either name or price ascendingly or descendingly as the user has selected. By default, the sort is by price descending.
You again show loading and error indicators. This is different from the above loading and error indicator as it only shows after you have data and after the Load More... button is clicked.
Increase the skip value by 20 so it pulls in 20 more startups when you click on the Load More... button

Your final AcquisitionList.js file should look like:

import React from 'react'
import { useQuery } from 'urql'

import { Card } from './Card'
import { GET_ACQUISITIONS_BY_PRICE } from '../graphql/getAcquisitionsByPrice'

const Text = ({ children, className }) => (
  <p className={`mt-10 text-xl md:text-4xl text-center ${className}`}>
    {children}
  </p>
)

export const AcquisitionList = ({ price, undisclosed, sortBy }) => {
  const [minPrice, maxPrice] = price
  const [acq, setAcq] = React.useState([])
  const [skip, setSkip] = React.useState(0)
  const [result, reexecuteQuery] = useQuery({
    query: GET_ACQUISITIONS_BY_PRICE,
    variables: {
      minPrice,
      maxPrice,
      undisclosed,
      sortBy,
      skip,
      take: 20,
    },
  })

  const { data, fetching, error } = result

  React.useEffect(() => {
    if (
      data !== undefined &&
      data !== null &&
      data.getAcquisitionsByPrice.length !== 0
    )
      setAcq([...acq, ...data.getAcquisitionsByPrice])
  }, [data])

  React.useEffect(() => {
    setAcq([])
  }, [minPrice, maxPrice])

  if (fetching && !data) return <Text>Loading...</Text>
  if (error && !data) return <Text>Oh no... {error.message}</Text>

  return (
    <>
      {(data.getAcquisitionsByPrice.length > 0 || acq.length > 0) && (
        <div className="flex flex-wrap justify-center mt-10">
          {acq
            .filter((startup) => {
              return undisclosed
                ? parseInt(startup.price_amount) === 0
                : parseInt(startup.price_amount) !== 0
            })
            .sort((a, b) => {
              const sortByNumber = (a, b) => a - b
              const sortByName = (a, b) => {
                const nameA = a.toUpperCase()
                const nameB = b.toUpperCase()
                if (nameA < nameB) return -1
                if (nameA > nameB) return 1
                return 0
              }

              switch (sortBy) {
                case 'name (desc)':
                  return sortByName(b.acquired_startup, a.acquired_startup)
                case 'name (asc)':
                  return sortByName(a.acquired_startup, b.acquired_startup)
                case 'price (asc)':
                  return sortByNumber(a.price_amount, b.price_amount)
                case 'price (desc)':
                default:
                  return sortByNumber(b.price_amount, a.price_amount)
              }
            })
            .map((startup, i) => {
              return <Card key={i} startup={startup} index={i} />
            })}
        </div>
      )}

      {fetching && <Text className="mt-16">Loading...</Text>}
      {error && <Text className="mt-16">Oh no... {error.message}</Text>}

      {data.getAcquisitionsByPrice.length !== 0 && !fetching && (
        <div className="flex justify-center mb-20">
          <button
            className="inline-flex items-center px-4 py-2 md:px-6 md:py-4 mt-16 text-sm md:text-xl font-medium text-white border border-transparent rounded-md shadow-sm select-none transform hover:-translate-y-0.5 transition-all duration-150 bg-gradient-to-br from-indigo-600 hover:bg-gradient-to-br hover:from-indigo-700 focus:ring-indigo-500 focus:outline-none focus:ring-2 focus:ring-offset-2 hover:shadow-lg"
            onClick={() => setSkip(skip + 20)}
          >
            <span className="ml-2">Load More...</span>
          </button>
        </div>
      )}
    </>
  )
}

Add filtering and sorting

You will use Headless UI to get a simple un-styled API for the Switch and Toggle component.

Now open up Toggle.js and paste the following:

import React from 'react'
import { Switch } from '@headlessui/react'

export const Toggle = ({ enabled, setEnabled }) => (
  <Switch.Group as="div" className="flex items-center justify-center mx-12">
    <Switch.Label className="mr-4 select-none text-md md:text-xl text-blue-gray-400">
      Show Undisclosed
    </Switch.Label>
    <Switch
      checked={enabled}
      onChange={setEnabled}
      className={`${
        enabled ? 'bg-gradient-to-br from-indigo-700' : 'bg-gray-200'
      } relative inline-flex items-center h-5 md:h-6 rounded-full w-11 transition-colors focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-indigo-500`}
    >
      <span
        className={`${
          enabled ? 'translate-x-7 md:translate-x-6' : 'translate-x-1'
        } inline-block w-3 h-3 md:w-4 md:h-4 transform bg-white rounded-full transition-transform`}
      />
    </Switch>
  </Switch.Group>
)

It simply takes an enabled and setEnabled variables as props and pass them to a Switch component from @headlessui/react. @headlessui/react offers a Toggle component which looks like:

You change a little bit of styling to make it look like:

Now, let's do the same with sliders. You will use react-slider package for implementing range sliders.

Open up Range.js and paste the following:

import React from 'react'
import ReactSlider from 'react-slider'

export const Range = ({ price, setPrice }) => (
  <div className="flex flex-col items-center justify-center mx-12 mt-10 mb-16 md:flex-row">
    <div className="flex flex-row items-center text-white md:flex-col">
      <span className="mr-4 text-md md:text-xl md:mr-0">Min</span>
      <input
        className="h-8 font-bold text-center md:h-12 text-md md:text-lg min-w-6 md:min-w-16 rounded-xl bg-gradient-to-b from-indigo-700 bg-blue-gray-900"
        name="minPrice"
        type="text"
        value={price[0]}
        readOnly={true}
      />
    </div>
    <ReactSlider
      step={1}
      min={0}
      max={1000000000}
      className="w-2/3 h-6 pr-2 mx-8 mt-4 mb-6 rounded-md md:w-1/2 md:mb-0 md:h-8 bg-blue-gray-700 cursor-grab"
      thumbClassName="absolute w-8 h-8 md:w-12 md:h-12 cursor-[grab] rounded-full focus:outline-none focus:ring-2 focus:ring-offset-2 ring-offset-blue-gray-700 -top-1 bg-gradient-to-b from-indigo-700 bg-blue-gray-900 focus:ring-indigo-500 focus:border-indigo-500"
      ariaLabel={['Min Price', 'Max Price']}
      value={price}
      onChange={(price) => {
        setPrice(price)
      }}
    />
    <div className="flex flex-row items-center text-white md:flex-col">
      <span className="mr-4 text-md md:text-xl md:mr-0">Max</span>
      <input
        className="h-8 font-bold text-center md:h-12 text-md md:text-lg min-w-6 md:min-w-16 rounded-xl bg-gradient-to-b from-indigo-700 bg-blue-gray-900"
        name="maxPrice"
        type="text"
        value={price[1]}
        readOnly={true}
      />
    </div>
  </div>
)

It takes in price and setPrice as props. price is an array of 2 values: minPrice and maxPrice. Both the input's are readOnly so you can select them but you cannot edit its values as you only want to edit the values through the range slider.

It should look like:

Now you will use Heroicons to get beautiful hand-crafted open-source SVG icons.

Open up SortBy.js and paste the following:

import React from 'react'
import { Listbox, Transition } from '@headlessui/react'
import { CheckIcon, SelectorIcon } from '@heroicons/react/solid'

export const SortBy = ({ options, selectedOption, setSelectedOption }) => {
  return (
    <Listbox
      as="div"
      className="flex items-center"
      value={selectedOption}
      onChange={(selectedOption) => {
        setSelectedOption(selectedOption)
      }}
    >
      {({ open }) => (
        <>
          <Listbox.Label className="mr-4 select-none text-md md:text-xl text-blue-gray-400">
            Sort by
          </Listbox.Label>
          <div className="relative mt-1">
            <Listbox.Button className="relative w-full h-10 py-2 pl-3 pr-10 text-left rounded-lg shadow-md cursor-default bg-gradient-to-br from-indigo-900 focus:outline-none focus-visible:ring-2 focus-visible:ring-opacity-75 focus-visible:ring-white focus-visible:ring-offset-orange-300 focus-visible:ring-offset-2 focus-visible:border-indigo-500 sm:text-sm">
              <span className="block ml-4 truncate">{selectedOption}</span>
              <span className="absolute inset-y-0 right-0 flex items-center pr-2 pointer-events-none">
                <SelectorIcon
                  className="w-5 h-5 text-blue-gray-400"
                  aria-hidden="true"
                />
              </span>
            </Listbox.Button>
            <Transition
              show={open}
              as={React.Fragment}
              leave="transition ease-in duration-100"
              leaveFrom="opacity-100"
              leaveTo="opacity-0"
            >
              <Listbox.Options
                static
                className="absolute top-0 left-0 z-10 w-full py-1 m-0 mt-1 overflow-auto text-base bg-indigo-900 rounded-md shadow-lg ring-1 ring-black max-h-56 ring-opacity-5 focus:outline-none sm:text-sm"
              >
                {options.map((option, index) => (
                  <Listbox.Option
                    key={index}
                    className={({ active }) =>
                      `${
                        active
                          ? 'text-white bg-indigo-800'
                          : 'text-blue-gray-400'
                      } cursor-default select-none relative py-2 pl-10 pr-4`
                    }
                    value={option}
                  >
                    {({ selected, active }) => (
                      <>
                        <span
                          className={`${
                            selected ? 'font-medium' : 'font-normal'
                          } block truncate`}
                        >
                          {option}
                        </span>
                        {selected ? (
                          <span
                            className={`${
                              active ? 'text-amber-600' : 'text-amber-600'
                            } absolute inset-y-0 left-0 flex items-center pl-3`}
                          >
                            <CheckIcon className="w-5 h-5" aria-hidden="true" />
                          </span>
                        ) : null}
                      </>
                    )}
                  </Listbox.Option>
                ))}
              </Listbox.Options>
            </Transition>
          </div>
        </>
      )}
    </Listbox>
  )
}

This component takes options, selectedOption and setSelectedOption variables as props. @headlessui/react offers a Select component which looks like:

The SortBy component looks complex but its just a copy-paste from the Headless UI Select docs while tweaking it a little bit (mostly styling) according to your needs.

In your app, it should look like:

Open up Home.js and paste the following:

import React from 'react'

import { AcquisitionList } from './AcquisitionList'
import { MoneybagIcon } from './MoneybagIcon'
import { Toggle } from './Toggle'
import { SortBy } from './SortBy'
import { Range } from './Range'

export const Home = () => {
  const timeout = React.useRef(null)
  const priceRange = [0, 100000000000] // minPrice = 0, maxPrice = 100 billion
  const [price, setPrice] = React.useState(priceRange)
  const [acquisitionPriceRange, setAcquisitionPriceRange] = React.useState(
    priceRange
  ) // 1

  React.useEffect(() => {
    timeout.current = setTimeout(() => {
      setAcquisitionPriceRange(price)
      timeout.current = null
    }, 2000)
    return () => {
      if (timeout.current) clearTimeout(timeout.current)
    }
  }, [price]) // 2

  const [enabled, setEnabled] = React.useState(false) // 3

  const options = ['price (desc)', 'price (asc)', 'name (desc)', 'name (asc)']
  const [selectedOption, setSelectedOption] = React.useState('price (desc)') // 4

  return (
    <div className="h-full min-h-screen p-8 text-white bg-blue-gray-900">
      <header className="flex justify-center mt-4 transform rotate-[-1deg]">
        <MoneybagIcon className="w-12 h-12 md:w-16 md:h-16" />
        <h1 className="px-2 ml-2 font-sans text-4xl font-medium tracking-wide lowercase md:text-6xl bg-gradient-to-br from-indigo-700">
          Startup Acquisitions
        </h1>
      </header>
      <h2 className="flex justify-center text-center mt-4 mb-8 ml-2 font-sans text-2xl md:text-4xl font-bold leading-none text-blue-gray-400 transform rotate-[-1deg]">
        a list of biggest startup acquisitions in tech
      </h2>

      <div className="flex items-center justify-center mt-16">
        <Toggle enabled={enabled} setEnabled={setEnabled} />
        <SortBy
          options={options}
          selectedOption={selectedOption}
          setSelectedOption={setSelectedOption}
        />
      </div>
      <Range price={price} setPrice={setPrice} />

      <AcquisitionList
        price={acquisitionPriceRange}
        undisclosed={enabled}
        sortBy={selectedOption}
      />
    </div>
  )
}

This component pulls the app together. Stepping through it:

You use priceRange in the React.useState() hook which ranges from zero to 100 billion. The first value is for minPrice and the second value is for maxPrice in the range slider. You also use temporary acquisitionPriceRange which is similar to priceRange. It is used to set the value 2 seconds later than priceRange (see the next point no. 2).
You listen to the changes in price in React.useEffect(). When the price changes, you set the acquisitionPriceRange to price in 2000 ms ,i.e, 2 seconds later. You pass price to the Range so the price variable gets updated whenever someone changes the range slider. Finally, you pass acquisitionPriceRange to AcquisitionList which is a 2-seconds delayed price so that you don't update the list as soon as someone changes the range slider. You wait 2 seconds.
Use enabled for your Toggle component which is then passed to AcquisitionList as a switch for undisclosed.
Finally, you have your list of options to display in the SortBy component. You keep track of the selection via the selectedOption variable which is also passed to AcquisitionList as sortBy to sort appropriately.

The final version should look like:

Conclusion

Using Airtable as your data source and SyncInc to convert Airtable to Postgres database helps you create blazing fast client-facing apps with the query language you already know (SQL).

You used Next.js as your React-powered front-end framework and Prisma as your ORM of choice. Prisma makes it simple to query the database by using static typing which allows you to code with confidence. The built-in autocompletion allows you to write applications at lightning speed.

You used Tailwind CSS to quickly make the applications look great. Thi focus on writing the logic rather than writing CSS. You also made use of Headless UI to easily create custom Switch and Select component.

You used urql as a lightweight GraphQL client to call your backend.

In conclusion, you launched a fully-functional app using real data from Airtable that is in real-time sync with Sequin.

Blog

Building a Startup Acquisitions list on Airtable with Next.js, Prisma and Sequin

Eric Goldman

Why choose Sequin

What you will be building

Airtable Setup

Sequin Setup

Why use Prisma

Prerequisites

Folder structure

Backend (Server-Side)

Setting up Prisma

Connect your database

Introspect the database

BigInt.ts

DateTime.ts

Acquisition.ts

Define your GraphQL query

Query.ts

Standup the GraphQL API

context.ts

schema.ts

server.ts

Start your server

Frontend (Client-Side)

Define the GraphQL query client-side

Build the Card component

Query for your data

Add filtering and sorting

Conclusion

Join Our Newsletter. No Spam, Only the good stuff.

Related