After I wrote about Stein earlier today, I got to wondering about open source alternatives to Google Sheets. Coincidentally, this article popped up in my RSS reader.

EtherCalc can be self-hosted or there are hosted offerings, including one at EtherCalc.org. It looks a bit rough around the edges, but that’s often the case with open source GUIs. Maybe kick the tires and blog about your experience? We’d happily log the results here on Changelog News.

This looks like a great option for proofs of concept or when you want to take an idea to market as fast as possible. It’s also probably empowering to non-developers on the team since so many people can slice-n-dice spreadsheets better than SQL databases. You can self-host the open source version or pay for the hosted offering. I’d love to see a comparison between this and Airtable.

Computer Scientist Yaw Anokwa joins the show to tell us how Open Data Kit is enabling data collection efforts around the world. From monitoring rainforests to observing elections to tracking outbreaks, ODK has done it all. We hear its origin story, ruminate on why it’s been so successful, learn how the software works, and even answer the question, “are people really using it in space?!” All that and more…

This wraps up a pre-trained model for SQLova. Here are some examples using the ‘bridges’ dataset. 👇

Michael Malis at !!Con 2019:

Writing SQL can be hard. SQL code is a bizarre combination of yelling and relational algebra. How can we make writing SQL easier? By embedding our own programming language in our SQL queries of course! In this talk, we’ll take a look at how you use a combination of various Postgres features to build a programming language out of SQL.

Verrrry interesting:

EdgeDB combines the simplicity of a NoSQL database with relational model’s powerful querying, strictness, consistency, and performance.

It boasts strongly typed schemas, native GraphQL support, a rich standard library, built-in support for schema migrations, and more.

How does a database work? What format is data saved in? How are indexes formatted? When and how does a full table scan happen? Join Connor Stack on his journey to answer these questions and more…

I’m building a clone of SQLite from scratch in C in order to understand, and I’m going to document my process as I go.

DJ hits the “why should I care” question of Spark SQL on the head. It’s a fun dive into a classic query language that has new value when mixed with the DataFrames API.

Craig Kerstiens:

I’ve learned a lot of skills over the course of my career, but no technical skill more useful than SQL. SQL stands out to me as the most valuable skill for a few reasons:

1. It is valuable across different roles and disciplines
2. Learning it once doesn’t really require re-learning
3. You seem like a superhero. You seem extra powerful when you know it because of the amount of people that aren’t fluent

I tend to agree. I still use (and sometimes love) ORMs and database libraries while building apps, but the more I’ve learned SQL over the years, the more I appreciate it for what it is.

Craig drills into each of his 3 points above in this excellent post.

This blog post is about Badger, the key-value database that makes it all happen under the hood, housing all Dgraph data, including Raft logs.

There are many key-value store options in Go-land. Still, the Dgraph team decided to roll their own solution 18 months back. Was it a bad case of NIH? A good idea? Would they do it all over again? This article answers those questions in-depth.

Who knew an audio post-mortem could be so fun to listen to!

On Thursday, November 8, Basecamp 3 went down for almost five hours. It was the worst outage to hit the company in a decade and a stress test of Basecamp’s practices around internal communication, customer support, and calm work. Today’s episode goes inside the company on November 8 to see how the outage unfolded.

Dgraph founder Manish R Jain gave us an excellent explanation of graph databases on episode 322 of The Changelog. I extracted that section from our trusty episode transcript for your reading pleasure…

The motivation behind NearDB:

While working on building edge applications for higher performance and lower latency there is a need store persistent data also on edge. There are multiple distributed database solutions but they are very involved and costly while having a much lower global footprint than a CDN. The idea came up to leverage ubiquitous and mature infrastructure like cloud storage and CDNs to deliver a persistent data solution from the edge.

This week we talk with Manish Jain about Dgraph, graph databases, and licensing and re-licensing woes. Manish is the creator and founder Dgraph and we talked through all the details. We covered what a graph database is, the uses of a graph database, and how and when to choose a graph database over a relational database. We also talked through the hard subject of licensing/re-licensing. In this case, Dgraph has had to change their license a few times to maintain their focus on adoption while respecting the core ideas around what open source really means to developers.

Choosing the right open source database is an important decision. Start by asking the right questions. All too often, people put the cart before the horse, making decisions before really understanding their needs.

Solid tips by Barrett Chambers. Here’s another one courtesy of yours truly: Start your database selection journey by asking yourself, “Why not use PostgreSQL?” 😉

Imagine being able to have many connections to the same database, each one reading a separate branch or commit at the same time. Or even writing to separate branches.

This is a good idea and a testament to the versatility of SQLite. Click through to read how it works and how they’ve managed to 2x the performance of SQLite on Linux and macOS.

Careful now, ‘sled’ is in its alpha stage. Heck, its name is a recursive acronym that means “sled likes eating data”, so that should give you an indication of its state (I hope they come up with a new one once the software is stable). The project’s goals are on point:

1. don’t make the user think. the interface should be obvious.
2. don’t surprise users with performance traps.
3. don’t wake up operators. bring reliability techniques from academia into real-world practice.
4. don’t use so much electricity. our data structures should play to modern hardware’s strengths.

Dimitri Fontaine, a PostgreSQL major contributor, has written an awesome blog series on PostgreSQL data types to show how to benefit from the PostgreSQL concept of a data type.

The PostgreSQL concept of a data type is more than input validation, a PostgreSQL data type also implements expected behaviors and processing functions. This allows an application developer to rely on PostgreSQL for more complex queries, having the processing happen where the data is — for instance when implementing advanced JOIN operations, then retrieving only the data set that is interesting for the application.

The posts in this series were extracted from his book Mastering PostgreSQL in Application Development — which teaches SQL to developers so they can replace thousands of lines of code with very simple queries.

Adam is on location at ZEIT Day talking with Jessica Rose about burnout, Henry Zhu about his passions and pursuit of open source, and Simon Willison about data and his passion for interesting datasets in the world.

Matt Jaffe was on a recent episode of Go Time and also gave this talk at OSCON recently on indexes as a first class citizen. In this video Matt talks about a piece of software that’s purely an index, not a database, not a datastore, just the index — and optimizing that single piece of software to be very fast!

Here’s a quick breakdown of an index as a first class citizen:

• Standalone application, not just a data structure
• Horizontally scalable, distributed
• FAST, indexes should make things faster
• Flexible, integrates with other datastores and data types

Also, learn more about Pilosa to see Matt’s work in action.

Straight from the horse’s mouth:

FoundationDB is a distributed datastore, designed from the ground up to be deployed on clusters of commodity hardware.

And:

The key-value store supports fully global, cross-row ACID transactions. That’s the highest level of data consistency possible. What does this mean for you? Strong consistency makes your application code simpler, your data models more efficient, and your failure modes less surprising.

They say it’s “actively developed and has years of production use”. I wish they’d say exactly how it’s being used in production. (Maybe they do and I haven’t found it yet?) Also, if you’re getting hung up on “key-value store”, the vision is much bigger than that.

Matt Jaffey joined the show and talked with us about Pilosa, building distributed index with Go, and other interesting projects and news.

Philipp Krenn joined the show to talk with us about Elasticsearch, the problem it solves, where it came from, and where it’s at today. We discussed the query language, what it can be compared to, whether or not it’s a database replacement or a database complement, Elasticsearch vs Elastic the company.

We also talked about the details behind Elastic’s plan of “doubling down on open” to open up X-Pack, which is open code paid add-on features to Elasticsearch. We discussed the implications of this on their business model, and what changes will take place at the code and license level on GitHub.

Dimitri Fontaine shares 3 classic data-modeling anti-patterns. The UUID section lacks strong argumentation, but the real gem in this article is his advice at the end. A snippet:

My advice is to always normalize your database model first, and then only fix the problems you have with that when you actually have them. Well except in those 3% of cases where really, really, it should be done in the design phase of the project. It’s quite hard to recognize those 3% though, and that ability is hard gained with experience.

Experience is the ultimate teacher.

This is plugin is by Tim Pope (the unofficial pope of Vim plugins), so the chances of it being high quality are… high. It supports the typical RDBMSes (Pg, MySQL, Sqlite) as well as a few NoSQL flavors (Mongo, Redis).