How Build a Fully Typed Web App Using Next.js, PlanetScale, Drizzle, and Kysely

Originally published at ayoubkhial.com When it comes to web development, choosing the right tools and technologies is crucial for creating robust and scalable applications. If you're working with a Next.js app based on TypeScript and searching for a reliable database solution, combining , , and can be a mighty stack. PlanetScale Drizzle ORM Kysely offers a highly available and scalable MySQL-based database, ideal for horizontally scalable applications. PlanetScale is a TypeScript ORM for SQL databases, ensures maximum type safety, and provides a convenient CLI tool, Drizzle Kit, for defining table schemas and generating SQL migrations effortlessly. Drizzle ORM is a type-safe TypeScript SQL query builder that complements the stack by simplifying the process of constructing SQL queries. Kysely You can develop scalable, type-safe, and efficient applications by combining PlanetScale, Drizzle ORM, and Kysely with Next.js based on TypeScript. Whether your project is small or large-scale, this stack empowers you to handle complex data operations while maintaining a clean and maintainable codebase. What Is PlanetScale? is a super cool database-as-a-service platform based on , the same technology that powers YouTube, Slack, and other mega-scale companies. It gives you a serverless MySQL-compatible database with an awesome Git-like development experience. PlanetScale Vitess Managing databases becomes a breeze with PlanetScale, and it scales like a champ while delivering top-notch performance. Here are some benefits of using PlanetScale: Scale your data horizontally across multiple servers effortlessly. Horizontal sharding: Make schema changes without impacting or locking database availability. Non-blocking schema changes: Manage different branches for development, testing, and production environments, just like Git. Branching workflow: Optimize performance by distributing your database closer to users in the different areas, reducing latency at the edge. Multiple regions: The PlanetScale hobby plan provides a single database with and branch. If you require multiple development branches, such as for different environments, you must upgrade your plan to accommodate them separately. one production one development See more information Setup PlanetScale Account and Create Your First Database To get started, we need to create a PlanetScale account. You have two options: visit the official website, and click the button or follow . Get Started this link You have two options for creating an account: sign up with your email and password, or use your GitHub account. In this tutorial, I'll be using the GitHub account signup method. Upon signing up, you will receive a verification email. Click on the link within the email to confirm your account, regardless of your chosen signup method. Once you click on the confirmation link, you'll be taken through an onboarding flow that highlights the amazing features of PlanetScale and provides guidance on how to use them. At the end of the flow, you'll see a button that says, . Create your first database Click on it, and give your database a name (I'll go with ). movies Ensure that you choose the region closest to your serverless function. This selection will help in minimizing latency and improving overall performance. Instead of using the dashboard, you can use the PlanetScale CLI to create your initial branch. Run the following command: . We'll explore the PlanetScale CLI in more detail, including its features and capabilities. pscale database create movies --region us-east Once you've provided a name for your first database, PlanetScale will start the initialization process. This may take a couple of minutes to complete. After the initialization is done, you'll land on the homepage of your database. This page provides an overview of your database and relevant details. It is the central hub for managing and interacting with your database within the PlanetScale platform. PlanetScale CLI The is a handy command-line tool provided by PlanetScale to make your life easier. Its user-friendly interface allows you to interact with your PlanetScale databases, perform various operations, and seamlessly manage your databases, branches, and deployments. PlanetScale CLI With the CLI, you can execute schema changes, create deploy requests, and more effortlessly. It's a valuable companion that streamlines your workflow and enhances your overall experience with PlanetScale databases. Using macOS, you can conveniently install the PlanetScale CLI using the . Just run the following command: Brew package manager brew install planetscale/tap/pscale Please follow for non-macOS users to install the PlanetScale CLI on your respective operating system. the instructions To confirm that the installation process was successful, you can run the following command: pscale version Upon running this command, you should see the version of your PlanetScale CLI printed in the terminal, similar to the following output: pscale version 0.146.0 (build date: 2023-06-06T15:12:18Z commit: 48fec66) Before using commands like , ensure MySQL is installed on your system. If you're using macOS, you can install MySQL by running the following command: . pscale shell brew install mysql-client To authenticate and log in to your PlanetScale account using the CLI, execute the following command: pscale auth login After executing the command, a new browser tab will open and prompt you to sign in via the browser. You will be asked to verify the device confirmation code displayed in your terminal. You should see a Successfully logged-in message displayed in your terminal once you click the button. Confirm code When we created a database in the previous section, it came with a default development branch called . As mentioned, you can have one production and one development branch in the free plan. Let's promote the branch as a production branch. movies main main pscale branch promote movies main Ensure that you enable on your production branch as well. This will grant you or your team the ability to create deploy requests. safe migrations A production branch adds an extra layer of protection for schema changes, just like a protected git branch. It ensures that any modifications or schema changes must go through a pull request for review and approval. This helps maintain the integrity and stability of the production environment. Read more about PlanetScale Branching By default, the CLI is disabled for production branches, but you can enable it in your database settings if desired. This gives you the flexibility to use the CLI for operations on your production branch when necessary. Now, let's create a new branch for our development environment. This branch will be specifically dedicated to our development work and experimentation. dev pscale branch create movies dev If you go to your PlanetScale dashboard, you can view the newly created branch listed among your branches. dev Here are some other helpful commands worth noting: pscale : Open a MySQL shell for a specific branch and database. Here, you can execute various MySQL commands like . pscale shell show databases : Manage the entire lifecycle of your database. Think of it as your database's CRUD interface. pscale database : Manage your branches and make schema changes. pscale branch These commands, among others, offer valuable functionality that can simplify your database management tasks. See the complete command list with the documentation on . PlanetScale CLI reference You can directly access the PlanetScale CLI web console within your PlanetScale database. Go to the Console tab and choose the dev branch. Create a TypeScript-Based Next.js App Create a new app by executing the following command: Next.js npx create-next-app@latest During the installation process, you will encounter the following prompts: Need to install the following packages: create-next-app@13.4.5 Ok to proceed? (y) y ✔ What is your project named? … favorite-movies ✔ Would you like to use TypeScript with this project? … No / Yes ✔ Would you like to use ESLint with this project? … No / Yes ✔ Would you like to use Tailwind CSS with this project? … No / Yes ✔ Would you like to use `src/` directory with this project? … No / Yes ✔ Use App Router (recommended)? … No / Yes ✔ Would you like to customize the default import alias? … No / Yes Need to install the following packages: create-next-app@13.4.5 Ok to proceed? (y) y ✔ What is your project named? … favorite-movies ✔ Would you like to use TypeScript with this project? … No / Yes ✔ Would you like to use ESLint with this project? … No / Yes ✔ Would you like to use Tailwind CSS with this project? … No / Yes ✔ Would you like to use `src/` directory with this project? … No / Yes ✔ Use App Router (recommended)? … No / Yes ✔ Would you like to customize the default import alias? … No / Yes Make sure to select the option during the app creation process, and feel free to include other technologies like ESLint and Tailwind CSS as per your needs. TypeScript Once you have responded to the prompts, the command will create a new folder with the specified project name and automatically install the required dependencies. create-next-app Utilizing the app router to leverage the latest features provided by Next.js is highly recommended. To ensure the proper initialization of your app, run the following command in the root folder of your project. npm run dev Connect Your Next.js app to a PlanetScale Database Go to your PlanetScale dashboard, and find the branch in the tab. Click on the button located at the top right corner. You will be asked to choose a name for your connection password. Leave the Branch and Role settings as they are by default. dev Branches Connect In the same branch, you can create multiple passwords to manage access rights for different developers. This gives you the flexibility to grant specific permissions based on their roles or requirements. By creating various passwords, you can ensure that developers have the necessary access privileges while maintaining security. Read more about the password roles concept Choose the option, and securely save the provided string. This string will be required when connecting your Next.js application to the PlanetScale database. Connect with Node.js Make sure to securely store these connection details, as PlanetScale automatically hides your password once it is initially generated. Create a file at the root of your project folder. In this file, store the database string obtained from PlanetScale. This string will securely establish the connection between your project and the PlanetScale database. .env DATABASE_URL='mysql://41n034teqmu82iodgr83:pscale_pw_ImHo71aUxU6iC9Z2OY6CwyxlYuO5WoECi7xFDJAWTO6@aws.connect.psdb.cloud/movies?ssl={"rejectUnauthorized":true}' Make sure to add the file to the to prevent committing this file to source control, as it may contain sensitive information. .env .gitignore Drizzle ORM is a TypeScript ORM for SQL databases that prioritizes type safety. It follows a familiar SQL-like syntax and catches errors at compile time. With Drizzle ORM, you can enjoy the convenience of automatic SQL migrations and seamless integration with any TypeScript project. Drizzle ORM Instead of manually creating tables using the PlanetScale CLI ( ), we can define our table schemas using Typescript interfaces. With the help of the (more on this later), we can effortlessly push these defined tables into our PlanetScale database. pscale shell Drizzle Kit This approach provides a convenient and efficient way to manage and synchronize our table structures. Let's begin by installing the following dependencies: npm install @planetscale/database drizzle-orm Next, we'll create a schema file called within the folder. schema.ts lib import { InferModel } from 'drizzle-orm'; import { int, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core'; export const movies = mysqlTable('movies', { id: serial('id').primaryKey(), title: varchar('title', { length: 256 }).notNull(), releaseYear: int('releaseYear'), rating: int('rating'), }); export type Movie = InferModel ; Here, we are defining a schema for the table using the package provided by drizzle-orm. This package offers the necessary data types to define our schema. movies MySQL-core Feel free to use different names for the file and the folder if you prefer. This is just a common convention followed by the community. schema.ts lib You can use different property names than the actual column names in the database. For instance, you can define a property like . This allows you to reference the column by using the property instead of the actual column . title: varchar('name', { length: 256 }).notNull() title name Additionally, it is necessary to install , a CLI tool that complements Drizzle ORM. Drizzle Kit enables the generation of migrations and facilitates database prototyping. Drizzle Kit Install it as a dev dependency, as we will use it through the terminal. npm install -D drizzle-kit To configure Drizzle Kit, we need to create a Drizzle configuration file. This file specifies the schema file location and the database connection URI. It enables Drizzle Kit to generate SQL prototyping and migrations. Create a new file named in the root folder of your application: drizzle.config.ts import type { Config } from 'drizzle-kit'; export default { schema: './lib/schema.ts', out: './drizzle-output', connectionString: process.env.DATABASE_URL, } satisfies Config; We specify the schema file, connection string, and the desired location for the generated migration file. You have the option to use separate files for each by modifying the schema property to . This allows you to have individual schema files for each table in your application. schema schema: "./src/schema/*" └── lib └── schema └── movie.ts └── actor.ts The generated migration file will have an SQL extension and contain the SQL commands required to create the table. CREATE TABLE `movies` ( `id` serial AUTO_INCREMENT PRIMARY KEY NOT NULL, `title` varchar(256) NOT NULL, `releaseYear` int, `rating` int DEFAULT 1); The drizzle kit offers various commands, and we'll use the command to push the table into our PlanetScale database. push movies Add a new script called in the list of scripts in your file. db:push package.json { ... "scripts": { "dev": "next dev", "build": "next build", "start": "next start", "lint": "next lint", "db:push": "drizzle-kit push:mysql --config=drizzle.config.ts" }, "dependencies": { ... }, "devDependencies": { ... } } Now, to execute the script, run the following command: npm run db:push You should see a successful message in your terminal indicating the changes have been applied. Go to the PlanetScale dashboard, and visit the "dev" branch. You will notice that our table has been successfully created. Now, we will create some records using the PlanetScale CLI, specifically the integrated MySQL shell, to demonstrate its functionality. Run the following command: pscale shell movies dev This will prompt the shell. Here we can use a plain SQL language to create our first record: INSERT INTO movies (title, releaseYear, rating) VALUES ('Scarface', 1983, 10); Use the operation to check if the record has been successfully registered. SELECT SELECT * FROM movies; +----+----------+-------------+--------+ | id | title | releaseYear | rating | +----+----------+-------------+--------+ | 1 | Scarface | 1983 | 10 | +----+----------+-------------+--------+ SELECT * FROM movies; +----+----------+-------------+--------+ | id | title | releaseYear | rating | +----+----------+-------------+--------+ | 1 | Scarface | 1983 | 10 | +----+----------+-------------+--------+ You can also see that the new record is displayed in the PlanetScale dashboard. Since the usage statistics displayed in the PlanetScale Dashboard are updated hourly, the record may take some time to appear. While utilizing an ORM such as Drizzle for query building is possible, it's essential to consider their limitations, especially when dealing with complex queries. Additionally, these ORMs can impact performance. Introducing , A type-safe SQL query builder. Kysely Kysely Query Builder is a type-safe TypeScript SQL query builder that makes writing SQL queries easier and less error-prone. It works seamlessly with TypeScript, providing autocompletion and type safety based on your database schema. Kysely It supports popular databases like MySQL, PostgreSQL, and SQLite; it also supports 3rd party dialects like PlanetScale and SurrealDB, eliminating the need for additional engine binaries. Install Kysely dependency by running the following command: npm install kysely We will also need to install the for the Kysely SQL query builder that enables compatibility with PlanetScale's MySQL-based database. PlanetScale dialect npm install kysely-planetscale Create a new file called in the folder, and add the following code: kysely.js lib import { Kysely } from 'kysely'; import { PlanetScaleDialect } from 'kysely-planetscale'; import { Kyselify } from 'drizzle-orm/kysely'; import { movies } from './schema'; interface Database { movies: Kyselify ; } export const queryBuilder = new Kysely ({ dialect: new PlanetScaleDialect({ url: process.env.DATABASE_URL, }), }); We use the utility to register typescript interfaces to Kysely table objects. kyselify We can now execute queries against the PlanetScale database with the dialect configured. In the next section, we will create a simple user interface to display a list of movies retrieved from our database. Kysely in Action Let's create a simple UI for displaying a static movie list. Modify the content of the file located in the folder to the following code snippet: page.tsx app export default function Home() { return ( Title Release year Rating title example 2023 7 ); } export default function Home() { return ( Title Release year Rating title example 2023 7 ); } Please refer to to get the CSS code. It's a little large and trivial to put here. this file There are two approaches to using Kysely in a Next.js app. We can directly call it in the file or create an API endpoint that returns the movies and then calls that endpoint in the file using the method. app.tsx app.tsx fetch Let's start with the easy one, calling Kysely directly on . page.tsx import { queryBuilder } from '@/lib/planetscale'; export default async function Home() { const movies = await queryBuilder.selectFrom('movies').selectAll().execute(); return ( ... {movies.map((movie) => ( {movie.title} {movie.releaseYear} {movie.rating} ))} ); } As you can see, we call the from the server component directly and treat it as any async function. queryBuilder The Kysely SQL builder utilizes method chaining to compose SQL commands. To become familiar with the syntax, you can use the . Additionally, you can explore provided by Kysely to discover more methods. Kysely playground these examples Navigate to in your web browser to see the list of movies retrieved from the database. localhost:3000 The approach described above is simpler and more straightforward for smaller applications. However, for larger applications with complex data retrieval requirements, it is better to use the route API approach to separate the database logic from the UI components, resulting in a more modular and maintainable codebase. Additionally, using the API allows you to fully leverage the enhanced function, which provides caching capabilities, among other benefits. fetch Under the folder, please create a new folder called and add a file named . api movies route.ts └── app └── api └── movies └── route.ts Since Next.js utilizes the folder structure to associate paths with each endpoint, any endpoint defined inside the file will be accessed via the path . route.ts /api/movies Let's add a method: GET import { queryBuilder } from '@/lib/planetscale'; import { NextRequest, NextResponse } from 'next/server'; const GET = async (_: NextRequest) => { const movies = await queryBuilder.selectFrom('movies').selectAll().execute(); return NextResponse.json(movies); }; export { GET }; We are using the same query builder command as on the file. page.tsx Now, on the file, we can use the method to call this endpoint. page.tsx fetch import { Movie } from '@/lib/schema'; const getMovies = async (): Promise => { try { const response = await fetch(`http://localhost:3000/api/movies`, { next: { revalidate: 3600 }, }); const movies: Movie[] = await response.json(); return movies; } catch (error: any) { throw new Error(error.message); } }; export default async function Home() { const movies = await getMovies(); return ( ... ); } You may notice the extra parameter we are passing to . This parameter is not supported by the native method. Next.js extends the options object to enable each request to set its own caching and revalidating. fetch fetch fetch By default, when the property is not set in the method, the fetched data is automatically cached indefinitely, meaning subsequent requests for the same data will be served from the cache without making additional network requests. revalidate fetch You can use the option to ensure that new data is fetched on every fetch request and prevent caching. By setting this option, each request will bypass the cache and retrieve the latest data directly from the server. cache: 'no-store' Deploying to Vercel The branch now holds our initial schema. Let's create a to push these changes to the production branch. dev deploy request main pscale deploy-request create movies dev If everything goes smoothly, you will see a success message with a link to the details of the deployment request. Deploy request #1 successfully created. View this deploy request in the browser: https://app.planetscale.com/ayoub-khial/movies/deploy-requests/1 Click on the link to redirect you to the deploy request details. From there, you can deploy the changes or close the request. Click on the deploy changes button, and after a few seconds, you'll see a message indicating the deployment was completed successfully. If you navigate to your main branch, you will find the newly created movies table. To set up your project on Vercel, commit it to Github and sync it with Vercel. Then, go to the tab and select from the left menu. From there, please create a new variable for the production environment, similar to what you did in the file. Settings Environment Variables .env But before that, get the database string from PlanetScale just like we did with the branch. But this time you need to get it from the branch. dev main It's essential to name your variable the same name as you named it in the file, in our case, . .env DATABASE_URL You're all set! Your database schema is now synchronized between your branches, and each branch is accountable for a particular environment. Conclusion The synergy between , , , and is a winning combination for creating scalable and fully typed applications. These tools offer the flexibility and power needed to build robust and efficient database-driven applications. Next.js PlanetScale Drizzle ORM Kysely You can find the complete code source in ; feel free to give it a star ⭐️. this repository Read more PlanetScale official website PlanetScale CLI features PlanetScale branching concept Drizzle ORM official website Kysely official website PlanetScale dialect for Kysely The complete source code repository If you enjoy what I write, I'd be thrilled if you could join ! my mailing list Originally published at ayoubkhial.com