quervana

a low-commitment, dynamic ORM for SQL Server

In most ORMs, out-of-the-box “magic” comes at a price. Some require you to use entire frameworks, build your database model in a very particular way, or use the tool itself to completely manage your database. This makes them questionable choices for many production applications, especially when needing to integrate with an existing database or database resources. Quervana provides a succinct object-relational mapping service for SQL server with low overhead, low commitments, but a nice amount of magic.

quervana key features:

1. Automatic operation batching, making it perfect for GraphQL; no more clunky loader patterns
2. Automatic parameter-to-type matching, meaning no need to pass parameter types to each execution
3. Automatic object-relational mapping that you can override to match your existing sql and javascript conventions, so it can work with any MSSQL database
4. Simple and succinct language that builds dynamic parameterized queries
5. Flexibility to execute custom SQL

Setting up

First, let's import the core components of the library. const { initQuervana, createPool, sqlTypes } = require('quervana');

initQuervana(pool, types, schemaName, translator) takes 4 arguments: a pool, a types object, a schema name, and a translator object.

The pool can be created using createPool like this: const pool = await createPool(config) where the config looks like this:

{
    server: 'mydatabase.database.windows.net',
    database: 'mydb',
    user: 'user',
    password: 'greatpassword',
    options: {
      ...{ optional parameters per mssql docs }
}

Your schema name is the name of the schema that your entities (table names) belong too. If you need to switch schemas, you will need to call initQuervana again with your original arguments but your new schema name.

The translator object is an object with two functions: objToRel and relToObj. These functions take care of the mapping between your database names and your code names, making it possible for you to integrate with existing databases without enforcing any particular naming convenctions. For example, the default translator assumes the names in your database are snake case, like my_snake_case_table, whereas the code is camel, so we want it to look like mySnakeCaseTable. Our translator will look like this:

{
    objToRel: (str) => str.split(/(?=[A-Z])/).join('_').toLowerCase(),
    relToObj: (str) => str.replace(/_([a-z])/g, g => g[1].toUpperCase())
}

You can use these functions to handle exceptions-to-the-rules as well.

Finally, the types object is a little tedious; SQL Server needs to know the types of the fields on your entities. For example, if we have this table:

CREATE TABLE animal (
      id integer IDENTITY CONSTRAINT pk_animal PRIMARY KEY NOT NULL,
      name VARCHAR(50),
      zoo_keeper_id integer REFERENCES zoo.zoo_keeper (id)
  );

we'll need to add this to our types object:

animal: {
    id: sqlTypes.int,
    name: sqlTypes.varChar(50)
    zooKeeper: sqlTypes.int
}

The sqlTypes object is imported from the library. Here are the supported types:

  bit: Bit,
  bigInt: BigInt,
  decimal: (precision, scale) => Decimal(precision, scale),
  float: Float,
  int: Int,
  money: Money,
  numeric: (precision, scale) => Numeric(precision, scale),
  smallInt: SmallInt,
  smallMoney: SmallMoney,
  real: Real,
  tinyInt: TinyInt,
  char: (length) => Char(length),
  nChar: (length) => NChar(length),
  text: Text,
  nText: NText,
  varChar: length => VarChar(length),
  nVarChar: length => NVarChar(length),
  xml: Xml,
  time: scale => Time(scale),
  date: Date,
  dateTime: DateTime,
  dateTime2: scale => DateTime2(scale),
  dateTimeOffset: scale => DateTimeOffset(scale),
  smallDateTime: SmallDateTime,
  uniqueIdentifier: UniqueIdentifier,
  variant: Variant,
  binary: Binary,
  varBinary: length => VarBinary(length),
  image: Image,
  udt: UDT,
  geography: Geography,
  geometry: Geometry

If a type is missing from your object, quervana will try and infer it, but note this will not always work (null values), and the explicit column definitions provide an additional layer of security.

Batching

All calls to quervana.get in a single tick of the event loop will be batched into atomic gets by entity and key. This uses Facebook’s dataloader under the hood, so if you are familiar with it, you can think of it as a dynamic one-size-fits-all loader.

Using the library

Because this library makes batching super easy, it's great for GraphQL. Let's attach a quervana instance to our context object. Note that we'll want to create a new instance for each request, as this instantiates a new loader instance as well.

const server = new ApolloServer({
  typeDefs,
  resolvers,
  context: async () => ({ quervana: initQuervana(pool, types, schemaName, translator ) }),
});

We can now really easily query entities!

Getting

A simple get:

const Query = {
  zooKeeper: async (obj, { id }, { quervana }) => quervana.get({ entity: 'zooKeeper', where: 'id', is: id }),
};

If you're expecting an array of entities back, add multiple = true to your query. Otherwise, we return the first instance:

const Habitat = {
  animals: async ({ id }, args, { quervana }) => quervana.get({ entity: 'animal', where: 'habitatId', is: id, multiple = true }),
};

Remember, all of these gets are being batched together for out-of-the-box efficiency!

Get all of an entity:

const Query = {
  animals: async (obj, { id }, { quervana }) => quervana.getAll({ entity: 'animal' }),
};

Calls to 'getAll' are currently not being batched, though they should be.

Inserting

A single insertion looks like this. The keys of the input should directly map to fields on your entity table per the specs of your translator:

const Mutation = {
  createAnimal: async (obj, { input }, { quervana }) => quervana.insert({ entity: 'animal', input }),
};

We can insert many like this, where inputArray is an array of inputs:

const Mutation = {
  createAnimals: async (obj, { input }, { quervana }) => quervana.insertMany({ entity: 'animal', inputArray }),
};

Deleting

To hard-delete, use the following:

const Mutation = {
  deleteAnimal: async (obj, { id }, { quervana }) => quervana.remove({ entity: 'animal', where: 'id', is: id }),
};

Updating

const Mutation = {
  updateAnimal: async (obj, { input: { payload, id } }, { quervana }) => quervana.update({
    entity: 'animal', input: payload, where: 'id', is: id,
  }),
};

Custom sql execution

We can use quervana.executeSql to run custom sql! It takes an object with 3 fields:

quervana.executeSql({ queryString, params, paramTypes });

The query string is our custom query.

params will be our input, like { name: 'George' } paramTypes describes the types of the input fields, like { name: sqlTypes.varChar(50) }

We can use a handy method on the quervana object called getParamTypes which will return the types as shown above:

const types = getParamTypes({ entity: 'animal', params: { name: 'George' }})

Examples

See the test.js file to see some basic usage.

Testing & Contributing

To run tests, use the command npm test. Note you will need Docker and docker-compose to run these. You will need to create an .env file with the env.example parameters in the top of the package. Feel free to make changes and let me know if you have feedback, code contributions, or found any bugs!

Anyway that's about it! Let me know if you found this useful or not.