egg-postgresql

PostgreSQL plugin for Egg.js

Based on node-postgres, refer to node-postgres documentation for specific usage.

Install

npm install egg-postgresql --save

Configuration

Change config/plugin.js to enable this plugin:

exports.postgresql = {
  enable: true,
  package: 'egg-postgresql',
};

configure database connection information in config/config.${env}.js:

Simple database instance

config.postgresql = {
  client: {
    user: 'database-user',
    password: 'secretpassword!!',
    host: 'my.database-server.com',
    port: 5334,
    database: 'database-name',
    // ...Other config options supported by `node-postgres`
  },
  // load into app, default is `true`
  app: true,
  // load into agent, default is `false`
  agent: false,
};

Shortcut, equivalent to the above configuration:

config.postgresql = {
  user: 'database-user',
  password: 'secretpassword!!',
  host: 'my.database-server.com',
  port: 5334,
  database: 'database-name',
  // ...Other config options supported by `node-postgres`
  // load into app, default is `true`
  app: true,
  // load into agent, default is `false`
  agent: false,
};

Usage:

// Single data source can be accessed through `app.postgresql`
app.postgresql.query(sql, values);
// Shortcut, equivalent to above
app.pg.query(sql, values);

Multiple database instance

exports.postgresql = {
  clients: {
    // clientId, access the client instance by app.postgresql.get('clientId')
    db1: {
      host: 'pg.com',
      port: '15432',
      user: 'test_user',
      password: 'test_password',
      database: 'test',
    },
    db2: {
      // second database config
    },
    // ...
  },

  // default configuration for all databases
  default: {
    max: 20,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 2000,
    // ...Other config options supported by `node-postgres`
  },

  // load into app, default is `true`
  app: true,
  // load into agent, default is `false`
  agent: false,
};

Usage:

const client1 = app.postgresql.get('db1');
client1.query(sql, values);

const client2 = app.postgresql.get('db2');
client2.query(sql, values);

Local develop

brew install postgresql@18
brew services start postgresql@18

psql postgres

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'postgres') THEN
    CREATE ROLE postgres LOGIN SUPERUSER;
  END IF;
END
$$;

ALTER ROLE postgres WITH LOGIN SUPERUSER;
ALTER ROLE postgres PASSWORD 'postgres';

\du postgres
\q

License

MIT