CRUD functions generator tool

Tool for application developers of PostgreSQL

Presented by:

Imagen foss

Javier Arao Toyohara

FOSS PEMEX

At Petróleos Mexicanos (PEMEX), the IT Group aims to promote enabling capabilities, platforms, and open source solutions; encouraging technological development to respond adequately to the specific needs outlined in PEMEX.

In 2019, Brenda Fierro (IT Principal) and Salvador Jiménez (IT advisor) founded the FOSS Community of PEMEX to adopt open-source solutions & tools to strengthen the organization's IT strategy, with the following commitments:

Share experience and skills on innovative technologies. Consolidate the FOSS PEMEX community, serving the needs of business areas through this collaboration model.

PostgreSQL is fundamental in PEMEX's FOSS Architecture because it provides a solid base for trustworthy solutions. Due to the significant investment of time to develop effective data access methods with a unified coding style, a team developed a tool to simplify writing CRUD functions. Team members: Diego Fabián López, Neto Velázquez, Lalo Alva, Alex Cruz,Jorge Azuela, Robert Lecona,José Mercado, Aldo Avilés, Julio Saucedo, Yamil Ortega, Miguel González, Miguel Morales, Luis Franco, Rafael Juárez, Joaquín Sánchez, Javier Mercado, Dani Vera, Elizabeth Velazco,Andrés Millán, Salvador Jiménez and Javier Arao.

Functions represent an efficient and secure method to manage CRUD operations in database applications. A CRUD function encapsulates data complexity and provides a standard technique to work with data access methods. Developers invest significant time and effort to write CRUD functions in a standardize style.

This proposal aims to present a tool to generate essential CRUD functions to aid PostgreSQL's developers to standardize coding style and simplify the time to analyze table structures.

The tool uses PLPGSQL to inspect a table's metadata (attributes, data types, pk fields, fk fields and constraints) and combines them with CRUD templates to produce the following CRUD functions:

  • Create. This function identifies the serial types fields (small serial, serial, big serial) and excludes them from the list of fields-values to insert.
  • Read 1. This function supports SELECT from multiple tables through foreign key (FK) fields to identify foreign tables and fields relationships. The SELECT clause includes the attributes from the foreign tables. The function contains the options LIMIT and OFFSET
  • Read 2. This function adds the WHERE clause to restrict the record selection by key values to the Read 1 function.
  • Update. The key fields are used in the WHERE clause to restrict the update operation. The key attributes are excluded from the SET list
  • Delete. The key fields are used in the WHERE clause to restrict the delete operation

The tool also generates a CREATE VIEW statement and supports SELECT from multiple tables through foreign key (FK) fields to identify foreign tables and field relationships. The SELECT clause includes the columns from the foreign tables.

Date:
2022 February 16 13:00 EST
Duration:
1 h
Room:
Online
Conference:
Postgres Conference Webinar Series 2022
Language:
Track:
Dev
Difficulty:
Easy
Requires Registration:
Yes (Registered: 571)