TypeScript and the Database: Who Owns the Types?

Rate this content
Bookmark

We all love writing types in TypeScript, but we often find ourselves having to write types in another language as well: SQL. This talk will present the choose-your-own-adventure story that you face when combining TypeScript and SQL and will walk you through the tradeoffs between the various options. Combined poorly, TypeScript and SQL can be duplicative and a source of headaches, but done well they can complement one another by addressing each other's weaknesses.

This talk has been presented at TypeScript Congress 2022, check out the latest edition of this JavaScript Conference.

FAQ

The app's database includes information about books, authors, and book reviews. It stores details like book ID, title, publication year, and content, which can be null if the publication year is unknown.

The app connects to the database, runs SQL queries to fetch data, and renders the results. It uses TypeScript to define interfaces for the data structure, ensuring type safety and accuracy in data handling.

ORM stands for Object Relational Mapper. In the app, TypeORM is used as an ORM to map database tables to JavaScript objects. This allows the app to interact with the database using high-level entity objects instead of raw SQL queries.

Issues can arise if the database schema and TypeScript types are not in sync, such as when a database column becomes nullable but the change is not reflected in TypeScript, leading to potential type errors.

The app can use ORMs or schema generators to synchronize changes. For example, using TypeORM or a schema generator like pg2ts ensures that any changes in the database schema are accurately reflected in TypeScript types.

Migrations in the app are used to apply changes to the database schema, such as altering columns or making them nullable. These migrations help maintain the database structure and ensure it aligns with the application's requirements.

Using a query builder like connects JS provides type safety for database queries, ensuring that the queries match the database schema. It simplifies writing SQL queries by allowing developers to use a more abstract syntax that integrates with TypeScript.

Tools like PGTyped read SQL queries and automatically generate TypeScript types for them. This process helps maintain type accuracy across the application by ensuring that data fetched from the database matches the expected types in TypeScript.

Using an ORM simplifies database interactions and ensures type safety but can obscure the underlying SQL and affect performance. Raw SQL gives more control and may be more efficient but requires careful management of types and database synchronization.

For complex queries, it's suggested to write raw SQL to ensure optimal performance and control. Tools like PGTyped can then be used to generate accurate TypeScript types for these queries, combining flexibility with type safety.

Dan Vanderkam
Dan Vanderkam
27 min
29 Apr, 2022

Comments

Sign in or register to post your comment.

Video Summary and Transcription

The Talk discusses the use of TypeScript and SQL together in software development. It explores different approaches, such as using an ORM like TypeORM or a schema generator like pg2ts. Query builders like connects JS and tools like PGTyped are also discussed. The benefits and trade-offs of using TypeScript and SQL are highlighted, emphasizing the importance of finding a middle ground approach.

1. Working with a Database and Types

Short description:

I'm working on a new app with a database for books, authors, and reviews. The web server connects to the database, runs a query, and renders all the books. I encountered a bug and discovered the need for types. By defining a book interface and specifying the structure of the table, I resolved the type errors. Running a migration allowed me to handle the possibility of null values for the publication year.

So I'm working on this cool new app, so it has a database where I have books and their authors and book reviews. I've even got some data I've put in there, so I've got some of my favorite books. And I've got a cool web server here, so it connects to the database, it runs a query, and it renders all the books. So yeah, I think it's pretty good. Let's take a look.

So I think, seems like it maybe has a bug. So let's see what's going on here. Let's see. So I guess, what is the type of books? It's Any, it's query result Any, which means that book has an Any type. So maybe I should write, maybe I should use types to solve this problem, because this is TS Congress after all, so we can define a book interface. And let's look at the structure of this table. So we've got an ID, which is a string, and we've got created by, which is a string. And we've got title, which is a string, and we've got publication year. We're just gonna copy that. For the JavaScript people here, integers are a special kind of number. So let's just go number. And last but not least, we have contents, which is a string, or I think it could be null. And with the node-progress library, I believe you can specify a return type for a query. And hey, lo and behold, we have some type errors. So yeah, I think I just misspelled year. It should be publication year. So let's fix that. And now if we head over here, hey, what do you know? Pretty good. So types can definitely be useful when you're working with SQL. But it's not the full story.

So let's see what happens when you run a migration. So I realized that the publication year could actually be null if we don't know when it's published. So for example, who knows exactly when the Iliad and the Odyssey were published? So better just leave it as null. So let's run that. And we can confirm that.

2. Handling Database Schema Changes

Short description:

We encountered an issue when our data schema in the database changed, but the type we manually wrote didn't update accordingly. By making the publication year nullable, we resolved the error. TypeScript provided an error message when we attempted to subtract null, allowing us to fix the issue by adding a conditional.

Yep, we have our new books and publication year is null. So yeah, let's see what happens. Yep, null. And fun fact, in JavaScript 2022 minus null is in fact 22 because JavaScript. So the problem here is that we changed our data schema in the database, but this type that we wrote by hand didn't update to reflect that change. So really, publication year now is nullable. So it should be number or null. And once we make that change, then TypeScript gives us an error, right? So yeah, TypeScript is nice enough to say that you can't subtract null. And so I think here I have the fix here. We just add a conditional. Yeah, great. And let's see if that fixes things. Sure enough, it does. So once again, types can be very helpful, but there's also some potential problems here.

Check out more articles and videos

We constantly think of articles and videos that might spark Git people interest / skill us up or help building a stellar career

React's Most Useful Types
React Day Berlin 2023React Day Berlin 2023
21 min
React's Most Useful Types
Top Content
Watch video: React's Most Useful Types
Today's Talk focuses on React's best types and JSX. It covers the types of JSX and React components, including React.fc and React.reactnode. The discussion also explores JSX intrinsic elements and react.component props, highlighting their differences and use cases. The Talk concludes with insights on using React.componentType and passing components, as well as utilizing the react.element ref type for external libraries like React-Select.
TypeScript and React: Secrets of a Happy Marriage
React Advanced Conference 2022React Advanced Conference 2022
21 min
TypeScript and React: Secrets of a Happy Marriage
Top Content
React and TypeScript have a strong relationship, with TypeScript offering benefits like better type checking and contract enforcement. Failing early and failing hard is important in software development to catch errors and debug effectively. TypeScript provides early detection of errors and ensures data accuracy in components and hooks. It offers superior type safety but can become complex as the codebase grows. Using union types in props can resolve errors and address dependencies. Dynamic communication and type contracts can be achieved through generics. Understanding React's built-in types and hooks like useState and useRef is crucial for leveraging their functionality.
Making Magic: Building a TypeScript-First Framework
TypeScript Congress 2023TypeScript Congress 2023
31 min
Making Magic: Building a TypeScript-First Framework
Top Content
Daniel Rowe discusses building a TypeScript-first framework at TypeScript Congress and shares his involvement in various projects. Nuxt is a progressive framework built on Vue.js, aiming to reduce friction and distraction for developers. It leverages TypeScript for inference and aims to be the source of truth for projects. Nuxt provides type safety and extensibility through integration with TypeScript. Migrating to TypeScript offers long-term maintenance benefits and can uncover hidden bugs. Nuxt focuses on improving existing tools and finds inspiration in frameworks like TRPC.
Stop Writing Your Routes
Vue.js London 2023Vue.js London 2023
30 min
Stop Writing Your Routes
Designing APIs is a challenge, and it's important to consider the language used and different versions of the API. API ergonomics focus on ease of use and trade-offs. Routing is a misunderstood aspect of API design, and file-based routing can simplify it. Unplugging View Router provides typed routes and eliminates the need to pass routes when creating the router. Data loading and handling can be improved with data loaders and predictable routes. Handling protected routes and index and ID files are also discussed.
Faster TypeScript builds with --isolatedDeclarations
TypeScript Congress 2023TypeScript Congress 2023
24 min
Faster TypeScript builds with --isolatedDeclarations
Top Content
This talk discusses the performance issues in TypeScript builds and introduces a new feature called isolated declarations. By running the compiler in parallel and using isolated modules, significant performance gains can be achieved. Isolated declarations improve build speed, compatibility with other tools, and require developers to write types in code. This feature has the potential to further increase performance and may be available in TypeScript soon.
Full-stack & typesafe React (+Native) apps with tRPC.io
React Advanced Conference 2021React Advanced Conference 2021
6 min
Full-stack & typesafe React (+Native) apps with tRPC.io
Top Content
Alex introduces tRPC, a toolkit for making end-to-end type-safe APIs easily, with auto-completion of API endpoints and inferred data from backend to frontend. tRPC works the same way in React Native and can be adopted incrementally. The example showcases backend communication with a database using queries and validators, with types inferred to the frontend and data retrieval done using Prisma ORM.

Workshops on related topic

React, TypeScript, and TDD
React Advanced Conference 2021React Advanced Conference 2021
174 min
React, TypeScript, and TDD
Top Content
Featured WorkshopFree
Paul Everitt
Paul Everitt
ReactJS is wildly popular and thus wildly supported. TypeScript is increasingly popular, and thus increasingly supported.

The two together? Not as much. Given that they both change quickly, it's hard to find accurate learning materials.

React+TypeScript, with JetBrains IDEs? That three-part combination is the topic of this series. We'll show a little about a lot. Meaning, the key steps to getting productive, in the IDE, for React projects using TypeScript. Along the way we'll show test-driven development and emphasize tips-and-tricks in the IDE.
Mastering advanced concepts in TypeScript
React Summit US 2023React Summit US 2023
132 min
Mastering advanced concepts in TypeScript
Top Content
Featured WorkshopFree
Jiri Lojda
Jiri Lojda
TypeScript is not just types and interfaces. Join this workshop to master more advanced features of TypeScript that will make your code bullet-proof. We will cover conditional types and infer notation, template strings and how to map over union types and object/array properties. Each topic will be demonstrated on a sample application that was written with basic types or no types at all and we will together improve the code so you get more familiar with each feature and can bring this new knowledge directly into your projects.
You will learn:- - What are conditional types and infer notation- What are template strings- How to map over union types and object/array properties.
Deep TypeScript Tips & Tricks
Node Congress 2024Node Congress 2024
83 min
Deep TypeScript Tips & Tricks
Top Content
Featured Workshop
Josh Goldberg
Josh Goldberg
TypeScript has a powerful type system with all sorts of fancy features for representing wild and wacky JavaScript states. But the syntax to do so isn't always straightforward, and the error messages aren't always precise in telling you what's wrong. Let's dive into how many of TypeScript's more powerful features really work, what kinds of real-world problems they solve, and how to wrestle the type system into submission so you can write truly excellent TypeScript code.
Best Practices and Advanced TypeScript Tips for React Developers
React Advanced Conference 2022React Advanced Conference 2022
148 min
Best Practices and Advanced TypeScript Tips for React Developers
Top Content
Featured Workshop
Maurice de Beijer
Maurice de Beijer
Are you a React developer trying to get the most benefits from TypeScript? Then this is the workshop for you.In this interactive workshop, we will start at the basics and examine the pros and cons of different ways you can declare React components using TypeScript. After that we will move to more advanced concepts where we will go beyond the strict setting of TypeScript. You will learn when to use types like any, unknown and never. We will explore the use of type predicates, guards and exhaustive checking. You will learn about the built-in mapped types as well as how to create your own new type map utilities. And we will start programming in the TypeScript type system using conditional types and type inferring.
How to Solve Real-World Problems with Remix
Remix Conf Europe 2022Remix Conf Europe 2022
195 min
How to Solve Real-World Problems with Remix
Featured Workshop
Michael Carter
Michael Carter
- Errors? How to render and log your server and client errorsa - When to return errors vs throwb - Setup logging service like Sentry, LogRocket, and Bugsnag- Forms? How to validate and handle multi-page formsa - Use zod to validate form data in your actionb - Step through multi-page forms without losing data- Stuck? How to patch bugs or missing features in Remix so you can move ona - Use patch-package to quickly fix your Remix installb - Show tool for managing multiple patches and cherry-pick open PRs- Users? How to handle multi-tenant apps with Prismaa - Determine tenant by host or by userb - Multiple database or single database/multiple schemasc - Ensures tenant data always separate from others
Building Your Own Custom Type System
React Summit 2024React Summit 2024
38 min
Building Your Own Custom Type System
Featured Workshop
Kunal Dubey
Kunal Dubey
I'll introduce the audience to a concept where they can have end-to-end type systems that helps ensure typesafety across the teams Such a system not only improves communication between teams but also helps teams collaborate effectively and ship way faster than they used to before. By having a custom type system, teams can also identify the errors and modify the API contracts on their IDE, which contributes to a better Developer Experience. The workshop would primarily leverage TS to showcase the concept and use tools like OpenAPI to generate the typesystem on the client side.