Contour ORM - A high perforamnce PostgreSQL ORM for nodejs

Schema driven sql orm, which supports a JSON query language inspired by GraphQL.

Install

npm install -s contour-orm

You will also want to install a unique id generator, such as guid or objectid

npm install -s objectid

The example.js file has a fully runable example.

Using with PostgreSQL

Contour ORM currently works exclusively with PostgreSQL. You will need to install postgreSQL locally or use AWS RDS or equivalent. A database client such as PGAdmin can also be handy though not required.

Setup

You only need to create a database to store your data, contour orm handles the rest. This can be done by executing the PostgreSQL create database ... command, or using a database client.

Usage

Setup

const contourOrm = require("contour-orm")

Setup postgreSQL provider:

const postgresProvider = new contourOrm.PostgresProvider({
    host: "localhost",
    database: "contourormexample",
    user: "postgres",
    password: "postgres"
})

Sychronise schema

Details of the schema format can be found at the end of the exmaple.js file, and in the testSchema.ts file in the tests directory.

The following code synchronises the database with the schema. The first time it is run it will create the database structure, the following times it will migrate the structure to match.

const schema = getSchema() //schema object, more details below

const schemaSychroniser = new contourOrm.SchemaSynchroniser(postgresProvider, {
    tableSchema: "public" //currently only `public` tableSchema supported
})

const transaction = await postgresProvider.beginTransaction()
await schemaSychroniser.synchronise(schema, transaction)

//Optional: use transaction.runCommand(...) to run any custom data migration SQL scripts

await transaction.commit()

Graph engine (Querying and mutating data)

Contour orm has its own query syntax for fetching data as an object graph, inspired by graphQL. There are methods to query, filter, sort and mutate data.

Querying

Queries can fetch any level of nested data, starting from any point.

A couple examples are shown below, more extensive examples can be seen in the graph engine unit tests:

const graphEngine = new contourOrm.SqlGraphEngine(postgresProvider, schema) 
const taskTypes = await graphEngine.runQuery({
    table: "taskTypes",
    fields: [
        //for non linked fields, you can use the shorthand notation
        "name", 
        
        //linked fields need object with sub fields specified
        { 
            name: "tasks",
            fields: ["description"]
        }
    ]
})

const tasks = await graphEngine.runQuery({
    table: "tasks",
    fields: [
        "description",
        {
            name: "taskType",
            fields: ["name"]
        }
    ]
})

Mutating

To mutate data through the graph engine, you use the mutate command with the relevant type. The save command will create a new record if the id does not yet exist, or update it if it does. The id is free text, we recommend using objectid to generate ids.

const saveResult = await graphEngine.mutate({
    metaData: {
        timeZone: "Europe/London"
    },
    table: "tasks",
    type: "save",
    id: objectid().toString(),
    fields: {
        description: "Task 1",
        taskType: newTaskTypeId
    }
})

A delete command deletes by id:

const deleteResult = await graphEngine.mutate({
    metaData: {
        timeZone: "Europe/London"
    },
    table: "tasks",
    type: "delete",
    id: '[idOfObjectToDelete]
})

Schema

You need to define a schema for your data. This schema is an object defining what tables you want to have, and their relationships.

A few points regarding schemas:

• All tables and fields need to have a unique id. This is free text, but should not be changed once you assign it.
• All tables must be named with a plural and a singular name
• All "linked fields" must specify a "reverse field id" to a corresponding field. At least one of these fields must have referencesMany = true, as 1:1 relationships are not currently supported.
• Delete behaviour can either be cascade (target rows are also deleted), prevent (delete not allowed if referenced by target) or nullTarget which keeps the target row but nulls the reference.
• As long as the id remains the same, tables and fields can be renamed as you wish. Names should follow camelCase naming, with no symbols.
• Fields and tables can be removed, and the resulting data will be deleted.
• IMPORTANT: When editing schemas, changing the field types or the relationship type of the linked fields is currently not supported. For this, create a new field with a different name and id, and use a script to migrate the data.

An example schema is shown below:

{
    tables: [
        {
            id: "t",
            name: "task",
            pluralName: "tasks",
            fields: [
                {
                    id: "t1",
                    name: "description",
                    type: "text"
                },
                {
                    id: "t2",
                    name: "endDateActual",
                    type: "dateTime"
                },
                {
                    id: "t3",
                    name: "taskType",
                    type: "linkedField",
                    referencesMany: false,
                    targetTableId: "tt",
                    reverseFieldId: "tt2"
                },
                {
                    id: "t4",
                    name: "subTasks",
                    type: "linkedField",
                    referencesMany: true,
                    deleteBehaviour: "cascade",
                    targetTableId: "st",
                    reverseFieldId: "st2"
                },
                {
                    id: "t5",
                    name: "approved",
                    type: "boolean"
                },
                {
                    id: "t5",
                    name: "totalHoursWorked",
                    type: "number"
                },
                
            ]
        },
        {
            id: "tt",
            name: "taskType",
            pluralName: "taskTypes",
            fields: [
                {
                    id: "tt1",
                    name: "name",
                    type: "text"
                },
                {
                    id: "tt2",
                    name: "tasks",
                    type: "linkedField",
                    referencesMany: true,
                    targetTableId: "t",
                    reverseFieldId: "t3"
                }
            ]
        },
        {
            id: "st",
            name: "subTask",
            pluralName: "subTasks",
            fields: [
                {
                    id: "st1",
                    name: "startDate",
                    type: "dateTime"
                },
                {
                    id: "st2",
                    name: "task",
                    type: "linkedField",
                    referencesMany: false,
                    targetTableId: "t",
                    reverseFieldId: "t4"
                }
            ]
        }
    ]
}

A more detailed extended schema is at the bottom of this document, or in the example js file.