Modern Database Clients On The Command Line with Amjith Ramanujam

Hello, and welcome to podcast dot in it, the podcast about Python and the people who make it great. When you're ready to launch your next app, you'll need somewhere to deploy it, so check out Linode. With private networking, shared block storage, node balancers, and a 200 gigabit network, all controlled by a brand new API, you've got everything you need to scale.

Go to podcastinit.com/linode

to get a $20 credit and launch a new server in under a minute.

To get worry free releases, download Go CD, the open source continuous delivery server built by Thoughtworks. You can use their pipeline modeling and value stream app to build, control, and monitor every step from commit to deployment in 1 place.

And with their new Kubernetes integration, it's even easier to deploy and scale your build agents. Go to podcastinit.com/gocd

to learn more about their professional support services and enterprise add ons, and visit the site at podcastinit.com

to subscribe to the show, sign up for the newsletter, and read the show notes. Your host as usual is Tobias Macey, and today, I'm interviewing Amjith Ramanujan

about DB CLI, an umbrella project for command line database clients with auto completion and syntax highlighting. So, Amjith, could you start by introducing yourself?

Yeah. As Tobias introduced, I am Amjith. I work as a, senior software engineer at Netflix for my day job.

And, as a side project, I started dvcli

open source organization.

And do you remember how you first got introduced to Python? Yes. That actually happened in 2010.

I was a c plus plus programmer before then. And, I someone told me that I should learn functional programming, and Haskell is the best way to do that because it's a purely functional language. So I went through the learn your Haskell for greater good book. And I reached the chapter

list comprehension in math before, but to be able to express that as a programming construct and be so succinct and yet be, you know, so clear and, and it doesn't obscure any of the complexity. I immediately fell in love and I wanted to find a language that supported list comprehension that can also get me paid for for writing code in it. So that's how I got started with Python. And, I started going to our local Python users group. This was back in Utah. It was a it was a set of friendly bunch of geeks hanging out and talking about Python. So, that's how it all got started. As we mentioned,

the project that we're talking about today is the D. B. C. L. I. Grouping of clients. So I'm wondering if you can just describe a bit about

the parent organization that you formed and some of the individual

clients that fall under that. Yeah. Absolutely. So d b c l I, it's a abbreviation. It stands for database command line interfaces.

And, it's a open source organization

that builds command line clients for different databases. Right now, we have, 3 separate products that that comes out of it. 1 is called pgcli for postgres and mycli for my SQL and m SQL CLI for the, MS SQL Server. The MS SQL CLI is actually created by Microsoft and they chose to fork the PGCLI project and they wanted to bring it under the d b c l I, organization umbrella, which we were very grateful for. And, it's built and supported entirely by Microsoft.

And

which 1 of the clients was the original client? And what was your motivation for creating it? Yeah. So p g CLI was the first 1. When I started it, it was not an umbrella organization or anything. It was just a personal project. Pgcli was created out of a scratching itch that I had. I was a big, big, huge fan of, b Python, which is, if you don't know what b Python is, it is a fancy REPL, which is a replacement for the regular Python REPL that comes by default. It has auto completion, syntax highlighting, and as you start typing, it'll suggest, you know, docstrings for different functions and so on. And I I I missed that, feature whenever I was working in p SQL, which is the default

postgres client. So I wanted that. And at the same time,

Jonathan Slanders

reached out to me via Twitter and asked if I'd be interested in trying out this new library that he's building called prompt toolkit. And I took a look at the examples that he provided, and I thought it was phenomenal idea. And, it fit perfectly into what I had in mind. And so I decided to use that as an opportunity to try out his library as well as build something that I wanted for my own usage. That's how PGCLI was born. And did you know Jonathan ahead of time? Do you did you guys have a history of collaboration and that's why he reached out to you? Or was there a particular project that he was aware of that you had done that caused him to reach out? So we had met in, believe it or not, not, in a in a island in France, for DjangoCon Europe. I was doing a lightning talk there about b Python. Since I'm a big proponent of b Python, I I would just, you know, pitch b Python any

at any stage I could get. And, I think he must have heard that talk. And I think we had dinner together that night just, you know, as a as a group. And, I asked him some really dumb questions like, you know, so you're from Belgium. You know, you must speak Belgian. And he said, no, Amjet. There is no such thing as a Belgian. I speak good Dutch.

So I think, that must have stuck with him. And and,

so after I came back from the conference,

about a month later, he reached out via Twitter and asked, you know, he's building something and command lines, and would I be interested in in exploring it? So, yeah, that's how that's how it started.

And at what point did you decide that you needed to create the umbrella organization for some of the different projects and what is the benefit that it provides to have them all grouped together like that? So when I created p g c l I, the reception for the project was phenomenal. Like, I blew away any of my expectations. I expected, you know, a couple of people to use it. Maybe I'll receive, you know, 1 or 2 pull requests every now and then or something. But it topped the Hacker News,

posting, and it and a lot of people adopted it and they loved it. And then I started getting requests for MySQL version of this. And I decided that, you know, if we're going to build multiple versions of PGCLI for different databases, it might be worth actually, you know, creating

a GitHub organization because it's not my personal project anymore.

Because at that point, I had accumulated a bunch of core developers who were contributing code and a bunch of issues were coming in. And I had a lot of, help at that point. And so I decided that this is not a single person's project anymore. This is a community project and I should make that clear by creating a GitHub organization and, making this project as part of the organization,

not as under my own personal name. The advantage that comes with it is that now we have a collection of libraries that we have created as part of the DB CLI, as well as for writing different command line applications,

makes it easier and so on that came out of it. And people are, you know, the core developers who work on this feel like this is their project. It's not just this 1 guy's,

creation.

Are there any shared aspects of the different clients or shared libraries that you leverage to be able to abstract out some of the common

requirements for the different clients?

Originally, when I started, no. I literally took the p g c l I code at some point and then forked it to create my c l I client, which was for MySQL.

I should have thought better. I should have,

extracted out, like, the completion engine into its separate,

library. I should have pulled out the the table generation and these kinds of things, but I did not. But then later on, we started to notice, you know, projects were starting to diverge. Like, some of the core devs were in my my in my CLI field, they extended it to do different things. In PGCLI area, they extended it to do different things. And so it started to diverge, and now we are trying to pull out the common components. And, I know that CLI helpers is 1 of the libraries that came out of this, which both of

the projects use, which makes it easy for us to, you know, use different table formats and manage our configuration files and so on. That was actually not created by me. That was created by, the the lead maintainer for my CLI right now called Thomas.

That was his brainchild.

So that is 1 of the bigger pieces that is commonly shared between both of the projects. We're still working on pulling out the various different common functionalities into different libraries. It's a work in progress.

And you mentioned that Microsoft

is donating the MS SQL client to the DBCLI

organization.

Is there any particular set of requirements

or,

best practices that you have published for anybody who wants

to write a client for a new database in order for it to be able to fit well under the DB CLI umbrella or interoperate or share any of those library dependencies?

When I created the fork for my CLI, I realized that there was 1 single file that actually talks to the database itself that needed to be modified, but everything else

was pretty much the same. And this was again reinforced

by,

the the engineers from Microsoft when they said, you know, we looked at PG CLI. We decided to create fork of data for MS SQL. And they were able to whip something up together in about a week by simply replacing that 1 file with the appropriate drivers that spoke to the MS SQL database,

servers.

And I think that is the biggest accolade that I have ever received for my code, which is, you know, I didn't have to change a lot. There was this it was nicely packaged up, and it was carved up into into neat little pieces. So I could just replace this 1 component and it started working.

So for the people who,

are interested in creating more of these types of, command line and clients for different databases, if you reach out to me or to any of the core devs in the, in the dvcliorg,

we'll be more than happy to guide you through the different

files that you need to edit in order to achieve, you know, create your own project. The only reason we haven't done that ourselves is because the lack of time. Like, all of us, all the core devs, everybody has day jobs, and we are all just doing this for fun in our as side projects in our free time. So it's just a matter of getting the time to

implement this same thing for multiple databases.

And in terms of the functionality that the clients provide over the native interfaces that are provided with the different engines,

what are some of the most useful capabilities?

So the couple of obvious things that come to mind are things like the auto completion. So auto completion was critical for me from the very beginning. I wanted it to automatically suggest the table name. So I wanted the auto completion to be context aware. Meaning, after the from keyword

and the select star from, it will only suggest the table names in that particular database that I've selected. And then inside the WHERE clause, you know, select star from table name WHERE, it will only suggest the column names from that table that I have, specified in the in the previous,

from clause. So I wanted the context aware, auto completion. That is 1 of the things that sets it apart from the existing default, database clients. I know that PSQL can do some of that, but it kind of falls apart if you do a little bit more complex things like a join statement or if you start using aliases for table names and such. It doesn't quite complete things for you. So that was 1 of the things. And, you know, I liked my terminal to be colorful. So syntax highlighting is another 1 of those, obvious things that sets it apart from, the other command line clients. And 1 of the things that I constantly

go back and reinforce,

in these clients is they have to be very, very user friendly. So 1 of the things that I've noticed in my SQL's

default client is if you type se l and you hit the tab key, it'll not auto complete. But if you type sel all uppercase and then you hit the tab key, it'll auto complete, you know, select as, as the auto completion. And that's such a trivial change. It should be just case insensitive. Why couldn't they just make that into, into a feature, into the built in default? So just putting some thought into the design of the actual program makes it that much more

some thought into the design of the actual program makes it that much more easier for a user to use it. And so that I think is the highlight of the, of the, programs that we have built, which is all these tools. We use it on a daily basis. And as we notice anything that is,

introducing a little bit of friction, we go and fix it right away and make sure that it is it is extremely user friendly. 1 of the things that can be challenging when dealing with databases

is that you can occasionally have a query that returns a large volume of data either because you didn't filter it down properly or because you need to be able to process that amount of data. So are there any optimizations that you've added in in terms of, how the client interacts with the database engine to be able to simplify or alleviate some of those challenges?

Yeah. So these optimizations kind of came on later.

The initial implementation

was very focused on just having it easier for the user interaction on a day to day basis. And this was designed as an exploratory tool where people who didn't know database would just jump into it and start using it and and,

discover the tables and columns as they start writing the queries. But then later on, we found out that, you know, it doesn't

thousands of, table names and and, hundreds of column names and so on. So 1 of the things we've done we have done is, made the auto completion refresher run-in the background thread. So as you start typing, it'll still be responsive and it'll start to complete as much as it has fetched from the database. But if it is still fetching things, it, you know, you just it just downgrades into a dumb client where you just continue to type your query. And as soon as the auto completions have come back and we have populated the data structures with all the necessary tables columns, it'll start appropriately

recommending things. 1 of the other things is we try to use the Python's generator functionality quite a bit in in all the places. And so all the results that come back from the database are held as, cursors, which are generators. And so we don't end up consuming the generator until the very last second when we are ready to display the table itself. This helps with keeping the memory footprint,

slightly smaller. And also,

again, we go a little bit further by by asking the question where if a user is doing select star from a table name and they have not given a limit clause or no where clause or anything, and we realize that the result set has more than 1, 000 rows, we actually ask a question. Are you sure you want to see

the the results because you have more than 1, 000 rows? And the user can then choose to say yes or no. And if they choose yes, then we'll, you know, actually consume the generator,

formatted into a nice

table, and then we'll try to display that for the user. But usually, the users

kind of catch themselves saying, like, oh, 1, 000 rows. That doesn't make sense. I, I I guess I must have forgotten a where clause. And then they immediately say no. And then they switch back to modifying the query and so on. So that those are the optimizations that I could think of. And in the process of building a client to communicate with the database, I imagine that there are certain edge cases or weird implementations

or just strange behaviors that you've encountered. So is there anything that was particularly notable or that you found most interesting or surprising in the process of building these various clients?

So Postgres

client was much harder than my SQL client, which was a bit of a surprise for me because Postgres is usually touted as a better database,

than than MySQL. The things that made it hard for me with Postgres was that in MySQL,

show tables or describe table or show columns from a table, These are all actual

database queries that you can send to the database and it'll give you results back. Whereas postgres does not have the equivalent of these kinds of SQL type queries. Instead, what it has is backslash commands. And these backslash commands are called meta commands in Postgres. And, these meta commands

translate it's like a backslash d will give you the list of all the tables and then backslash d t and then or backslash d and then the table name will describe how that,

what the table has and as in columns and triggers and and so on. These, shortcut backslash commands are actually implemented as

extremely complicated SQL queries that are not implemented in the database itself, but they are implemented in p SQL, the the client,

library. So when I was creating pgcli,

I had to spend

nearly 2 weeks or so translating the c code that does these backslash d into SQL commands into Python code that would do the exact same thing,

which was a which is quite a pain in the, pain in the neck. But,

once we were able to do that, now that is actually a standalone library. It's called PG special. And, this library came out of DBCLI as well, which

it implements these backslash commands as actual SQL queries.

And if there are other libraries or other clients who who are writing a client for Postgres, they could now use this pg special,

module to implement these backslash commands for themselves. So that was a little bit of a surprise for me, and,

it,

I'm I'm glad we now have a library to to handle that. And and there are other projects that are actually using PG Special, which is a nice, compliment.

And so in terms of running and maintaining the project, what have been some of the most notable

aspects of that, both positive and negative? So let me, pitch the negative first, and then I'll talk about that positive. So I think every open source maintainer at some point in their life has kind of or or gone through a little bit of burnout. I didn't quite experience an entire burnout, but I did come very close and I was able to pull back and and, rescue myself. And the way this happened was I,

as I was, bootstrapping my CLI and I that was going really well, I started to obsess over the project. Every waking minute, I was starting to think about like, oh, these issues were filed on p g c l I. I need to port this feature from my c l I to p g c l I and this feature request sounds really awesome. How am I going to implement it? And this was constantly in the back of my mind. I I just could not get it out of my head. And, it was starting to,

affect my day to day life and activities. Fortunately, 1 of my coworkers at the time, he asked me if I wanted to learn how to longboard, in a class. This is something that I never really would have gone and done myself. But since he asked me, I was like, yeah. Sure. Why not? And, it turns out longboarding

takes every single neuron in my brain to keep myself upright in the on the on the longboard.

So that was a nice 2 hour break that I received where I couldn't think about anything else except live in the moment, learn how to stand on a longboard and not fall. That kind of worked as an escape hatch and that, happened once a week. I was able to practice

how to stop thinking about my projects all the time and recover from it. But that was a good wake up call that I, I catch myself even now when I'm working on things. If I start obsess over something, I need to dial back and and and stop thinking or worrying about it. Some of the positive aspects of of running the open source project. 1 of the biggest positive things that has come out of this is people always say that, you know, if you have diversity of thought, then, you just end up producing a much better product or a much better result. And I got to experience that firsthand.

I I mean, we all have read this in blog posts and and intuitively it makes sense in some level, but to be able to to experience this firsthand was phenomenal. To give you a very concrete example, 1 of the users or 1 of the early developers

suggested that, you know, we should automatically auto escape some of the table names. For example, Postgres only allows lowercase table names without quotes around them. If you want to have uppercase or mixed case table names, then you have to put quotes around them. And,

I was very hesitant to introduce a feature like that simply because I basically told him that after a user has typed in the query, we have to scan the query, figure out if it has mixed case table names, and then go in and modify the query to add automatically add codes to it. And that sounds kind of dangerous to me. I don't want to mess with the user's queries automatically. And, fortunately, the user, the the the developer didn't listen to, what I was saying. And so he went out and implemented the solution. What what he did was when we scan through the table or when we scan through the database to get fetch all the table names, he automatically noticed if there are any tables that have a mixed casing or, a keyword is, used as a table name, then instead of listing just the table name, he would list the table name with quotes around it. And so when you start typing it and you say select star from, in the drop down menu, you will see the table name with mixed casing with quotes around it. And so when you select it from the menu, it'll automatically be escaped for you, which was a very nice solution where it still gives the control to the user. At the same time, you're not messing around with the query. And, I was completely blown away by the solution. There are many examples of these kinds of solutions, where people have just, implemented phenomenal features that wouldn't have thought of myself. So, it was a nice reminder that having having diversity of thought, it just produces a better quality product at the end. You recently handed over the primary maintainership of the majority of the projects to other people so that you could step back. I'm wondering if you can just discuss a bit about your decision making process around that and some of the impact that that has had for you on your personal and professional life. Yeah. Sure. So now PGCLI is maintained by a good friend of mine called Irina Irina Truong. She is now the the lead mat maintainer for pgcli, and my CLI is now maintained by Thomas, Roten. They were they both joined

the the the project as core devs in the early early stages of the project. The reason I decided to give that give the control to to them is because I am a huge believer of, you know, having the space and and stepping back to let someone else grow. So

I've been in teams in the past where

you have, you know, 4 senior developers and then 2 junior folks joining the team. And the junior folks have no way to grow and sort of, make the mistakes and and learn for themselves

and grow into a senior engineer because the senior folks tend to take all the interesting projects or they tend to stop the juniors before they make the mistake. And then the juniors kinda tend to learn to become dependent on the seniors. And so, what I have found is when the seniors leave the team, the juniors automatically try to fill that role by stepping into that. Maybe, you know, stumbling at the first couple of steps, but then they grow to that role and they become seniors and then they grow and and so on. So I wanted that to happen

for the the the folks that were in the team. And also, it gave me a nice opportunity for me to sort of, you know, hand over the range to some of the most, prolific,

contributors to the project and, give them complete control and then let them I've taken it to as far as I could in in my vision. And now, it's up to them, whatever reason they they have,

they get to take it and and run with it. So, it's been a great experience so far because it has definitely freed up some of my time. I don't have to think about like, what is this going to be

doing in the future? And any of that, like, they care about that. I get to work on issues, fix the bugs and

answer questions that comes up in the GitHub issues. It's, it's really nice and, it's such a relief.

And was it difficult

to find and identify people to become those core maintainers or were they just people who had naturally

gravitated to the project and started becoming involved with their own accord? So I've been very, diligent in in inviting people to join the core team. So it is not a huge, ordeal.

You submit, you know, 2 substantial pull requests and then I immediately send out an email asking, hey, would you be interested? Because you obviously care enough to send pull requests to the project. Do you want to join the team and make the change from the inside sort of a thing? And people have been very receptive to that those kinds of requests and they join in and and, it gives them a nice incentive where they feel like they are part of the the team that maintains the project and they start to even more. In my area, like, in with these 2 selections, it was

not a very difficult selection at all. So they both have been long time contributors and they have both done substantial amounts of rewrites

of the code,

that I had huge confidence in

optimize on being nice over being correct or or over being right. So I'm wondering if you can talk a bit about what you mean by that and how it manifests.

So this is mentioned in the about page and Dbcli, which is

we optimize for being

nice rather than being right.

The reason I say this is because originally when I launched the project, I wanted to give a bit of human feeling to the project which is this there is a human being behind this. There is someone who deeply cares about this project, who's working on this. And so there's even more that I go into into into the details of the about page where I talk about, you know, you will not be discriminated. I will be responsive,

to your requests. Because these are things that I noticed in other open source projects that have been successful, which is they're very welcoming to the newcomers. They don't,

chide, silly questions or or questions that have been answered before and so on. So I wanted to embrace that culture that made those projects successful. And so I decided to optimize on that. And a nice outcome out of that has been,

we we get,

we get requests from, a bunch of different countries. And, 1 of the things is anytime there is like an abrupt question that comes across as very brusque and,

almost to the point of being rude. I check the username, and it is usually someone who's not a native English speaker. And there is a high chance that they probably typed in the question in you

know, we you know, we rephrase the question that they have asked and ask, is this what you're asking for? And then, you know, it it,

it's kind of smooth sailing from then on. 1 of the greatest advantages,

of, this approach has been we have not had any users who have been complaining or being aggressive

or we haven't had the problems that a lot of open source projects face, which is we haven't had, any amount of backlash or anything in any sense. People have generally been very, very welcoming and and very, very grateful that this project exists,

which I've been very lucky to have, great users as well. So yeah. And how has that philosophy and that approach to the project

affected the reception

of people using the project and the feedback that they provided?

Yeah.

So, 1 of some of the issues that we receive, typically start out by saying, hey, this is a great project I just found it. I use it every day. Oh, and by the way, I just noticed this as something that is lacking or, you know, this is this doesn't quite work as I expect. Is this something you'd be willing to,

to change or to to show me how to change or something? In that sense, I'm extremely lucky to to have users who are, you know, who start out a request by asking by basically just praising and then, asking if they can help in any way, which is which has been very rewarding. It's it's,

it has been received very well, and I'm I'm very lucky to have great reception so far. And do you have any particular plans

or feature requests or new ideas for the projects in upcoming releases?

Right now, what we are doing is is trying to bring things to feature parity. So like I said, p g c l I and my c l I has diverged quite a bit in terms of the feature set. A good example is PGCLI's completion engine is, slightly more powerful than my CLI's completion engine. So if you were to write a join clause, typically joins happen between 2 tables and pgcli is smart enough to introspect both the tables, determine which columns are actually capable of joining with each other, and it'll it'll actually suggest the entire join clause for you. It'll do like, table 1 dot ID equals table 2 dot ID because IDs are the only thing that you can actually join on because they have the matching data type or something. That level of, sophistication is not available in my CLI. And,

so we're trying to bring both these projects to kind of feature parity and, maybe even pull out the SQL completion,

engine into its own library. So if someone else is building a a client, they could just simply include it as a, import it and and include it in their project. Those are some of the things that I I could think of in the top of the top of my head. For the as far as the rest of the thing goes, we had a booth in Pycon, which is an open source booth that Pycon was gracious enough to give us a free booth to pitch our project and promote it. We had a lot of requests from a lot of people about SQLite,

CLI. And, I told everyone that, you know, if you are interested in writing this yourself, I'm happy to help you. And so send me an email and I gave them all a card. So, I'm really hoping that someone will reach out and, ask me ask me to show them how to write a SQLite CLI. And, we will add that to the suite as well. And are there any other database engines that you're planning on targeting in the future? As I sort of,

hinted,

we currently don't have the bandwidth to handle any more databases,

simply because creating 1 of these things will only take a week, but then maintaining it and adding features and fixing bugs as they come up, that is the 1 that actually kind of drowns us out. So if anyone else is interested in, I I did get a couple of requests from people about Oracle CLI,

a few requests for writing 1 for Cassandra. And these are all, you know, great suggestions. And I'd love for someone to reach out and say if, you know, they are interested in in leading the charge, then then I'm happy to help them, do it. Well, for anybody who wants to get in touch with you and follow the work that you're up to and maybe get involved with some of the projects under the d b c l I umbrella. I'll have you add your preferred contact information to the show note. And so with that, I'll move us to the picks. And this week, I'm going to choose the movie Downsizing that I watched recently. It's just a really interesting movie with some social commentary and an amusing approach

to some of the,

problems associated

with overpopulation.

So I thought it was a very entertaining movie and well done, and,

I recommend that to anybody else who is interested. And so with that, I'll pass it to you. Amjit, do you have any picks for us this week? So I like listening to classical music,

Western classical music while I'm programming

because it does not have any words and it's it's nice and, easy to listen to. If you are getting into classical music, you know, after you've, you've exhausted the,

the usual heavyweights like Beethoven and Mozart and Bach and

other big, heavyweights. If you're interested in violin specifically,

I would highly recommend,

Pablo de Sarasate. He's a composer,

who wrote a lot of violin music, and, all his music is, sort of upbeat and, and very,

virtuosic, which is,

kinda hard to play on a violin, but, it sounds wonderful when you listen to it. So, that would be my pick. Alright. Well, thank you very much for taking the time to talk to me today and describe the work that you're doing with the d b c l I projects.

I've used some of them myself and found them to be quite useful. So thank you for that and I hope you enjoy the rest of your day. Thank you. Appreciate it.

The Python Podcast.init

Summary

Preface

Interview

Keep In Touch

Picks

Links

The Python Podcast.__init__