In a relational database, foreign keys are normally used to associate records stored in different tables. Let’s say we want to add a forum to our website where people can discuss matters related to our product. To keep things simple, we will model our forum with just three tables: user, post and topic. The relationships between the tables can be modeled by asking a few questions:

  • Who wrote a given post? post.user_id references user.id.
  • Which posts are under a given topic? post.topic_id references topic.id.
  • Who started a given topic? topic.user_id references user.id.

Using foreign keys to associate tables for a simple forum. Using foreign keys to associate tables for a simple forum.

This is all well and good, but each relationship requires adding a new column to a table, and we don’t even have many-to-many relationships in this example, which will necessitate creating additional tables.

Wouldn’t it be nice to define relationships dynamically without having to add extra columns or tables? And while we’re at it, how about having sparse relationships by associating a record directly with any other record like “post X was last edited by user #123” or “post X was flagged for review by user #456” (who happens to be a moderator)?

With that idea in mind, I’ve created Tie-in, a relational data component library for Node.js. Tie-in lets you store and query records that can be related to any other record.

Let’s create a user, a post, and a topic by using data components (more on how to define them later) in the relational database of our choice (in this case PostgreSQL):

const dbConfig = {
    client: 'pg',
    connection: 'postgresql://localhost/me'
};

// Load Tie-in and data component definitions
const tie = require('tie-in')(dbConfig);
const user = require('./components/user')(tie);
const post = require('./components/post')(tie);
const topic = require('./components/topic')(tie);

async function firstSteps() {
    // Register the components we will use
    await tie.register([user, post, topic]);

    // Create a user named Asuka
    const userId = await tie.create(user({ username: 'Asuka', email: 'asuka@localhost', country: 'JP' }));

    // Create a post and make its author Asuka
    const postId = await tie.create(post({ content: 'Hi!' }), {
        upstream: [user({ id: userId, relType: 'author' })],
    });

    // Create a topic and make the topic starter Asuka, also make the post a child of this topic
    const topicId = await tie.create(topic({ title: 'First Topic' }), {
        upstream: [user({ id: userId, relType: 'starter' })],
        downstream: [post({ id: postId, relType: 'child' })],
    });

    // Retrieve topic and related records
    const topicRecs = await tie.get(topic({ id: topicId }));
    console.log(JSON.stringify(topicRecs, null, 2));
}

firstSteps();

Once the individual records are in place, we retrieve the newly created topic with tie.get, which recursively retrieves all related records and groups them together. In Tie-in, related records can be upstream (referencing a record) and/or downstream (referenced from another record). Related records can optionally have types specified by relType.

{
  "topic": [
    {
      "self": {
        "id": 1,
        "title": "First Topic",
        "createdAt": "2024-03-28T12:27:51.542Z",
        "updatedAt": "2024-03-28T12:27:51.542Z"
      },
      "user": [
        {
          "self": {
            "relType": "starter",
            "id": 1,
            "username": "Asuka",
            "email": "asuka@localhost",
            "country": "JP",
            "createdAt": "2024-03-28T12:27:51.531Z",
            "updatedAt": "2024-03-28T12:27:51.531Z"
          }
        }
      ],
      "post": [
        {
          "self": {
            "relType": "child",
            "id": 1,
            "content": "Hi!",
            "createdAt": "2024-03-28T12:27:51.538Z",
            "updatedAt": "2024-03-28T12:27:51.538Z"
          }
        }
      ]
    }
  ]
}

Defining Components

To define a component, you call tie.define with the following arguments:

  • name: Name of the component
  • schema: A function that defines the database table schema. Tie-in uses knex under the hood. Table field names must be in snake_case for maximum compatibility across different database systems. Tie-in does the snake_case to camelCase conversions and vice versa automatically.
  • data: A function that accepts an object with field names in camelCase, maps those fields to the database table fields created with schema, and returns the resulting object.

In its simplest form, a component definition should look like the example below. The only requirement is that there must be a field named id that uniquely identifies each record.

module.exports = (tie) => {
    const name = 'post';

    async function schema(knex, tablePrefix) {
        const tableName = tablePrefix + name;
        if (!(await knex.schema.hasTable(tableName))) {
            return knex.schema.createTable(tableName, function (table) {
                table.increments('id').primary();
                table.text('content').notNullable();
                table.timestamps(false, true);
            });
        }
    }

    function data(input) {
        return {
            id: input?.id,
            content: input?.content,
            created_at: input?.createdAt,
            updated_at: input?.updatedAt,
        };
    }

    return tie.define(name, schema, data);
};

You might probably want to validate the input passed to the data function. Tie-in uses fn-arg-validator for internal data validation, and exposes it through tie.is, but you can of course use any other library you would like.

tie.get

Syntax: tie.get(comp, filters = {})

When you pass a component instance (comp) to tie.get, it uses the component instance’s data for search. Here are some examples:

  • await tie.get(user( {country: 'JP'} )) returns the users from Japan.
  • await tie.get(user( {country: 'JP', username: 'Asuka'} )) returns the users from Japan having the username ‘Asuka’.
  • await tie.get(user()) returns all users.

tie.get supports a wide range of options to filter the results returned. For example:

async function getPostCountsGroupedByUser(topicId) {
    const filters = {
        aggregate: [{ fn: 'count', args: '*' }],
        group: { by: user(), columns: ['id', 'username'] },
        filterUpstreamBy: [topic({ id: topicId })],
        orderBy: [{ column: 'username', order: 'asc' }],
        limit: 20,
    };
    return tie.get(post(), filters);
}

For a complete list of the available functions and options, you can visit Tie-in’s GitHub page.

How is this Different than an ORM?

Tie-in isn’t an ORM (Object-Relational Mapper). In fact, Tie-in’s data component instances are immutable – once set, you can’t change the values a component instance holds.

Tie-in components have embedded schema definitions. In a sense, they are self-contained.

Tie-in doesn’t suffer from the worst performance issues that plague various ORMs such as the infamous N+1 query problem. Tie-in also tries its best to reduce the number of queries it generates by merging related queries together.

Can I still use Foreign Keys?

Absolutely. There’s nothing preventing you from using them. Foreign keys are great! Tie-in doesn’t replace foreign keys, but supplements them.

Fun with Relationships

In my opinion, Tie-in makes it fun to write database-level code by removing the need to define relationships for anything and everything. You can easily have an ad-hoc relationship between two records and nothing else.

Tie-in’s recursive record retrieval functionality makes it almost too easy to get the data you need without having to worry about all the joins, and dare I say, it’s a little like magic.

Links:

Discussions:

Related: