Beyond SQL: A relational database for modern applications

• Connection centric: SQL's session-based transaction model is maladapted to modern cloud computing abstractions such as serverless, and is difficult to manage at scale.
• Inflexibility of result structure: SQL is particularly inflexible in terms of control over response: A query result is always a set of tuples. A query is incapable of returning a rich structure that is aligned with how the application needs to consume the result. ORMs solve this partially at best at the cost of hiding the capabilities of the underlying database. They also obscure the structure of generated queries, making it difficult to understand the resulting performance implications.
• Complicated, irregular syntax: As a language, SQL's syntax is complex, unpredictable, and allows only limited forms of composition. Newcomers find that it is unlike any other language they are already working with. As a result, while most developers wind up with a passing familiarity, few end up comprehensively learning SQL beyond simple SELECTs and JOINs.
• Opaque, unpredictable performance: SQL query performance is opaque and dependent on an optimizer to make the right decision. The developer has imprecise control and a given query pattern's plan of execution is subject to change at unpredictable times.
• Rigid tabular data model: Despite the theoretical adaptability of the relational model to many domains, the flat, uniform structure the traditional SQL database imposes on records is especially rigid, forcing a significant amount of upfront design. Introducing new query patterns and schema evolution is fraught with a significant amount of operational risk. Combined, these hinder iterative application development and risk locking in bad data model design decisions.

• Unable to leverage the power of the database: Developers cannot take advantage of the full power of the underlying database for fear that the complex, opaque nature of SQL query behavior will overwhelm their ability to understand how queries perform, and quickly address problems when they do come up.
• Increasingly complex application architectures: Conversely, application architectures compensate by becoming more complex. Database concerns such as caching and transaction logic are pushed to the application layer in an ad hoc manner, resulting in consistency challenges and more sophisticated application logic.
• Significant operational bleed: Entire teams must be dedicated to the care and feeding of the database itself as introducing new query patterns or migrating schema—which should be simple—become fraught, complex operational tasks requiring a significant amount of operator expertise to accomplish.

FQL: a document-relational database language

• Everything is a transaction: Database communication should be built around one-shot requests, not sessions, with requests and transactions being one-to-one. Fauna's language should be a full-fledged database language capable of expressing both declarative queries and procedural flow, such that individual requests can encode required transactional logic and tailor results to precisely what the application needs.
• A database language like a programming language: Fauna's language should provide a developer experience similar to that of a general programming language, including syntax, development workflow, debugging facilities, and tooling support. Performance should be predictable and easy to understand, and possible to infer and control based on query structure.
• A model for schema which adapts over time: Fauna's data model and schema management system should support flexible application development. Constraints should be gradually adoptable and capable of evolving over time with the application. It should always be safe to introduce change without affecting existing workloads in an online system. It should be possible to manage schema alongside application source, with the database itself functioning as just another deployment target.

• Stateful connections vs transactional requests
• A database language like a programming language
• Document-relational: model for schema which adapts over time

Stateful connections vs transactional requests

Database Connections: an expensive, scarce resource

// The classic account transfer transaction, which is dispatched to Fauna
// as one request. The equivalent SQL transaction must be at least three
// separate queries.

await client.query(fql`
  let account1 = Account.byId(${id1})!
  let account2 = Account.byId(${id2})!
  let amount = ${amt}

  if (account1 == account2) {
    abort("transferor and transferee cannot be the same account")
  }

  if (account1.balance - amount < 0) {
    abort("insufficient funds")
  }

  account1.update({ balance: account1.balance - amount })
  account2.update({ balance: account2.balance + amount })
`);

The impoverished nature of SQL as a database language

• It cannot encode conditional control flow, nor is it generically composable⁶.
• The table, being a collection of uniform rows, is limited as a response format in its ability to accurately represent data in a relationship preserving way.

// An example of a tree-based "join". Start by constructing the basic
// shape of the query. 
let user = User.where(.username == "alice")!

let posts = Post.where(.author == user)
  .order(desc(.createdAt))
  .map(post => {
    post: post,
    comments: Comment.where(.post == post).order(desc(.createdAt)) 
  })

// Return a composite structure containing the user's basic profile,
// along with a timeline of their posts and comments. Client-side,
// "posts" will be a result set which supports cursor-based pagination.
// Within each post object, the "comments" object will be a result set
// as well, which can be independently paginated. FQL's projection syntax
// allows precise control of the final included data.
{
  user: user {
    name,
    avatarUrl,
    bio
  },
  posts: posts {
    createdAt,
    text,
    comments {
      createdAt,
      text,
      author {
        name,
        avatarUrl
      }
    }
  }
}

A database language like a programming language

Syntax

Debugging

// In FQL, use logging to trace behavior within a query.
Post.where(.category == null).forEach(post => {
  log("Post #{post.id} did not have a category.")
  dbg(post)
  post.update(.category == "Uncategorized")
})

Performance

// Add supporting indexes to the User, Post, and Comment collections
// with the right predetermined order and data coverage.
collection User {
  index byUsername {
    terms [.username]
  }
}

collection Post {
  index userTimeline {
    terms [.author]
    values [.createdAt, .text]
  }
}

collection Comment {
  index postTimeline {
    terms [.post]
    values [.createdAt, .author, .text]
  }
}

// Update our original profile query.
let user = User.byUsername("alice")!

let posts = Post.userTimeline(user).map(post => {
  post: post,
  comments: Comment.postTimeline(post)
})

{
  user: user {
    name,
    avatarUrl,
    bio
  },
  posts: posts {
    createdAt,
    text,
    ...

Document-relational: A model for schema which adapts over time

Conclusion

References

1. Stonebraker 2005 - What Goes Around Comes Around
2. Relational AI's Rel language
3. Connection pooling itself comes with the risk of crossed wires and mixing up statements from unrelated transactions. Developers can misuse clients, or process-based connection poolers (such as pgbouncer for Postgres) can misinterpret SQL statements.
4. A memorable production incident was when a service's production PostgreSQL database started experiencing connection starvation due to periodic storms of transactions consisting only of a BEGIN followed by a COMMIT statement. These storms were highly correlated with intermittent increased latency enqueuing to RabbitMQ as part of the application code. How, you may ask, could these possibly be related? It turned out that the application framework was wrapping every request in BEGIN and COMMIT in order to make every request atomic by default, leading to RabbitMQ becoming part of the database transaction lifecycle!
5. Expressive power
6. While it is true that the vast majority of SQL databases support procedural logic, it is limited to stored procedures and UDFs which must be defined ahead of time and bypass SQL's inherent limitations on response shape, which we will discuss below.
7. N+1 problem
8. Feral concurrency
9. Against SQL
10. Why SQL syntax sucks, and why it matters
11. A Critique of the SQL Database Language also see this great interview with Chris Date from 2014
12. In comparison, the ECMAScript 2021 spec is 879 pages and the C++ 2020 spec is 1853 pages.
13. PostgreSQL SELECT documentation
14. As of writing, Fauna's entire FQL/FSL parser module is around 2000 lines of code, compared to PostgreSQL's Bison grammar, which is almost 19000 lines long.
15. “The most effective debugging tool is still careful thought, coupled with judiciously placed print statements.” -- Brian Kernighan, “Unix for Beginners” (1979)
16. The situations which lead to this sort of heuristics failure tend to be ones where the cost model the optimizer uses cannot adequately capture the application domain, leading it to make the wrong choice. A good example might be a JOIN in a multitenant app predicated on a tenant key. The developer knows the importance of tenants, so starting with an index partitioned by tenant key is always the right choice. However the database itself cannot know that for a fact and can wind up choosing the wrong index or table to start the JOIN off with.
17. Ironically, where SQL queries are declarative, SQL's data definition language is fundamentally imperative.
18. These systems are detailed in Fauna's architectural whitepaper