PostgreSQL Helpers

To aid in the process of authoring injection-safe SQL queries, the Joystick PostgreSQL driver includes a handful of helper functions for generating SQL statements in a JavaScript-friendly format.

All methods can be accessed via the PostgreSQL driver at process.databases.postgresql.<method_name> when a PostgreSQL database is connected to your app.

Example Usage

create_table()

The create_table() method can be used to create new PostgreSQL tables in your database:

import joystick from '@joystick.js/node';

joystick.app({
  routes: { ... }
}).then(async () => {
  await process.databases.postgresql.create_table({
    table: 'posts',
    columns: {
      id: 'serial primary key',
      title: 'text',
      slug: 'text',
      author_id: 'text',
      content: 'text',
    },
  });
});

Above, we use the create_table() method to create a mock table posts with four columns. The method accepts a single options object with:

• table: The name of the table to create.
• columns: An object where the key is the column name and the value is the PostgreSQL data type.

Behind the scenes, Joystick builds and executes a CREATE TABLE IF NOT EXISTS statement.

add_column()

The add_column() method can be used to add a new column to an existing table:

import joystick from '@joystick.js/node';

joystick.app({
  routes: { ... }
}).then(async () => {
  await process.databases.postgresql.add_column({
    table: 'posts',
    column_name: 'tags',
    column_type: 'text',
  });
});

This creates a column tags in the posts table. Behind the scenes, Joystick runs an ALTER TABLE <table> ADD COLUMN IF NOT EXISTS statement.

select()

The select() method queries data from a PostgreSQL table:

import joystick from '@joystick.js/node';

joystick.app({
  routes: { ... }
}).then(async () => {
  const rows = await process.databases.postgresql.select({
    table: 'posts',
    columns: ['title', 'slug', 'author_id'],
    where: {
      author: 'abcd1234',
    }
  });
});

This queries the posts table and selects specific columns where author matches the value.

insert()

The insert() method inserts data into a table:

import joystick from '@joystick.js/node';

joystick.app({
  routes: { ... }
}).then(async () => {
  const rows = await process.databases.postgresql.insert({
    table: 'posts',
    data: {
      title: 'How to Upload Files to Amazon S3',
      slug: 'how-to-upload-files-to-amazon-s3',
      author_id: 'abcd1234',
      content: 'Some placeholder content...',
      tags: `['upload', 'files', 'aws', 's3']`,
    },
  });
});

This creates a new row in posts.

update()

The update() method updates data in a table:

import joystick from '@joystick.js/node';

joystick.app({
  routes: { ... }
}).then(async () => {
  const rows = await process.databases.postgresql.update({
    table: 'posts',
    data: {
      title: 'How to Upload Files to AWS S3',
    },
    where: {
      id: 5,
    },
  });
});

This updates the title field where id equals 5.

API

create_table()

Definition

process.databases.postgresql.create_table(options: object) => void;

Parameters

table string required

The name of the table to create.

columns object required

An object of key/value pairs where the key is the column name and the value is the PostgreSQL data type.

add_column()

Definition

process.databases.postgresql.add_column(options: object) => void;

Parameters

table string required

The name of the table to add the column to.

column_name string required

The name of the column to add.

column_type string required

The PostgreSQL data type for the column.

select()

Definition

process.databases.postgresql.select(options: object) => array[object];

Parameters

table string required

The name of the table to query.

columns array[string] required

An array of column names to return.

where object required

An object of key/value pairs where the key is a column name and the value is the value to match.

insert()

Definition

process.databases.postgresql.insert(options: object) => void;

Parameters

table string required

The name of the table to insert into.

data object required

An object of key/value pairs representing the data to insert.

update()

Definition

process.databases.postgresql.update(options: object) => void;

Parameters

table string required

The name of the table to update.

data object required

An object of key/value pairs representing the columns to update.

where object required

An object of key/value pairs representing the condition for which rows to update.