Using Posgres Together with MongoDB in Phoneix

I am writing a simple blogging application. I have the following relationship,

user -> blogs -> posts

user can have many blogs, and each blog can have many posts.

So far I been just working with Ecto and posgres, so far so good. What I plan to do is to have a rich media editor when people wanted to add a new post, and this can support images, video, text etc.

the problem is that you have a mixture of different content per post. I plan to use QuillJS as the front end interface for people to add content. Now the data comes out as something call QuillDelta which looks like this

var delta = new Delta([
  { insert: 'Gandalf', attributes: { bold: true } },
  { insert: ' the ' },
  { insert: 'Grey', attributes: { color: '#ccc' } }


now above is a simple example. in reality, each post array can have many items, all with different attributes, maybe images etc.

I want to store this “blob” coming from Quill in my backend, This is where the issue comes up, if its just strings, then no problem, I can just stuff it into a text column or something, but there could be binary data, such as images and audio, which I will upload to S3 and save a references.

Lets say the post delta is like 300 items in the array. I don’t really want to stuff them in 300 rows of a another table with relationship to the post itself. I feel like the retrival might be a bottle neck, especially if you scroll through continues posts, then the database will be hit heavily.

So my idea is to store the delta as a document, and write the whole thing into a mongoDB store.The delta serves as the actual post. Then when someone looks at a blog, the mongodriver will look up the blog id and fetch all of the posts and display them.

Is it possible to use MongoDB along side posgres? I know Ecto is not supported with the elixir mongo drive. I have used the elixir mongodrive before by itself, and Ecto with posgres by itself, just never together at the same time

Or is it better to just switch the entire thing with MongoDB, and ditch postgres entirely, since the postgres part only manages user and blog, and follower relationships.

It’s possible to have multiple repos, You don’t need to choose, You can use both, one repo with postresql, the other with mongodb.

It is also possible to store data in jsonb format for postgres, (not speaking of binaries).

I have been trying draft.js, which is also a rich editor, it uses json representation of data that could be saved as is in the db.

Although as @kokolegorille mentions there’s no problem in using two adapters, I’m curious as to what advantage you foresee in using Mongo instead of a Postgresql record with a JSONB field? Specially given that Ecto allows you to “type” JSONB (through embeds) and treat it as if it was a NOSQL element (while keeping the relational options and having typed jsonb keys/fields)?

i suppose one thing is I plan to have tags fields in the post itself, so I can do a cross post search in the entire post collection? or it allows me to do incremental updates to the post itself. I have not really worked with the jsonb column with postgres before. I figure if i go that route, then my post data would be myabe 2 columns

1 .blog_id
2. post_data

in which post data would have this massive blob worth of stuff, and it will be hard for me do operate on it. where as If i had it as a mongo document, then I can operate on the individual fields within the post

So, as long as it’s not 10 levels deep json you’re searching through, json is ok (and can be indexed as well). It’s not exactly as basic as indexing a column, but not much different either. The queries for specific jsonb fields also have their own operators, but you can easily use that syntax with Ecto’s fragments. But usually when I’m doing something with JSONB I have some column that is the primary key, like your blog_id, this would be the same as if you had a NOSQL document right? You would need a field to be the key of it? Then I use that key as the relational part. In your case it would probably mean you would have 2 records, one where you store the deltas as jsonb (either map or typed) and its pk is the foreign key to the “displayable” record, where you have a version that is already ready to be retrieved and displayed. Then you probably would need a function that when you accept a change to the “delta” record, it would compute the new “display” record and update it (probably both actions as part of a transaction so they don’t fall out of synch)

To work with the jsonb fields it depends, there’s embeds_many and embeds_one where you can “type” the document if you know for sure it’s always going to conform to a specific structure, but you can also use plain :maps. It looks like the deltas in this case would be such thing. You can give them unique ID’s too (to each item in the embeds_many) or make it keyless if you don’t need them to be uniquely identified (which I think is the case) and handle them as a list (collection) of items. Then you can either use cast_embeds or put_embeds to change its values. You can also mix and match, so you can have plain :maps (when you don’t know the form of the data, or have variable key names) without any type information as jsonb columns, or have a first level as an embed and keys inside this first levels as subsequent maps.

It might need some toying around for getting used to it, but I think it’s a very useful feature to use and learn.

For instance in one project I have this:

defmodule Duel do
  use Ecto.Schema

  schema "duels" do
    belongs_to :challenger, Player, foreign_key: :player_1
    belongs_to :duelist,    Player, foreign_key: :player_2

    embeds_one :player_1_grimoire, Grimoire
    embeds_one :player_2_grimoire, Grimoire

    field      :finished,   :boolean, default: false
    field      :type,       Duel.Type, default: :default
    field      :start_time, :utc_datetime
    field      :end_time,   :utc_datetime
    field      :public,     :boolean, default: true
    field      :password,   :string

    field      :winner,     :integer
    field      :result,     DuelGame.Finished.Reason

    embeds_one :game,       DuelGame, on_replace: :delete

    timestamps(type: :utc_datetime)


defmodule DuelGame do
  use Ecto.Schema

  defmodule VCTX do
    @enforce_keys [:active, :o]
    defstruct [:active, :o]

    @type t() :: %__MODULE__{
      active: :player_1 | :player_2,
      o: :player_1 | :player_2
  alias DuelGame.Players
  alias DuelGame.Status
  alias DuelGame.Context
  alias DuelGame.Aethermap
  alias DuelGame.Message
  alias DuelGame.Finished

  @blood_invocation "blood_servant"

  @derive {Jason.Encoder, except: [:vctx, :messages]}
  @primary_key false
  embedded_schema do
    embeds_one  :players,          Players, on_replace: :delete
    embeds_one  :player_1,         Context, on_replace: :delete
    embeds_one  :player_2,         Context, on_replace: :delete
    embeds_one  :status,           Status,  on_replace: :delete
    field       :duel_id,          :integer
    embeds_many :messages,         Message, on_replace: :delete
    field       :msgs,             {:array, Message}, virtual: true
    field       :sounds,           {:array, :string}, default: []
    field       :last_action,      :integer
    field       :message_count,    :integer, virtual: true
    field       :blood_invocation, :string
    field       :vctx,             :map, virtual: true
    embeds_one  :finished,         Finished, on_replace: :delete

defmodule DuelGame.Status do
  use Ecto.Schema
  @derive Jason.Encoder

  @primary_key false
  embedded_schema do
    field :turn,              :integer,            default: 1
    field :phase,             DuelGame.Phase
    field :player_turn,       DuelGame.Player
    field :player_active,     DuelGame.Player
    field :clean_battle,      :boolean,            default: false
    field :strike_able,       {:array, :string},   default: []
    field :guard_able,        :map,                default: %{}
    field :striking,          :map,                default: %{}
    field :guarding,          :map,                default: %{}
    field :triggered_effects, DuelGame.TriggerMap, default: %{}
    field :global_effects,    DuelGame.GlobalMap,  default: %{}

    embeds_many :stack,       DuelGame.Stack,      on_replace: :delete

So if you’re always working with the full list of deltas, then it’s as easy as using put_embeds with the list. Or just update the field if it’s a :map.


O wow thats is very detailed answer! Thank you for taking the time to answer it. I will definitely play around to see what I can do with the deltas and searchs. I’d imagine its not as complex as the duel game you have shown here. I did not realize you can have en embedded schema that maps to a json internally. Thank you for that.