Connecting Node.js app on Google Cloud App Engine to a Google Cloud SQL instance

I have a Node app which uses MySQL, connecting via a config json:

I have a Node app which uses MySQL, connecting via a config json:

{
    "client": "mysql",
    "connection": {
        "host": "something",
        "user": "something",
        "password": "something",
        "database": "daimonion-db",
        "debug": false
    }
}

I've created a Google Cloud Platform SQL instance. I'm seeing an IP address and instance connection name.

I've also deployed the Node app to Google Cloud App Engine in a flexible environment.

How do I connect the Node app to the SQL instance? I'm seeing this explanation: https://cloud.google.com/sql/docs/mysql/connect-app-engine which tells me to add a settings string to my app.yaml to connect with either a Unix domain socket or TCP connection, but how do I connect to these from my Node app?

Top 7 Most Popular Node.js Frameworks You Should Know

Top 7 Most Popular Node.js Frameworks You Should Know

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser. In this post, you'll see top 7 of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser.

One of the main advantages of Node is that it enables developers to use JavaScript on both the front-end and the back-end of an application. This not only makes the source code of any app cleaner and more consistent, but it significantly speeds up app development too, as developers only need to use one language.

Node is fast, scalable, and easy to get started with. Its default package manager is npm, which means it also sports the largest ecosystem of open-source libraries. Node is used by companies such as NASA, Uber, Netflix, and Walmart.

But Node doesn't come alone. It comes with a plethora of frameworks. A Node framework can be pictured as the external scaffolding that you can build your app in. These frameworks are built on top of Node and extend the technology's functionality, mostly by making apps easier to prototype and develop, while also making them faster and more scalable.

Below are 7of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Express

With over 43,000 GitHub stars, Express is the most popular Node framework. It brands itself as a fast, unopinionated, and minimalist framework. Express acts as middleware: it helps set up and configure routes to send and receive requests between the front-end and the database of an app.

Express provides lightweight, powerful tools for HTTP servers. It's a great framework for single-page apps, websites, hybrids, or public HTTP APIs. It supports over fourteen different template engines, so developers aren't forced into any specific ORM.

Meteor

Meteor is a full-stack JavaScript platform. It allows developers to build real-time web apps, i.e. apps where code changes are pushed to all browsers and devices in real-time. Additionally, servers send data over the wire, instead of HTML. The client renders the data.

The project has over 41,000 GitHub stars and is built to power large projects. Meteor is used by companies such as Mazda, Honeywell, Qualcomm, and IKEA. It has excellent documentation and a strong community behind it.

Koa

Koa is built by the same team that built Express. It uses ES6 methods that allow developers to work without callbacks. Developers also have more control over error-handling. Koa has no middleware within its core, which means that developers have more control over configuration, but which means that traditional Node middleware (e.g. req, res, next) won't work with Koa.

Koa already has over 26,000 GitHub stars. The Express developers built Koa because they wanted a lighter framework that was more expressive and more robust than Express. You can find out more about the differences between Koa and Express here.

Sails

Sails is a real-time, MVC framework for Node that's built on Express. It supports auto-generated REST APIs and comes with an easy WebSocket integration.

The project has over 20,000 stars on GitHub and is compatible with almost all databases (MySQL, MongoDB, PostgreSQL, Redis). It's also compatible with most front-end technologies (Angular, iOS, Android, React, and even Windows Phone).

Nest

Nest has over 15,000 GitHub stars. It uses progressive JavaScript and is built with TypeScript, which means it comes with strong typing. It combines elements of object-oriented programming, functional programming, and functional reactive programming.

Nest is packaged in such a way it serves as a complete development kit for writing enterprise-level apps. The framework uses Express, but is compatible with a wide range of other libraries.

LoopBack

LoopBack is a framework that allows developers to quickly create REST APIs. It has an easy-to-use CLI wizard and allows developers to create models either on their schema or dynamically. It also has a built-in API explorer.

LoopBack has over 12,000 GitHub stars and is used by companies such as GoDaddy, Symantec, and the Bank of America. It's compatible with many REST services and a wide variety of databases (MongoDB, Oracle, MySQL, PostgreSQL).

Hapi

Similar to Express, hapi serves data by intermediating between server-side and client-side. As such, it's can serve as a substitute for Express. Hapi allows developers to focus on writing reusable app logic in a modular and prescriptive fashion.

The project has over 11,000 GitHub stars. It has built-in support for input validation, caching, authentication, and more. Hapi was originally developed to handle all of Walmart's mobile traffic during Black Friday.

Dynamically generating SQL queries using Node.js

Dynamically generating SQL queries using Node.js

Introduction to building dynamic SQL queries safely in Node.js using Slonik.

Ever since I have released Slonik (PostgreSQL client for Node.js) and written a controversial Stop using Knex.js article (tl;dr; query builders are designed to be building blocks for ORMs; they do not add value when majority of the query is static.), I have been asked a lot – then how do I generate dynamic queries? I will answer this by sharing a couple of real-life examples.

All of the queries in this article are actual queries used in real-life business, Applaudience which heavily relies on PostgreSQL.

Disclaimer: (1) All examples discuss only SQL injection threats. Authorization logic (e.g. whitelisting columns user is authorized to access) is not in the scope of this article. (2) All statements assume there are no bugs in Slonik implementation.

Static query with dynamic value bindings

If your query logic does not change depending on user's input, then simply construct SQL query using sql tagged template literal, e.g.

sql`
  SELECT c1.country_id
  FROM cinema_movie_name cmn1
  INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
  WHERE cmn1.id = ${cinemaMovieNameId}
`;

If you are using Slonik, it is safe to pass values as template literal placeholderssql will interpret all placeholder tokens and construct final SQL query. In this case, the only dynamic part of the query is the value bindings themselves, therefore the final query is:

SELECT c1.country_id
FROM cinema_movie_name cmn1
INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id
WHERE cmn1.id = $1

Query and bound values will be sent to PostgreSQL separately: no risk of SQL injection.

Binding a list of values

When your query input is a list of values (e.g. such as when retrieving rows matching multiple identifiers), then you may use sql.valueList, e.g.

sql  SELECT m1.*   FROM movie m1   WHERE m1.id IN (${sql.valueList(movieIds)});

This will generate a query with dynamic set of value bindings, i.e. if movieIds is [1, 2, 3] the query that is sent to PostgreSQL will be:

SELECT m1.*
FROM movie m1
WHERE m1.id IN ($1, $2, $3)

However, despite this being a common pattern, I do not advise to use this pattern. Instead, use sql.array, e.g.

sql  SELECT m1.*   FROM movie m1   WHERE m1.id = ANY(${sql.array(movieIds, 'int4')});

This will generate a fixed-length query that does not change based on its inputs, i.e.

SELECT m1.*
FROM movie m1
WHERE m1.id = ANY($1::"int4"[])

Continue reading sql.array vs sql.valueList.

Query with dynamic columns

If your query result refers to columns that depend on user's input, then use sql.identifier to generate SQL that identifies those columns, e.g.

(Note: Not an actual query used in business. See next paragraph.)

sql  SELECT m1.id, ${sql.identifier(['m1', movieTableColumnName])}   FROM movie m1   WHERE     m1.id = ${moveId};

This query will produce a query that selects exactly 1 dynamically identified column. There is no risk of SQL injection, i.e. even if logic leading to generation of movieTableColumnName was somehow compromised, the worst that can happen is that query attacker will be able to return any column under m1 alias or execute query with invalid column identifier values (both carry risk; business logic is not in scope of this article).

Just because you can do this, you probably shouldn't. When your application requires to return different columns depending on user's query, it is better to select all columns that are in scope of the business logic and pick value of the needed column, i.e. If the intent of the latter query was to return a different movie identifier based on movieTableColumnName, then it is better to write a static query:

sql  SELECT     m1.id,     m1.foreign_comscore_id,     m1.foreign_imdb_id,     m1.foreign_metacritic_id     m1.foreign_rottentomatoes_id,     m1.foreign_tmdb_id,     m1.foreign_webedia_id   FROM movie m1   WHERE     m1.id = ${moveId};

The latter has does return some superfluous data on every query, but it has several advantages:

  1. It reduces risk of SQL injection (regardless of how much you trust code generation logic, static code is always safer than dynamic code).
  2. It produces only one entry pg_stat_statements. You will learn to appreciate as few as possible queries in pg_stat_statements as your application scales.
Query with multiple dynamic columns

Same as the above, but sql.identifierList.

Nesting dynamic SQL queries

sql tagged template literals can be nested, e.g.

(Note: Simplified version of an actual query used in business.)

const futureEventEventChangeSqlToken = sql  SELECT     ec1.event_id,     ec1.seat_count,     ec1.seat_sold_count   FROM event_change_future_event_view ec1;

sql SELECT event_id, seat_count, seat_sold_count FROM ( ${futureEventEventChangeSqlToken} ) AS haystack WHERE ${paginatedWhereSqlToken} ORDER BY ${orderSqlToken} LIMIT ${limitSqlToken}

This allows to pass pre-bound SQL queries as first-class citizens across your program. This is handy when the intent is to isolate SQL generation logic for testing or when large SQL fragments are shared between queries or when the intent is to simply reduce concentration of code complexity in one place.

Injecting dynamic SQL fragments

sql.raw is used to inject dynamic SQL fragments, i.e.

sql  SELECT ${sql.raw('foo bar baz')}

translates to (invalid) query:

SELECT foo bar baz

Unlike the previous example using sql tagged template, sql.raw is not safe – it allows to create dynamic SQL using user input.

There are no known use cases for generating queries using sql.raw that aren't covered by nesting bound sql expressions (described in "Nesting dynamic SQL queries") or by one of the other existing query building methodssql.raw exists as a mechanism to execute externally stored static (e.g. queries stored in files).

Query with a dynamic comparison predicate members or operator

If an operator of a comparison predicate present in your query is dynamic, then use sql.comparisonPredicate, e.g.

(Note: Not an actual query used in business.)

sql   SELECT     c1.id,     c1.nid,     c1.name   FROM cinema c1   WHERE     ${sql.comparisonPredicate(       sqlc1.name,       nameComparisonOperator,       nameComparisonValue     )} ;

nameComparisonOperator can be values such as =><, etc. Assuming nameComparisonOperator is "=", then the resulting query is going to be:

SELECT
c1.id,
c1.nid,
c1.name
FROM cinema c1
WHERE
c1.name = $1

The latter is an extremely rare use case, reserved almost entirely to building higher level SQL abstraction tools (such as ORMs). It may be useful for "advance search" scenarios, however continue reading to familiarise with alternative patterns (see sql.booleanExpression).

Query with dynamic WHERE clause members

If presence of WHERE clause members is dynamic, then use sql.booleanExpression.

const findCinemas = (root, parameters, context) => {
const booleanExpressions = [
sqlTRUE,
];

if (parameters.input.query) {
const query = parameters.input.query;

if (query.countryId !== undefined) {
  booleanExpressions.push(
    sql`c2.id = ${query.countryId}`
  );
}

if (query.nid !== undefined) {
  booleanExpressions.push(
    sql`c1.nid % ${query.nid}`
  );
}

if (query.name !== undefined) {
  booleanExpressions.push(
    sql`c1.name % ${query.name}`
  );
}

}

const whereSqlToken = sql.booleanExpression(
booleanExpressions,
'AND'
);

return context.pool.any(sql SELECT c1.id, c1.nid, c1.name, c2.code_alpha_2 country_code, c2.name country_name FROM cinema c1 INNER JOIN country c2 ON c2.id = c1.country_id WHERE ${whereSqlToken} );
},

findCinemas is an implementation of a GraphQL resolver. WHERE clause of the query is constructed using a combination of 3 possible boolean expressions. As is the case with all the other query building methods in Slonik, all expressions can be nested: you can have other boolean expressions as members of a boolean expression or even SQL expression constructed using sql tagged template literal.

Summary

These examples cover every common dynamic SQL building scenario and provide enough knowledge of how Slonik works to enable reader to continue journey of familiarising with other query building methods provided by Slonik. The primary intent of this article was to demonstrate that Slonik provides a safe abstraction for constructing SQL queries keeping the static parts of the query intact.

If you value my work and want to see Slonik and many other of my Open-Source projects to be continuously improved, then please consider becoming a patron:

Originally published by Gajus Kuizinas at medium.com

=========================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

Learn more

How To Build a Blog with Nest.js, MongoDB, and Vue.js

MongoDB, Express, Vue.js 2, Node.js (MEVN) and SocketIO Chat App

First CRUD Node Express Js Mysql Example

Angular7 CRUD with nodejs and mysql example