Tutorials on Databases

Type safety is a critical feature of TypeScript that aids in preventing runtime errors and boosting developer productivity. In this article, I will dive into how tRPC, a TypeScript RPC framework known for its type safety, builds upon this feature. I’ll be discussing the fundamental concepts of TypeScript type safety, the advantages of tRPC, and its role in augmenting type safety in projects as well. My goal is to give readers a comprehensive understanding of how tRPC can enhance development workflows and minimize technical debt. Before delving into tRPC, it's important to understand the foundational concepts of type safety in TypeScript: With a clear understanding of the basics, let's examine how tRPC boosts type safety in TypeScript: By leveraging tRPC's features, developers can achieve an advanced level of type safety in TypeScript projects, lowering the risk of runtime errors and enhancing overall code reliability. To exemplify the practical application of tRPC in TypeScript, let's consider a scenario with a User entity with a type definition shared between the client and server. In this instance, any modifications to the User type or the getUser endpoint will instantaneously reflect on both the client and server, ensuring type safety and minimizing the risk of runtime errors. The tRPC framework is part of a broader stack that includes a variety of modern and mature technologies. Here's a glance at the stack: These technologies collaborate to create a robust and efficient development environment. The stack integrates by leveraging the strengths of each technology: This blend of technologies facilitates seamless integration and efficient development. The stack offers several exclusive benefits that augment type safety and enhance development workflows: These benefits contribute to a more reliable and maintainable codebase. For those interested in delving deeper into the stack and harnessing its capabilities, Kristian Dupont's —which is me— course, " Fullstack TypeScript with TailwindCSS and tRPC Using Modern Features of PostgreSQL ," is a valuable resource. The course covers various aspects, including: This course offers a hands-on and comprehensive guide to constructing robust applications with the stack. I am the author of the course, an experienced full-stack developer with over 25 years in the field. I’ve worked on various projects, from embedded C code for tiny processors to expansive SaaS products. My deep understanding of the software industry and practical expertise make me an ideal guide for learners. The course emphasizes on building a simple booking system, demonstrating the stack's capabilities. It covers various aspects, such as: By working on a real-world project, learners gain hands-on experience and insights into building production-ready applications. TypeScript's type safety is a formidable feature that enhances code reliability and developer productivity. By integrating tRPC into TypeScript projects, developers can further enhance type safety and streamline development workflows. The tRPC framework provides automatic type inference, eliminates code duplication, and improves developer experience. When combined with the stack of modern technologies, tRPC empowers developers to build robust and efficient applications. For those interested in delving deeper into the stack and learning how to harness its capabilities, Kristian Dupont's course, " Fullstack TypeScript with TailwindCSS and tRPC Using Modern Features of PostgreSQL, " is a valuable resource.

This article will dive into a development stack that is gaining traction due to its robustness and adaptability. We'll explore what sets this stack apart from well-established stacks like MEAN and MERN, and why developers should consider its adoption. The cutting-edge stack we're exploring comprises several technologies that, although not entirely new, are combined uniquely to boost development efficiency and code quality. This modern stack includes: This stack facilitates enhanced type safety, seamless management of monorepo structures, shared configurations across packages, and a streamlined frontend setup with Vite, React, and Tailwind. Moreover, this stack enables database migration with raw SQL and access via knex.js , using tRPC as the API layer and Koa as the backend framework. Although this modern stack shares some technologies with MEAN and MERN, it distinguishes itself with the inclusion of TypeScript , TailwindCSS , Koa , Knex , and tRPC . These additions bolster type safety with TypeScript, reduce plumbing code requirement with tRPC, and optimize the use of PostgreSQL. The components of this stack are interchangeable, providing developers with the freedom to substitute any part of the stack with alternatives that better cater to their needs. This adaptability and interchangeability lead to a slightly more complex setup process than MEAN and MERN, but the trade-off is justifiable for the resulting control and flexibility it offers. This advanced stack is suitable for developers who: The strength of this stack lies in its capability to build type-safe full-stack applications, establish robust and scalable application architectures in a monorepo, and use the database as the underlying source of truth. It allows for database migration using raw SQL and capitalizes on all the sophisticated features that PostgreSQL offers. The modern stack, Fullstack TypeScript with TailwindCSS and tRPC Using PostgreSQL , presents a distinctive mix of technologies that boost development efficiency, code quality, and type safety. It may demand more setup effort than traditional stacks, but the resultant flexibility and control over the components make it a formidable contender for your next project. For an extensive exploration of this stack, consider Kristian Dupont’s course Fullstack TypeScript with TailwindCSS and tRPC Using Modern Features of PostgreSQL . The course provides a detailed understanding of the stack and its benefits, making it a precious resource for developers keen on exploring this modern stack.

Are you ready to dive into the world of powerful database management with PostgreSQL and NodeJS? This guide is designed for beginners who want to understand how to use PostgreSQL in their NodeJS projects. Whether you're new to databases or looking to expand your skills, this tutorial will help you get started with confidence. In this guide, we'll cover: By the end of this tutorial, you'll have a solid foundation for building applications with PostgreSQL and NodeJS. For a more visual representation and in-depth details, you may refer to this video tutorial by me, Kristian Dupont, on \newline’s YouTube channel. Before we begin, you'll need to have PostgreSQL installed on your system. Here are two ways to do it: If you prefer using Docker, you can run PostgreSQL in a container with this one-liner: This command will: Now that we have PostgreSQL installed, let's set up our NodeJS project and connect to the database. Let's create a simple Koa server that we'll use to handle our API requests. Create a new file named server.mjs : Now, let's implement three endpoints for basic CRUD operations on a users table. First, let's create our users table. Run this SQL command in your PostgreSQL client: Now, let's add our routes to server.mjs : These routes allow you to: At this point, you should be able to start your server with the following command line: It should print that the server is running on port 3000. You can try it out by opening this route in a browser: http://localhost:3000/users — which should give you an empty array. Use a tool like cUrl or Postman to try out the POST route and see that you can create users in your database. The guide above forms part of a broader context, which includes working with a monorepo , setting up npm workspaces in monorepos , sharing configurations across packages, setting up the frontend with Vite , React , and Tailwind , and more. The full stack comprises the following technologies: This development stack provides end-to-end type safety, a greater confidence level when refactoring, and the ability to work with the database using plain SQL while still maintaining a type-safe architecture. Mastering PostgreSQL doesn't have to be intimidating. With this guide as a base, you can embark on your journey towards advanced database management using a full stack of contemporary technologies. To enhance your skills further, check out my course Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL . Happy learning!

If you are using PostgreSQL for storing data of multiple users, you might want to apply row-level security, or RLS. It’s good practice even if you are manually writing all the queries you send to your database but it’s especially important if you have any type of LLM or similar generating queries for you! Let’s create a trivial data model. Users and items, whatever that might be. Each item belongs to a user. Now, per default, if you ask the database about any users items, it will just tell you. By introducing RLS, you can limit what the responses will be to add a layer of protection. Even if you should create a buggy query, you will not accidentally get the items belonging to someone else, just like you cannot accidentally change or delete items belonging to someone else. We do that like this: Note that app.curren user id isn’t something PostgreSQL knows about per default, it’s a variable that we have introduced. Now basically, we need to execute SET app.curren user id = '(user_id)' , before making some user-specific query. Or if you have a transaction, you could use SET LOCAL which will make the transaction scope function as “auth scope” as well. Depending on your setup, there are going to be different ways to set the app.curren user id setting. In this example, I will assume a setup based on Express or similar, and Knex for creating queries, but the pattern can easily be changed to fit whatever your setup requires. The app.curren user id needs to be set only once per session. This session variable is local to the database connection, meaning each connection has its own set of session variables. When using Knex or similar to execute a query, it acquires a connection from the pool. The session variables set in that connection are isolated from other connections. Now, with Express and similar frameworks, you will typically use the context variable to store state that is related to a given request. You might already have some code in middleware that checks a token and fetches the corresponding user from the database. What we could do is to put a knex instance into this context, and make sure that instance has set the user id. However, this forces us to do quite a bit of “prop drilling” as we need to pass this instance around as a parameter to any function that needs to access the database. What we can do instead is to use a class called AsyncLocalStorage which allows us to store state related to a specific async context. This is similar to “thread-local storage” in other languages, only we don’t have threads in NodeJS but async contexts. Apply the dbMiddleware to your server whereever you set up other middleware, and now you can use the getKnexInstance to get the authenticated database connection. Here is an example of a very simple server: As you can see, this endpoint selects every items row. I don’t recommend this, you should still create your queries as you normally do, but even if you released this, no items belonging to other users would leak anywhere. Mastering Row Level Security in NodeJS doesn't have to be intimidating. With this guide as a base, you can embark on your journey toward something bigger. To enhance your skills further, check out my course Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL . Happy learning!