DEV Community: Forbes Lindesay

Postgres UNNEST cheat sheet for bulk operations

Forbes Lindesay — Fri, 21 Jan 2022 15:07:21 +0000

originally posted on the @databases blog

Postgres is normally very fast, but it can become slow (or even fail completely), if you have too many parameters in your queries. When it comes to operating on data in bulk, UNNEST is the only way to achieve fast, reliable queries. This post has examples for using UNNEST to do all types of bulk transactions.

All the examples in this article assume a database schema that looks like:

CREATE TABLE users (
  email TEXT NOT NULL PRIMARY KEY,
  favorite_color TEXT NOT NULL
)

INSERT thousands of records in one go

To insert many records into a Postgres table in one go, the most efficient method is to provide each column as a separate array and then use UNNEST to construct the rows to insert.

You can run the following query:

INSERT INTO users (email, favorite_color)
SELECT * FROM UNNEST(?::TEXT[], ?::TEXT[])

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["red", "green", "indigo"]
]

Notice that you're only passing 2 parameters, no matter how many rows you want to insert. You're also using the same query text no matter how many rows you want to insert. This is what keeps the query so efficient.

The resulting table would look like:

email	favorite_color
`joe@example.com`	`red`
`ben@example.com`	`green`
`mary@example.com`	`indigo`

UPDATE multiple records to different values in a single query

One of the most powerful use cases of UNNEST is to update multiple records in a single query. The normal UPDATE statement only really lets you update multiple records in one go if you want to set them all to the same value, but this approach is way more flexible.

You can run the following query:

UPDATE users
SET
  favorite_color=bulk_query.updated_favorite_color
FROM
  (
    SELECT *
    FROM
      UNNEST(?::TEXT[], ?::TEXT[])
      AS t(email, updated_favorite_color)
  ) AS bulk_query
WHERE
  users.email=bulk_query.email

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

The resulting table would then look like:

email	favorite_color
`joe@example.com`	`purple`
`ben@example.com`	`violet`
`mary@example.com`	`orange`

Not only does this let you update all these records in one statement, but the number of parameters remains fixed at 2 now matter how many rows you want to update.

SELECT with thousands of different conditions in one go

You can always build a very large query by combining OR and AND, but eventually, if you have enough parameters, this may start to become slow.

You can run the following query:

SELECT * FROM users
WHERE (email, favorite_color) IN (
  SELECT *
  FROM UNNEST(?::TEXT[], ?::TEXT[])
)

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

and it will be equivalent to running:

SELECT * FROM users
WHERE
  (email='joe@example.com' AND favorite_color='purple')
  OR (email='ben@example.com' AND favorite_color='violet')
  OR (email='mary@example.com' AND favorite_color='orange')

Using UNNEST here lets us keep the query constant, and use only 2 parameters, regardless of how many conditions we want to add.

An alternative if you need more control can be to use an INNER JOIN instead of the IN part of the query. For example, if you needed the tests to be case insensitive you could do:

SELECT users.* FROM users
INNER JOIN (
  SELECT *
  FROM
    UNNEST(?::TEXT[], ?::TEXT[])
    AS t(email, favorite_color)
) AS unnest_query
ON (
  LOWER(users.email) = LOWER(unnest_query.email)
  AND LOWER(users.favorite_color) = LOWER(unnest_query.favorite_color)
)

DELETE with thousands of different conditions in one go

Just like SELECT, DELETE queries can become slow if the complexity of your conditions grows too extreme.

You can run the following query:

DELETE FROM users
WHERE (email, favorite_color) IN (
  SELECT *
  FROM UNNEST(?::TEXT[], ?::TEXT[])
)

With parameters like:

[
  ["joe@example.com", "ben@example.com", "mary@example.com"],
  ["purple", "violet", "orange"]
]

and it will be equivalent to running:

DELETE FROM users
WHERE
  (email='joe@example.com' AND favorite_color='purple')
  OR (email='ben@example.com' AND favorite_color='violet')
  OR (email='mary@example.com' AND favorite_color='orange')

Just like with SELECT, using UNNEST here lets us keep the query constant, and use only 2 parameters, regardless of how many conditions we want to add.

If you're using node.js, you can do all these operations without having to memorize the syntax by using @database/pg-typed or @database/pg-bulk.

How to create an SQLite database in node.js

Forbes Lindesay — Sun, 07 Feb 2021 03:35:10 +0000

This was originally posted on atdatabases.org

SQLite is a great database for embedded use cases. e.g. if you are using node.js in IOT, or an Electron app.

To get started, install @databases/sqlite using either yarn or npm:

yarn install @databases/sqlite

npm install @databases/sqlite

Then you can import it (if you are using TypeScript/Babel/some other environment that supports ESModules) or require it (if you are using plain JavaScript), and call connect to create the database file if it does not exist, and open it if it already exists.

Here is an example of using SQLite as a basic key value store of strings (here VARCHAR is the SQLite data type that is equivalent to string in JavaScript).

const connect = require('@databases/sqlite');
const {sql} = require('@databases/sqlite');

const db = connect('temp.db');

async function prepare() {
  await db.query(sql`
    CREATE TABLE IF NOT EXISTS app_data (
      id VARCHAR NOT NULL PRIMARY KEY,
      value VARCHAR NOT NULL
    );
  `);
}
const prepared = prepare();

async function set(id, value) {
  await prepared;
  await db.query(sql`
    INSERT INTO app_data (id, value)
      VALUES (${id}, ${value})
    ON CONFLICT (id) DO UPDATE
      SET value=excluded.value;
  `);
}

async function get(id) {
  await prepared;
  const results = await db.query(sql`
    SELECT value FROM app_data WHERE id=${id};
  `);
  if (results.length) {
    return results[0].value;
  } else {
    return undefined;
  }
}

async function remove(id) {
  await prepared;
  await db.query(sql`
    DELETE FROM app_data WHERE id=${id};
  `);
}

async function run() {
  const runCount = JSON.parse((await get('run_count')) || '0');
  console.log('run count =', runCount);
  await set('run_count', JSON.stringify(runCount + 1));
  console.log(await get('name'));
  await set('name', 'Forbes');
  console.log(await get('name'));
  await set('name', 'Forbes Lindesay');
  console.log(await get('name'));
  remove('name');
}
run().catch((ex) => {
  console.error(ex.stack);
  process.exit(1);
});

For more information, check out the API docs for @databases/sqlite and the SQLite Language Docs.

Choosing a node.js database

Forbes Lindesay — Sun, 07 Feb 2021 03:32:34 +0000

Originally posted on atdatabases.org

One of the first things you'll need to do when starting most node projects is to chose a database and database library. You'll normally want to choose your database before you choose the library, but in the case of @databases, we support a few different databases, so you do have some leeway.

SQL vs. NoSQL

In recent years, NoSQL databases have grown, and then somewhat declined in popularity. They often seem much easier to get started with than SQL databases because you don't need to learn any new language, and you don't need to define your schema up-front. They also promised (in theory) some improvements in scalability vs. more traditional SQL databases.

While there are some situations where the scalability part is relevant, you should keep in mind that Facebook stores the vast majority of its data in a few MySQL servers. Unless you are a very big tech company like Google or Facebook, it is likely that Postgres or MySQL will scale to meet your needs with no problems.

The idea that you can get away without thinking as carefully about your schema in NoSQL databases is also often flawed. You normally end up with just as rigid a schema definition, except nobody has written down what that schema is.

The point about learning SQL is valid, but you can get started without needing to learn very much, and it is an extremely useful skill to develop. If you must use a NoSQL database, MongoDB remains one of the most popular, but I hope you will at least try using an SQL database first.

Choosing an SQL Database

SQL Server and Oracle are both used in many busnesses, but their licensing and pricing models make them difficult and expensive to deploy and manage, and they don't offer any real upsides over MySQL or Postgres, so I won't consider them in depth here.

SQLite

SQLite is great if you need to store data in a small project that will run on people's own machines, rather than on a server. It's very portable, very lightweight, and runs within the node.js process itself. This is also great if you are building an Electron app.

It cannot be shared amongst multiple instances of your app, which makes it a poor choice for most server side applications.

If you decide to use SQLite, you should read Getting started with SQLite and Node.js next.

MySQL

MySQL is widely used and deployed. It is easy to get hosted versions from any of the major cloud providers. If you're already using it, there's certainly no reason you should panic about that choice. I wouldn't choose MySQL for new projects though, because:

It suffers from serious issues when handling time zones & dates/timestamps.
It doesn't have a propper type for BOOLEAN, which leads to messy translation between 0/1 and true/false.
It also doesn't have nearly as good support for JSON as Postgres

If decide to use MySQL, you should read Getting started with MySQL and Node.js next.

Postgres

Postgres is one of the most fully featured databases, and has good handling of dates & times, as well as excellent support for storing and querying unstructured JSON when you need to.

If you decide to use Postgres, you should read Getting started with Postgres and Node.js next.

Storing JSON in Postgres using Node.js

Forbes Lindesay — Fri, 21 Feb 2020 16:08:09 +0000

One of the things that made NoSQL databases like MongoDB so popular was how easy it is to just throw a blob of JSON in, when that’s what you needed. What you may not realise though, is that Postgres supports JSON pretty much equally well. In addition to just throwing some JSON in a field, you can query it using all the usual SQL operations. You get JOINs, transactions, indexes etc.

JSON vs. JSONB

The first step, when adding a JSON field to postgres is to decide between JSON and JSONB. I’ll make this easy for you:

Always use JSONB, never use JSON

JSONB is just like JSON, except that it doesn’t store the actual string of JSON, instead it stores an efficient binary representation. The only reason you might ever want to store JSON is if you wanted to keep track of the whitespace in the original JSON serialisation or the key order in your objects. If you want a “pretty” view of your JSON, you can just use JSON.stringify to pretttify it.

Creating the Table

Now that you’ve decided on JSONB as the format, you can create the table just like you normally would.

CREATE TABLE my_data (
  id TEXT NOT NULL PRIMARY KEY,
  data JSONB NOT NULL
);

This creates a table with a primary key called id that’s of type TEXT and a data column to store our JSON data.

Reading and Writing JSON Data

If you’re using the @databases/pg client, you can read and write Postgres data just like you would any other value:

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

const db = connect();

export async function get(id) {
  const [row] = await db.query(
    sql`
      SELECT data
      FROM my_data
      WHERE id=${id}
    `
  );
  return row ? row.data : null;
}

export async function set(id, value) {
  await db.query(sql`
    INSERT INTO my_data (id, data)
    VALUES (${id}, ${value})
    ON CONFLICT id
    DO UPDATE SET data = EXCLUDED.data;
  `);
}

This gives us a simple key value store for JSON blobs using a postgres database.

Querying JSON

Imagine we’re storing some blog posts in our “NoSQL Postgres Database”:

await set('post-a', {
  author: 'ForbesLindesay',
  title: 'Post A',
  body: 'This post is about the letter A',
});

await set('post-b', {
  author: 'ForbesLindesay',
  title: 'Post B',
  body: 'This post is about the letter B',
});

await set('post-a-rebuttal', {
  author: 'JoeBloggs',
  title: 'Post A - Rebuttal',
  body: 'Forbes was wrong about the letter A',
});

Now imagine we want to get a list of all the blog posts by ForbesLindesay. The author field is buried in the JSONB field, but that doesn’t mean we can’t use it in our query.

export async function listByAuthor(author) {
  return await db.query(
    sql`
      SELECT data
      FROM my_data
      WHERE
        data ->> 'author'
          = ${author}
    `
  );
}

Here, the ->> operator means “get the value of this property”. It will only work when the value is a string, number or boolean. If the value is another object, you must use the -> operator, which means “get the value of this property as JSON”.

Hopefully it’s clear that this means you can use the full power of SQL here, but just to give another example, we could get a list of all the authors:

export async function getAuthors() {
  return (await db.query(
    sql`
      SELECT DISTINCT
        data ->> 'author' as author
      FROM my_data
    `
  )).map(({author}) => author);
}

Here, we’re extracting the author from the data, and then using SQL’s DISTINCT operator to only return each author once.

Conclusion

In Postgres, you can use JSON just like any other value, and it would be easy to set up a NoSQL like store for JSON blobs and just use that as your entire database. This doesn’t necessarily mean you should. This JSON data is totally schema-less, so it makes it very important that you properly validate that it matches any expected structure, before inserting it into the database. It’s very useful when you need to store large JSON structures and you’re not yet sure how you’re going to want to query them, but for the most part, I still recommend having explicit field in SQL, and using JOINs to store nested lists etc.

Originally published on medium