MySQL & Firebase: Build a Serverless Cloud Database with Node.js & TypeORM

Introduction

Firebase offers a robust and scalable NoSQL database solution with Firestore. However, sometimes the best tool for the job is a relational database like MySQL, especially when dealing with legacy systems or complex data relationships. This blog post explores how to integrate MySQL with Firebase using Cloud Functions, Google Cloud SQL, and TypeORM, creating a serverless and efficient database solution.

Why MySQL with Firebase?

Firestore is Firebase's default database and a great choice for many applications. It's fast, scales automatically, and handles relational data modeling. However, there are scenarios where MySQL shines. Perhaps you're working with an existing MySQL database, or your data model benefits from the structure and querying capabilities of SQL. Regardless of the reason, integrating MySQL with Firebase is easier than you might think.

Provisioning Google Cloud SQL

The first step is setting up a MySQL instance in Google Cloud SQL. While not a free service (approximately $7/month for the smallest instance), it offers a cost-effective and fully managed MySQL solution. Here's how to get started:

1. Navigate to the SQL tab in the Google Cloud Platform console.
2. Select MySQL as the database engine.
3. Create a root user password.
4. Copy the instance connection name; you'll need it later.

Remember that a Cloud SQL instance can host multiple databases. For a production application, it's recommended to have separate databases for development and production environments. Create these databases within the SQL instance. Once the instance is created you can use it with local development tools by using the cloud SQL proxy. Download the cloud SQL proxy and run the following command:

./cloud_sql_proxy -instances=[YOUR_INSTANCE_CONNECTION_NAME]=tcp:3306
    

TypeORM: TypeScript-Powered Object-Relational Mapping

TypeORM simplifies database interactions by providing object-relational mapping (ORM) with TypeScript. It allows you to define database tables as TypeScript classes (entities) and perform queries without writing raw SQL. To get started you need to install TypeORM, Reflect Metadata, and MySQL client:

npm install typeorm reflect-metadata mysql
    

Here is some code to consider when setting up tsconfig.json:

{
    "compilerOptions": {
        "experimentalDecorators": true,
        "emitDecoratorMetadata": true,
        "strict": false,
    }
}
    

To ensure proper database connections during development and production, you can implement logic within your `config.ts` file. This file imports `reflect-metadata` and then connects to your database. It is possible to switch between development and production based on the `NODE_ENV` variable. Here's a basic example of how to configure TypeORM based on this setup:

    import "reflect-metadata";
    import { createConnection, ConnectionOptions, getConnection } from "typeorm";

    const isProduction = process.env.NODE_ENV === 'production';

    const connectionOptions: ConnectionOptions = {
      type: "mysql",
      host: "localhost", // Or your development host
      port: 3306,
      username: "root",
      password: "your_root_password",
      database: "development_db",
      synchronize: !isProduction, // Only synchronize in development
      logging: !isProduction, // Log queries in development
      entities: [__dirname + "/entity/*.js"], // Path to your entities
      migrations: [__dirname + "/migration/*.js"], // Path to your migrations
      subscribers: [__dirname + "/subscriber/*.js"], // Path to your subscribers
      cli: {
        entitiesDir: "entity",
        migrationsDir: "migration",
        subscribersDir: "subscriber",
      },
    };

    let connection;

    export const connect = async () => {
      try {
        connection = getConnection();
      } catch (e) {
        connection = await createConnection(connectionOptions);
      }
      return connection;
    };
    

With this you can model your tables using entities:

    import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

    @Entity()
    export class Hippo {

        @PrimaryGeneratedColumn()
        id: number;

        @Column()
        name: string;

        @Column()
        weight: number;

    }
    

And with that entity, you can perform standard CRUD operations.

    import * as functions from 'firebase-functions';
    import { connect, Hippo } from './config';

    export const getHippos = functions.https.onRequest(async (request, response) => {
      try {
        const connection = await connect();
        const hippos = await connection.getRepository(Hippo).find();
        response.json(hippos);
      } catch (error) {
        console.error(error);
        response.status(500).send('An error occurred');
      }
    });
    

Conclusion

Combining MySQL with Firebase using Cloud Functions and TypeORM offers a powerful and flexible approach to building serverless applications. While Firestore remains an excellent NoSQL option, integrating MySQL provides the benefits of a relational database when needed, especially when working with legacy databases or complex data relationships. TypeORM further streamlines development by enabling efficient data modeling and querying with TypeScript.

Keywords: Firebase, MySQL, Cloud Functions, TypeORM, Serverless Database