Architecture and Data Blog

Lots of time hand coded SQL is written in the applications for performance sake, getting data using complex SQL out of the tables. As the database structure changes it becomes really hard to find out all the SQL that needs to be changed. In other cases SQL is generated based on certain conditions in the code and its hard to find if the generated SQL is valid after database changes are done.

When accessing the database using stored procedures for basic Create, Read, Update and Delete (CRUD) functions, or when you want to write triggers that capture the before and after values in tables, or when you want to create Plain Old Java Objects (POJO’s) that match the database objects, writing these by hand takes a lot of effort and also since the requirements are changing in an agile projects at a frequent rate, there will be design changes to meet the requirement changes, so the triggers, CRUD stored procedures or Data Access Objects (DAO) are going to be out of data, instead of hand coding, its better to generate the code, using the metadata of the database

Traditionally the data-team is used to sitting in their own area and working for many project teams by handling requests either via a ticketing system or vi email. The hand-over of work or throwing of work over the wall creates knowledge silos and inefficiencies.

In all new development and sometimes during legacy codebase modernization, developers tend to add code quality checks and static analysis of codebase such as style checks, bug finders, cyclomatic complexity checking etc. into the CI/CD pipeline. When we inherit a codebase that has much PL/SQL and there is a desire to put the PL/SQL code base through the same types of code analysis, what options does a developer/dba have?

Many projects need addition of identical columns to all the tables created by the project. Audit columns are an example of such a requirement. The requirement is to add columns such as created_by, created_date, modified_by and modified_date to all the tables, these columns store, who created the row, when the row was created, who modified the row last and when was it modified. created_by and created_date are required to be present when the row is inserted and thus are required to be not nullable. Adding these columns to each and every table is a lot of work for developers.

In many development shops, developers are not allowed to access the database schema directly, and are not allowed to create tables, indexes, views etc, instead are given access via a different schema that allows SELECT, UPDATE and DELETE access on data. The general reason is to avoid developers creating database objects without

IN many projects, there are tables which need default audit columns such as Created_By, Created_Date, Modified_By, Modified_date and other columns that need to be updated every time some actions are done against the tables and/or columns. This type of functionality can be implemented using triggers.

In every enterprise and every project we end up having multiple environments, especially the database side of the enterprise tends to stick around for a longer period of time and has much more dependencies or application integration as opposed to application urls etc. Given this, how to name the servers, databases and schemas becomes a very important decision, do these names provide for an easy way to use the application and not make it harder or the developers to access the database.