MySQL on Cloud Functions
Firebase’s NoSQL databases are excellent for flexibility and scale, but there are times when a traditional relational SQL database is a better tool for the job (or you may not have a choice with a legacy system). Does that mean you should ditch the Firebase platform altogether? Of course not! In fact, connecting a Cloud SQL instance to Firebase Cloud Functions is one of the easiest ways to hook up a database to a web or mobile app.
The following lesson will show you how to connect a MySQL database to Firebase and use TypeORM to write efficient, type-safe, readable backend code.
Initial Setup
Start from an empty directory in your editor. Make sure you have the Google Cloud SDK and NodeJS installed on your system.
Initialize Cloud Functions
Initialize Cloud Functions and choose TypeScript as the language.
npm i -g firebase-tools
firebase init functions
Create a Cloud MySQL Instance
Head over the to the Google Cloud Platform console and create a new MySQL instance. You also have the option for Postgres, but the setup is slightly different. Make a note of the instance connection name.
Choose MySQL for this lesson
Cloud SQL instance running on GCP. Copy the instance connection name.
Install the Cloud SQL Proxy for Local Testing
A proxy is required connect to a Cloud SQL database locally. Download the binary for your OS and save it to the root of your project.
Download the Cloud SQL proxy binary
Run the command below with your connection name. You should see the message: Ready for new connections.
./cloud_sql_proxy -instances=YOUR_CONNECTION=tcp:3306
Install TypeORM with Cloud SQL
The final setup step is to install TypeORM and its dependencies in the Cloud Functions environment.
cd functions
npm install reflect-metadata typeorm mysql
Update the default TS config with the following changes for TypeORM.
{
"compilerOptions": {
// ...
"strict": false,
"experimentalDecorators": true,
"emitDecoratorMetadata": true,
},
}
Connect to the MySQL Database
The code in this section connects a development database for local testing and a production database for live data. Create the database instances from the GCP dashboard.
Create two databases, one for development and production
Connecting MySQL with TypeORM is slightly different in the Cloud Functions environment because they are ephemeral compute instances and the connection is not long-lived. In other words, the same database connection may or may not be available between requests.
Connection Options
The connection below is setup specifically for Google Cloud SQL. Make sure to update the username/password and connection name for your instance.
import { ConnectionOptions, Connection, createConnection, getConnection } from 'typeorm';
import 'reflect-metadata';
// Will be true on deployed functions
export const prod = process.env.NODE_ENV === 'production';
export const config: ConnectionOptions = {
name: 'fun',
type: 'mysql',
host: '127.0.0.1',
port: 3306,
username: 'root', // review
password: 'root', // review
database: 'development',
synchronize: true,
logging: false,
entities: [
'lib/entity/**/*.js'
],
// Production Mode
...(prod && {
database: 'production',
logging: false,
// synchronize: false,
extra: {
socketPath: '/cloudsql/YOUR_CONNECTION_NAME' // change
},
})
}
Cloud Functions Connection
The connect function looks for an existing database connection. If available we use it for a slight performance gain, otherwise a new connection is created.
export const connect = async () => {
let connection: Connection;
try {
connection = getConnection(config.name)
console.log(connection)
} catch(err) {
connection = await createConnection(config);
}
return connection;
}
Create an Entity
A TypeORM entity is a class that maps TypeScript code to a database table.
Hippo Entity
import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from "typeorm";
@Entity()
export class Hippo extends BaseEntity {
@PrimaryGeneratedColumn()
id: number;
@Column()
name: string;
@Column()
weight: number;
@Column()
createdAt: Date;
}
Cloud Functions
Write to the Database
The first function creates a new record in the database. When called the function will make a connection to the database, then reference the Hippo repository. The repository is like a reference that exposes methods to read, write, and query a database table. In this example, we save a new record and send the response back to the client.
import * as functions from 'firebase-functions';
import { connect } from './config';
import { Hippo } from './entity/Hippo';
export const createHippo = functions.https.onRequest(async (request, response) => {
const { name, weight } = request.body;
try {
const connection = await connect();
const repo = connection.getRepository(Hippo);
const newHippo = new Hippo();
newHippo.name = name;
newHippo.weight = weight;
const savedHippo = await repo.save(newHippo);
response.send(savedHippo);
} catch (error) {
response.send(error)
}
});
Query the Database
TypeORM provides a large collection of methods to reading and querying data. The function below queries all Hippo entities or rows from the database.
export const getHippos = functions.https.onRequest(async (request, response) => {
const connection = await connect();
const hippoRepo = connection.getRepository(Hippo);
// Get all rows
const allHippos = await hippoRepo.find();
response.send(allHippos);
});
Serve the Cloud Functions
Run the command below to serve the Cloud Functions on localhost.
firebase serve --only functions
I highly recommend using a REST client like Insomnia to make requests to functions efficiently.
Advanced
Relational Data
TypeORM provides a variety of decorators for modeling relational data.
In the code below, a second entity or database table Hat is created. Each hat record is owned by one Hippo, but a hippo can own multiple hats.
import { Hat } from './Hat';
@Entity()
export class Hippo extends BaseEntity {
//...
@PrimaryGeneratedColumn()
id: number;
@OneToMany(type => Hat, hat => hat.owner)
hats: Hat[];
}
The owner field references the primary key or ID of a Hippo entity.
import { Hippo } from './Hippo'
@Entity()
export class Hat extends BaseEntity {
@PrimaryGeneratedColumn()
id: number;
@ManyToOne(type => Hippo, hippo => hippo.hats)
owner: Hippo;
}
The entity relationship allows us to make a JOIN query to the database to query all Hippos, along with all the hats they own.
export const getHippos = functions.https.onRequest(async (request, response) => {
const connection = await connect();
const hippoRepo = connection.getRepository(Hippo);
// JOIN Query
const hipposWearingHats = await hippoRepo
.createQueryBuilder('hippo')
.leftJoinAndSelect('hippo.hats', 'hat')
.getMany();
response.send(hipposWearingHats);
});
export const createHat = functions.https.onRequest(async (request, response) => {
const { owner, color } = request.body;
const connection = await connect();
const repo = connection.getRepository(Hat);
const newHat = new Hat();
newHat.owner = owner;
newHat.color = color;
const savedHat = await repo.save(newHat);
response.send(savedHat);
});
Listeners
A TypeORM listener allows you hook into the database lifecycle. For example, we may want to add a timestamp to a record before it is inserted in the database.
@Entity()
export class Hippo extends BaseEntity {
//...
@Column()
createdAt: Date;
@BeforeInsert()
addTimestamp() {
this.createdAt = new Date();
}
}