DEV Community: Forest Admin

An admin panel with thousands of n8n automations under the hood

Monika Ambrozowicz — Tue, 20 Jun 2023 16:00:10 +0000

We're thrilled to announce an integration of Forest Admin and n8n. This integration allows Forest Admin to take the first steps towards workflow automation, bringing even more power than a person could build.

n8n is an open-source workflow automation tool that allows users to quickly and easily create powerful workflows, automating tasks and processes across different applications and services. It supports thousands of services out of the box, including Slack, Google Sheet, Hubspot, Customer.io, custom HTTP endpoint, and much more.

For the past few years, n8n has become really popular as an open-source workflow automation tool. Here at Forest Admin, we are huge fans of it. We use n8n regularly, and it's become one of the most important resources in our company. We highly respect the company and almost cannot stop talking about it and expressing our admiration every day.

An admin panel with thousands of automations under the hood

When we created Forest Admin, our mission was to help companies focus their development effort on customer-facing apps, thereby reducing the time and resources they spend on internal tools like the admin panel.

The admin panel is initially seen as a simple CRUD interface. As time goes on, it becomes increasingly necessary to have custom actions, permission control, and an optimized user interface in order to effectively oversee the company's entire business operations.

With the n8n integration, we are taking a big leap forward in workflow automation with Forest Admin, providing an admin panel that offers a wealth of features that would be out of reach for most development teams.

Forest Admin starts off from the foundation of the CRUD admin panel to end with the go-to-tool for all of a company’s operations.

The integration of Forest Admin and n8n opens thousands of possible actions and automations launched from an admin panel. Here are just a few of them identified by the first users of this integration:

Enrich user information with CRM data for better lead scoring.
Anonymize user data with one click on the button. Send a SMS reminder for nearing subscription renewals.
Export customer orders and generate automated invoices.
Change a user's subscription and send a Slack notification to the team.
Export a list of transactions, and send an automated email with a csv file to the accounting.
Generate a push notification when a payment is received.
… And much more. The possibilities and third-party integrations available are endless!

How does it work?

Getting started with automations on Forest Admin is super easy, and it doesn’t require coding unless you want to add additional customization and functionalities.

Create your n8n workflow

Go to n8n and create a new workflow with a webhook trigger.

Then select POST as the HTTP method and copy the Webhook URL.

Create the Forest Admin action

In the settings panel of the collection that the action is associated with, create a new action and select n8n. Simply paste the Webhook URL and you're all set!

Real-life end-to-end example

In the following example, let's say I am part of the Customer Care team of a fintech company and one of my responsibilities is to onboard new companies that have just signed up. To do my job efficiently, I go to my Forest Admin "Onboarding" Workspace every day and make a decision as to whether or not to validate the new account.

In order to have a maximum amount of information on the company that I must validate, I have created an "Enrich Data" action button which gives me a whole series of information that will help me make the decision such as the number of employees of the company, its estimated annual income, the total amount of its funding raises, its creation date, etc.

In the following example, I use the company "Avvo". Based on the enrichment data, I can confidently validate the account by clicking on the "Mark as live" button. All of this is done through one single interface, optimizing the number of clicks to access the information and trigger the necessary actions depending on the situation.

The implementation of the n8n workflow is simple. First, I retrieve the details of the selected company through a SQL query on my database. I then ask Clearbit for the company domain name and ask it to enrich this company. Then, I do a second enrichment phase with another tool, Apollo. Finally, I update the record of my database with the new data retrieved from this enrichment.

Try it by yourself for free

Postgres Enums dissected

Gabriel — Fri, 26 May 2023 11:00:59 +0000

PostgreSQL is a popular open-source object-relational database management system known for its stability, performance and feature-rich SQL implementation.

Postgres enums (short for "enumerated types") represent one special data type in PostgreSQL that allows you to define a list of possible values for a column. They can be used to enforce data integrity and make it easier to query and sort data.

Let's sky dive through them to see what they have to offer and how to best use them.

What are Postgres enums for?

In PostgreSQL, an enum is a data type that consists of a set of symbolic names (enumerators) and their values.

Enums are created using the CREATE TYPE command and can be used in a table column just like any other data type (e.g. integer, varchar).

Enums have some important differences from other data types:

Enum values are stored as integers, with the enumerator names serving as aliases for the integer values.
Enums have a fixed internal storage size, which is the size needed to store the largest enumerator. This means that enums may take up more space than other data types (e.g. varchar) in some cases.
Although Enums were originally fixed – meaning you could not add or remove values once you defined the set of enumerators – Postgres 9.1 released in 2011 enabled the ALTER function, as shown here:

Keep in mind however that when you alter an enum type, all columns that use this specific enum type will also be affected. It is therefore important to update all relevant code and scripts as to avoid any potential backfiring.

How to create and use Postgres enums?

To create an enum type in PostgreSQL, you can use the following syntax:

Here is an example of creating an enum type called "status" with the possible values "active", "inactive", and "deleted":

To use an enum type in a table, you can specify it as the data type for a column:

Enums also have a number of specific functions and operators that you can use when querying data. For example:

enum_range() returns an array of all enumerators for an enum type, in the order they were defined.
enum_first() returns the first enumerator for an enum type.
enum_last() returns the last enumerator for an enum type.

Postgres enums cons

As mentioned earlier, enums are fixed and cannot be altered once created. In other words, they are not flexible.

This can be a disadvantage if you need to add or remove values from an enum type.

Furthermore, if you need to change the values of an enum type (e.g. renaming an enumerator), you will need to create a new enum type, migrate the data to the new type, and then update any references to the old enum type. This can be a time-consuming process.

Best practices for using Postgres enums

It is important to keep your enums organized and maintain them properly to avoid issues down the road. Here are some tips:

Choose meaningful names for your enumerators
Avoid using abbreviations or acronyms that may not be immediately clear
Use enum ranges to define groups of related enumerators (e.g. "pending", "approved", "rejected" for a "status" enum)
Consider using a tool or script to automate the process of creating and updating enums

Considerations for using Postgres enums in relation to other data types:

Enums may be more suitable for small lists of fixed values (e.g. "male" or "female" for a "gender" column)
For more extensive lists or sets of values that may need to be updated or changed more frequently, other data types (e.g. varchar, integer) may be more appropriate.
Be aware of the potential for increased storage size when using enums, especially if you have many enumerators with long names.

In summary, enums are a useful data type in PostgreSQL that can help improve data integrity, facilitate querying and sorting, and potentially improve performance.

However, it is important to consider the limitations and potential challenges of using Postgres enums, and to choose the data type that is relevant to your needs.

Who are we?

Forest Admin is an admin panel solution that saves your back-end engineers time and gives your operational teams more autonomy. Our highly customizable admin panel connects to your databases and APIs to ease your operations so that you can focus more on your business and less on backend operations.

If you have any doubts, here's a quick view of the features Forest Admin brings to the table

Data: Forest Admin's standout feature is its ability to scan your data structure and automatically generate an operational admin panel.

Customization: Once the initial admin panel has been auto-generated, Forest Admin enables you to tailor its functionality to match your precise needs, making the tool adaptable for a wide array of business applications.

Workspace: The dream tool to manage your daily operations. This drag and drop feature enables you to create workflows at will in order to ease your workload. The rule is simple: one workflow, one workspace.

Dashboard: Customize your own dashboard to take a quick look at key metrics.

Integration Capabilities: Forest Admin can be installed in two ways: connecting it to an existing application built on frameworks like Node.js, Django, Laravel, Ruby on Rails, or linking it directly to one or multiple databases – check all of our integrations on our integration page.

Ready to streamline your operations? Sign up for Forest Admin today.

If you want to know more about various Databases, here are a few articles we’ve written on the subject:

PostgreSQL data types and more

Gabriel — Thu, 11 May 2023 13:25:10 +0000

In object-oriented programming [OOP], what a programmer does ultimately can be summarized by the manipulation of objects. These objects are various data types that interact with each other while respecting a specific set of rules – themselves depending on the programming language or framework in use.

When writing a program that manipulates these objects, the programmer must create a database to save all the work performed on the aforementioned objects by the program– these objects are also referred to as data types.

Although there are many different databases, we will focus on one only here : PostgreSQL – and more specifically on the types of data it can store.

Let's start by addressing the elephant – pun intended – in the room: what exactly is Structured Query Language [SQL]?

What is SQL?

SQL is a programming language used to interact with and manipulate databases. It enables to create, modify and query databases, as well as to control access to them.
It has become the standard language in the development world.
Anything that has to do with databases, such as web applications, data warehouses and business intelligence systems is often handled in SQL.

SQL is a powerful and flexible language that allows users to perform a wide range of tasks, including creating and altering database structures, inserting and updating data, and querying data for specific information.

There are many SQL databases tools to be used, but some rise in popularity faster than others.

Why is Postgres so popular?

Postgres has undeniably become one of – if not the one – most popular relational database management system [RDBMS].

The reasons why are many but, if we’re to name a few, one could argue that it is largely due to the facts that it is secure, scalable, protects data integrity and because it complies with ACID principles [Atomicity, Consistency, Isolation, Durability] to ensure safe transactions.

Alternatives to Postgres include but are not limited to MySQL, MariaDB, Microsoft SQL Server or MongoDb – all of which Forest Admin integrates at ease.

There are several factors that can influence the choice of a database for a particular project. The Data model, performance, cost and the possibility to integrate the database with other systems if needed.

Getting into the meat of it now, what data types can be stored in PostgreSQL?

Data types

In PostgreSQL, a data type is a specific kind of object that can hold a particular type of data.

There are data types families and then the actual data itself.
When it comes to families, here they are, from the most basic to the most special:

Boolean
Character Types [char, varchar, and text]
Numeric Types [integer and floating-point number]
Temporal Types [date, time, timestamp, and interval]
UUID [Universally Unique Identifiers]
Array [ array strings, numbers, etc.]
JSON [JSON data]
hstore [key-value pair]
Special Types [network address and geometric data]

Here is a list of some common data types in PostgreSQL with a detail of what it is, and an example – again, each of them belonging to a family mentioned above:

integer: stores a 32-bit signed integer – 42
bigint: stores a 64-bit signed integer – 8092233477720368557
smallint: stores a 16-bit signed integer – 26727
decimal: stores a fixed-point decimal number – 56.11
numeric: stores a variable-precision decimal number – 12345.678
real: stores a single-precision floating-point number – 3.14
double precision: stores a double-precision floating-point number – 7.14159232384665358979
boolean: stores a true or false value – true
char: stores a single character – ’J’
varchar: stores a variable-length character string – ’We are Forest Admin’
text: stores a variable-length character string – ’ We are a back office solution that helps businesses manipulate and better understand their data.’
date: stores a date (year, month, day) – '2022-12-16'
time: stores a time (hour, minute, second) – '18:45:22'
timestamp: stores a date and time (year, month, day, hour, minute, second) – '2022-12-16 18:45:22'
interval: stores a duration of time (e.g. 2 days, 3 hours) – ’5 days 7 hours’
array: a collection of items that are stored in a contiguous block of memory – [‘Hello’, 4, ‘World’, 89]

Note that these are just examples and may not necessarily represent the exact syntax for using these data types in a PostgreSQL database.

Char may require a length specifier and timestamp most often than not has additional formatting options.

Each data type has its own set of properties and limitations.

Alternatives to SQL

Although we covered PostgreSQL, there are various alternatives to SQL that you may want to consider depending on your use case and, let’s face it, your inclinations.

Alternatives such as NoSQL [MongoDB, Cassandra], NewSQL [Google Cloud Spanner], Graph [Neo4j, Apache TinkerPop], Key-value [DynamoDB], Column-oriented [Apache HBase] or Object-oriented [GemStone].

If you feel like chatting with us because you're curious about what we do and how we can help you, please reach out to us.

Who are we?

Forest Admin is an admin panel solution that saves your back-end engineers time and gives your operational teams more autonomy. Our highly customizable admin panel connects to your databases and APIs to ease your operations so that you can focus more on your business and less on backend operations.

Ready to streamline your operations? Sign up for Forest Admin today.

If you want to know more about various Databases, here are a few articles we’ve written on the subject:

Laravel vs Symfony

Gabriel — Mon, 19 Dec 2022 17:48:08 +0000

If you’re thinking of what web application framework to choose for your PHP project, you’ve come to the right place.

Picking the good framework is paramount to increase efficiency and ensure delivery, depending on what you do. The two most popular PHP frameworks to date remain Symfony and Laravel.

While both frameworks have similar capabilities and share many common features, there are some key differences between the two that we list here to help you make up your mind.

Let’s start with a bit of history.

Genesis

PHP – standing for Personal Home Page – is a programming language that was originally created by a Danish-Canadian programmer named Rasmus Lerdor, in 1993, to handle his own website.

It was designed to make it easier to create dynamic web pages, and it quickly became a popular choice for web developers because of its simplicity.

Laravel and Symfony are both open-source PHP web frameworks used for web application development. Laravel was created by Taylor Otwell in 2011, while Symfony was created by Fabien Potencier in 2005.

Laravel is known for its elegant syntax and ability to handle complex web applications easily, making it a popular choice among developers. Symfony, on the other hand, is known for its stability, flexibility, and the large number of features it offers.

Many popular websites and web applications, such as Drupal and Magento – acquired by Adobe Inc in May 2018 for $1.68 billion – are built using one of these frameworks.

Both Laravel and Symfony have been widely adopted by the web development community, and have a strong presence in the PHP ecosystem.

Splitting the bill

Although you can build any app from scratch with both frameworks, their technical differences sets them apart.

Symfony is a full-stack framework that provides a wide range of tools and components that can be used to build a wide variety of web apps. That being said, it is often used by larger companies tackling bigger and more complex issues – often involving substantial features.

It allows for a high degree of flexibility and customization, which logically makes the learning curve steeper for new users.

Laravel’s smooth code and various magic methods have helped, among other things, to make it one of the most popular PHP frameworks. It is however more opinionated a framework in the sense that it has a specific set of conventions and assumptions on the way web apps should be built. In other words, flexibility is not its forte. It has a structure and wants you to respect it.

It is often used by smaller teams and individual developers who wish to turn-on the auto-pilot – so to speak. Providing such a strict structure and guidelines helps with its simplicity and ease of use.

Moreover, Laravel is built on top of a monolithic architecture – meaning your entire application runs as one single program – which provides a more integrated and cohesive framework for building web applications. This can make it easier to learn and use, but may also limit the flexibility and customization of the framework.

Overall, Symfony and Laravel are both popular PHP web application frameworks, but they have some key differences in terms of their focus, target audience, architecture, and design. Which framework is better will depend on the project you’re working on.

No need to choose with Forest Admin

Regardless of which framework you chose to build your application – we’ve got you covered on both.

Forest Admin provides a highly flexible and secure internal tool solution to make sure you have the admin panel you need to understand your business and grow.

Find all you need to know about our integration for Symfony here, and Laravel there.

If you feel chatting with us because you're curious about what we do and how we can help you, please reach out to us, or sign up directly.

We will be happy to discuss with you and book a demo to show you what Forest Admin can do.

Bullet point recap

Laravel pros

Easy to code and maintain.
Built-in features that help developers build sophisticated web applications quickly and easily.
Easy unit testing, which can help ensure that your application is reliable and bug-free.
Scalable for small to medium size web applications.
Secure with its built-in access control system.
Active community of developers who contribute to the framework. This means that you can easily find help and support when you need it.
Easy Data migration

Laravel Cons

Can be difficult to learn to those new to web development.
Lighter framework, which means more limited in scale.
Slower when application is getting too big for the framework.
Opinionated.
Frequent updates.

Symfony pros

Great code quality.
Easy integration with other popular frameworks and libraries. This allows developers to easily use the best tools for each task, rather than being limited to a single framework's capabilities.
Large and active community. Symfony has the largest community in the market.
Built-in testing support.
Highly flexible and customizable, which allows developers to tailor the framework to their specific needs and preferences.
Good documentation. Symfony has extensive documentation that is regularly updated.

Symfony cons

Steep learning curve due to its wide range of possibilities. It just takes time to wrap one’s head fully around it.
Performance issues because of its complex framework and because it also relies on other technologies.
Longer tests because of the need to prebuilt code for multiple uses.
Lack of support. Although Symfony has a large and active community, it is not as well-supported as some other PHP frameworks.

Forest Admin, the pioneer of low code internal tools, is now free with unlimited users

Monika Ambrozowicz — Wed, 02 Nov 2022 10:55:53 +0000

In the last couple of weeks, the tech industry has witnessed several announcements about increasing prices of popular tools. Heroku has canceled its free plan after offering it for over a decade. Slack has increased its price and limited its free plan to only 3 months. Figma lovers are worried if Adobe – the new owner of their essential design tool – is going to limit its free plan as well, even if there are no such talks at the moment.

Forest Admin, the pioneer of low code internal tools, has made the decision to go against the trend and offer a fully-functional and customisable admin panel solution for free for unlimited users. It means that regardless of how many people use Forest Admin in a given company, it will be free of charge. This update reflects the long-term mission of Forest Admin to make developers’ lives easier and, even more importantly, they feel that it better fits its users’ different stages of growth.

“We want as many people as possible to be able to use Forest Admin and reap the benefits of having a powerful, easy-to-use admin panel from day one.”
Sandro Munda, Forest Admin’s CEO

Now, developers can sign up for a free account (no credit card required), set up a fully-functional internal tool in the blink of an eye, and invite an unlimited number of users. All of it for free – no catch.

Forest Admin also has a paid offer for businesses that need more than what the free plan offers. The paid version enables setting up roles and permissions to determine who can do what on the admin panel and quickly adjust it when needed. For example, a C-level executive will not see or use an admin panel in the same way as a developer who needs more technical data. Likewise, an operational person will need to see the data in a specific way to do their work efficiently. This feature is a part of a Team plan that costs $12 per user.

On top of that, there are advanced functionalities for established businesses that need advanced security features like SSO or IP Whitelisting, approval workflows, collaborations features, and more. These can be found in a new Plus plan that costs $48 per user. All details can be found on the pricing page.

About Forest Admin

Founded in 2017, Forest Admin is a low-code internal tool solution that helps developers save time and provide their business’ teams with internal tools such as fully-featured admin panels, dashboards and database manipulation tools that are tailored to their operations and ready to scale. Thanks to its unique hybrid architecture, Forest Admin offers a business-grade level of security as user data never reaches Forest Admin servers.

Top 5 MariaDB GUI tools in 2022

Monika Ambrozowicz — Mon, 01 Aug 2022 12:48:57 +0000

According to the DB-Engines Ranking, MariaDB ranks 12th overall, and 8th among the relational databases, ahead of Microsoft Azure, Amazon DynamoDB, Snowflake, Redshift, and hundreds of others. What is more interesting though, is that MariaDB was ranked 20th just five years ago, and its popularity only seems to grow.

MariaDB is an open-source fork of MySQL, compared to which it has faster performance, and is more suitable for large-sized data. Both databases are highly compatible with each other, which allows a lot of tools to work equally well with MariaDB and MySQL. It is also true for Forest Admin as the onboarding is exactly the same whether you choose one of them or another. The same can be said for other popular MariaDB GUI tools, like DBeaver, dbForge Studio, HeidiSQL, but some tools are either dedicated to one database or they perform slightly better depending on the database.

Now let’s have a look at the most widely-used database GUI tools that are proven to work well with MariaDB. We have chosen them based on the official MariaDB Knowledge Base, their popularity on the r/MariaDB community on Reddit, and reviews left on TrustPilot.

DBeaver

DBeaver is a free and open source multiplatform tool for all people who work with databases. Apart from MariaDB, it supports MySQL, PostgreSQL, Oracle, MS Access, MongoDB, Redis, and many more databases. It is actively maintained, and users are invited to report bugs and suggest features on the DBeaver GitHub page.

DBeaver for MariaDB. Source: https://dbeaver.io/

Basic features:

Data viewer and editor: sorting, filtering, export, etc.,
Data and metadata search,
Metadata browser: viewing and editing tables, views, columns, users, roles, procedures, etc.
File export and import, from a variety of formats such as CSV, HTML, XML, JSON, XLS, and XLSX.
SQL editor.

Supported platforms:

Linux,
Windows,
macOS.

Pros:

DBeaver is easy to install and to connect to a database. It runs as a desktop application, which is good for beginners as no CLI proficiency is required to get started.
You can run SQL queries even without strong SQL skills.
It comes with strong features to view and manipulate your data.
Users praise its predictive typing and claim DBeaver saves them a lot of time.

Cons:

Some users complain about old school-looking UI.
The fact DBeaver runs locally, as a desktop application, may also decrease its performance. To tackle this problem, you need to switch to an enterprise plan.

dbForge Studio

dbForge Studio is a fully-featured GUI tool to develop, manage, and administer MariaDB and MySQL databases. It is developed by Devart, one of the leading developers of database management software, ALM solutions, and data providers for the most popular database servers. dbForge is a full-fledged MariaDB (and MySQL) GUI that is used to create and execute queries, analyze data, compare and synchronize databases, and much more. dbForge Studio is not available for free, the pricing starts at $179.95 per year.

dbForge Studio for MariaDB. Source: https://www.devart.com/

Basic features:

Intelligent SQL Coding,
Database compare and sync,
Data import and export,
Database copying, designer, refactoring, data generator, administration, backup, migration,
Query profiling,
Visual query builder,
MySQL debugger,
Table designer,
Reports and analytics.

Supported platforms:

Windows

Pros:

dbForge Studio is a powerful GUI tool with a rich visual design, and an intuitive UI.
Users praise its SQL editor, code suggestions, stability, and a user-friendly interface.

Cons:

dbForge Studio is not officially available for Linux and MacOs.
It is neither free nor open source, but there is a 30-day free trial.

DbSchema

DbSchema is a visual database design and management tool for all relational and a few No-SQL databases. It claims to help with design, documenting and managing databases without having to be an SQL expert, as the schema can be edited with a few clicks, without writing complex SQL queries. DbSchema is reverse-engineering the database schema and visualizing it as diagrams. After 15 days of a free trial, the price of DbSchema starts at $98 for academic purposes.

DB Schema dor MariaDB. Source: https://dbschema.com/

Features:

Logical & physical design,
Interactive diagrams,
Generating interactive HTML5 documentation,
Schema synchronization,
Visual query builder,
Relational data editor,
Editing and executing SQL queries,
CSV editor for large files,
Database reports & charts,
Automating schema deployment & tasks,
Dark theme, and more.

Supported platforms:

Linux,
Windows,
macOS.

Pros:

DbSchema’s users praise it for supporting multiple databases, simple and intuitive UI, and useful but not overly complicated features.
It uses a copy of schema structure, which is independent and separated from the MariaDb database. DbSchema can handle large databases with more than 10.000 tables. Connecting to another database won't change the model schema unless you choose to refresh it from the database.

Cons:

The free version is limited.

Navicat for MariaDB

Navicat is a graphical front-end for MariaDB. It is compatible with all versions of MariaDB and with MySQL 3.21 and above. It is a part of a bigger Navicat ecosystem of products like Navicat Premium (a multi-connection database development and administration tool which can simultaneously connect 7 databases), Navicat for MySQL, Navicat for MongoDB, Navicat for Oracle, and more. All of them are commercial tools, with pricing that starts at $119.00 per user.

Navicat for MariaDB. Source: https://www.navicat.com/

Supported platforms:

Linux,
Windows,
macOS
iOS.

Basic features:

SQL processing: SQL editor, find and replace, create parameter queries, open query files in an external editor, console.
Data Viewer and Editor: grid view; Text, Hex, Image or Web viewer/editor, raw mode editing, filter records.
Import/export data from/to plain text file formats: TXT, CSV, XML, JSON
Dump/Execute SQL file
Collaboration: create projects, add members
Synchronize connections and queries
More features are available in higher plans. Check Navicat for MariaDB’s website for the complete feature matrix.

Pros:

As a commercial product, it comes with more features than most of its open-source counterparts.
Navicat is user-friendly, users like its modern UI, and the fact that Navicat is very quick and easy to get started with.

Cons:

Navicat’s pricing can be a blocker for smaller projects.
It tends to be resource-intensive.

HeidiSQL

HeidiSQL is a free open source tool initially built for MariaDB and MySQL, and now also supporting PostgreSQL, MicrosoftSQL, and SQLite. HeidiSQL is only available for Windows, as a Windows app, which doesn’t prevent it from being ranked as one of the most popular tools for MariaDB and MySQL.

HeidiSQL UI. Source: https://www.heidisql.com/screenshots.php?which=database

Basic features:

Connecting to multiple servers in one window.
Connect to servers via command line.
Creating and edit tables, views, stored routines, triggers and scheduled events,
Exporting from one server/database directly to another server/database.
Managing user-privileges.
Exporting table rows as CSV, HTML, XML, SQL, LaTeX, Wiki Markup and PHP Array.
Browsing and editing table-data using a comfortable grid.
Bulk edit tables (move to db, change engine, collation etc.).
Writing queries with customizable syntax-highlighting and code-completion.

Supported platforms:

Windows

Pros:

For non-English speakers, HeidiSQL is available in 22 other languages.
It’s a very good lightweight tool for managing several databases at the same time.
It’s free and open source.
HeidiSQL is easy to install as a Windows app and to use.

Cons:

According to some users, HeidiSQL is buggy and it doesn’t provide advanced debugging features.
Low stability. Despite the wide popularity, HeidiSQL is known for bugs and issues.
It is only available for Windows, just like dbForge Studio

MariaDB GUI tool or MariaDB admin panel?

Admin panels, internal tools, and database GUI tools are often used interchangeably. After all, their main functionalities are similar – all allow accessing data and performing at least basic CRUD operations. Database GUI clients give their users full control over their data but they need to operate on raw SQL queries, which is a blocker for non-technical users. If it’s not an issue, and/or a database GUI tool is used infrequently, mostly for firefighting, you may not need a fully-featured admin panel.

The situation changes when sales, operations, customer support, marketing, and other, typically non-technical teams need secure access to data to perform their day-to-day activities in an efficient manner. Internal tools like admin panels offer much more functionalities, including roles and permissions management, approval workflows, adding data, creating visual dashboards, editing content, contacting users, validating documents, sharing internal tools with external partners, and so on.

Forest Admin - a MariaDB admin panel

Forest Admin belongs to the second category. As a fully-featured admin panel solution, it can act as a database GUI, or easily become a powerful internal tool that fits every business logic. Unlike competitors, Forest Admin doesn’t just provide the UI, but it scans the data structure and generates a fully-functional admin panel with all basic features like CRUD, search, export, and more, out-of-the-box. Then, you can customize it according to your needs. Thanks to its unique architecture, Forest Admin can never access user data, which stays on their servers.

Forest Admin UI with out-of-the-box features like search and filters. Source: https://www.forestadmin.com/usecases

There are 2 ways of installing Forest Admin – by connecting it to an existing app (built in Django, ExpressJS, Laravel, NestJS, Ruby on Rails, and more integrations are on the way) or directly to a database. At the moment, Forest Admin is available for MariaDB, MySQL, PostgreSQL, MicrosoftSQL Server, and MongoDB. And here is what one of our users has recently told us:

I really appreciate how easy ForestAdmin makes it to make frequent DB schema changes without breaking the UI. I haven't heard of any other tools that are able to handle schema changes so well!

Summary

As you can see, there is no shortage of database tools. Some provide basic functionality, while others offer advanced all-in-one tools used by thousands of team members to manipulate the data. There are also solutions like Forest Admin that let you quickly start with a small project and then easily scale it once it grows. If you want to give it a try, get started for free today.

NestJS vs ExpressJS: Which is better for your project

Monika Ambrozowicz — Mon, 11 Jul 2022 13:08:22 +0000

Rumour has it that ExpressJS has been slowly declining in popularity. Although the latest results of Stack Overflow’s 2022 survey don’t seem to prove it, it’s true that Express has gained serious competitors. NestJS, Koa, Fastify, and other frameworks are becoming more popular, and product managers tend to debate whether to build their next project with ExpressJS, or rather give new (yet already mature) frameworks a chance.

Forest Admin is compatible with apps built with ExpressJS, NestJS, and soon it will also be available for Koa and Fastify developers. In this article, we compare ExpressJS with NestJS to help you choose the best framework for your application, as some of the differences between them are fundamental.

ExpressJS: Lightweight and the most widely-used

According to Stack Overflow’s 2022 survey, ExpressJS is still among the most widely used web frameworks, and it is considered a very rational choice to build large-scale applications with. Express is a JavaScript framework, so both the frontend and the backend can be built using the same (extremely popular) language.

One of the biggest strengths of Express is its strong community and the number of available plugins. For example, storing sessions, cookies, parsing a payload, and so on, are available out-of-the-box, and it takes much less time and lines of code to build an application with this framework. Another big advantage of Express is that it is supported with the Google V8 engine, which ensures high performance without lags or errors in the processing.

NestJS: Reliable and scalable

Finally, ExpressJS is considered lightweight and straightforward. Its popularity and a strong community makes it easier not only to find resources but also support.

NestJS is a rapidly growing NodeJS framework, and it seems the growing number of developers prefers it over ExpressJS. It serves the same purpose – to build efficient, reliable and scalable server-side applications – but it does it in a very different way.

NestJS is inspired by Angular, which is a big advantage for developers who are already familiar with it. At the same time, everything supported in Express, for example Express functions, is also supported in Nest, which makes switching from one framework to another easier.

A big advantage of NestJS is that it is built with TypeScript, which makes the application reliable and less prone to bugs. It makes NestJS a particularly good choice for building large-scale applications. However, using JavaScript is also possible with Nest.

ExpressJS vs. NestJS

The biggest difference between these frameworks is that NestJS is opinionated, and ExpressJS is not. It means Express gives developers the freedom to make multiple possibilities and implement code as per the need, as it doesn’t have a set of pre-defined rules to follow. Such a flexibility is appreciated by many developers, and it’s beneficial for smaller flexible teams, but once team size and app’s complexity grows, the lack of structure becomes a problem.

NestJS, on the other hand, uses the “convention over configuration” paradigm that attempts to decrease the number of decisions developers need to make, and it is expected of them to write the repos, service, and controllers in a specific manner.

On top of that, NestJS follows the MVC architecture with components like modules, controllers, and providers, used to control logic, data, and implement the UI. ExpressJS doesn’t follow MVC, which in some projects leads to ineffectiveness and inefficiency due to the lack of structure.

Despite these differences, both Express and Nest are suitable frameworks for building single-page applications, e-commerce websites, fintech, and streaming applications. Both are the most widely-used Node.js frameworks, and although Express is more popular (57k stars on Github compared to 48k given to NestJS), the popularity of Nest is growing faster.

Forest Admin for ExpressJS & NestJS

An example of an admin panel built with Forest Admin. Source: Live Demo, Forest Admin has no access to user data!

Whether you choose building your web application with ExpressJS or NestJS, you can still equip it with a powerful admin panel thanks to Forest Admin. However, the choice between Express and Nest will impact the way you integrate it with Forest Admin, as our brand new NestJS integration has been built in a recently developed way, with lots of new benefits, such as:

Low code customization. You will write less code, only when you really need to do it. Forest Admin magic will do the rest, without compromising on data security. It is also true if you choose Express, but requires a bit more coding.
Clearer concepts. We no longer distinguish between collections and smart collections, relationships and smart relationships, etc. Forest Admin for NestJS/SQL has collections, relationships, fields, and so on. Easier concepts means faster and hassle-free customization.
Always in sync with your database. Thanks to Forest Admin SQL integration, each time your server restarts, your admin panel UI is in-sync with your database.

We strongly recommend giving Forest Admin for NestJS a try, but our integration with Express is equally reliable and it has been tested by a thousand of users. Whether you need a robust and flexible admin panel for an Express, Nest, (and soon also Koa or Fastify) app, get started with Forest Admin for free.

The guide to Laravel admin panels

Monika Ambrozowicz — Mon, 23 May 2022 11:35:57 +0000

Are you researching different options to ship an admin panel for your Laravel application? One idea would be to build it from scratch with Laravel. After all, this PHP-based framework facilitates building various products, including internal tools.

Where is the catch, then? Well… You’ll have to engage not only Laravel developers but also UX, Design, and Product. Otherwise, you risk equipping your business teams with inefficient tools and jeopardizing their productivity. Even the founders of Laravel have thought about it and created Laravel Nova with out-of-the-box functionalities like CRUD interface, search, filters, notifications, and more. But is Laravel Nova the only option you have? Not at all. We have tested and compared other admin panel solutions for Laravel to help you make an informed choice.

Types of Laravel admin panels

Before diving in, let’s have a quick look at different types of Laravel admin panels because it is important to differentiate back-end CRUD interfaces from visual builders and admin panel templates.

Visual builders

Thanks to visual UI builders, you can create and manage your admin panel directly from the no-code GUI, typically using drag-and-drop builders. They remind CMS systems but usually, visual admin panel builders offer more functionalities than adding and updating content.

Visual builders are useful when your team is small and there are either no developers who can manage internal tools, or when you want to give non-technical business teams the ownership of the tools they use. However, keep in mind that no-code builders typically have limited customization options.

In the next section, you will find a detailed description of visual builders for Laravel admin panels, such as Voyager and Backpack DevTools.

CRUD interfaces

CRUD operations are the fundament of internal tools. That’s because these days it’s hard to find an application that involves users and/or data that doesn’t interact with a database. Every user that makes an edit to an item in the system, adds a new one or deletes data that is no longer necessary, performs one of the CRUD (create, read, update, delete) operations.

A CRUD interface can be built from scratch, but there are many solutions that gives it out-of-the-box. In the next section, you will learn about the most popular packages for Laravel, such as Laravel Nova, Filament, Backpack, and Quick Admin Panel.

The most popular Laravel admin panels

Laravel Nova

Nova is the official admin panel for Laravel, built by Laravel’s creators. It is powered by Vue.js, Vue Router, and Tailwind.css on the front-end, and configured using simple PHP classes on the back-end, which fits the Laravel ecosystem very well. Its primary features include a full CRUD interface, search and filters, graphs and metrics, authorisation, notifications, conditional fields, and more. Additional functionalities are shipped by the community as open-source packages.

Laravel Nova: Pros

Laravel Nova has been built and maintained by the Laravel team so it fits the ecosystem, its design and features.
The UI is clean and user-friendly.
It fits big enterprise projects.

Laravel Nova: Cons

Laravel Nova is not available for free. There is no free trial either but you will get reimbursed if you cancel your license in the first 14 days.
Laravel Nova is not a low code solution. Using it to build internal tools is faster than doing it from scratch but still, it requires some coding and the knowledge of Laravel, PHP, and composer. It also means Laravel Nova can’t be edited and adjusted by non-technical team members.
The out-of-the-box features are hard to customize.

Filament

Unlike other solutions from this article, Filament doesn’t advertise as an admin panel or internal tool solution but as a collection of tools for rapidly building beautiful TALL (tailwindccc, Alpine.js, Laravel, Livewire) stack apps. It has an admin panel, a table builder, and a form builder. The Filament community is also creating packages with complementary features like custom fields, different integrations, charts, and much more.

Filament: Pros

Filament is an open-source project with a strong community that keeps building packages and supporting other Filament users.
It is a clean and simple solution that makes it possible to get an up and running admin panel in a few minutes.

Filament: Cons

There is a simple feature for roles but Filament doesn’t allow to setup a more complex system for teams and permissions.
There is no visual layout editor, which doesn’t allow non-tech team members to customize the tool they use on a daily basis.

Backpack

Backpack is another popular solution for shipping a Laravel admin panel. It requires a minimal technology stack of Laravel, Bootstrap, and jQuery to customize everything in Backpack. On top of that, it’s possible (but not required) to use Vue, React, Webpack, Mix, Less, Saass, NPM, and more.

Backpack has two core packages: Backpack\CRUD that accelerates the process of building CRUD interfaces, and Backpack\Base responsible for login, password reset, error pages, and so on. However, it is also modular and easy to extend, which makes it a powerful solution, also for complex apps.

Laravel Backpack: Pros

Backpack gives Laravel developers a lot of customization opportunities, much more compared to Nova and Filament.
Filters are powerful and can be easily implemented.
Clear and detailed documentation

Laravel Backpack: Cons

Backpack is not free for any commercial use.
Its customization opportunities are a double-edged sword as every change requires quite a lot of manual coding.

Voyager

Voyager is called the Missing Laravel Admin, probably because unlike Nova, Filament, and Backpack, Voyager is a visual builder that makes it easy to use by junior devs and those with limited coding skills. Voyager has six main features: Media Manager (similar to what we know from WordPress), Menu Builder (allowing to quickly add, edit, and delete menu items), Database Manager (an alternative to Laravel’s Schema), a BREAD/CRUD builder that allows to Browse, Read, Edit, Add, and Delete entries and views of any table in the database, Settings, and Compass that helps with Voyager resources.

Laravel Voyager: Pros

Easy to start with and use, clear documentation and a strong community.
It’s free and open source.

Laravel Voyager: Cons

The UI, which makes it so easy to manage, is also limiting.
It doesn’t fit complex apps that require setting up role-based permissions, approval workflows, and other less typical features.

Quick Admin Panel

Quick Admin Panel is an online internal tool generator for Laravel apps. To get started, you need to sign up on the website, create a project, and add chosen functionalities, such as menus, fields, and relationships. Then, Quick Admin Panel will generate Laravel files that need to be downloaded and installed just like any other Laravel project.

Quick Admin Panel: Pros

Quick Admin Panel is, as the name suggests, quick. Even the website hints that it’s a good choice for an MVP, a first version of a new Laravel project.
Quick Admin Panel generates Laravel code and files, and everything can be customized later.

Quick Admin Panel: Cons

Your needs and models need to be defined upfront, before Quick Admin Panel is generated.
Further customization requires a lot of manual coding.

Forest Admin for Laravel: An all-in-one Laravel admin panel

Last but not least, Forest Admin has recently gained a new integration, and now it’s also available for Laravel. It takes the best of both worlds: With Forest Admin, you get both a CRUD interface out-of-the box, you are free to add functionalities that fit your specific business scenarios, and finally, it has a UI layout editor for non-technical team members.

Forest Admin set up as a KYC solution

Forest Admin: A drag-and-drop layout editor

Here is what else makes it different from the solutions listed above:

Forest Admin has a unique hybrid architecture. Only the frontend is managed on Forest Admin servers, so your customer data remain invisible to Forest Admin. It gives you the flexibility of a no-code SaaS tool without compromising on data security.
Once you install Forest Admin, you get more than 30 features out-of-the-box: CRUD, search and filters, role-based permissions, approval workflows, low code components for chars, etc. Then, you’re free to customize your admin panel by implementing your own actions, visualizing data after applying a specific set of conditions, mirroring your business operations, building virtual relationships between collections, computing data on the go, and more.
Thanks to an intuitive drag and drop UI editor and recently released Workspaces, Forest Admin can be managed by non-technical team members, who typically use it on a daily basis.
Forest Admin is scalable, so you don’t need to worry about what happens if you start with an MVP and then celebrate the exponential growth. Forest Admin is used by new startups, established unicorns and all types of companies in between.

Conclusion

As you can see, if you’re not eager to build an admin panel for your Laravel app from scratch, you’re not alone. The Laravel community has already created a couple of user-friendly and efficient solutions, including those that I didn’t mention in this article, like Admin Architect, Argon, InfyOm Laravel Generator, LaraAdmin, Orchid, and many more. The choice is not easy, but answering these questions will help you make the decision:

Do you have enough resources in development, product, UX, and design in order to build a Laravel admin panel from scratch?
Do you want non-technical team members take ownership of their tools?
Do you need to create a quick MVP or do you need an admin panel that will scale from a few to thousands of users?

I hope this article gave you an overview of different ways of building internal tools for your Laravel application. If you decide to give Forest Admin a try, sign up for free and try it yourself.

MongoDB Cheat Sheet

Matthieu Blandineau — Mon, 23 May 2022 11:35:05 +0000

We gathered a list of handy MongoDB and mongosh commands and queries when working with MongoDB databases. Please ping us @ForestAdmin if you’d like to add something to the cheat sheet!

This is the second post from our Cheat Sheets series following the PostgreSQL one.

Getting started with MongoDB

Install MongoDB Shell mongosh

$ mongosh
//OR
$ mongosh "mongodb://localhost:27017"

List MongoDB databases

show dbs

Connect to MongoDB database

use databaseName

Exit MongoDB Shell mongosh

quit()

Managing MongoDB

Check MongoDB version

db.version()

Check if MongoDB is running

$ pgrep -fa -- -D | grep mongo

Restart MongoDB on Linux

db.shutdownServer()
$ sudo systemctl start mongodb

Restart MongoDB on Mac OSX

$ brew services stop mongodb-community@yourMongodbVersionNumber

Restart MongoDB on Windows

Winkey + R
Type "services.msc"
Search "MondoDb"
Click "restart"

Change MongoDB user password

db.changeUserPassword(username, NewPassword)

Exit from mongosh

quit()

Interacting with MongoDB databases

List MongoDB databases

show dbs

Connect or switch to MongoDB database

use databaseName

Create new MongoDB database

use newDatabaseName

Show name of the current database

db.getName()

Delete MongoDB database

db.dropDatabase()

Rename MongoDB database

$ mongodump -d oldDatabaseName path
$ mongorestore -d newDatabaseName path/oldDatabaseName.bson
$ mongosh
use oldDatabaseName
db.dropDatabase()

Interacting with MongoDB collections

List MongoDB collections

show collections

Describe MongoDB collection

db.getCollectionInfos( { name: "collectionName" } )

Describe all collections in MongoDB database

db.getCollectionInfos()

Create MongoDB collection

db.createCollection("collectionName", options)

Delete MongoDB collection

db.collectionName.drop()

Rename MongoDB collection

db.oldCollectionName.renameCollection("newCollectionName")

Interacting with MongoDB indexes

List MongoDB indexes in a collection

db.collectionName.getIndexes()

Create a MongoDB index in a collection

db.collectionName.createIndex(keys, options, commitQuorum)

Create multiple MongoDB indexes in a collection

db.collectionName.createIndexes( [ keyPatterns ], options, commitQuorum )

Delete MongoDB index

db.collectionName.dropIndex("indexName")

Delete all indexes in MongoDB collection

db.collection.dropIndexes()

Rebuild MongoDB indexes

db.collection.reIndex()

Interacting with MongoDB documents

Count documents in a MongoDB collection

db.collectionName.countDocuments()

List all documents in a MongoDB collection

db.collectionName.find().toArray()

Create document in MongoDB

db.collectionName.insertOne(
   <document>
)

Display a document in MongoDB

db.collectionName.find({ "_id": documentID })

Update a document in MongoDB

db.collectionName.updateOne(
    { "_id": documentID },
    <update>
)

Delete a document in MongoDB

db.collectionName.deleteOne({ "_id": documentID })

Replace a document in MongoDB

db.collectionName.replaceOne(
    { "_id": documentID },
    <replacement>
)

Backup and restore MongoDB database

Backup MongoDB database

$ mongodump -d oldDatabaseName path

Restore MongoDB database

$ mongorestore -d newDatabaseName path/oldDatabaseName.bson

Managing roles, users and permissions in MongoDB

List MongoDB roles

db.getRoles(
    {
      rolesInfo: 1
    }
)

Create MongoDB user

db.createUser(
    {
        user: "userName",

        pwd: "userPassword",

        roles:[{role: "roleName" , db:"databaseName"}]
    }
)

Delete MongoDB user

db.dropUser(userName)

Change MongoDB user password

db.changeUserPassword(userName, newPassword)

Need to build admin panels or a GUI tool for MongoDB? Check out Forest Admin for MongoDB

Most popular PostgreSQL GUIs in 2022: the (almost) scientific list

Matthieu Blandineau — Tue, 17 May 2022 12:12:03 +0000

Wjether you're starting with PostgreSQL (in which case you might fancy a cheat sheet) and looking for the best postgres GUI or postgres client to get started, or you're a veteran (in which case ... you might also fancy a cheat sheet!) and you've hit the limit of your current tool — or you just can't stand it anymore, this list is for you. We've compiled the most popular postgreSQL GUIs and clients among your postgres developers peers.

Before we jump in, a few words about the methodology: we started from the list of PostgreSQL clients referenced on the PostgreSQL official wiki. For each client, we looked at the number of occurrences of their name in r/PostgreSQL discussions and comments. Of course, this methodology has some limitations, including: r/PostgreSQL developers are not all the PostgreSQL developers in the world and we only count the postgres clients mentions, not whether a mention is positive or negative. So it's not a list of "best postgres GUIs". But what's a best postgres GUI anyway, as its value will most likely depend on your needs? Let's start

1. pgAdmin

pgAdmin is the most popular PostgreSQL GUI. It is purpose built for Postgres and supports all its features and operations. pgAdmin is open source and also supports Postgres derivative databases such as EDB Postgres Advanced Server. You can test it live here.

pgAdmin supported platforms

You can install pgAdmin on

Linux
macOS
Windows

And then access it locally or over internet via your navigator.

Pros

You can run pgAdmin as a web application, on your computer or on any server, meaning you can use pgAdmin to access and manage distributed Postgres databases, from anywhere.
pgAdmin allows you to create, read and edit all PostgreSQL objects
You can easily rearrange its UI
pgAdmin offers advanced server monitoring capabilities

Cons

pgAdmin isn't as straight forward to install on your desktop as other postgres clients
pgAdmin is more suited for administrating your PostgreSQL than developing on it

pgAdmin pricing: pgAdmin is free and open source

2. DBeaver

DBeaver is a widely used multi database GUI tool, particularly popular among postgreSQL developers. Just like pgAdmin, it's open source. In addition to postgreSQL, it supports MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto, and more.

DBeaver supported platforms

Linux
macOS
Windows

Pros

DBeaver is super easy to install as a desktop application, and to connect to your PostgreSQL database
DBeaver offers a visual query builder to create advanced SQL queries without advanced (or any) knowledge of SQL
It comes with strong data access, search, visualization and edition capabilities
DBeaver also offers a fake data generator to populate your database and test it and the applications built on it

Cons

DBeaver runs locally and is sometimes resource intensive, which can lead to performance issues
And that's pretty much it! Other than that, PostgreSQL DBeaver users haven't expressed strong concerns.

DBeaver pricing: DBeaver is free and open source

3. Postico

Postico is macOS PostgreSQL client for reading data, doing basic manual data entry and editing your database structure.

Postico supported platforms

macOS

Pros

Postico is extremely straight forward to execute basic tasks over your PostgreSQL database
Postico easy to install and to securely connect to your Postgres database

Cons

Postico's strenghts are also its weaknesses: it doesn't offer advanced administration, monitoring or querying capabilities
Postico is only available on macOS

Postico pricing: $39.99 for 1 user, $319.99 for 10 users, free trial with feature limitations but no time limit.

4. DataGrip

DataGrip is a multi database IDE to install locally, that can act as your Postgres GUI and much more. It is developed by JetBrains who are known for their numerous dev tools. On top of Postgres, it supports MySQL, Oracle Database, Redshift, MariaDB, Cassandra, MongoDB and more.

DataGrip supported platforms

Linux
macOS
Windows

Pros

Powerful data viewer and editor
Great and customisable UI
A lot of powerful features to develop on your PostgreSQL database, such as context and schema based code completion, and real-time bug detection in your code (and fix suggestions!).

Cons

DataGrip can be resource intensive
Because it's much more than a Postgres client, DataGrip can be a bit overwhelming for beginners

DataGrip pricing: $89 per year for individual users, $199 per user per year for organisations, with commitment and product packs options.

5. Navicat

Navicat is a GUI focused on database development. As it is multi database, on top of acting as your PostgreSQL GUI, it can connect MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL, and SQLite.

Navicat supported platforms

Linux
macOS
iOS
Windows

Pros

Easy and powerful SQL development tools, such as a visual SQL builder, code completion and customisable Code Snippets.
Good database structure visualisation and edition capabilities.
Collaboration feature

Cons

Limited PostgreSQL database administration capabilities

Navicat pricing: $349.99 per year for non commercial usage, $699.99 per year for commercial usage, with different commitment options

6. TablePlus

TablePlus is a database management GUI supporting various databases including MySQL, Redshift, Redis, MongoDB and of course PostgreSQL.

TablePlus supported platforms

Linux
macOS
iOS
iPadOS
Windows

Pros

Clean and intuitive data browsing, querying and editing UI
Various and easy to use security features
Good query editor including autocomplete, highlight syntax, query reformatter and history.

Cons

TablePlus doesn't go deep in any specific area (for instance PostgreSQL development or monitoring). But is it really a con?

TablePlus pricing: from $49 to $99

7. OmniDB

OmniDB is a browser-based app to access and manage various databases such as Oracle and MySQL. And of course, it can act as your PostgreSQL client.

OmniDB supported platforms

Linux
macOS
Windows

Pros

OmniDB supports plugins, so developers can add functionalities to their PostgreSQL GUI
It can be accessed by multiple users from several computers, making it a good option to collaborate on your PostgreSQL databases
OmniDB offers intuitive data editing features such as ability to copy and paste blocks
Good SQL editing with a customisable SQL editor featuring syntax highlight and code completion

Cons

Light documentation especially on the usage of the tool
OmniDB is more complex than a Postico or TablePlus but less advanced than a DataGrip or Navicat. It's a good free option but maybe less complete than DBeaver, which has a better documentation and a seemingly larger community.

OmniDB pricing: free and open source

8. SQuirreL SQL

SquirreL SQL is a Java tool to view the structure of databases, browse their data, issue SQL commands and more. It supports many databases including SQL Server, MySQL, Oracle Database and of course PostgreSQL.

Squirrel SQL Supported platforms

Linux
Mac OS
Windows

Pros

SquirreL SQL supports Java plugins to extend its capabilities

Cons

Configuration is hard for first time users
The interface isn't the most intuitive

SquirreL SQL pricing: free and open source

9. HeidiSQL

HeidiSQL is a client supporting various databases including MariaDB, MySQL and PostgreSQL. It's been built with the goal of being easy to learn.

HeidiSQL supported platforms

Windows

Pros

HeidiSQL is particularly good at managing several databases/servers simultaneously. Not only it can connect to multiple servers, but allows direct exports from one server or database to another and search across all the connected databases from the same server.
HeidiSQL is easy to install as a Windows app.

Cons

Limited to Windows
Users have reported that the tool can be quite buggy
HeidiSQL doesn't provide advanced code debugging features

HeidiSQL pricing: free and open source

10. Shameless plug: Forest Admin

Forest Admin is an admin panel builder that can easily act as your Postgres GUI. It's packaged as a web application, with backend component to install on your servers. Upon installation, Forest Admin introspects your database and automatically creates a GUI including CRUD operations and tables visualisation, as well as functionalities like search, filters, pagination, exports and more. You can then customise it, add your custom actions or data visualisations.

Forest Admin is focused on easing the visualisation and manipulation of your PostgreSQL database data, even by non technical users, from anywhere with an internet connection.

Forest Admin pricing: free for 1 user, then $24 to $48 per user per month, commitment options.

Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL

What is a CRUD app and how to build one?

Matthieu Blandineau — Thu, 12 May 2022 12:11:04 +0000

Virtually any application involves users – or other applications it connects with – interacting with data stored in a database. Those interactions can be summarised in four operations: create, read (or view), update and delete. Those are the CRUD operations.

CRUD operations explained

More concretely, what are CRUD operations? Let’s use our community forum as an example.

CRUD operations descriptions and examples

CREATE – Being a forum, it needs to let our users create new topics. When a user opens the topic creation form, fills it and clicks on the "Create topic" button, it will trigger the forum's CREATE function that will create a new "Topic" record with the "title", "content", "tags", etc values in the forum's database

READ – Once the post is created, anyone will be able to read it when browsing on the forum and clicking on it, or directly accessing its URL. Doing so will call the forum's READ function, that will retrieve the topic including its attributes (title, content, tags, etc) from the database. The READ function doesn't modify any information from this topic.

UPDATE – After a user has created a topic, our forum allows them to edit it. Useful if they made a mistake or forgot something. To do so, users can simply click the edit button and modify the values of the different attributes. After they click "Save Edit", the forum's UPDATE function will be called, and change those values accordingly in the database.

DELETE – If for any reason, the user wants to delete their topic, they can click the "delete topic" button,. The forum's DELETE function will be called, and will delete the topic record and its attributes from the database.

This is a simple explanation of CRUD operations on a single type of records. Even basic applications will involve CRUD operations on various types of records, with different users being authorised to perform different operations on different types of records. In our forums examples, a user can CREATE a topic, UPDATE their account, READ comments in a topic, and DELETE a bookmark, while admins can UPDATE users rights or CREATE new forum sections.

CRUD operations, REST and SQL

Now we know what are CRUD operations at a high level. We saw that essentially, CRUD operations are triggered by users' (or automated) actions, and alter or read the database. Usually, in web applications, those CRUD operations will be triggered by users' actions generating calls to a REST API, and will alter or read the database via SQL queries.

The table below describes how CRUD operations map to HTTP methods from REST APIs called to trigger the operations, and to SQL queries performed to alter or read the database:

What is a CRUD app?

A you might have noticed, even for the most basic use case, those CRUD operations aren't enough to achieve any meaningful results by themselves, they interact with other elements. All together, those elements make a CRUD app.

Front-end

The front-end, or User Interface, is what enable users to interact with the application, call the REST API to trigger the CRUD operations and ultimately interact with the database.

Back-end

The back-end is what will translate the REST API calls into SQL queries to perform CRUD operations on your database.

Database

The database is where your data, and where the CRUD operations are ultimately performed via SQL queries.

Role-Based Access Control Activity logs, ...

In addition to the components listed above, that are the essential components of a CRUD app, additional capabilities are required to build a robust app. Indeed, you might want to restrict which users can perform which operations (for instance deleting the entire database), and log every operations performed for debugging or compliance purposes.

Hosting

All the components above have to be hosted somewhere to always be accessible for your and your users.

Now, let's see different options to build a CRUD app.

Building a CRUD app from scratch

An obvious option to build a CRUD app is to build it from scratch. Most likely, if you're thinking about building such an app, it's because you already have a database storing data you want to enable your users to easily visualise or manipulate. So we will assume you already have an up and running database.

While CRUD apps seem fairly basic, developing one requires solid front-end, back-end and database knowledge. In this blog, we won't cover everything in detail but list the main steps to build a CRUD app on top of your database, from scratch, and link to useful resources.

For the sake of the exercise, we'll assume you're using the following popular technologies: React for the front-end, Node.js for the back-end and PostreSQL for your database.

Build your CRUD app front-end

We used this great tutorial "How to Perform CRUD Operations using React, React Hooks, and Axios" as a reference.

Architecture & design: Let's go. As we're starting from an existing database, you already know what data is stored in this database, and how it is structured. So the first you might do is to think about how you want your users to interact with this data, how you want to represent it, and what will the experience look like.
Create your React application
Install the Semantic UI Package for React This package is an UI library providing pre-built components such as buttons, tables and much more.
Create your app first screen
Create your CREATE, READ and UPDATE files for your CREATE, READ and UPDATE component, no need for a new component for DELETE
Build the CREATE, READ, UPDATE and DELETE elements, buttons and logic
Build the CRUD requests to the API (once you've built the API)
Implement forms validation

Build your CRUD app back-end

Once you're done with the front-end, it's time to build what will be the link between the front-end and the database. We used this "Developing a CRUD Node.js Application with PostgreSQL" guide as a reference.

Create your app architecture in Node.js
Configure your app and its connection to your PostgreSQL database
Create the routes for your CRUD operations, including writing the API endpoints for their respective methods, and their respective logic to query the database

Deploy your CRUD app

As we mentioned previously, you need to host your database, back-end and front-end online if you want you and your users to be able to access it anytime from anywhere.

Bonus points: add Role-Based Access Control

From installing the necessary packages to setting up user authentification (both signup and login) and their front-end manifestations to configuring the rights on the routes, implementing RBAC on your CRUD app adds significant amount of development time.

Useful tools to reduce CRUD app development time

As we've seen, developing a basic CRUD app from scratch can take significant time. Fortunately, there are tools to accelerate this process, especially on the backend part. Tools like Hasura or PostgreSQL, depending on your stack, will automatically create APIs based on your database.

Building a CRUD app with a CRUD app builder

Even with tools like Hasura or PostgreSQL, building a CRUD still takes time. Even more so if you need specific capabilities in addition to CRUD operations. To prevent you from reinventing the wheel every-time you need to enable your users to manipulate and visualise the data stored in your database, tools exist to build CRUD apps in minute and allow you to focus on adding advanced logic and functionalities to your applications. Examples include Django Admin or Laravel Nova depending on your stack.

In this blog, we'll see how to build a CRUD app with Forest Admin. We'll assume you're building a CRUD app for a PostgreSQL database.

Install your CRUD app builder

The first step is to install the tool that will help your generate your CRUD app. Using our example Forest Admin with a PostgreSQL database, it only takes a few steps:

Install Forest Admin CLI

$ npm install -g lumber-cli@latest -s

$ forest login

Generate the backend application

$ forest projects:create "CRUDapp" --databaseConnectionURL "postgres://root:password@localhost:5432/myDatabase" --databaseSchema "public" --applicationHost "localhost" --applicationPort "3310"  
$ cd "CRUDapp" 
$ npm install -s

Run your backend application

$ npm start

Build your CRUD app

The catch is that with Forest Admin, you get an up and running CRUD app right after installing it on top of your database. It automatically generates visualisations of your tables, CRUD operations and more, as well as the associated front-end.

You also benefit from built-in Role-Based Access Control, Activity logs and more so you can focus on coding your own business logic, adding custom actions to your new app and customising it.

Click here to learn more about Forest Admin, read the doc or get started now.

PostgreSQL Cheat Sheet

Matthieu Blandineau — Wed, 11 May 2022 08:41:13 +0000

We gathered a list of handy PostgreSQL commands and queries when working with PostgreSQL databases. Please ping us @ForestAdmin if you’d like to add something to the cheat sheet!

Getting started with PostgreSQL

$ sudo su - postgres psql

List postgres databases

\l

Connect to postgres database

\c databaseName

Exit postgreSQL command line utility psql

\q

Managing PostgreSQL

Check PostgreSQL version

SELECT version();

Check if PostgreSQL is installed

$ which psql

Check if PostgreSQL is running

$ pgrep -fa -- -D | grep postgres

Restart PostgreSQL on Linux

# systemctl restart postgresql
// or
# service postgresql restart
// or
# /etc/init.d/postgresql restart
// or
# /sbin/service postgresql restart

Restart PostgreSQL on OSX

# brew services restart postgres
// or
# pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log restart

Restart PostgreSQL on Windows

Winkey + R
Type "services.msc"
Click "restart"

Change PostgreSQL user password

$ sudo -u postgres psql
\password postgres

Exit from psql

\q

Interacting with databases in PostgreSQL

List postgres databases

\l

Connect or switch to postgres database

\c databaseName

Create new postgres database

CREATE DATABASE databaseName

Delete postgres database

DROP DATABASE databaseName

Rename postgres database

ALTER DATABASE old_databaseName TO new_databaseName

Query postgres JSON data

SELECT *
FROM mytable
WHERE myfield @> '{"key1":"valueA"}'

Interacting with tables in PostgreSQL

List postgres tables

\dt

Describe postgres table

\d tableName

Create postgres table

CREATE TABLE tableName( 
    columnName columnType,
    columnName columnType
);

Delete postgres table

DROP TABLE tableName CASCADE

Backup and restore PostgreSQL database

Backup postgres database

$ pg_dump -d databaseName -U userName > backupFile

Restore postgres database

psql databaseName < backupFile

Managing roles and permissions in PostgreSQL

List postgres roles

SELECT rolname FROM pg_roles;

Create postgres user

CREATE USER userName WITH PASSWORD 'userPassword';

Delete postgres user

DROP USER userName;

Change postgres user password

ALTER ROLE userName WITH PASSWORD 'userPassword';

List all assigned roles postgres

SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof,
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

Grant all permissions on postgres database

GRANT ALL PRIVILEGES ON DATABASE databaseName TO userName;

Grant connection permissions on postgres database

GRANT CONNECT ON DATABASE databaseName TO userName;

List permissions for specific role postgres

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = userName ORDER BY table_name;

ProstgreSQL psql commands recap

\? List all available psql commands

\h COMMAND Get help on specific command

\l List databases

\c databaseName Connect to database

\dt List tables

\d tableName Describe table

\d+ tableName Describe table with details

\dn List schemas

\df List functions

\dv List views

\du List users

\dy List events

\di List indexes

\q Exit

Need to build admin panels or a GUI tool for PostgreSQL? Check out Forest Admin for PostgreSQL