Building a production database in ten years or less | Hacker News Reader

Building a production database in ten years or less

edgedb.com

94 points1st134 comments

Thank you for sharing the story of EdgeDB!

I'm probably one of the early adopters of it. Honestly I don't want to write SQL never ever ever again after using EdgeQL.

Currently I'm migrating some of my projects to EdgeDB and already have one in production (it's low importance project in a beta-stage, so I can afford some risks). I also wrote my own scaffold generator for the stack I'm using (EdgeDB+Go+gRPC+Flutter), which generates .esdl and Go database-related files for me.

7+ months later I'm really impressed with user/dev-friendliness of the tooling. It's mostly a "just work" experience, error messages are surprisingly clear (and, thanks God, no python stacktraces on each error or wrong input). Speaking of which, I would still be happier if EdgeDB was written in Go (seems like the best fit for this niche), but I get that authors were more proficient in Python, and it works pretty great so far.

Some nitpicks that I have (all minor ones): - in edgedb console shell if you hit Enter key it doesn't execute the query, but breaks the line. So there is an extra step to move cursor back to the end of the query before executing it (which is frustrating when you have to go to the middle of the query, change it and execute). - I'm still not happy with how Optional types are implemented in Go library – end result, while being 100% correct and explicit, is overly verbose. Not a big deal when using code generator, but I feel like library API would benefit from simplifying it (I wrote some comments on github some months ago).

Anyhow, EdgeDB is a game changer and I'm looking forward for the stable 1.0 release!

Thank you for great feedback!

> in edgedb console shell if you hit Enter key it doesn't execute the query, but breaks the line. So there is an extra step to move cursor back to the end of the query before executing it

You should be able to hit Alt+Enter to run a query regardless of where the cursor is. Let's discuss on our GH [1].

> I'm still not happy with how Optional types are implemented in Go library

Perhaps we should continue that conversation, now that you have a lot more experience with using the API!

[1] https://github.com/edgedb/edgedb-cli/

This is very interesting. For years I've been primarily working on LOB software and always wished for such a movement.

My thought stream in no particular order: I intentionally make these comments before reading the complete documentation, as I'll forget some higher-level thoughts.

1- Adding schema to the DBMS is a big step. Suddenly most queries become much simpler and generating them dynamically even more so.

2- The schema you have designed helps a lot on the server-side. If this schema can be extensible (that is, people can make extensions by allowing extra information in the schema that the DBMS ignores, similar to comments) and available to an authorized user, people can create great tools for the client-side as well.

3- A surprisingly huge portion of developer time is spent on building data administration tools, even in projects and companies that are focused on something else.

4- It is vital to allow the developer to take control and query the underlying Postgresql whenever needed. Most huge abstractions that are all or none are doomed to fail in non-trivial ways. I'm throwing away Prisma integration of my company's project because they did not provide enough extension points, hooks and all the other ways to use the full power of the database. So as soon as you need something they don't support, you have to bypass the whole abstraction layer.

(EdgeDB CTO here)

> The schema you have designed helps a lot on the server-side. If this schema can be extensible (that is, people can make extensions by allowing extra information in the schema that the DBMS ignores, similar to comments) and available to an authorized user, people can create great tools for the client-side as well.

EdgeDB supports arbitrary JSON annotations on all schema objects, for example:

  type User {
    property name -> str {
      annotation render_as := '"textbox"';

      constraint expression on (len(.name) < 10) {
        annotation comment := '"needed for external reasons"';
      }
    }
    
    annotation data_for_app := "some-json-data"
  }

> I'm throwing away Prisma integration of my company's project because they did not provide enough extension points, hooks and all the other ways to use the full power of the database. So as soon as you need something they don't support, you have to bypass the whole abstraction layer.

This is why we are investing so hard into EdgeQL. If you can't express something with EdgeQL that you can with SQL -- it's a bug. If whatever you do seems _harder_ (or slower) with EdgeQL than with SQL, it's also a bug. Same applies to generated database clients (we'll be releasing a JavaScript/TypeScript client generator / query builder alongside EdgeDB 1.0).

When working on the perl ORM DBIx::Class and its underlying SQL generator SQL::Abstract, we always took the principle "if you can't get it to generate the exact SQL you'd've written by hand, it's a bug" and lots of people I know who have since (for entirely understandable reasons) moved on to other languages as their daily driver continue to periodically complain about everybody else's ORMs to me (though when I've had beer with sqlalchemy devs at conferences they've been notable for having the same attitude, more power to 'em).

I have a horrible feeling I'll end up cracking and porting the whole stack to typescript at some point if nothing else because peer pressure/nerd sniping (and if I can make that work, probably also golang) but that's said in the spirit of "you are doing the right thing where so many people aren't".

I'm kinda waffling and not entirely sure how to best end this comment but I both hope you have great success and also that we get to have beer at a conference sometime to share war stories and trade-offs because you sound like you have the Right Ideas (because they're similar to mine and I'm always right, obviously, but still ;).

> I both hope you have great success

Thank you!

> also that we get to have beer at a conference sometime to share war stories and trade-offs

Looking forward!

> If you can't express something with EdgeQL that you can with SQL -- it's a bug.

This is a huge promise. Especially with all the little "extra"s all different vendors have added. Love to read more.

One thing that separates EdgeDB from ORMs is the explicit design decision to be coupled with one specific database.

One more thing that can make me happy is to see if connection cost to EdgeDB is much lower than PostgreSql (one of the few architectural problems with PG). Connecting only (or almost only) through EdgeDB fixes this one as a side effect.

> One more thing that can make me happy is to see if connection cost to EdgeDB is much lower than PostgreSql

It is. Connecting to EdgeDB is cheap and we have no problem handling thousands concurrent connections.

> One thing that separates EdgeDB from ORMs is the explicit design decision to be coupled with one specific database.

Yes, this is fundamental, and it is what enables us to drive the innovation in the query language. PostgreSQL's query engine is awesome and very powerful, but it is hopelessly obscured by SQL and its data model, and then further by the ORMs. EdgeDB and EdgeQL liberate that power and present it in a modernized way. EdgeQL wouldn't be possible or practical today without Postgres.

Love what you're doing. I've built a fairly sizable cloud based POS with postgres on the backend. In doing so I've written thousands of lines of sql and stored procedures over the last 3 years. You've managed to tackle almost all the stuff I've been bitching about in sql.

any plans to implement a full stored procedure language?

how well does it integrate with calls to postgres functions?

> Love what you're doing.

Thanks!

> any plans to implement a full stored procedure language?

There are plans, and they have the word "Wasm" in them :-) You can also do a lot with pure-EdgeQL functions already.

> how well does it integrate with calls to postgres functions?

In theory all you need to do is write a function declaration with `USING SQL FUNCTION` [2], however we are currently limiting this functionality to the standard library, until we grow a well-defined user extension mechanism (a-la `CREATE EXTENSION` in Postgres).

[1] https://github.com/edgedb/edgedb/blob/6f87f44fbc9854ba060994...

    annotation render_as := '"textbox"';

This is the thing that piqued my interest when looking into EdgeDB a few weeks ago. Imagine developing your front end by first designing your data queries, and calling a function on the query definition that generates the frontend ui derived from the schema's type system and annotations that can display the result of that query. A man can dream... :)

Oh, that's very real with EdgeDB!

We used to have a React UI framework for exactly this: ease the pain of generating server and client code for form and data-grid components. You could either describe a form layout in declarative YAML file (binding data types to form fields) or just use the framework to generate components for types fully automatically (based on types and annotations). Our data model, query language, and full introspection makes this possible and almost simple to implement in a generic way. We are thinking about building and open sourcing some of this functionality for EdgeDB, but we don't have an ETA for that yet.

We have big plans for something quite similar to this. Stay tuned.

Thanks!

> 2- The schema you have designed helps a lot on the server-side. If this schema can be extensible (that is, people can make extensions by allowing extra information in the schema that the DBMS ignores, similar to comments) and available to an authorized user, people can create great tools for the client-side as well.

This is so very much on point!

We've designed EdgeDB with schema re-use in mind. Our type system allows types to be composed with mixins. And the query language allows to write polymorphic queries against all types having a particular mixin [1] (like "give me all objects that implement NamedObject type).

The core idea here is that we can make re-usable libraries of schema types that's easy to integrate into your project. Finally making "schema re-use" and "code re-use" possible.

> 4- It is vital to allow the developer to take control and query the underlying Postgresql whenever needed. Most huge abstractions that are all or none are doomed to fail in non-trivial ways. I'm throwing away Prisma integration of my company's project because they did not provide enough extension points, hooks and all the other ways to use the full power of the database. So as soon as you need something they don't support, you have to bypass the whole abstraction layer.

And this is also on point. That's why we spent years designing EdgeQL - our query language and really a foundation of our project. It had to be sound in terms of its design and underlying math to allow proper composability. We hint at some design aspects of EdgeQL in this blog post [2]

[1] https://www.edgedb.com/docs/edgeql/select#ref-eql-select-pol...

[2] https://www.edgedb.com/blog/we-can-do-better-than-sql

Hi there! I'm Yury, the author of the blog post. Ask me anything about contributing to Python, uvloop, or, of course, EdgeDB!

Most importantly, EdgeDB 1.0 is launching live in just a couple of weeks. Join our live launch event here: [1]

[1] https://lu.ma/edgedb

"EdgeDB is an open-source object-relational database built on top of PostgreSQL. The goal of EdgeDB is to empower its users to build safe and efficient software with less effort."

So, the actual database engine is Postgres?

Yeah, that's true - Postgres is the engine. On top of that we have a lot of infra though: our IO server (amongst other things it acts as a server-side connection pool), compilers to compile our query language to SQL behind the scenes and to enforce our type system, a migrations engine, etc.

It's not that uncommon to stack DB engines on top of each other, we're just taking this concept a bit further.

So you wrote the database engine in Python and the database client in Rust? Not the most conventional choice. :) But nice work sticking with it over the years. Congrats!

Thanks!

> Not the most conventional choice.

Yeah... :) To be clear we've already started rewriting parts of core EdgeDB in Rust with the goal to rewrite the IO server in the next year. Some less performance-critical parts of it will stay in Python though for the foreseeable future or until we have more resources.

This is amazing work!

Have you all taken a look at collaborating/interoperating with Materialize.com ? You've built a composable query language on top of PostgreSQL; they've built an in-memory database that accepts PostgreSQL and behind-the-scenes builds composable reactive real-time materialized views on top of high-volume streaming data, based on the Timely Dataflow framework, to the extent that BI tools can run on top of it.

They have a similar blog post on their journey here, which also started in 2014: https://materialize.com/blog-roadmap/

The one area that still holds back Materialize's developer experience, and I say this with all the love in the world, is... well, all the limitations of SQL, particularly when you're dealing with a chain of dozens of tables and views and CTEs for a complex analytical query. (I cannot stress enough that it's aeons better than writing thousands of lines of low-level stream handling code oneself!)

If only there was a sane layer on top that could make it far easier to reason about the queries you're typing...

The idea that some day, someone designing an application with real-time high-data-volume needs could build some combination of SDL schemas and EdgeQL queries that creates and awaits an optimized/tunable cascade of materialized views a query might need... and if built on Materialize's database this would mean that subsequent calls would retrieve fully real-time results for those views... is intoxicating. I'm excited to keep my eyes on this space.

This got me very excited. How does one get involved to contribute? Is there any Slack/Discord group where devs hangout? I did not see anything mentioned in the README

edit: seems there is none right now: https://www.edgedb.com/community

Github discussions are a good place for long form questions/proposals. We also just created a Discord server, everyone is welcome to join: https://www.edgedb.com/p/discord

Wow, I remember first hearing about EdgeDB years ago, insane to see the progress that has been made! I noticed that there's also builtin GraphQL support; can this do the majority of things the query language can, or would there be particular road blocks trying to use it in that manner?

GraphQL is a lot more limited because it was never really intended to be used as a full-blown database query language. While EdgeDB supports GraphQL as a way to do queries, this support is currently experimental and is not intended to be used as an alternative for EdgeQL. Our position is that GraphQL should be used as originally intended: as an introspectable and more flexible replacement for REST APIs. There is work in progress to add a declarative way to define explicit API schemas as controlled mappings of EdgeDB schemas, (e.g. implementing GraphQL "resolvers" as EdgeQL functions or expression aliases, adding access control, etc). This would address the issues with the direct "Database-as-GraphQL" approach: 1) your API is not a direct reflection of your internal database schema, and 2) there is granular control over what and how things can be queried, which is important for performance and security.

Impressive track indeed, I feel that despite the complexity of building a database engine, many companies / individuals are giving it a take (particularly in the golang community) Simple question for a novice, what makes edgedb different from existing alternatives like Neo4J?)

Thank you!

EdgeDB is fundamentally a relational database -- it has a schema and a powerful query language. It makes sense to store application data in it (just like you would in Postgres or MySQL). It's a general purpose DB.

neo4j is a graph database. It's great for storing large graphs of data and finding/traversing paths between them and many other things.

Now what makes EdgeDB different from other relational databases is our focus on improving the developer experience. EdgeDB is installable in minutes, has built-in migrations, a type system that supports type composition with mixins [1], and a cool query language - EdgeQL [2]. If you look at EdgeQL it will remind you of both GraphQL and SQL and that's not a coincidence.

[1] https://www.edgedb.com/docs/datamodel/index [2] https://www.edgedb.com/tutorial

Ok, looks like I have misread, I thought it was a graph database. Thanks for clearing my misunderstanding

We call EdgeDB a graph-relational database. Its type system is essentially a graph of object types, yet out set-based query language and the fundamental data model are still relational.

I commend the optimism it took to attempt to create a database using pure python.

Python allows for high development velocity. Combined with our knowledge of the ecosystem and understanding of the limits to which we can push Python, as well as EdgeDB using Postgres as foundation, it wasn't an unreasonable choice. We needed to be able to refactor large swaths of code quickly. Besides, back then, Rust for instance, was still a relatively new contender.

Now we have more choices, more resources, and most importantly stable language / type system / protocol so we can commit to using lower level languages.

Lastly, according to benchmarks, EdgeDB is quite fast. Check out our 2 years old blog post here https://www.edgedb.com/blog/edgedb-1-0-alpha-2#results for example.

Having before now helped replace a (very naive, this is -not- an attack on golang) 8 core golang bulk pg data loader with a single threaded perl script and got about a 6x performance improvement, "high level languages wielded by somebody who understands the limitations of the VM" are really much more capable than a lot of people think.

Of course, "lower level languages wielded by somebody equally proficient with that language" will still be way more performant, but sometimes velocity of development wins out over straight line performance when the dynamic code is fast enough for the uses cases you're currently supporting.

Wanting to rewrite components in rust is both completely logical and also (and I mean this as a compliment) a Nice Problem To Have.

My original post was not intended to shortchange the work you and your team have done. I was just saying that when you decide to create a database or database-like application, python wouldn't have been my first choice.

In regards to your comments about development velocity and ability to refactor quickly - why are you attributing those things to Python as if it has an advantage over other languages? It seems like velocity and ease of refactoring does have some dependency on the language you are using but is more a factor of one's familiarity with the language and its tools/ecosystem. I've written lots of Python and it's no better or worse than most other languages for either velocity or ease of refactoring.

Reply on news.ycombinator.com