DEV Community: Gabriel

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.