@next-model/data-api-connector

Connector for AWS Aurora Data API.

Usage no npm install needed!

<script type="module">
  import nextModelDataApiConnector from 'https://cdn.skypack.dev/@next-model/data-api-connector';
</script>

README

AWS Aurora Data API Connector

Data API connector for NextModel. Build Status

Allows you to use Knex as Database Connector for NextModel:

Supports:

  • AWS Data API for PostgreSQL
  • AWS Data API for MySQL (not tested)

Roadmap / Where can i contribute

See GitHub project for current progress/tasks

  • Fix Typos
  • CI is missing for some Databases
  • Add more examples
  • Add exists, join and subqueries
  • There are already some tests, but not every test case is covered.

TOC

Example

Create Connector

import DataApiConnector from '@next-model/data-api-connector';

const connector = new DataApiConnector({
  secretArn: 'arn:aws:secretsmanager:us-east-1:XXXXXXXXXXXX:secret:mySecret',
  resourceArn: 'arn:aws:rds:us-east-1:XXXXXXXXXXXX:cluster:my-cluster-name',
  database: 'myDatabase', // default database
});

Use Connector

The connector is used to connect your models to a database.

const User = class User extends NextModel<UserSchema>() {
  static get connector() {
    return connector;
  }

  static get modelName() {
    return 'User';
  }

  static get schema() {
    return {
      id: { type: 'integer' },
      name: { type: 'string' },
    };
  }
}

Create an base model with the connector to use it with multiple models.

function BaseModel<T extends Identifiable>() {
  return class extends NextModel<T>() {
    static get connector() {
      return new Connector<T>();
    }
  }
};

const User = class User extends BaseModel<UserSchema>() {
  static get modelName() {
    return 'User';
  }

  static get schema() {
    return {
      id: { type: 'integer' },
      name: { type: 'string' },
    };
  }
}

const Address = class Address extends BaseModel<AddressSchema>() {
  static get modelName() {
    return 'Address';
  }

  static get schema() {
    return {
      id: { type: 'integer' },
      street: { type: 'string' },
    };
  }
}

Build Queries

This connector uses Knex to query SQL databases, but the query syntax is different from the Knex documentation. Samples of possible queries are listed below.

Query

An object passed to query will filter for object property and value.

User.query({ name: 'foo' });
select "users".* from "users" where ("name" = 'foo')

If the Object has multiple properties the properties are connected with and.

User.query({ name: 'foo', age: 18 });
select "users".* from "users" where ("name" = 'foo' and "age" = 18)

An query connected with another query. A second query will encapsulate the query on the topmost layer.

User.query({ name: 'foo', age: 18 }).query({ name: 'bar' });
select "users".* from "users" where (("name" = 'foo' and "age" = 18) and ("name" = 'bar'))

And

Special properties are starting with an $ sign. The $and property connects all values which are passed as Array with an SQL and operator.

User.query({ $and: [{ name: 'foo' }] });
select "users".* from "users" where (("name" = 'foo'))
User.query({ $and: [{ name: 'foo' }, { age: 18 }] });
select "users".* from "users" where (("name" = 'foo') and ("age" = 18))

The special properties can also chained with other where queries.

User.query({ $and: [{ name: 'foo' }, { age: 18 }] }).query({
  $and: [{ name: 'bar' }, { age: 21 }],
});
select "users".* from "users" where ((("name" = 'foo') and ("age" = 18)) and (("name" = 'bar') and ("age" = 21)))

Or

The $or property works similar to the $and property and connects all values with or.

User.query({ $or: [{ name: 'foo' }] });
select "users".* from "users" where (("name" = 'foo'))
User.query({ $or: [{ name: 'foo' }, { name: 'bar' }] });
select "users".* from "users" where (("name" = 'foo') or ("name" = 'bar'))
User.query({ $or: [{ name: 'foo' }, { age: 18 }] }).query({ $or: [{ name: 'bar' }, { age: 21 }] });
select "users".* from "users" where ((("name" = 'foo') or ("age" = 18)) and (("name" = 'bar') or ("age" = 21)))

Not

The child object of an $not property will be inverted.

User.query({
  $not: {
    name: 'foo',
  },
});
select "users".* from "users" where (not ("name" = 'foo'))
User.query({
  $not: {
    name: 'foo',
    age: 18,
  },
});
select "users".* from "users" where (not ("name" = 'foo' and "age" = 18))
User.query({
  $not: {
    name: 'foo',
    age: 18,
  },
}).query({
  $not: {
    name: 'bar',
    age: 21,
  },
});
select "users".* from "users" where ((not ("name" = 'foo' and "age" = 18)) and (not ("name" = 'bar' and "age" = 21)))

Nesting

The $and, $or and $not properties can be nested as deeply as needed.

User.query({
  $not: {
    $or: [{ name: 'foo' }, { age: 21 }],
  },
});
select "users".* from "users" where (not (("name" = 'foo') or ("age" = 21)))
User.query({
  $not: {
    $and: [{ name: 'foo' }, { $or: [{ age: 18 }, { age: 21 }] }],
  },
});
select "users".* from "users" where (not (("name" = 'foo') and (("age" = 18) or ("age" = 21))))

Null

The $null property checks for unset columns and takes the column name as value.

User.query({ $null: 'name' });
select "users".* from "users" where ("name" is null)

NotNull

The $notNull property checks if an column is set and takes the column name as value.

User.query({ $notNull: 'name' });
select "users".* from "users" where ("name" is not null)

Equation

There are five different equation properties available.

  • $eq checks for equal
  • $lt checks for lower
  • $gt checks for greater
  • $lte checks for lower or equal
  • $gte checks for greater or equal

The property needs to be an object as value with the column name as key and the equation as value.

User.query({ $lt: { age: 18 } });
select "users".* from "users" where ("age" < 18)
User.query({ $lte: { age: 18 } });
select "users".* from "users" where ("age" <= 18)

Please note: Just one propery is allowed!

This is invalid:

User.query({
  $lt: {
    age: 18,
    size: 180,
  },
});

This is valid:

User.query({ $and: [{ $lt: { age: 18 } }, { $lt: { size: 180 } }] });
select "users".* from "users" where ("age" < 18 and "size" < 180)

In

The $in property needs an object as value with the column name as key and the Array of values as value.

User.query({
  $in: {
    name: ['foo', 'bar'],
  },
});
select "users".* from "users" where ("name" in ('foo', 'bar'))

Please note: Just one propery is allowed!

This is invalid:

User.query({
  $in: {
    name: ['foo', 'bar'],
    age: [18, 19, 20, 21],
  },
});

This is valid:

User.query({ $and: [{ $in: { name: ['foo', 'bar'] } }, { $in: { age: [18, 19, 20, 21] } }] });
select "users".* from "users" where ("name" in ('foo', 'bar') and "age" in (18, 19, 20, 21))

NotIn

$notIn works same as $in but inverts the result.

User.query({
  $notIn: {
    name: ['foo', 'bar'],
  },
});
select "users".* from "users" where ("name" not in ('foo', 'bar'))

Please note: Just one propery is allowed!

This is invalid:

User.query({
  $notIn: {
    name: ['foo', 'bar'],
    age: [18, 19, 20, 21],
  },
});

This is valid:

User.query({ $and: [{ $notIn: { name: ['foo', 'bar'] } }, { $notIn: { age: [18, 19, 20, 21] } }] });
select "users".* from "users" where ("name" not in ('foo', 'bar') and "age" not in (18, 19, 20, 21))

Between

The $between property needs an object as value with the column name as key and an Array with the min and max values as value.

User.query({
  $between: {
    age: [18, 21],
  },
});
select "users".* from "users" where ("age" between 18 and 21)

Please note: Just one propery is allowed!

This is invalid:

User.query({
  $between: {
    age: [18, 21],
    size: [160, 185],
  },
});

This is valid:

User.query({ $and: [{ $between: { age: [18, 21] } }, { $between: { size: [160, 185] } }] });
select "users".* from "users" where ("age" between 18 and 21 and "size" between 160 and 165)

NotBetween

$notBetween works same as $between but inverts the result.

User.query({
  $notBetween: {
    age: [18, 21],
  },
});
select "users".* from "users" where ("age" not between 18 and 21)

Please note: Just one propery is allowed!

This is invalid:

User.query({
  $notBetween: {
    age: [18, 21],
    size: [160, 185],
  },
});

This is valid:

User.query({ $and: [{ $notBetween: { age: [18, 21] } }, { $notBetween: { size: [160, 185] } }] });
select "users".* from "users" where ("age" not between 18 and 21 and "size" not between 160 and 165)

Raw

The $raw property allows to write custom and database specific queries. Pass queries as object, where key is the query and value are the bindings.

Note: See Knex documentation for more details about bindings.

User.query({
  $raw: {
    $query: 'age = ?',
    $bindings: 18,
  },
});
User.query({
  $raw: {
    $query: 'age = :age',
    $bindings: { age: 18 },
  },
});
select "users".* from "users" where ("age" = 18)

Changelog

See history for more details.

  • 1.0.0 2020-01-06 Initial Release