Database Review: Top Five Missing Features from Database APIs

For the moment in time, there are no perfect query APIs. However, I think when one API decides to support all of these features, the dev experience will change dramatically. These are all extremely common use cases, and ignored by API creators as nonessential, sometimes for years. For this article, I will be referring to Database APIs in common Database Platforms.

• Hasura (GraphQL)
• Firebase (Firestore, Auth, Rules)
• Supabase (postgREST, Auth)
• 8base (GraphQL)
• Dgraph (GraphQL, DQL)
• MongoDB (Mongoose)
• Prisma (ORM)
• Neo4j (GraphQL)
• Appwrite (REST, GraphQL)
• Grafbase (GraphQL)
• Fauna (FQL SDK, GraphQL)

5. Realtime Updates

Firebase set the standard for this, and now everyone wants it. Adding chat to our apps, notifications, and real time changes can be tedious to build, but when it is built into an API, it can save days of work. What makes this difficult is having a secure client side subscription mechanism. Third Party does not count.

• Hasura ✅
• Firebase ✅
• Supabase ✅ (limited queries)
• 8base ✅
• Dgraph ✅
• MongoDB ❌
• Prisma ❌ (third party), depreciated in V1!
• Neo4j ❌ (third party)
• Appwrite ✅
• Grafbase ❌
• Fauna ✅

Workaround

• Firebase (use a second database for realtime part)
• Ably
• Pusher
• Kafka (most complex)

4. Authentication

Only a few of these have a built in authentication system, but that doesn't mean third party apis are hard to integrate otherwise.

• Hasura ✅❌ (technically yes with Nhost)
• Firebase ✅
• Supabase ✅
• 8base ❌
• Dgraph ❌
• MongoDB ❌
• Prisma ❌ (third party)
• Neo4j ❌ (third party)
• Appwrite ✅
• Grafbase ❌
• Fauna ❌

Workaround

• AuthJS - still not sure why NextAuth and AuthJS are still separate things? Combine them please, including the website. This is confusing!
• Firebase Auth
• Clerk - not free
• Auth0 - not free
• Magic - not free

3. Authentication (Security, Roles, and Constraints)

• Hasura ✅ (dashboard)
• Firebase ✅ (rules)
• Supabase ✅ (postgres RLS, postgres roles)
• 8base ✅ (dashboard)
• Dgraph ❌✅ (@auth GraphQL, limited until end of year, no constraints)
• MongoDB ❌
• Prisma ❌
• Neo4j ✅ (@auth GraphQL)
• Appwrite ✅ (permissions created on document create)
• Grafbase ✅ (rules in sdk)
• Fauna ✅

Workarounds

• Postgres Row Level Security (RLS) - Finding integrations with Prisma or others may be tough. Post a link in comments if you know how.
• Valibot (good for types and constraints)
• Add filters to your queries, custom views, functions, pre-triggers

2. Sort By Nested Column and Nested Aggregates

I am showing examples here to get the full understanding. These seem like complicated queries, but are needed in any non-basic application. The GraphQL listed here is just an example, as GraphQL is unfortunately not standardized in its implementations.

Example 1:

I want to sort all posts by the latest comment. This is different from sorting the latest comments inside each post.
graphql

query {
  posts(orderBy: { 
    field: "comments.createdAt",
    direction: "DESC"
  }) {
    id
    title
    content
    comments {
      id
      text
      createdAt
    }
  }
}

sql

SELECT p.*
FROM posts p
JOIN comments c ON p.id = c.post_id
GROUP BY p.id
ORDER BY MAX(c.created_at) DESC;

Example 2:

I want to get the top posts. This would require me sorting the posts by the number of likes each post has.
graphql

query {
  posts(orderBy: {
    field: "hearts.count",
    direction: "DESC"
  }) {
    id
    count
  }
}

sql

SELECT p.*
FROM posts p
LEFT JOIN hearts h ON p.id = h.pid
GROUP BY p.id
ORDER BY COUNT(h.pid) DESC;

As you can see, these kinds of queries are represented easily in GraphQL. Thinking about GroupBy is way more complicated.:

• Hasura ✅
• Firebase ❌
• Supabase ❌
• 8base ✅
• Dgraph ❌
• MongoDB ❌
• Prisma ✅
• Neo4j ❌
• Appwrite ❌
• Grafbase ❌
• Fauna ✅ (FQL, not GraphQL)

Workarounds

• In SQL or a Graph Database, you create custom Views or Functions that are visible to the API level like so:

CREATE OR REPLACE FUNCTION top_posts()
RETURNS SETOF posts AS $$
BEGIN
  RETURN QUERY 
    SELECT p.*
    FROM posts p
    LEFT JOIN hearts h ON p.id = h.pid
    GROUP BY p.id
    ORDER BY COUNT(h.pid) DESC;
END;
$$ LANGUAGE plpgsql;

In noSQL or SQL you could also use a computed column. This is usually computed upon a write operation:

db.collection('posts').orderBy('likes, 'desc');

1. Nested Mutations (Write / Update)

This is the granddaddy of all missing features that to me is one of the most useful. Let's say you want to add tags with a post.

{
  title: 'Back to the Future'
  created_at: '1985',
  tags: ['movie', 'sci-fi', 'delorean'],
  ...
}

Usually your tags is a different table / node so that it can be queried separately. There are two basic possible SQL schemas:

CREATE TABLE posts (
  id uuid,
  ...
);
CREATE TABLE tags
  id uuid,
  name text
);
CREATE TABLE post_tag (
  post_id uuid REFERENCES posts(id),
  tag_id uuid REFERENCES tags(id),
  PRIMARY KEY (post_id, tag_id)
);

Or you may see a simplified version:

CREATE TABLE posts (
  id uuid,
  ...
);
CREATE TABLE tags (
  post_id uuid REFERENCES posts(id),
  name text,
  PRIMARY KEY (post_id, name)
);

• Hasura ✅❌ - Insert but not Update
• Firebase ❌
• Supabase ❌
• 8base ✅
• Dgraph ✅❌ - Insert but not Update
• MongoDB ❌
• Prisma ✅
• Neo4j ✅
• Appwrite ❌
• Grafbase ❌
• Fauna ✅ (FQL, not GraphQL)

None of these APIs can handle all cases, as they all have limitations.

Workaround

For Graph and SQL Database, you need to make a custom function to insert the post, and another function to update the post. This gets complicated too as you may have options. Do you want to replace all tags with the new tags (delete and then insert), or do you want to just add new tags? Some GraphQL implementations use a create or update keyword. However, I think this should be a constraint on the schema level similar to cascade delete. You often see the ability to connect and disconnect nodes. This is useful if you don't want to delete a zipcode in a zip codes table for example. I may write a whole article about this and link it here later...

This is just an example insert, doesn't account for the ID etc...

DROP FUNCTION IF EXISTS insert_post;
CREATE OR REPLACE FUNCTION insert_post(post posts)
RETURNS posts
LANGUAGE plpgsql
AS $$
DECLARE
  new_post posts%ROWTYPE;
BEGIN

  -- Insert a new post
  INSERT INTO posts (
      title,
      content,
      published_at
  )
  VALUES (
      post.title,
      post.content,
      post.published_at        
  )
  RETURNING * INTO new_post;

  -- Insert new tags with the post
  INSERT INTO tags (name, pid)
  SELECT unnest(post.tags), new_post.id;

  -- Return the new post
  RETURN new_post;

END;
$$;

The other obvious work around is to use 3rd party APIs:

• Drizzle ORM
• Kysely

But these are really just other ways to write sql functions. When these support RLS, I'm in.

In noSQL you are usually using a batch to insert the data in two different places simultaneously:

// add the post document
const batch = db.batch();
const newPostRef = db.collection('posts').doc();
batch.set(newPostRef, postData);

// repeat for each tag...
const newTagRef = db.collection('tags').doc();
batch.set(newTagRef, { name: tag });
await batch.commit();

Look at more complicated noSQL schemas like the follower feed.

Summary in Order

I think Prisma has the most features, then Hasura, then 8base, then Neo4j. However, if you want to actually have security, Prisma is out. The best all around platforms are Supabase, Firebase, Hasura (with Nhost), then 8Base. The best for real time is Firebase, Hasura, Supabase, 8Base, then Appwrite. I'm personally bullish on Supabase. Fauna (with FQL) is extremely powerful as well, but I have no experience with it.

Feel free to comment if you want me to add other database APIs worth looking into.

This could literally change in 6 months. Also be on the lookout for:

• EdgeDB
• SurrealDB
• TypeDB

These are databases, but their platform APIs could be considered in the future.

How to help...

For the database platforms, let them know what features you use. The more money you give for cloud services, the more say you have. Upvote Github issues for feature requests. Contribute to the core code bases (if possible). Post work arounds on blogs like this one.

I am very partial to Supabase for the moment, so I suggest you support postgREST on Patreon to support Steve Chavez.

Check out my Perfect Database in 2022 post from earlier.

Other than that, if you're creating an ORM, API, Query Language, GraphQL, REST endpoint, or even an RPC, let's add these features!

J

Code.Build - Currently rebuilding!

Note: If you see any mistakes, let me know in comments and I will promptly update!

Comments

[Michael Tharrington]

Nice, helpful post here! Thanks for sharing Jonathan. 🙌