Sync Inc

Building an App on Airtable using Prisma, Next.js, and Sync Inc

Let's build a website that shows off what you can build on your Airtable data using Prisma, Next.js, and Sync Inc.

The website you'll build will allow users to explore an Airtable base with 10,000 records about tech startup acquisitions. When completed, users will be able to filter by acquisition price so they can see which startups trotted into a new company as a unicorn and which got aqua-hired:

Startup Acquisitions

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 Sync Inc 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 Sync Inc 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 NameAirtable Field Type
Parent CompanySingle line text
Acquired StartupSingle line text
Price AmountNumber
Currency CodeSingle line text
Acquired AtDate
Source URLURL
Source DescriptionSingle line text

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

Startup Acquisitions Airtable base

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

Modal

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

Sync Inc Setup

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

Go to https://app.syncinc.so/signup and create an account.

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

Add Base

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.

Select Base

Sync Inc 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 Sync Inc database to your app using Prisma.

Postgres Database Credentials

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

Startup Acquisitions SyncInc

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 Sync Inc 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 Sync Inc and GraphQL.

Backend

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 Sync Inc database to Prisma, you need to set the `url` field of the datasource block in your Prisma schema to your Sync Inc 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 Sync Inc database.

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

Postgres Database Credentials

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 Sync Inc 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:

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

Because users do not have write access to the Sync Inc 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:

  1. Starting off, you import `queryType` from `nexus`. This tells `nexus` that you are declaring a `Query` and not a custom type.
  2. 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.
  3. 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.
  4. 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.
  5. The arguments you passed into the query in the previous step can be accessed in the 2nd parameter of the `resolve` function.
  6. 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.
  7. 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.
  8. 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:

  1. The `args` parameter takes in `undisclosed`. `undisclosed` is of type `Boolean`. It is also a required field.
  2. The arguments you passed into the query can be accessed in the 2nd parameter of the `resolve` function.
  3. 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.
  4. `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.
  5. 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:

  1. The `args` parameter takes in `sortBy`. `sortBy` is of type `String`. It is also a required field.
  2. The arguments you passed into the query can be accessed in the 2nd parameter of the `resolve` function.
  3. `sortByPrice` checks if the `sortBy` variable includes `price` while the `sortByDesc` checks if the `sortBy` variable includes `desc`. It returns a boolean value.
  4. 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.
  5. 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:

  1. The `args` parameter takes in `skip` and `take`. `skip` and `take` are of type `Integer`.
  2. The arguments you passed into the query can be accessed in the 2nd parameter of the `resolve` function.
  3. 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;
},
});
},
});

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:

GraphQL Playground

Frontend

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.

  1. You take two props `startup` and `index` and render them into cards in your `render()` function.
  2. 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`.
  3. 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.

  1. 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.
  2. 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 (
.
.
.
)
}
  1. 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.
  2. 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`.
  3. 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 */}
</>
)
}
  1. First, filter the `startup` array by `undisclosed` variable. This variable shows or hides acquisitions whose numbers were `undisclosed`.
  2. 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.
  3. 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.
  4. 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:

Headless UI Switch

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

Switch

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:

Range Slider

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:

Headless UI Select

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:

Select

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:

  1. 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).
  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.
  3. Use `enabled` for your `Toggle` component which is then passed to `AcquisitionList` as a switch for `undisclosed`.
  4. 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:

Startup Acquisitions Long

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 SyncInc.

PowerBI
Retool

Was this helpful?