Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

Thanks for the fantastic questions!  Answers inline:

I had never heard of you guys, this looks amazing! How can you only have
222 github stars?!

Heh, feel free to give it one more!

 

Paxos, so needs at least 3 nodes?

It works with any number of nodes, but I recommend 6 as the ideal: two nodes in each of three different datacenters.  More on how the synchronization technology works is here: http://bedrockdb.com/synchronization.html

 

How do you use it from an application that normally uses sqlite? Is it a
drop-in replacement? I use Node.JS…

BedrockDB has an extremely simple wire protocol based around the concept of "plugins".  The "db" plugin is described here: http://bedrockdb.com/db.html, but there is also a full featured "cache" and *extremely* full featured "jobs" plugin as well.  The protocol is easy enough that you can "netcat" in and use it effectively by hand, or you can use our PHP binding: https://github.com/Expensify/Bedrock-PHP

Interesting that you emulate mysql, given that sqlite tries to be
postgresql compatible…

Our main use for this was to accommodate users who prefer certain clients like SequelPro or Mode Analytics.  More information on our MySQL emulation is here: http://bedrockdb.com/mysql.html

 

Any war stories around developing this? Unexpected issues due to a missing
comma etc?

Omg, so many war stories.  This has been powering Expensify from day one, so we have about 8 years of scars as a result.  However, Bedrock gains the benefit of all that painfully earned experience, and is hardened against a million real-world edge cases that you won't need to worry about.

 

Would you recommend this for any workload? I'm paticularly interested in
replicating my append-only event log db.

In general I recommend Bedrock for more or less any workload that you would give to a database (which in my experience, is about any workload involving persistent data).  I've long wanted to build a "logs" plugin to Bedrock that would provide full-text indexing ala Elasticsearch (which we use at Expensify, but have pretty steady problems with), but it hasn't gotten bad enough yet to worry about.  Thanks for asking!

-david

Founder and CEO of Expensify (and BedrockDB)

 

On Thu, Oct 26, 2017 at 10:19 PM, Wout Mertens <wout.m...@gmail.com> wrote:

I had never heard of you guys, this looks amazing! How can you only have
222 github stars?!

Paxos, so needs at least 3 nodes?

How do you use it from an application that normally uses sqlite? Is it a
drop-in replacement? I use Node.JS…

Interesting that you emulate mysql, given that sqlite tries to be
postgresql compatible…

Any war stories around developing this? Unexpected issues due to a missing
comma etc?

Would you recommend this for any workload? I'm paticularly interested in
replicating my append-only event log db.

Wout.

On Thu, Oct 26, 2017, 8:15 AM David Barrett <dbar...@expensify.com> wrote:

> I'm glad you liked it!  I'd be happy to answer any questions you have about
> http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> listening!
>
> -david
>
> On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie <jungle...@gmail.com>
> wrote:
>
> > Hi All,
> >
> > Pardon the usual interruption of complex sqlite questions...
> >
> > David Barrett was interviewed on Floss Weekly today and gave a rave
> > review of his project, which is based on the wonderful sqlite3
> > database.
> >
> > I'm only 10 minutes into the interview and really love it already!
> > https://twit.tv/shows/floss-weekly/episodes/456
> >
> > Thanks to David for appearing on the show and of course to the Sqlite3
> > team for their amazing efforts to make, and maintain the most widely
> > deployed database engine in the world - maybe even in the galaxy.
> >
> > Thanks,
> > j.b.
> >
> > --
> > -------
> > inum: 883510009027723
> > sip: jungle...@sip2sip.info
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[David Barrett]

On Fri, Oct 27, 2017 at 4:34 AM, Warren Young <war...@etr-usa.com> wrote:

Before I get to the questions, I haven’t listened to the FLOSS episode yet, so please forgive me if these were asked and answered on the podcast.  Just tell me if so, because I will eventually get to it.

1. I don’t see a C API.  If so, that means it’s up to the caller to handle quoting and escaping properly to avoid inadvertently creating SQL injection vulnerabilities.  Is that true, and if so, is there a plan to solve it?  I don’t mind running my queries through an HTTP[-ish] layer, but getting quoting and escaping right in hand-written code is a big source of errors I’d rather not return to.

There is a PHP binding (https://github.com/Expensify/Bedrock-PHP) but none for C yet -- PRs welcome!  That said because it uses standard HTTP escaping it should also be compatible with pretty much any HTTP C API -- or it's very easy to hand roll.  When it comes to escaping, for example, you can generally put the content directly in the HTTP content body, so Bedrock introduces no "extra" escaping (beyond what is normally required by SQL, of course).

 

2. No DBD::BedrockDB yet, I see.  When? :)

Heh, we don't have much Perl in our environment, but again, this would be straightforward to add.

 

3. What happens if a local program attaches to the SQLite DB and queries it in the face of active replication?

SQLite is crazy hardened and reliable in this scenario.  You are free to access the underlying SQLite database via the command line if you like, but the locking behavior will be more restrictive.  BedrockDB not only supports concurrent reads (via SQLite's standard WAL functionality), but concurrent *writes* as well (via an experimental "BEGIN CONCURRENT" branch developed by the SQLite team that we're hammering the kinks out of before its merged into the mainline).  So use the Bedrock plugin architecture where possible to get maximum concurrency, and never do writes from the command line as it'll screw up our replication journal, but you are free to do reads via the command line when more convenient (and we do it all the time).

 

4. What happens if a local program *inserts* data via the SQLite interface?  Does the data get replicated, or does that happen only if you insert via port 8888?

Correct, only insertions done via port 8888 will get replicated, so it's not recommended (but always possible for extreme maintenance scenarios -- about as often as you'd directly modify a local MySQL file with a hex editor -- meaning, almost never).

 

5. I think your web pages answer this one implicitly, but let me ask just to be sure I’m guessing right: Does the MySQL API smooth out any of the semantic and language differences between MySQL and SQLite, or must you simply give SQLite-compatible queries over the MySQL interface?  (Expected answer: “No.”)

No -- we only support SQLite syntax (which is really more modeled off of Postgres), but in practice the various MySQL clients we've tried don't actually care about the syntax -- only the protocol -- so it works well enough in practice.

6. What MySQL API version are you compatible with?  As maintainer of MySQL++, I can tell you, there is not just one version. :)

Good question, we don't have an exhaustive list.  The versions we use, at least. :)  But the most recent version of SequelPro, the MySQL command line client that ships with Ubuntu, and Mode Analytics to start.

 

7. Does using your MySQL API solve problem #1?  That is, if you use one of the available ways for constructing guaranteed-sound queries via libmysqlclient or one of the many libraries built atop it, does BedrockDB get the benefit?

I'm not sure what "guaranteed-sound" means, sorry!

 

8. Does your MySQL API solve problem #2?  That is, have you tested it against DBD::mysql?

I don't think we've tested against that as we don't use a lot of Perl, but I'd be curious for your results!