Thanks to services like PlanetScale, creating a MySQL database for your site is easier than ever! And by adding Netlify Identity you get to protect your site with authentication as well. In this tutorial we’ll guide you through setting up PlanetScale through our amazing integration and how to implement Netlify Identity as well. The finished result will be a very simple site using Next.js, PlanetScale, Netlify Identity and Netlify Functions where a user can sign up, log in and where we can get, post and delete data from our database.
TL;DR
The finished result will be a very simple site using Next.js, PlanetScale, Netlify Identity and Netlify Functions where a user can sign up, log in and where we can get, post and delete data from our database. For a quick talk through some of the details, you can dip in to this short video.
Or if you want to dive right in to explore the repository and deploy a demo, click the deploy To Netlify button below (After you’ve clicked that button, make sure to follow the steps to enable PlanetScale and Netlify Identity down below, and it should work!).
Prerequisites
These are the things you’ll need to set up before starting this tutorial:
Getting our site ready
Done exploring the example site and ready to explore and understand the steps to build this? Let’s go! Let's create a basic Next.js site and deploy it to Netlify, in your terminal run:
npx create-next-app
Follow the default options, we decided to use the src directory. Then, let's deploy it to Netlify. Make sure to have the Netlify CLI installed and to log in using the command netlify login. Then, run cd my-next-app and commit and push your repository to a service like GitHub. After this run netlify init and follow the prompts. When you're done, run netlify open to open your site's settings in your Netlify account.
Enable PlanetScale
The database in your PlanetScale account should have the following table:
CREATE TABLE `issues` (
`id` int NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`assignee_name` varchar(255) NOT NULL,
`assignee_email` varchar(255),
`status` enum('to-do', 'in progress', 'done') DEFAULT 'to-do',
PRIMARY KEY (`id`)
);
Netlify integrations provide a convenient way to authenticate with third-party tools and services so that developers in your organisation can easily make use of them.
In your Netlify site view, navigate to Integrations and under the Database category enable PlanetScale. On the next page, click 'connect'. You'll be redirected to PlanetScale where you can authorise Netlify to access your database. Once you've done that, you'll be redirected back to Netlify and you can select your organisation, database and configure which PlanetScale branch should be used in what environments for your Netlify site.
Enable Netlify Identity
The composable architecture we’re using here affords us the option to use a variety of different Identity providers, but for this example we’ll use Netlify Identity. In your Netlify Site Configuration, go to the Identity tab and enable Identity. You won't be using any external providers for this example so you don’t need to configure those.
Configuring your Next.js app
Now that we’ve set up PlanetScale and Netlify Identity, we’re ready to install some of our dependencies:
npm install netlify-identity-widget @types/netlify-identity-widget @netlify/functions @netlify/planetscale
Configure Netlify Identity
We'll be using React Context to make the Netlify Identity instance available to all components in our app. Create a new file src/context/authContext.tsx and add the following contents:
// src/context/authContext.tsx
import netlifyIdentity, { type User } from "netlify-identity-widget";
import { createContext, useEffect, useState } from "react";
declare global {
interface Window {
netlifyIdentity: any;
}
}
interface NetlifyAuth {
isAuthenticated: boolean;
user: User | null;
initialize(callback: (user: User | null) => void): void;
authenticate(callback: (user: User) => void): void;
signout(callback: () => void): void;
}
const netlifyAuth: NetlifyAuth = {
isAuthenticated: false,
user: null,
initialize(callback) {
window.netlifyIdentity = netlifyIdentity;
netlifyIdentity.on("init", (user: User | null) => {
callback(user);
});
netlifyIdentity.init();
},
authenticate(callback) {
this.isAuthenticated = true;
netlifyIdentity.open();
netlifyIdentity.on("login", (user) => {
this.user = user;
callback(user);
netlifyIdentity.close();
});
},
signout(callback) {
this.isAuthenticated = false;
netlifyIdentity.logout();
netlifyIdentity.on("logout", () => {
this.user = null;
callback();
});
},
};
This will create a netlifyAuth object that we can use to interact with Netlify Identity. Now let's create AuthContext and implement the netlifyAuth object.
// src/context/authContext.tsx
interface AuthContextType {
user: User | null;
login: () => void;
logout: () => void;
loading: boolean;
deleteAccount?: () => void;
}
export const AuthContext = createContext<AuthContextType>({
user: null,
login: () => {},
logout: () => {},
loading: false,
deleteAccount: () => {},
});
And now we'll create the provider:
// src/context/authContext.tsx
export const AuthContextProvider = ({ children }: { children: React.ReactNode }) => {
const [loggedIn, setLoggedIn] = useState<boolean>(netlifyAuth.isAuthenticated);
const [user, setUser] = useState<User | null>(null);
const [loading, setLoading] = useState<boolean>(true);
const login = () => {
netlifyAuth.authenticate((user) => {
setLoggedIn(!!user);
setUser(user);
});
};
const logout = () => {
netlifyAuth.signout(() => {
setLoggedIn(false);
setUser(null);
});
};
const deleteAccount = () => {
// TODO
};
useEffect(() => {
netlifyAuth.initialize((user: User | null) => {
setUser(user);
setLoggedIn(!!user);
});
setLoading(false);
}, [loggedIn]);
const contextValues = { user, login, logout, loading, deleteAccount };
return <AuthContext.Provider value={contextValues}>{children}</AuthContext.Provider>;
};
We’ll now be navigating to your app's layout file, in our case it is located at src/app/layout.tsx, we'll wrap the app in the AuthContextProvider:
// src/app/layout.tsx
"use client";
import { AuthContextProvider } from "@/context/authContext";
export default function Layout({ children }: { children: React.ReactNode }) {
return (
<html lang="en">
<body>
<AuthContextProvider>{children}</AuthContextProvider>
</body>
</html>
);
}
From now on, all children of the AuthContextProvider will have access to the AuthContext we created earlier. This means we'll be able to use the different functions from the AuthContext to add login and logout buttons to our app.
// src/app/page.tsx
"use client";
import { AuthContext } from "@/context/authContext";
import { User } from "netlify-identity-widget";
import { useContext, useEffect, useState } from "react";
export default function Home() {
const { user, login, logout, loading } = useContext(AuthContext);
if (loading) return <div>Loading...</div>;
return user ? (
<>
<button onClick={logout}>Log out</button>
</>
) : (
<button onClick={login}>Log in / Register</button>
);
}
Time to try it out! Commit your work and push it.
The site will automatically be redeployed on Netlify. You can jump straight to the deployed site to try it out with netlify open --site but to assist with troubleshooting during development you can also run it locally, complete with access to the integrations with netlify dev. You should be able to log in and log out of the example app now! The first time you register it will send you through the production registration flow. This means that locally you'll have to log in with the credentials you set up on production to see your logged in state. You can also check the Netlify Identity tab in your Netlify Site Configuration view to see the users that have been created.
Adding a form to create database entries
Now that we have Netlify Identity set up, let's add a form to create issues in our PlanetScale database. Add the following code to src/app/page.tsx:
// src/app/page.tsx
"use client";
import { AuthContext } from "@/context/authContext";
import { User } from "netlify-identity-widget";
import { useContext, useEffect, useState } from "react";
export default function Home() {
const { user, login, logout, loading } = useContext(AuthContext);
const [issueTitle, setIssueTitle] = useState("");
const handleInputChange = (event: React.ChangeEvent<HTMLInputElement>) => {
setIssueTitle(event.target.value);
};
const handleSubmit: React.FormEventHandler<HTMLFormElement> = async (event) => {
event.preventDefault();
try {
await fetch(`/.netlify/functions/create`, {
method: "POST",
headers: {
// this will ensure that the Netlify function has access to the user object.
Authorization: `Bearer ${user?.token?.access_token}`,
},
body: JSON.stringify({
title: issueTitle,
}),
});
} catch (error) {
console.error("Error submitting issue:", error);
}
};
if (loading) return <div>Loading...</div>;
return user ? (
<>
{/* Our new form */}
<form onSubmit={handleSubmit}>
<label htmlFor="title">Title:</label>
<input
onChange={handleInputChange}
type="text"
id="title"
name="title"
placeholder="Add an issue"
required
value={issueTitle}
/>
<button type="submit">Submit</button>
</form>
<button onClick={logout}>Log out</button>
</>
) : (
<button onClick={login}>Log in / Register</button>
);
}
We need something to handle and process the POST submissions from our form. Netlify Functions are an ideal way to do this without adding the dependency of a server. We’re actually going to add a number of APIs using serverless functions to support the functionality of our app as we progress.
Let’s start with handling the form submissions and creating a new issue in our database. Create a new file netlify/functions/create.ts and add the following code:
// netlify/functions/create.ts
import connection from "@netlify/planetscale";
import type { Handler, HandlerEvent, HandlerContext } from "@netlify/functions";
const handler: Handler = async function (event: HandlerEvent, context: HandlerContext) {
// We get the user from the context's clientContext
const { user } = context.clientContext as {
identity: { url: string; token: string };
user: { sub: string; email: string; user_metadata: { full_name: string } };
};
if (!event.body) {
return {
statusCode: 400,
body: "Please provide a title for the issue",
};
}
// This checks wether the user is logged in or not
if (!user?.sub || !user?.email) {
return {
statusCode: 401,
body: "Unauthorized",
};
}
const body = JSON.parse(event.body);
// We insert the issue into the PlanetScale database using the user's name and email from the user object
return connection
.execute(
`
INSERT INTO issues (title, assignee_name, assignee_email)
VALUES (?, ?, ?)
`,
[body.title, user.user_metadata.full_name, user.email]
)
.then(() => {
return {
statusCode: 200,
body: JSON.stringify({ message: "Issue created" }),
};
})
.catch((error) => {
return {
statusCode: 500,
body: `Internal Server Error: ${error}`,
};
});
};
export { handler };
It should now be possible to create issues in your PlanetScale database! Try adding one and then checking your PlanetScale database to see if it worked.
Showing a list of entries from your database
Now that we can create issues, let's add a list of issues to our app. We'll create a new serverless function to get all issues from our PlanetScale database. Create a new file netlify/functions/get.ts and add the following code:
// netlify/functions/get.ts
import type { Handler, HandlerEvent, HandlerContext } from "@netlify/functions";
import connection from "@netlify/planetscale";
const handler: Handler = async function (_event: HandlerEvent, context: HandlerContext) {
// We get the user from the context's clientContext
const { user } = context.clientContext as {
identity: { url: string; token: string };
user: { sub: string; email: string };
};
// This checks wether the user is logged in or not
if (!user?.sub || !user?.email) {
return {
statusCode: 401,
body: "Unauthorized",
};
}
// We get all issues from the PlanetScale database
return await connection
.execute(`SELECT * FROM issues`)
.then((issues) => {
const { rows } = issues;
return {
statusCode: 200,
body: JSON.stringify(rows),
};
})
.catch((error) => {
return {
statusCode: 500,
body: `Internal Server Error: ${error}`,
};
});
};
export { handler };
Now inside of src/app/page.tsx we'll add a new useEffect hook to fetch the issues from our PlanetScale database. We'll also add a new state variable to store the issues in. After a new issue is submitted we'll also fetch the issues again to update the page. The code will look like this:
// src/app/page.tsx
"use client";
import { AuthContext } from "@/context/authContext";
import { User } from "netlify-identity-widget";
import { useContext, useEffect, useState } from "react";
interface Issue {
id: string;
title: string;
}
const fetchIssues = async (user: User) => {
const res = await fetch("/.netlify/functions/get", {
headers: {
Authorization: `Bearer ${user?.token?.access_token}`,
},
});
const data = await res.json();
return data;
};
export default function Home() {
const { user, login, logout, loading } = useContext(AuthContext);
const [issues, setIssues] = useState<Issue[]>([]);
const [loadingIssues, setLoadingIssues] = useState<boolean>(true);
const [issueTitle, setIssueTitle] = useState("");
useEffect(() => {
if (user) {
fetchIssues(user).then((issues) => {
setIssues(issues);
setLoadingIssues(false);
});
}
}, [user]);
const handleInputChange = (event: React.ChangeEvent<HTMLInputElement>) => {
setIssueTitle(event.target.value);
};
const handleSubmit: React.FormEventHandler<HTMLFormElement> = async (event) => {
event.preventDefault();
try {
await fetch(`/.netlify/functions/create`, {
method: "POST",
headers: {
// this will ensure that the Netlify function has access to the user object.
Authorization: `Bearer ${user?.token?.access_token}`,
},
body: JSON.stringify({
title: issueTitle,
}),
});
if (user) {
fetchIssues(user).then((issues) => {
setIssues(issues);
});
}
} catch (error) {
console.error("Error submitting issue:", error);
}
};
if (loading || loadingIssues) return <div>Loading...</div>;
return user ? (
<>
{/* Our new form */}
<form onSubmit={handleSubmit}>
<label htmlFor="title">Title:</label>
<input
onChange={handleInputChange}
type="text"
id="title"
name="title"
placeholder="Add an issue"
required
value={issueTitle}
/>
<button type="submit">Submit</button>
</form>
{/* List our issues */}
<ul>
{issues.map((issue) => (
<li key={issue.id}>{issue.title}</li>
))}
</ul>
<button onClick={logout}>Log out</button>
</>
) : (
<button onClick={login}>Log in / Register</button>
);
}
Delete users from Netlify Identity and their entries from PlanetScale
We want to make sure that users can also delete their account and issues. Let's make a new serverless function in netlify/functions/delete.ts. In that file we'll check if the user is currently logged in and authenticated, and then we delete that user Netlify Identity, and delete all of their entries from our PlanetScale database. The code will look like this:
// netlify/functions/delete.ts
import type { Handler, HandlerEvent, HandlerContext } from "@netlify/functions";
import connection from "@netlify/planetscale";
const handler: Handler = async function (_event: HandlerEvent, context: HandlerContext) {
const { identity, user } = context.clientContext as {
identity: { url: string; token: string };
user: { sub: string; email: string };
};
if (!user?.sub || !user?.email) {
return {
statusCode: 401,
body: "Unauthorized",
};
}
const userUrl = `${identity.url}/admin/users/{${user.sub}}`;
const adminAuthHeader = `Bearer ${identity.token}`;
return fetch(userUrl, {
method: "DELETE",
headers: { Authorization: adminAuthHeader },
})
.then(async () => {
console.log("Deleted a user!");
await connection.execute(
`
DELETE FROM issues
WHERE assignee_email = ?
`,
[user.email]
);
})
.then(() => {
console.log("Deleted all issues assigned to the user!");
return { statusCode: 204 };
})
.catch((error) => ({
statusCode: 500,
body: `Internal Server Error: ${error}`,
}));
};
export { handler };
Inside the deleteAccount function in the authContext file, we'll do a call to the function we just created. We'll first ask the user to confirm they want to delete their account, and then we'll call the serverless function. The code will look like this:
// src/context/authContext.tsx
const deleteAccount = () => {
if (window.confirm("Are you sure? This will delete the issues you created and your account. ")) {
fetch("/.netlify/functions/delete", {
method: "DELETE",
headers: {
Authorization: `Bearer ${user?.token?.access_token}`,
},
})
.then(async () => logout())
.catch((err) => console.error(err));
}
};
This function is exported in the AuthContext provider, so you can use it in any children of the AuthContextProvider. For example by providing a button that calls the function.
// src/app/page.tsx
"use client";
import { AuthContext } from "@/context/authContext";
import { User } from "netlify-identity-widget";
import { useContext, useEffect, useState } from "react";
interface Issue {
id: string;
title: string;
}
const fetchIssues = async (user: User) => {
const res = await fetch("/.netlify/functions/get", {
headers: {
Authorization: `Bearer ${user?.token?.access_token}`,
},
});
const data = await res.json();
return data;
};
export default function Home() {
const { user, login, logout, loading, deleteAccount } = useContext(AuthContext);
const [issues, setIssues] = useState<Issue[]>([]); // Provide type for issues state variable
const [loadingIssues, setLoadingIssues] = useState<boolean>(true);
const [issueTitle, setIssueTitle] = useState("");
useEffect(() => {
if (user) {
fetchIssues(user).then((issues) => {
setIssues(issues);
setLoadingIssues(false);
});
}
}, [user]);
const handleInputChange = (event: React.ChangeEvent<HTMLInputElement>) => {
setIssueTitle(event.target.value);
};
const handleSubmit: React.FormEventHandler<HTMLFormElement> = async (event) => {
event.preventDefault();
try {
await fetch(`/.netlify/functions/create`, {
method: "POST",
headers: {
// this will ensure that the Netlify function has access to the user object.
Authorization: `Bearer ${user?.token?.access_token}`,
},
body: JSON.stringify({
title: issueTitle,
}),
});
} catch (error) {
console.error("Error submitting issue:", error);
}
};
if (loading || loadingIssues) return <div>Loading...</div>;
return user ? (
<>
{/* Our new form */}
<form onSubmit={handleSubmit}>
<label htmlFor="title">Title:</label>
<input
onChange={handleInputChange}
type="text"
id="title"
name="title"
placeholder="Add an issue"
required
value={issueTitle}
/>
<button type="submit">Submit</button>
</form>
{/* List our issues */}
<ul>
{issues.map((issue) => (
<li key={issue.id}>{issue.title}</li>
))}
</ul>
<button onClick={logout}>Log out</button>
<button onClick={deleteAccount}>Delete account</button>
</>
) : (
<button onClick={login}>Log in / Register</button>
);
}
The full example
If you want to see the full code of our example, check out the repository here. If you want to deploy the example site to your Netlify account, feel free to click the Deploy to Netlify button down below
After you’ve clicked that button, make sure to follow the steps to enable PlanetScale and Netlify Identity in this article.
What's next?
Congrats! You now have a working example and you are ready to start building your own app! You’ve learned how to use the PlanetScale integration on Netlify and how to implement Netlify Identity. If you want to try out more, here are some ideas:
- Add a form to update issues
- Add a button to delete issues
- Make it possible to change the status of an issue
Resources
- Build your own integrations with Netlify: check out the Netlify SDK!
- The PlanetScale integration
- Learn more about Netlify Serverless Functions
- Learn more about Netlify Identity
- Next.js
Top comments (0)