Introducing dORM: bringing SQL ORM and query building to Deno

Presented by Myounghan Chae, Hanji Chen, Myo Aung, and Nick Stillman

Meet dORM, an uber-lightweight postgreSQL query builder for Deno, currently being expanded into a full-fledged object-relational mapping (ORM) tool.

Some people love writing SQL queries, but let’s face it, some people don’t. SQL syntax can be intimidating, exhausting, and just plain no fun to write. It’s powerful, but requires extra cognitive load to transition away from thinking in Javascript/Typescript syntax in the middle of the development process.

dORM’s purpose is to make your life easier when making postgreSQL queries. It lets you write queries in familiar Javascript/Typescript syntax and dot notation, and offers flexibility you don’t have when writing raw SQL.

dORM runs in Deno, a secure runtime environment which supports Typescript out of the box and offers cloud-based package management among other great features. Although Node.js has a plethora of SQL query builders and ORMs, similar Deno-compatible tools remain few.

Got an example?

Sure! Let’s check out ORM in action. Imagine you have a JSON request from the client packed with objects containing key/value pairs corresponding to columns and values in one of your relational database tables. It might look something like this:

const bigArray = [{
    username:'Golden_Retreiver',
    password: 'golDenR',
    email:'iamagooddog@dogs.com'
  },
  {
    username:'Superman',
    email:'superman@superman.com',
    password:'IamnotHuman'   
  },
  {
    username:'MrBing',
    height: 178,
    isMarried: false,
    hasChildren: null
  }];

Oh boy. That’s a lot of data. To write a query that inserts this into your database’s “people” table, you’ll have to sort out the column names and values and integrate them into the query string.

On top of that, it’s best practice to parameterize your values, so that’s another chore to tackle. Wouldn’t it be nice if you could just plug this array into a Javascript method and be done with it?

Yes, yes it would. And dORM lets us do exactly that:

const inserted = await dorm
.insert(bigArray)
.into('users')
.returning();

dORM lines everything up, greatly simplifying the process of inserting multiple values into multiple columns of a table. And all values are automatically parameterized by dORM, so the final query string sent to the server looks like this:

`INSERT INTO users (username, password, email, height, isMarried, hasChildren) VALUES ($1, $2, $3, $4, $5, $6), ($7, $8, $9, $10, $11, $12), ($13, $14, $15, $16, $17, $18) RETURNING *`

How would you like to write that query string yourself? That’s right, you wouldn’t. Thanks dORM!

dORM will send an array of the values as the second argument to the Postgres server, safeguarding your queries against SQL-injection attacks.

You can chain our methods together, use .then() at the end of the query methods or simply await the results; you can even take advantage of Deno’s top-level await. dORM is promise-based and makes async database queries a breeze. It also handles creating the connection to the database server, using deno-postgres under the hood.

Going deeper into dORM…

Now that we’ve won you over, let’s take a deeper look at some of dORM’s features.

To set up dORM to work with your PostgreSQL database, from anywhere in your Deno project folder, you can run the following command in your terminal:

$deno run --allow-read --allow-write --allow-net --unstable deno.land/x/dorm/models/init.ts

dORM will create an .env file in your project’s root directory; you just need to put in your postgres connection string.

In your project, import the dORM query builder with:

import { Dorm } from etc etc;

If using a .env file, you can use config like so:

import { config } from 'https://deno.land/x/dotenv/mod.ts'

const env = config();

const URL = `postgres://${env.USERNAME}:${env.PASSWORD}@${env.SERVER}:5432/${env.USERNAME}`;

Now it’s time to instantiate the Dorm class:

const dorm = new Dorm(URL);

If you don’t use .env (we recommend using it!), you can of course pass your database connection string directly to Dorm.

And that’s it — you’re ready to start making queries!

Craving that CRUD functionality?

Did we mention that dORM methods work great with Deno’s top-level await? Like so:

try {
  const results = await dorm
  .select('name')
  .from('people')
  .where('_id=1');

console.log(results.rows);
} catch(e) {
  console.log(e);
};

The methods .from(), .into(), and .table() are all equivalent — in fact, dORM offers multiple aliases for many of its methods, letting you use syntax that suits you.

Query your way…

Currently you can put your dORM methods in whatever order you like. For example, you could put your .where() at the beginning of the chain. It’s all up to you! That said, we don’t know if this feature will last forever, so take advantage of it while you can!

dORM gives you a good night’s rest

Question: do you ever wake up from a nightmare in which you’ve forgotten to add a WHERE clause to your UPDATE query and you end up updating the whole table? Well, with dORM, you’ll sleep soundly knowing that we’ve got you covered. Our .update() method won’t work without a .where() attached. If you for some extravagant reason wanted to update your whole table in such a way, that’s fine: for your convenience and well-being, we’ve provided an .updateAll() method that requires (and accepts) no .where().

await dorm
.update({'username':'Dogs', 'email': 'iamnotagooddog@dogs.com'})
.table('users')
.where('_id = 10');

Similarly, dORM has .delete()and .deleteAll(). The .delete() method requires a .where() clause, .deleteAll() does not. And as an extra safeguard, if you do include a .where() with .deleteAll(), dORM will throw an error because it can read your mind and it knows you didn’t intend to do that.

await dorm
.delete()
.from('SuperImportantTable')
.returning();

dORM: “Nope!” ☝☝☝

Finally, there’s .drop() for deleting tables. Pass the table as an argument to .drop(), or use the .table() method or one of its aliases: .from() or .into(). Please proceed with caution!

await dorm
  .drop()
  .from('dropThis dropThisToo');

In the argument string, you can pass multiple table names separated by spaces.

Join us!

Now for the really fun stuff. dORM puts several join methods at your fingertips, each with an alias for flexibility.

Every .join() must have an accompanying .on() method. Here’s a sample usage:

await dorm
  .select()
  .from('people')
  .join('people_in_films')
  .on('people._id = people_in_films.person_id')
  .leftJoin('films')
  .on('people_in_films.film_id = films._id');

.on() takes a string argument that defines a condition for the .join(). Although it’s probably most common to put the .on() directly after the .join() it refers to, dORM allows you considerable leeway here. As long as the number of .on() methods equals the number of .join() methods, dORM is happy. It will pair them up in the order they appear, ie. the first on with the first join, second on with second join, etc.

Raw power…

Sometimes you just can’t or don’t want to use our chainable methods to access your database. We get it. For those funky queries that our methods don’t quite (yet) cover, we give you the dorm.raw() method. Pass in your query string and we will make the connection for you and send it off to the db server as-is. If you’ve parameterized your values — and of course you have! — you can pass your ordered values array as a second argument to .raw() and we’ll send that along too. This method also has aliases: .rawr() and .rawrr(), of course.

const values = [1, 'Bob'];
const results = await dorm.raw('SELECT * FROM people WHERE id = $1 OR name = $2', values);

toString or not toString, that is the query…

There might be times when you want to create a query, but don’t want to send it off to the database just yet. dORM has a couple of methods to help you with that.

A dORM query string is sent off to the database upon reaching a .then() in the chain, or an await. You can intercept the query string with the .toString() method, which returns just the string with the values parameterized (ie. ‘…WHERE id = $1’). If you already have the values handy, that’s great, but if you want the values array returned as well, the .toObject() (alias .toObj) method will return an object with properties text and values.

Example:

const querystring = await dorm.select().from('people').where('id = 1');

Returned: {
text: 'SELECT * FROM people WHERE id = $1',
values: [1]
}

We’ve got your values covered.

For .where() and .on() arguments, dORM will parse the argument and parameterize any string, number, boolean, or null values, adding each value to an array to send separately to the db server. One less thing for you to worry about!

Into the short future…

Models:

Our next order of business is to implement object relational mapping into dORM. This will let developers represent and access their relational database data completely in object-oriented form. dORM will use “model” classes to create a layer of model instances with methods for manipulating data and interacting with the database.

However, currently dORM has taken its first steps towards being a grown-up ORM, and can do some pretty great stuff. After you’ve supplied your PostgreSQL connection string in your .env file, if you run this command inside your project:

$deno run --allow-read --allow-write --allow-net --unstable https://deno.land/x/dorm/models/model-generator.ts

dORM will fetch all tables, columns, and primary key/foreign key relationships from your database and use them to generate model files. Not only is this useful for seeing an overview of your database, it will also be a key component for creating model classes to represent your data and its relationships.

Stay tuned for upcoming developments!

Feed us!

dORM is young and growing — and hungry! Please feed us your comments, questions, suggestions, criticisms, donations, and anything else that might guide us as we add features and refine our approach to query building and object relational mapping.

Have fun dORMing!