Dropping User Tables in 2023: The Future of Auth and Database Relationships

User tables have been around for decades, serving as the foundation for user authentication in many applications. However, with the advent of Auth providers and the constant evolution of technology, do we really need User tables in 2023? This blog post explores how you can optimize your authentication process by possibly dropping user tables and utilizing Auth providers like Clerk to handle user authentication.

Traditional User Tables vs Auth Provider

Typically, a traditional application would have a User table built around relationships based on user attributes such as user ID, first name, or email. This allows for retrieving and managing data related to specific users.

Here's an example of a traditional application structure:

(User table)
User ID | First Name | Email

(Post table)
Author ID (connected to User ID in User table) | Content

In this example, you might use NextJS Auth or Roll Your Own Auth for authentication. But if you decide to use an Auth provider like Clerk, you can eliminate the need for a User table altogether.

With an Auth provider, you can use the user ID throughout the application stack, whether on the client, server, or middleware. This allows you to create relationships and retrieve data after sign-up or upon creating a post, for example. In a multiple user scenario, you can use the user IDs to access additional user data from the Auth provider.

However, there are cases when you might need a relationship within your database for more complex scenarios. This is where tenant tables come into play.

Tenant Tables vs User Tables

Tenant tables differ from User tables in terms of the data they store. User tables generally store all the necessary data for building an application, including last signed in, updated at, email verification status, etc.

In contrast, tenant tables store only some essential pieces of information needed for a specific scenario, such as user or organization ID and profile image. These tables are particularly useful for building complex applications.

For most developers, using tenant tables may not be necessary, as the majority of applications do not require such an advanced setup. Auth providers like Clerk can handle most user data management tasks without the need for a separate table.

An Example Application Without a User Table

Chirp, a Twitter-like application developed by Theo, serves as an excellent example of an application that does not use a User table. It only requires user IDs and uses Clerk for handling user authentication and pulling user-related data, like profile images.

Code Explanation

Here's a brief explanation of how Chirp's code works without a User table, specifically focusing on the post creation and retrieval process:

1. Creating a post: Chirp uses the context to grab the user ID provided by Clerk and inserts it into the database using Prisma and create.

app.post(async (req, res) => {
  const content = req.body.content;
  // Ensure the content is an emoji
  if (!isEmoji(content)) {
    return res.status(400).json({ error: "Invalid emoji" });
  }
  const userId = res.locals.context.userId;
  await prisma.post.create({ data: { content, authorId: userId } });
});

1. Retrieving posts: Chirp uses the authorized user ID for retrieving posts and adds user data to the posts by calling Clerk for additional user details.

app.get(async (req, res) => {
  const userId = res.locals.context.userId;
  const posts = await prisma.post.findMany({
    where: { authorId: userId },
    take: 100,
    orderBy: { createdAt: "desc" },
  });
  const postsWithUserData = await addUserDataToPosts(posts);
  res.json(postsWithUserData);
});

Handling Cascade Deletes

When discussing User tables, one common concern is how to handle cascade deleting when using Auth providers. Deleting a user and all related data should work the same whether you are using a User table or an Auth provider like Clerk.

The process of deleting all related data and then the user occurs in two steps:

1. Remove data from the database: Make a request to the database to delete all associated data in tables joined with the user ID, such as posts or subscriptions.
2. Delete the user from the Auth provider: If the data removal is successful, delete the user from Clerk. If the deletion fails, inform the user.

This approach ensures that data is removed from both the database and the Auth provider in a consistent manner, whether or not a User table is used.

Conclusion

So, should we drop User tables in 2023? The answer largely depends on the complexity of your application and the capabilities of your chosen Auth provider. For many applications, relying on an Auth provider like Clerk can replace the need for a User table, simplifying the authentication process and streamlining data management.

Ultimately, it's essential to evaluate your application's requirements and weigh the benefits of using an Auth provider over maintaining a separate User table. If you're unsure, subscribing to a channel that provides useful insights and updates on the topic can be an excellent step forward in making an informed decision.