Using TypeScript with MySQL: A Guide for Type-Safe, Scalable Backend Development

In today’s development landscape, combining TypeScript’s type safety with MySQL’s powerful database capabilities can create an optimal environment for building secure and scalable backend applications. TypeScript offers all the benefits of JavaScript but with strong typing, which helps reduce bugs and improve code readability. MySQL, on the other hand, is one of the most popular relational databases, providing reliable storage and query capabilities for large data sets.

---

Why Use TypeScript with MySQL?

Using TypeScript with MySQL brings a range of advantages:

1. Enhanced Type Safety: TypeScript’s static typing allows for better error detection during development, reducing runtime errors and making code maintenance easier.
2. Scalability: TypeScript’s strict typing and modularity improve project scalability, making it suitable for large applications with complex data interactions.
3. Improved Code Readability: Strong typing leads to self-documenting code, which helps new developers onboard quickly and understand existing code.
4. Intelligent Autocompletion and Refactoring: TypeScript’s type inference improves the developer experience by making code autocompletion and refactoring more accurate.

---

Setting Up TypeScript and MySQL

Let’s walk through the setup process, starting with the basics.

Step 1: Initialize the Project

Begin by setting up a new Node.js project and installing TypeScript:

mkdir ts-mysql-project
cd ts-mysql-project
npm init -y
npm install typescript --save-dev
npx tsc --init

This initializes a TypeScript configuration file (tsconfig.json), where you can adjust compiler settings as needed.

Step 2: Install MySQL and TypeScript Dependencies

Next, you’ll need to install the mysql2 package to connect with the MySQL database. Additionally, install @types/mysql to provide TypeScript definitions for MySQL:

npm install mysql2
npm install --save-dev @types/mysql2

Step 3: Create a Database Connection

Now let’s set up a database connection in TypeScript. First, create a file called db.ts:

import mysql from 'mysql2';

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'yourusername',
  password: 'yourpassword',
  database: 'yourdatabase',
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL database!');
});

export default connection;

Here, we’re using mysql2’s createConnection method to establish a connection with the MySQL database. Replace the placeholders with your MySQL credentials.

Step 4: Write Type-Safe Database Queries

With TypeScript, we can define types to represent our database tables, making our queries type-safe and reducing errors. Let’s say we have a users table with columns id, name, and email. We can create an interface to represent a User:

interface User {
  id: number;
  name: string;
  email: string;
}

Now, let’s write a function to retrieve users from the database:

import connection from './db';

function getUsers(): Promise<User[]> {
  return new Promise((resolve, reject) => {
    connection.query('SELECT * FROM users', (err, results) => {
      if (err) return reject(err);
      resolve(results);
    });
  });
}

This function returns a Promise<User[]>, which guarantees that the returned data will match the structure defined in the User interface.

---

Using an ORM: Sequelize with TypeScript and MySQL

While writing raw SQL queries works well for small projects, ORMs (Object-Relational Mappers) can simplify interactions with the database in larger applications. One popular ORM for Node.js is Sequelize, which has excellent support for both TypeScript and MySQL.

Step 1: Install Sequelize and Required Packages

To start using Sequelize, install the necessary packages:

npm install sequelize sequelize-typescript mysql2
npm install --save-dev @types/sequelize

Step 2: Set Up Sequelize with TypeScript

Set up a sequelize.ts file for configuring Sequelize:

import { Sequelize } from 'sequelize-typescript';

const sequelize = new Sequelize({
  dialect: 'mysql',
  host: 'localhost',
  username: 'yourusername',
  password: 'yourpassword',
  database: 'yourdatabase',
  models: [__dirname + '/models'], // Path to your models
});

export default sequelize;

With Sequelize, you can define models that map to database tables. Create a User model that represents the users table:

import { Table, Column, Model, DataType } from 'sequelize-typescript';

@Table({ tableName: 'users' })
export class User extends Model<User> {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id!: number;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  name!: string;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  email!: string;
}

This User model uses decorators to specify database column properties, making code more readable and ensuring strong typing.

Step 3: Run Database Queries

With Sequelize, querying the database becomes simpler and type-safe:

import sequelize from './sequelize';
import { User } from './models/User';

async function fetchUsers() {
  await sequelize.sync(); // Synchronize models with database
  const users = await User.findAll();
  console.log(users);
}

Sequelize’s ORM approach enables complex queries, relationships, and migrations while maintaining TypeScript’s type safety.

---

Conclusion

By using TypeScript with MySQL, you gain all the advantages of type safety, scalability, and maintainable code. Whether you’re working with raw SQL queries or using an ORM like Sequelize, TypeScript ensures that your database operations are robust and less prone to errors. This combination is ideal for developers building large applications that require structured data handling, and it improves both the developer experience and the overall reliability of the codebase.

Consider giving TypeScript and MySQL a try for your next backend project and experience the productivity gains firsthand! Happy coding!

---

Further Reading

• TypeScript Documentation
• MySQL Official Documentation
• Sequelize Documentation

Comments

[sndp]

Thank you. Great Article!

I have one question. Let's say if the backend was implemented using different
language runtime let's say golang.
Then is it possible to use the database types on the node typescript frontend
using an ORM?

[Tina Huynh]

Yeah, it is totally possible to use the same database types on a Node.js TypeScript frontend as you would with a backend implemented in a different language runtime like Go! If you set up your database schema the same way in your Go backend and TypeScript frontend, and use an ORM in your Node.js app, you’ll keep things nice and type-safe. This makes development smoother and helps you maintain your app better since any changes to the database can easily be updated in both the frontend and backend. It’s a win-win!

You can define your database models (tables and their columns) in a format that can be shared between your Go backend and your Node.js frontend. This can be done using TypeScript interfaces or classes in the frontend and equivalent structs in Go on the backend.

There are several ORM libraries available for Node.js that can work with various databases. Examples include:

• Sequelize: A promise-based Node.js ORM for PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server.
• TypeORM: An ORM for TypeScript and JavaScript that supports many SQL databases and also MongoDB.
• Objection.js: A SQL-friendly ORM for Node.js, built on top of Knex.js.

These ORMs allow you to define your models in a way that reflects the structure of your database.

You can use tools that generate TypeScript interfaces/types based on your database schema. This can help ensure that your frontend is in sync with your backend models.

• TypeORM provides a way to define models in TypeScript and can auto-generate migrations based on your model definitions.
• Schema-to-Code tools can generate TypeScript interfaces from existing database schemas.

You can implement validation in both your backend and frontend to ensure that the data structures conform to the expected formats. Libraries like Joi or Zod can be used in TypeScript to validate data before sending it to the backend.

Use REST APIs or GraphQL to communicate between your frontend and backend. Your Node.js application can make HTTP requests to your Golang backend, and you can ensure that the data formats and types are compatible based on the models you've defined.

Assuming you have a User model in both your Go backend and your TypeScript frontend:

Go Backend:

type User struct {
    ID        int       `json:"id"`
    Email     string    `json:"email"`
    Password  string    `json:"password"`
    CreatedAt time.Time `json:"created_at"`
}

TypeScript Frontend:

export interface IUser {
    id: number;
    email: string;
    password: string;
    createdAt: Date;
}

Now, to make sure the data you're working with is valid, you can use libraries like Joi and Zod for validation. Here’s how you might set that up:

Using Joi:

import Joi from 'joi';

const userSchema = Joi.object({
    id: Joi.number().required(),
    email: Joi.string().email().required(),
    password: Joi.string().min(6).required(),
    createdAt: Joi.date().iso().required()
});

// Example of validating a user object
const userInput = {
    id: 1,
    email: "[e-mail]",
    password: "securepassword",
    createdAt: new Date().toISOString()
};

const { error } = userSchema.validate(userInput);
if (error) {
    console.error("Validation Error:", error.details);
} else {
    console.log("User input is valid!");
}

Using Zod:

import { z } from 'zod';

const userSchema = z.object({
    id: z.number(),
    email: z.string().email(),
    password: z.string().min(6),
    createdAt: z.string().transform((val) => new Date(val))
});

// Example of validating a user object
const userInput = {
    id: 1,
    email: "[e-mail]",
    password: "securepassword",
    createdAt: new Date().toISOString()
};

try {
    userSchema.parse(userInput);
    console.log("User input is valid!");
} catch (error) {
    console.error("Validation Error:", error.errors);
}

Using these schemas lets you check user input on the frontend before passing it over to your Go backend. Just ensure your API is set up correctly, whether you’re going with REST or GraphQL, and you’re good to go! Keep those types aligned, leverage a reliable ORM, and validate your data properly, and you’ll find the app development process to be a lot smoother.

Hope this helped!!

[sndp]

This is great. Thanks