Introducing SQLTools - relational database tools in your browser

We’re happy to announce SQLTools, a suite of relational database tools that work in the browser and share code with the core Beekeeper Studio app. SQLTools is tailored to work with Postgres, MySQL, Sqlite, SQL Server, and Redshift.

SQLTools will grow, but right now it provides:

1. Table Builder - A visual builder for CREATE TABLE statements - point and click your way through SQL table creation.
2. SQL Formatter - - Copy and paste ugly SQL queries from your log files and get prettier, higlighted, SQL queries back.
3. Table Templates - Simple CREATE TABLE templates that can help you build your app faster. These are customizable in the Table Builder.

Why SQLTools Exists

Beekeeper Studio was born from a frustation with existing relational database tools. We wanted a friendlier, open source database GUI, so we built one.

Desktop apps aren’t the only frustrating thing about working with databases. Even when building Beekeeper Studio I continually needed to search for such terms like:

• Example Postgres CREATE TABLE
• Sqlite timestamp field default now
• SQL Server create table syntax

This is fine, I search all the time! What isn’t fine? The results. Dry blog posts, SEO pumped single purpose domains, and thorough technical documents…which are super dense and hard to skim.

This led us to SQLTools.

SQLTools Is Made Possible By Electron And JavaScript

If you just want to use SQLTools, now is a good time to stop reading. I’m going to talk below about the technical background of SQLTOOLS

Gregory and I do not get paid to work on Beekeeper Studio (yet). We do the work because we think it’s valuable, and we hope eventually it can be full time.

For most small part time teams, making a second app would be productivity suicide. Fortunately we have a secret superpower - our app is built with Electron, and all of our code is JavaScript.

Beekeeper Studio is built with Vue.js and JavaScript. SQLTools is also built with Vue.js and JavaScript. See where this is going?

We have reorganized our repo to include both apps, and importantly a shared directory. This allows us to build Vue components that can be used across both apps with minimal effort.

As we add features to Beekeeper Studio, we can add features to SQLTools with minimal effort. We can’t afford to maintain two totally separate apps, but this set up allows us to continually improve and update SQLTools by simply improving Beekeeper Studio (or vice versa).

The Prototype - SQL Table Builder

We recently introduced the table properties view into Beekeeper Studio, which shows read-only column, index, trigger, and relation information for exisiting tables. The logical next step for us was building a way to create database tables.

Remember my web search pattern from earlier? I always forget the syntax for CREATE TABLE statements, so wouldn’t it be nice if we could also expose this functionality to the broader web? Maybe someone else (like me) would find it useful.

We accomplish this with a new shared component - Schema Builder.

Truthfully this was an experiment. Can we share code between two projects without it becoming a total nightmare?

While we’ve made some compromises, the answer is yes. SchemaBuilder.vue was created for SQLTools, but yesterday I integrated it into Beekeeper Studio in about an hour. Even I was surprised by how easy it was.

[Rant] Problems With Monorepos

This could be it’s own blog topic entirely, but let me just highlight a few points. In summary while plenty of organizations use monorepos for JavaScript and Typescript, we were very surprised at how immature the tooling is.

1. Yarn workspaces is totally flawed and does not isolate dependencies properly.

   We initially tried to use Nuxt for SQLTools, but Yarn was unable to properly resolve differences in Vue versions and the build chain. The only way around this is to prevent hoisting. Nohoist is NOT the default. It should be. Unless each project uses the same dependencies, hoisting is a total nightmare. We decided to simplify and use the same toolchain for both apps.

2. Webpack’s dependency model causes lots of issues.

   Webpack and webpack plugins are used to relying on transitive dependencies from other packages

3. Having a shared ‘package’ is a bad dev experience

   Initially we had three packages - app-studio, app-sqltools and lib-shared, so to build either app, you first have to build lib-shared. This is an awful experience as it requires you set up lib-shared like a real package, with typings, source maps, and explicit exports. Updating a shared file also makes dev servers freak out and hot module reloading rarely works right.

Try SQLTools

Anyway, if you need to create a database table, or want to format some SQL, please checkout SQLTools. We hope you find it as useful as we do :-).

---