Creating a Low-Code CRUD Prototype

I needed to create a prototype for a client a couple of weeks ago in order to pitch an idea for a more automated internal system to manage key business processes.

Wwriting code is expensive and using a low or no code tool sounded like a great idea to throw something together ahead of time. Since the chances that I even made correct assumptions when I build the app are going to be wrong, it's best for me to spend as little time as possible building a prototype.

I need to make sure I choose the right provider

With the vast amount of available providers gravitating towards building CRUD (create, read, update, delete) apps with some drag and drop component layout manager, I had a pretty good pool to draw from. After a quick Google search I came up with this:

The decision was easy - I'm going with Retool!

I've heard a lot about Retool over the last couple of years since they advertised themselves as Excel with higher order primitives!

Retool is similar to other drag-and-drop, low-code editors and comes with a well established component library. There's also a ton of capabilities to call external APIs, running different types of data queries, and of course roll your own Javascript either on its own or related to some component.

Most of the drag-and-drop editors I saw with other apps felt a little underwhelming. I wouldn't be surprised if Retool had adopted an existing design system similar to Tailwind or made its own, but there's definitely a lot of attention to detail in there that makes usage very simple.

Retool creates a workflow trinity that is flexible and works exceptionally well. You make queries to fetch the data you need. You use javascript to transform the data you fetched. You craft visual components to display the data you transformed.

Realizing how many things aren't necessary to build a new app was enlightening

Since most developers I know don't have much UI / UX skills, the need to create wireframes wasn't a thing with Retool. The drag-and-drop editor works so well it's easy for technical minded developers to be able to select components from a visual component library and solve the interaction needs of the application needing to be built.

Armed with the simplicity of the Retool tooling, I was able to throw together the first screen in no time.

This page was fairly simple. It was essentially just a table filled in with some basic placeholder information, as well as a details pane to display additional information. Since the list the sole purpose of the listview is to promote a quick search, the details view would get populated with information related to the selected row that would otherwise clutter the list view.

What's great about things applications like Retool is lots of the boilerplate functionality related to the core purposes of the application being built is already done.

When creating the list view in the screen above, I didn't once have to think about implementation of things like:

Table pagination
Table filters and sorting
Table selection + multi-selection controls and logic
Loading spinners and other loading states
Notifications and other visual alerts
Transforming query output into JSON

With Retool, these are handled automatically and the aspects of which are either captured in a UI control or a simple variable or management field.

For the demo, I didn't have a live PostgreSQL database anywhere in the cloud I could use, and spinning up a server was more time and money I didn't want to spend.

Luckily for me, Retool has the capability to popuplate a table and wire up a search field to it with just a bunch of raw JSON. So I could do something like:

select * from {{[
  {
    id: 0,
    name: "Reliable Customer",
    phone_number: "5551235555",
    email: "reliable@gmail.com",
    notes: "This guy always pays on time and pays cash!"
  },
  {
    id: 1,
    name: "Bad Customer",
    phone_number: "5205555555",
    email: "doesntexist@gmail.com",
    notes: "This person is really hard to get ahold of"
  }
]}} where name ilike {{ '%' + customer_name_input.value + '%'}};

wire it up to an input field to provide filtering functionality and keep moving on to other things.

something here about javascript and the drag and drop editor

Initially, I was intimidated by the large amount of controls that queries and modules seemed to have. Once I started to understand what's going on, it's really simple to start moving forward at a rapid pace.

I eventually only used the Queries manager and wiring up data fields to tables, and use a bunch of dot notation wrapped in template strings. Having a background in Javascript, I was already really used to template strings and that's all Retool seems like it's doing: a bunch of SQL mixed in with some subset of Javascript standards.

For things like form components and buttons, I found it very simple to write more complex Javascript to accomplish some simple functionality.

The below is just a way to set the default value for a date picker. It's 100% Javascript but it was really cool to know that I could access any Query to pull some information out. All that I would have to access it is obey the same curly bracket syntax to break out of a text editor and get to the Javascript editor.

Arguably Retool's most indispensable feature is its fantastic interop with Javascript. You can slap in some Javascript almost anywhere you can think of, and it provides you with tremendous flexibility and power.

{{(function () {
  const today = new Date()
  const tomorrow = new Date(today)
  return tomorrow.setDate(tomorrow.getDate() + 1)
})()}}

but SQL Queries aren't the only thing I found out that I can create data with. In the estimates page, I have a table that lists out potential calendar slots for estimates.

The rows here aren't done by a SQL query, but rather a Javascript Query as Retool calls it:

const businessStartHour = 8
const businessEndHour = 17
let hours = []
for(let i = businessStartHour; i <= businessEndHour; i++){
  hours.push(i)
}

// `requested_job_start_date_picker` is the Retool Start Date picker control
const requestedStartDateMoment = moment(requested_job_start_date_picker.value)

// `estimates` is the Retool SQL Query
estimates.data
  .map(estimate => {
    // Convert start / end dates to moments
    return {
      end_date: moment(estimate.end_date),
      start_date: moment(estimate.start_date)
    }
  })
  .filter(estimate => {
    // Filter out any estimates not scheduled for the selected formatDataAsObject
    return estimate.start_date.date() == requestedStartDateMoment.date()
  })
  .forEach(estimate => {
    // Remove hours already taken up by the existing estimates
    const startHour = estimate.start_date.hour()
    if(hours.indexOf(startHour) >= 0) {
      const endHour = estimate.end_date.hour()
      for(let currHour = startHour; currHour <= endHour; currHour++) {
        const currIndex = hours.indexOf(currHour)
        hours.splice(currIndex, 1)
      }
    }
  })

// Make sure hours are zeroed out from the date/time selected in `requested_job_start_date_picker`
requestedStartDateMoment.set('minute', 0)

const requestedJobDurationHours = request_job_duration_hours_select.value
hours = hours.reduce((curr, startHour, i) => {
    let expectedHour = startHour
    for(let iter = i; iter <= i + requestedJobDurationHours; iter++) {
      if(typeof hours[iter] === 'undefined') {
        return curr
      }
      if(expectedHour === hours[iter]) {
        expectedHour++
      } else {
        // The start hour is not able to support the requested job duration
        return curr
      }
    }
    // Current hour is valid start hour
    return [...curr, startHour]
},[])

return hours.map(hour => {
  return {
    date: requestedStartDateMoment.set('hour', hour).local().toISOString()
  }
})

This is a lot of Javascript code and all it does is return a bunch of instants of start times for a predefined hour window that don't already exist in an estimates Retool SQL Query.

Since this Query lives in Javascript land, I don't have to break out using the curly brackets. I can also directly access other queries, or page components without having to do anything special; these properties are available in the global namespace. The only side effect to that obviously is I need to make sure I don't declare any variables that would create a collision.

The job list view is pretty much the same as the estimates view and I didn't have to learn any additional skills here to build the app. Since I'd already learned a ton from build the estimates page, I think I was able to get this page created and wired up in maybe 15 minutes.

I also created a basic customer detail screen that was a sort of enhanced lens into estimates and jobs, and could help inform the business owne what estimates or jobs a customer has ongoing if they were to call in. Again, it didn't do anything super different than what I already learned in the Estimates page.

I did learn about more component layout and which components are more appropriate in certain scenarios. As I progressed to different screens, I went back and applied lessons learned on layouts and queries etc, so the screens you see here were not the initial screens I had before moving on. These are more of the final version before I showed it to the client.

I'm sure there could have been some reuse between a lot of the queries and modules. Retool does make it easy to reuse pieces of apps or queries that can be imported into other apps so this is something I'll probably leverage in future applications.

Low / no-code platforms are game changers, but there's still a ways to go

A prototype like this would've taken me at least a day or two in React / Redux Javascript land. This is definitely where things are going in the future and I'm slightly afraid for myself and other web application developers who have invested so much into their skillset over the years. Once tools like this can get a little more polished, I definitely see them replacing much if not all of the the modern web development workforce.

As a consultant, I see software development as a means to an end. The business doesn't care if a developer uses React or Vue, Redux or Angular, Node or Deno, just like I don't care what kind of tools my mechanic uses. In that sense, business owners just want something that works.

If I can build a tool ten times faster in Retool and achieve the same effect as a custom-built app, I'm going to choose Retool every time. This commission management app seemed like an excellent opportunity to use Retool and get some experience with it. If Retool didn't work out, I felt like I could always build a custom React or Vue app as a fallback.