Catalyst Cooperative
Tool Review: NocoDB (No-Code Database) #2288
TrentonBush
started this conversation in
Ideas
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
NocoDB - A Solution for Non-Technical Data Access and Data Entry
NocoDB (as in "No code Database") offers a new option on the tradeoff spectrum between accessibility for non-technical users and technical quality for knowledgable data engineers.
NocoDB's advantage over a database is accessibility: it offers a spreadsheet-like GUI to view and interact with data. Users can perform CRUD (create, read, update, delete) operations on a database backend that conform to schema contraints like foreign keys, set membership, uniqueness, and nullability.
NocoDB's advantage over a spreadsheet is data quality: it empowers less-technical users to use a GUI to make and enforce schemas that define relationships between tables.
NocoDB is NOT:
NocoDB is likely best for simple data access and data entry.
Links:
NocoDB Makes Databases as Accessible as Spreadsheets
Our consulting work mostly consists of providing data engineering services to external analysts with great domain knowledge but less data/programming skill. Analysts often need to combine multiple sources of data, and a database is an excellent technical tool for this problem. NocoDB offers a semi-technical GUI to build schemas and interact with databases in a familiar spreadsheet-like UI.
Highly technical solutions ("all data must pass through a well tested and documented ETL pipeline into a database in the 5th normal form") force every tiny piece of data to pass through a data engineering bottleneck, frustrating analysts and engineers alike. Highly non-technical solutions ("just whack everything in Excel") allow quality problems to accumulate into untenable swamps of buggy, unsynchronized Excel sheets, frustrating analysts and engineers alike. NocoDB is one attempt to find middle ground.
NocoDB solves several problems for less-technical analysts and data engineers alike:
NocoDB also supports other view styles: gallery view, form view, and kanban board. These are each best at specific kinds of workflows (images/documents, data entry, task management) that I don't think are directly relevant to PUDL.
NocoDB Is Not a BI Tool
You cannot visualize data in NocoDB.
Formulas and transforms are available as a thin wrapper around SQL operations which provide fundamental tools for manipulating strings, numbers, dates, and conditional logic. Still, they are a slightly different vocabulary from Excel and thus present a learning curve to analysts who don't already know SQL.
I don't believe there is currently a way to create or access custom functions or stored procedures from the parent database. This means the computation ability of NocoDB is not extensible.
NocoDB Is Not an ETL Tool
Users can define schemas and import CSVs to tables, but that's not the hard part of ETL. NocoDB can't magically resolve inconsistency between sources or do record linkage or deduplicate records. That is still labor best performed by a technical person/team due to the data knowledge and domain knowledge required.
Notable Features
Easy Sharing, Including Publicly
Share whole bases, specific views, or even individual rows with a link, either to specific people or anyone with the link, with optional password gating. This is particularly nice for sharing forms for people to fill. Permissions can be set between editor/viewer or revoked at any time.
The NocoDB interface can be embedded as an HTML iframe.
Built In API Access (REST or GraphQL)
External services can access/edit NocoDB via auto-built API (either REST or GraphQL).
Bi-Directional Sync With Existing Databases
As of writing, NocoDB supports Postgres, SQLite, MySQL, MSSQL and Snowflake. Some other no-code tools use custom backends for simplicity, but this means you are forced to use the GUI or API to interact with the tool. NocoDB lets you alter the database in whatever way you want. It fills in sensible defaults for app-level metadata (like column view options) when changes come from an external source like SQL or python.
Built-in Communication Tools: Webhooks and Comments
Webhooks trigger actions on, for example, updating a particular table. It can send payloads to aribtrary URL endpoints, but builtin services include Slack, email, and various storage services like S3.
NocoDB also supports row-level comments through the GUI, so analysts can discuss specific data points in a shared viewing space.
Notable Limitations
Role-Based Access Controls Are Currently Insufficient
As of writing, you cannot assign read/write privileges with any more granularity than an entire project. The architectural hierarchy of NocoDB is Project -> Base -> Table -> View. Each user is invited to join specific projects and is assigned one of 5 pre-set roles per project (owner, creator, editor, commenter, viewer). There are no Base level permissions. The only Table level permissions are to completely hide it from any of the bottom three roles, effectively bundling read and write permissions together.
There are no user-level permissions. Everything is done through the 5 built-in roles.
If you want to set table-level permissions, such as making certain data warehouse tables read only while still allowing custom views off of them, I believe those permissions have to be managed in the RDBMS itself by denying the entire NocoDB service write permissions on those tables.
NocoDB's Commercial Future isn't Locked In
The open source product launched to much fanfare back in May 2021, but they still have not launched their hosted service. NocoDB raised $10.5m in Oct 2022 including a bunch of big Silicon Valley names, and seem to have about 6 active devs (based on GitHub commits). 3rd parties offer to host (presumably licensed), but ideally NocoDB would have a stronger revenue stream to support development.
No-code Tools Can Only Partially Solve Conceptual Deficiencies
Oftentimes analysts need to add small pieces of outside information to the curated, centralized datasets, but they lack either/both the conceptual understanding or skills to implement things like unique IDs, foreign key constraints, proper joins, and data modelling more generally.
The good news is that NocoDB addresses some conceptual issues by default! IDs are automatically created for every row. The built-in column types make it easy to define foreign key constraints, set membership constraints, and ensure uniform representation of things like date/time values and some common special cases like currency, phone numbers, emails, and URLs. But NocoDB cannot prevent a determined analyst from setting all dtypes to string!
NocoDB provides a GUI inferface to simplified SQL DDL, but cannot magically train people how to structure data or what problems to look for. NocoDB mostly addresses the technical barrier to implemention. Ideal usage probably involves a data literate person creating the core schemas for less technical users to extend.
Only One External Database Schema Per Project
Not only can a project only connect to one external database, it can only connect to one schema within that database. You cannot join or otherwise mix data from two separate schemas. This means you cannot separate a data warehouse from a data mart as different schemas in your database. It all gets mixed together.
Connecting Multiple Projects To The Same Database Leads To Conflicts
This is not surprising, but if multiple projects connect to the same database, they can make conflicting changes. Changes to individual values are synced immediately to all connected projects. Schema changes (changes to columns or tables) must be synced manually in the UI.
Competitors
The NocoDB devs describe the project as an open source alternative to AirTable. Other products in this family include BaseRow, Directus, and AirTable itself.
Beta Was this translation helpful? Give feedback.
All reactions