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

How to create an SQLite database in node.js

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:

```sql
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:

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

With parameters like:

```json
[
  ["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:

```sql
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:

```json
[
  ["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:

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

With parameters like:

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

and it will be equivalent to running:

```sql
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:

```sql
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:

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

With parameters like:

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

and it will be equivalent to running:

```sql
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`](https://www.atdatabases.org/docs/pg-guide-typescript) or [`@database/pg-bulk`](https://www.atdatabases.org/docs/pg-bulk).

UNNEST is the only way to make Postgres queries fast and reliable, if you want to interact thousands of rows at a time.

Postgres UNNEST cheat sheet for bulk operations

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](https://www.atdatabases.org/docs/sqlite) 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:

1. It suffers from serious issues when handling time zones & dates/timestamps.
1. It doesn't have a propper type for `BOOLEAN`, which leads to messy translation between `0`/`1` and `true`/`false`.
1. 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](https://www.atdatabases.org/docs/mysql) 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](https://www.atdatabases.org/docs/pg) next.

Choosing a node.js database

In addition to working with a file, you can also use SQLite as an in-memory database. If you do this it will not be persisted, but it can be very useful for tests.



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

```yarn
yarn install @databases/sqlite
```

```npm
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 an in memory database.

```typescript
import connect, {sql} from '@databases/sqlite';

// We don't pass a file name here because we don't want to store
// anything on disk
const db = connect();

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

async function set(id: string, value: string) {
  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: string): string | undefined {
  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: string) {
  await prepared;
  await db.query(sql`
    DELETE FROM app_data WHERE id=${id};
  `);
}

async function run() {
  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);
});
```

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

// We don't pass a file name here because we don't want to store
// anything on disk
const db = connect();

async function prepare() {
  await db.query(sql`
    CREATE TABLE 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() {
  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](https://www.atdatabases.org/docs/sqlite) and [the SQLite Language Docs](https://sqlite.org/lang.html).

Node.js in memory database

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
yarn install @databases/sqlite
```

```npm
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).

```typescript
import connect, {sql} from '@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: string, value: string) {
  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: string): string | undefined {
  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: string) {
  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);
});
```

```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](https://www.atdatabases.org/docs/sqlite) and [the SQLite Language Docs](https://sqlite.org/lang.html).

SQL Injection remains one of the most prevalent and easily exploitable security vulnerabilities in modern web applications. The main reason for this is SQL libraries making it so easy to get this wrong.



## What's the big deal?

The problem with SQL is that it mixes code with data. Consider the following statement:

```sql
INSERT INTO posts (body, username)
VALUES (@[[["Hello World"]]]@, @[[["my_username"]]]@)
```

The sections `@[[[highlighted in yellow]]]@` are data, but the rest of that SQL statement is code. If we try to make this part of some JavaScript we might want to take the body and username as parameters and do something like this:

```js
function addPost(body, username) {
  return db.query(`
    INSERT INTO posts (body, username)
    VALUES ("@[[[${body}]]]@", "@[[[${username}]]]@")
  `);
}
```

> N.B. this code is not secure in most database libraries and would therefore throw an exception in @databases

This code is invitingly simple, but consider a typical scenario:

- the body is provided by the user, who types their message into a text box.
- the username is provided by the server, based on some secure authentication, e.g. a username and password.

A malicious user might try to post the message:

```
I am stupid", "someone_else") --
```

This would result in the SQL

```sql
INSERT INTO posts (body, username)
VALUES ("I am stupid", "someone_else") --", "my_username")
```

This would insert the post "I am stupid" as if "someone_else" had said it, even though I should only be able to post as myself. To make it clearer what's going on, I've `@[[[highlighted in yellow]]]@` the two variables that have been inserted into my SQL:

```sql
INSERT INTO posts (body, username)
VALUES ("@[[[I am stupid", "someone_else") --]]]@", "my_username")
```

## Separating Code From Data

If the problem is that SQL combines code with data, the solution is to separate the code from the data. To continue with the example from before, we could have written our JavaScript function as:

```js
function addPost(body, username) {
  return db.query(
    `
      INSERT INTO posts (body, username)
      VALUES (?, ?)
    `,
    [body, username],
  );
}
```

Here we've used a `?` as a placeholder in the code for "some data". The `?` is no longer in `"quotes"` as it is not the string literal `"?"`. If we were to pass it the same malicious input as before, the database engine would be passed the SQL string:

```sql
INSERT INTO posts (body, username)
VALUES (?, ?)
```

along with the values:

```js
['I am stupid", "someone_else") --', 'my_username'];
```

This is totally safe. If you can be disciplined about writing your queries this way, your code will be secure.

## Why @databases

There are a few downsides to the approach above that I wanted to fix with @databases:

1. It's easy to forget to do this, and accidentally use the insecure approach from time to time. This is especially true if you're new to writing server side JavaScript, or if you're working on a large team where it might not even be you who messes up.
2. It can be hard to review code and be **sure** this approach has been followed consistently. You might be constructing somewhat dynamic queries, where you're not always querying for the same list of fields. You have to read code very carefully to distinguish variables containing trusted strings, from variables containing user entered strings.
3. I find it harder to read/follow, especially when the query gets long/complex. The data can become very separated from the location in the query where it is used.

With @databases you can just write:

```js
function addPost(body, username) {
  return db.query(sql`
    INSERT INTO posts (body, username)
    VALUES ("${body}", "${username}")
  `);
}
```

The `sql` tag before the SQL query tells JavaScript to pass the literal strings separately from the values to the `sql` function, whichh can process them as needed. The `sql` function then returns the query and the values separately. In this example, the database gets passed the query:

```sql
INSERT INTO posts (body, username)
VALUES (?, ?)
```

along with the values for `body` and `username`. The great thing about using `@databases` consistently is that `@databases` will throw an exception if you try to pass it anything that's not been tagged as `sql` so you can't accidentally pass it a string (and neither can your team mates). If you're using TypeScript, you even get a type error at build time if you forget to tag your SQL statements. You can use `@databases` with Postgres, MySQL, SQLite and WebSQL.

## Alternatives

There are a couple of alternative approaches:

1. Using a query builder like "knex" that lets you safely construct SQL statements using a JavaScript API.
2. Using an ORM like Sequelize that maps your database into a native feeling JavaScript API.

These are a great option for accessing databases without risking SQL injection. If they work for you, please continue using them. The biggest problem with both of these is that they separate you from the actual SQL, which often means you miss opportunities to take advantage of the most useful/powerful features of databases, and can lead to much less efficient code. I've found that the more I actually use SQL directly, the less I dislike working with it.

SQL Injection remains one of the most prevalent and easily exploitable security vulnerabilities in modern web applications. It doesn't have to be!

SQL Injection shouldn't be your responsibility to prevent.