LearnDB: Learn how to build a database (opens in new tab)

It's been a very long time so I'm not sure I can name many specific examples, but IIRC some of the topics that became very clear were things like:

* the importance of the order in which JOINs and WHEREs are applied (to limit the number of rows being accessed per step)

* the relationship between columns that are selected and those that appear in WHERE and ORDER BY clauses.

* the value of tables with a small number of columns (limiting data that must be read per row)

* the cost/complexity of variable-width columns (VARCHAR vs a fixed-length string), again because of the time and complexity required to do something like "skip ahead three rows" in a data file

* the behavior of CLOB/BLOB types (stored external to the "main" table content)

* the various types of transaction isolation levels and the conditions in which it becomes hard or impossible to guarantee isolation without table or row locking

  * etc.

Also, there's a post at http://heyrod.com/articles/radio-blog/pleasures-of-profiling... that describes a session of performance-tuning on the index implementation that gets into some of the nitty-gritty implementation topics. (But a lot of the issues addressed there were an artifact of Java's primitive vs. object representation, which has been reduced by things like generics.)

The more we used HSQL the more it became clear that it was more like a SQL-parser wrapping a simple key/value store than a full-on ACID database. We created AxionDB precisely because we needed the kinds of capabilities you mention and at the time HSQL did not provide them and wasn't remotely architected to support them.

To be honest though, creating a moderately robust RDBMS from "scratch" turned out not to be the most ambitious or complex part of the overarching project that spawned AxionDB. The harder part was trying to use Java's primitive, built-in HTML-renderer to create something approximating a fully featured browser. The effort and complexity behind something like Gecko, WebKit, Edge, Blink, etc. is very easy to underestimate. It's a hard problem, made much harder by having to tackle the kinds of content you find "in the wild. Frankly building a database was a much more straightforward problem than that.

Build a working Tetris game from literal logic gates up.

Not really. The author doesn't leverage any kind of static typesystem, which would at least mitigate some of the warts. He's chosen Node.js instead of leveraging the browser, so no free visualization layer for doing anything interesting with, unfortunately.

> Have you worked with a JS language for any extended period of time?

Most of my day job is working in a really, really big Javascript codebase. I can say with confidence that the language is something that we're absolutely stuck with, and I still have no idea why somebody would implement a pedagogical database with it (well, then again, they haven't; they've implemented a key-value store, which is trivial).

To offer an alternative, they could have chosen something boring but everywhere like Java, which is just as accessible to those with less experience. Then they'd have the possibility of doing fine-grained parallelism, file access, designing abstractions that fit within a statically typed language.

Maybe you’re right, but this page doesn’t actually teach you databases, and most of those courses don’t actually teach you programming.

At least not efficiently.

I have worked with JS for a long time, and I don’t hate it, but it’s such a terrible language and environment that the most popular part of it is literally a strict syntactical superset of of it.

I don’t particularly like Typescript by the way, and I don’t think it’s really that useful, but you can’t deny that most people do.

I think 95% of all projects would be better of using something that wasn’t JavaScript for the whole stack, and I think that’s the reason so few things are build on node. I like graphql as much as you do, I also think Prisma is okish, but I’d rather use Django, Flask, Java Spring it .Net Core because they are so much more efficient in the long term.

Of course on the client side, all the innovations and all the talent lies with JS, and there is some advantages for using JS for your whole stack. Those advantages end at the DB though, at least in my opinion.

This isn’t a problem, you can use Prisma for Postgres, and there are decent drivers for mssql, but I’d never advise people to use nosql unless they had a very specific reason for doing so, and I can’t think of one.

Why choose a language plagued with warts and a half-baked ecosystem for something pedagogical? A language with all sorts of peculiarities from the '95 browser era, and not at least leverage browser technology? A language which gives you very non-interesting coarse control over resource, and has no concept of parallelism?

which one can teach how to build a DB system from scratch? I'm not talking about SQL theory or implementing a SQL parser but the actual persistence, indexing part.