How to Securely Query Postgres in Node.js

How to Securely Query Postgres in Node.js

In this article, we discuss how to use both Postgres ORM and raw SQL in order to securely query data from a Postgres database with Node.js.

When you’re querying Postgres, you need to choose between:

  • Using an ORM. This gives you “native” feeling APIs to query the database.
  • Using raw SQL. This gives you the ultimate flexibility and performance and gives you more transferable skills. It’s always helpful to know how to write SQL.
Postgres ORM

If you want to use an ORM to query Postgres, I recommend using https://typeorm.io. If you’re starting with a fresh project, you can use their typeorm init CLI command:

npx typeorm init --name MyProject --database postgrescd MyProject && yarn

You’ll then need to edit ormconfig.json to add your database connection options. You’ll need to add a file in src/entity for each table in your database.

You may also like: Restful API with NodeJS, Express, PostgreSQL, Sequelize, Travis, Mocha, Coveralls and Code Climate.

You can then use a JavaScript API to create records in your database:

import {createConnection} from "typeorm";
import {Photo} from "./entity/Photo";

async function createPhoto() {  
  const connection = await createConnection({    
    type: 'postgres',    
    url:  process.env.DATABASE_URL || 'postgres://test:[email protected]/test' 
  });  

  const photo = new Photo();  
  photo.name = "Me and Bears";  
  photo.description = "I am near polar bears";  
  photo.filename = "photo-with-bears.jpg";  
  photo.views = 1;  
  photo.isPublished = true;  
  const {id} = await connection.manager.save(photo);  

  console.log("Photo has been saved. Photo id is", photo.id);}

createPhoto();

There are advantages to this approach (the biggest being that it supports strong types), but I personally feel that it makes the code pretty hard to read/follow, and the skills you learn on TypeORM will be of no use if you move to a different ORM

Raw SQL

I believe that the simplest and easiest way to query Postgres is to directly write the SQL that will be run against your database.

“Using SQL directly, means there’s nothing to configure”

yarn add @databases/pg

You’ll need to set theDATABASE_URL environment variable to a database connection string.

import connect, {sql} from '@databases/pg';

const db = connect();

export async function getAllUsers() {
  return await db.query(sql`SELECT * FROM users;`);
}

export async function getUserById(userId) {
  return (await db.query(sql`
    SELECT * FROM users WHERE user_id=${userId}
  `))[0];
}

export async function createUser(u) {
  return (await db.query(sql`
    INSERT INTO users (name, email)
    VALUES (${u.name}, ${u.email})
    RETURNING user_id;
  `))[0].user_id;
}

export async function deleteUserById(userId) {
  await db.query(sql`DELELTE FROM users WHERE user_id=${userId}`);
}

export async function updateUserById(userId, u) {
  await db.query(sql`
    UPDATE users
    SET name=${u.name}, email=${u.email}
    WHERE user_id=${userId}
  `);
}

export async function upsertUser(userId, u) {
  return (await db.query(sql`
    INSERT INTO users (user_id, name, email)
    VALUES (${userId}, ${u.name}, ${u.email})
    ON CONFLICT (user_id)
    DO UPDATE SET name=${u.name}, email={u.email}
    RETURNING *;
  `))[0];
}

N.B. The [@databases](https://www.atdatabases.org/) library does not just concatenate your user input into a string of SQL, it separates your parameters from the actual query, and uses prepared statements to run the query. It throws a clear runtime exception if you forget to tag your sql with the sql tag. This means it’s virtually impossible for you to introduce SQL Injection vulnerabilities by accident.

Conclusion

For most projects, I recommend querying your Postgres database directly using @databases/pg. It gives you the ultimate flexibility. If you need TypeScript types, I recommend declaring the types along with the SQL that queries. TypeScript isn’t currently able to check that the types match your database schema, but at least if they’re in the same file, you’ll probably remember to keep them in sync.

Thank for reading! If you liked this post, share it with all of your programming buddies!

Develop this one fundamental skill if you want to become a successful developer

Throughout my career, a multitude of people have asked me&nbsp;<em>what does it take to become a successful developer?</em>

Throughout my career, a multitude of people have asked me what does it take to become a successful developer?

It’s a common question newbies and those looking to switch careers often ask — mostly because they see the potential paycheck. There is also a Hollywood level of coolness attached to working with computers nowadays. Being a programmer or developer is akin to being a doctor or lawyer. There is job security.

But a lot of people who try to enter the profession don’t make it. So what is it that separates those who make it and those who don’t? 

Read full article here

Top Vue.js Developers in USA

Top Vue.js Developers in USA

Vue.js is an extensively popular JavaScript framework with which you can create powerful as well as interactive interfaces. Vue.js is the best framework when it comes to building a single web and mobile apps.

We, at HireFullStackDeveloperIndia, implement the right strategic approach to offer a wide variety through customized Vue.js development services to suit your requirements at most competitive prices.

Vue.js is an open-source JavaScript framework that is incredibly progressive and adoptive and majorly used to build a breathtaking user interface. Vue.js is efficient to create advanced web page applications.

Vue.js gets its strength from the flexible JavaScript library to build an enthralling user interface. As the core of Vue.js is concentrated which provides a variety of interactive components for the web and gives real-time implementation. It gives freedom to developers by giving fluidity and eases the integration process with existing projects and other libraries that enables to structure of a highly customizable application.

Vue.js is a scalable framework with a robust in-build stack that can extend itself to operate apps of any proportion. Moreover, vue.js is the best framework to seamlessly create astonishing single-page applications.

Our Vue.js developers have gained tremendous expertise by delivering services to clients worldwide over multiple industries in the area of front-end development. Our adept developers are experts in Vue development and can provide the best value-added user interfaces and web apps.

We assure our clients to have a prime user interface that reaches end-users and target the audience with the exceptional user experience across a variety of devices and platforms. Our expert team of developers serves your business to move ahead on the path of success, where your enterprise can have an advantage over others.

Here are some key benefits that you can avail when you decide to hire vue.js developers in USA from HireFullStackDeveloperIndia:

  • A team of Vue.js developers of your choice
  • 100% guaranteed client satisfaction
  • Integrity and Transparency
  • Free no-obligation quote
  • Portal development solutions
  • Interactive Dashboards over a wide array of devices
  • Vue.js music and video streaming apps
  • Flexible engagement model
  • A free project manager with your team
  • 24*7 communication with your preferred means

If you are looking to hire React Native developers in USA, then choosing HireFullStackDeveloperIndia would be the best as we offer some of the best talents when it comes to Vue.js.