Needle - Universal foreign keys, shared data fields, virtual and configurable schemas for Ecto

Your Libraries & OS Mentoring Libraries
, , , , , , ,
1

When using a relational DB (such an Ecto schema with a Postgres table), usually a foreign key field has to be pre-defined with a reference pointing to a specific field in a specific table.

A simple example would be a blogging app, which might have a post table with author field that references the user table.

A social network, by contrast, usually requires a graph of objects, meaning objects need to be able to refer to other objects by their ID without knowing their type.

A simple example would be likes, you might have a likes table with liked_post_id field that references the post table. But what if you don’t just have posts that can be liked, but also videos, images, polls, etc, each with their own table, but probably do not want to have to add liked_video_id, liked_image_id, ad infinitum?

We needed the flexibility to have a foreign key that can reference any referenceable object. We call our system Needle.

Note: This library was developed as part of the Bonfire project, which has some open bounties for any help improving performance:.

Besides regular schemas with universal foreign keys (Pointable), Needle provides Virtual for schemas that don’t have any fields on their own, and Mixin for storing common fields that can be re-used by multiple Pointables or Virtuals (eg. if you have multiple types that all have a name/description/body, they can share those and optionally the associated changesets and other logic).

Needle also comes with two other homegrown libraries: needle_ulid which provides a ULID datatype for Ecto (using ex_ulid) and related helpers, and exto which enables extending Ecto schema definitions in config (especially useful for adding Mixin associations to different schemas)…

7 Likes
2

Your docs are very thorough and well-written :slight_smile: If I had to offer one suggestion there, I think some query examples would be helpful near the end - perhaps some real-world examples from Bonfire? But really, overall excellent docs.

Also, is there a reason you chose to go all in on ULID instead of UUIDv7, which is now standard? I assume you guys have probably been at this since before UUIDv7 was standardized, so perhaps it was too late to change?

I mention it because it probably wouldn’t be very hard to switch over since they’re both 128 bit binaries in the DB at the end of the day and have a pretty similar structure (timestamp + random). Generating a UUIDv7 in Elixir is like three lines of code, and then you can just delegate all the Ecto.Type functions over to Ecto.UUID since they’re compatible anyway.

For anyone coming across this post who’s curious, the “correct” way to do this in an RDBMS is to create a parent table (e.g. likeable) and then give each “child” table a foreign key to the parent table (e.g. likeable_id on posts). Then a user can have a like which joins through likeable to the post. This way the new foreign key columns always end up on the new table.

Indeed, if I understand correctly the above is actually exactly what this project is doing, except with lots of tooling to generalize it to the entire schema, which is cool!

2 Likes
3

Thanks for the feedback! I’ll look for some simple query examples to add to the docs.

Regarding UUIDv7, yeah we chose ULID before it was an option, and actually have an open issue about migrating to it (and at the same time adopting prefixed “object ids”), feedback welcome: Consider using prefixed UUIDv7 instead of ULID · Issue #941 · bonfire-networks/bonfire-app · GitHub

4

Here are a few examples of schema that will finally be.

-- Parent table for all likeable content
CREATE TABLE likeable (
    id SERIAL PRIMARY KEY,
    content_type VARCHAR(50) NOT NULL
);

-- Posts table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    likeable_id INTEGER UNIQUE NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (likeable_id) REFERENCES likeable(id)
);

-- Videos table
CREATE TABLE videos (
    id SERIAL PRIMARY KEY,
    likeable_id INTEGER UNIQUE NOT NULL,
    url VARCHAR(255) NOT NULL,
    duration INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (likeable_id) REFERENCES likeable(id)
);

-- Images table
CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    likeable_id INTEGER UNIQUE NOT NULL,
    url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (likeable_id) REFERENCES likeable(id)
);

-- Polls table
CREATE TABLE polls (
    id SERIAL PRIMARY KEY,
    likeable_id INTEGER UNIQUE NOT NULL,
    question TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (likeable_id) REFERENCES likeable(id)
);

-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Likes table
CREATE TABLE likes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    likeable_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (likeable_id) REFERENCES likeable(id),
    UNIQUE (user_id, likeable_id)
);

This is a rough idea. Feel free to correct it this is wrong.

Sample queries -

  1. Inserting a new post and making it likeable:
-- Insert a new likeable entry
INSERT INTO likeable (content_type) VALUES ('post') RETURNING id;
-- Let's say this returns id 1

-- Insert a new post
INSERT INTO posts (likeable_id, content) VALUES (1, 'This is a sample post');
  1. A user liking a post:
-- Assuming user_id 1 wants to like the post we just created
INSERT INTO likes (user_id, likeable_id) VALUES (1, 1);
  1. Retrieving all likes for a specific post:
SELECT u.username, l.created_at
FROM likes l
JOIN users u ON l.user_id = u.id
JOIN posts p ON l.likeable_id = p.likeable_id
WHERE p.id = 1;
5

With Needle, we don’t need a separate Likeable schema because all Pointables or Virtuals can be likeable by default. Let’s revise these examples to reflect how Needle can be used.

Note: this is a slightly edited version of the result of feeding Claude.AI with the above sample migrations and queries and the Needle and Exto readmes. It looks correct at a glance but please consider it pseudocode and read the docs if you want to implement something like this.

Here’s those same schemas as you might define them with Needle:

defmodule YourApp.Post do
  use Needle.Pointable,
    otp_app: :your_app,
    table_id: "P0STSC0NTENTF0RV1EW1NG000",
    source: "posts"

  pointable_schema do
    field :title, :string
    field :summary, :string
    field :content, :string
  end
end

defmodule YourApp.Video do
  use Needle.Pointable,
    otp_app: :your_app,
    table_id: "V1DE0C0NTENTF0RV1EW1NG000",
    source: "videos"

  pointable_schema do
    field :duration, :integer
  end
end

defmodule YourApp.Image do
  use Needle.Virtual, # use virtual here because schema has no fields, and uses URI mixin instead
    otp_app: :your_app,
    table_id: "1MAGEC0NTENTF0RV1EW1NG000",
    source: "images"

  virtual_schema do
  end
end

defmodule YourApp.Poll do
  use Needle.Pointable,
    otp_app: :your_app,
    table_id: "P011C0NTENTF0RV1EW1NG000",
    source: "polls"

  pointable_schema do
    field :question, :string
  end
end

defmodule YourApp.User do
  use Needle.Pointable,
    otp_app: :your_app,
    table_id: "VSERSYS1NGTHESYSTEM00000",
    source: "users"

  pointable_schema do
    field :username, :string
    field :email, :string
  end
end

defmodule YourApp.Like do
  use Needle.Pointable,
    otp_app: :your_app,
    table_id: "11KES0NBJECTSBYVSERS0000",
    source: "likes"

  pointable_schema do
    belongs_to :user, YourApp.User
    belongs_to :liked, Needle.Pointer
  end
end

# Example of a mixin that can be used across different types of content
defmodule YourApp.URI do
  use Needle.Mixin,
    otp_app: :your_app,
    source: "uri_mixin"

  mixin_schema do
    field :uri, :string
  end
end
  • We’ve removed the separate Likeable schema.
  • All Pointable or Virtual schemas (Post, Video, Image, Poll) can now be liked without needing a specific Likeable association.
  • The Like schema now has a liked field that references Needle.Pointer, allowing it to like any Pointable object.
  • We’ve added a URI mixin to show how mixins can be used to add shared fields across different types of content.

Now, let’s update our query examples to show how to use these schemas with Needle:

  1. Creating a new post:
defmodule YourApp.Posts do
  alias YourApp.{Repo, Post}

  def create_post(content, title, summary) do
      %Post{
      	content: content,
        title: title,
        summary: summary}
      |> Repo.insert()
  end
end
  1. Creating a like:
defmodule YourApp.Likes do
  alias YourApp.{Repo, Like}

  def record_like(user_id, liked_id) do
    %Like{
      user_id: user_id,
      liked_id: liked_id
    }
    |> Repo.insert()
  end
end
  1. Querying likes for a specific object:
defmodule YourApp.Likes do
  import Ecto.Query
  alias YourApp.{Repo, Like}

  def list_object_likes(object_id) do
    from(l in Like,
      where: l.liked_id == ^object_id
    )
    |> preload([:user])
    |> Repo.all()
  end
end

Next we can also add useful associations in config using Exto. Add the following to your config.exs:

config :your_app, YourApp.Post,
  has_many: [
    likes: {YourApp.Like, foreign_key: :liked_id}
  ]

Listing likes for a post using the association:

defmodule YourApp.Posts do
  import Ecto.Query
  alias YourApp.{Repo, Post}

  def list_post_likes(%Post{} = post) do
    post
    |> Repo.preload(likes: [:user])
  end

  def list_post_likes(post_id) when is_binary(post_id) do
    Post
    |> Repo.get(post_id)
    |> list_post_likes()
  end
end
1 Like