db-util-lite v2.0.48

Db Util Lite

A lite database set of utilities for node apps. Currently only supports Postgres.

Current functionality:

• migrations pertaining to database schemas

In order to use any of the functionality, the databases has to already have been created. This code will not create nor drop databases.

Migrations

When creating a migration, a file is generated. That file has SQL that is used when moving forward and SQL that is used when going backwards.

Here is the template for this migration file:

import { SQL, SQLStatement } from "sql-template-strings";

export const name = "";

export const up = (): SQLStatement => {
    const query = SQL`
        SELECT 'up'
    `;

    return query;
};

export const down = (): SQLStatement => {
    const query = SQL`
        SELECT 'down'
    `;

    return query;
};

The name constant should match a name in the db-util.json file.

the migration file is in TypeScript so before running migrations run a transpiler.

Bash Commands

Setup (creates a table used for managing migrations)

node node_modules/db-util-lite setup --src /some/path/db-util.json

Remove (only removes the one table from the setup)

node node_modules/db-util-lite remove --src /some/path/db-util.json --env localhost

Create a migration:

node node_modules/db-util-lite createMigration --name first --src /some/path/db-util.json  --db pg --dir /Users/Isaac/projects/db-util/migrations

Run migrations backwards:

node node_modules/db-util-lite runMigrations --src /some/path/db-util.json --go down

Run migrations foward:

node node_modules/db-util-lite runMigrations --src /some/path/db-util.json --go up

File:db-util.json

{
  "version": "1.0.0",
  "appDir": "/path/to/app/",
  "environment": "localhost",
  "seedDir": "seeds",
  "migrationDir": "src/migrations",
  "databases": [
    {
      "name": "one-db",
      "type": "postgres",
      "connStrs" :
        [{
          "env": "localhost",
          "user": "postgres",
          "host": "localhost",
          "database": "this-new-app",
          "password": "postgres",
          "port": "5433"
        },
        {
          "env": "development",
          "user": "postgres",
          "host": "localhost",
          "database": "this-new-app",
          "password": "postgres",
          "port": "5433"
        }]
    },
    {
      "name": "another-db",
      "type": "postgres",
      "connStrs" :
        [{
          "env": "localhost",
          "user": "postgres",
          "host": "localhost",
          "database": "this-new-app",
          "password": "postgres",
          "port": "5433"
        },
        {
          "env": "development",
          "user": "postgres",
          "host": "localhost",
          "database": "this-new-app",
          "password": "postgres",
          "port": "5433"
        }]
    }
  ]
}

Query

This feature exposes the query module that was built for migrations and allows for that module to be used anywhere in the app.

Features:

• allows for the app to talk to many different dabases
• runs all queries in UTC time

import { db as dbUtil } from "db-util-lite";
import { SQL, SQLStatement } from "sql-template-strings";

dbUtil.db.conf.setup("/some/path/db-util.json");
dbUtil.db.conn.setup(dbUtil.db.conf.allDatabases(), "localhost");

const query: SQLStatement = SQL`SELECT count(*) FROM persons`;

const dbOpts: dbUtil.db.conn.QueryOptions  = {
    name: "value-from-file",
};

await dbUtil.db.conn.rows(query, dbOpts);