One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that are ensured through the relational data structures you design. Another piece of control is using the correct data type. Then, we get to constraints. A constraint is a way to validate data prior to adding it to your database. This is one more tool in the toolbox that helps you maintain good data.… Read more
Optimizing read operations is one of the most prominent problems concerning any database administrator. No matter what kind of database management system is in use – MySQL, its flavors like Percona Server or MariaDB, MongoDB, TimescaleDB, SQL Server, or others, read queries are a concern. The primary read query example is SELECT queries, but a lot of this pertains to UPDATE and DELETE as well since they too have to fetch rows to be operated on.… Read more
Serverless computing is pushing C# to evolve to the next level. This is exciting because you pay-per-use and only incur charges when the code is running. This means that .NET 6 must spin up fast, do its job, then die quickly. This mantra of birth and rebirth pushes developers and the underlying tech to think … Read more
A term I have only recently learned is "shift left testing." You can read more about this on Wikipedia here. The term was coined in 2001 and generally means testing earlier in the development lifecycle. Hence, shifting your testing left in the timeline. Just how left should you shift your testing, though? In my mind, so early, the rooster hasn't entirely fallen asleep yet.… Read more
Information security, whether in databases, applications, or larger IT systems, is based on the principles of Authentication and Authorization, often referred to as AuthN and AuthZ respectively.
Likewise, PostgreSQL security involves creating roles that allow access to server resources (AuthN) and then granting privileges to database objects appropriately (AuthZ). Understanding how each of these components work together is essential to effectively manage security in a PostgreSQL cluster.… Read more
Once data is in a table it needs to be maintained. New records will need to be added, and existing records will need to be updated, and/or deleted. To keep data maintained, the insert, update or delete actions may need to be performed. This article will show how those three data manipulation language (DML) actions can be synchronized together within a single basic MERGE statement.… Read more
As with any other relational data management system (RDBMS), PostgreSQL uses indexes as a mechanism to improve data access. PostgreSQL has a number of different index types, supporting different behaviors and different types of data. In addition, again, similar to other RDBMS, there are properties and behaviors associated with these indexes.
In this article I’m going to go over the different types of indexes and some index behaviors. We’ll get into what the indexes are, how they work, and how best you can apply them within your databases. I’m hoping you’ll develop an understanding of which indexes are likely to work better in each situation.… Read more
As with many relational database management systems, MySQL provides a variety of methods for combining data in a data manipulation language (DML) statement. You can join multiple tables in a single query or add subqueries that pull data in from other tables. You can also access views and temporary tables from within a statement, often … Read more
Fish are beautiful creatures, but they have one minor issue. The main thing I remember from the years I fished is that fish can be susceptible. If you have a fishing lure that shows off with a bright, colorful, shiny appearance that splashes around just right, fish will lose their minds and go after it, … Read more
Let’s talk about authentication between Azure Functions and resources used by Azure Functions and conclude with many poorly documented secrets about how to use User Assigned Managed Identity. When we build Azure functions, they usually need to authenticate against other Azure resources: Azure SQL Database, Storage Accounts, Service Bus and many more. Each of these services … Read more
It is almost New Year’s Day, that time when the optimistic at heart start thinking about making changes in their life on that magical day that ends the year. After the glorious gluttony-fest that has only recently ended in many cultures, it does feel like a great time to start. If you have your life/work … Read more
From personal observation and experience, most people want to be comfortable in their vocation. Getting comfortable and not keeping up with new products, versions, and trends can be dangerous to your employer's business and your future.… Read more
A subquery is a type of query that is embedded—or nested—into a data manipulation language (DML) statement. The data returned by the subquery is passed into the DML statement and incorporated into its overall logic. The subquery itself is typically a SELECT statement, although you can also use a TABLE statement or VALUES statement. Even so, the SELECT statement continues to be the most common choice for subqueries, and that’s the one I focus on in this article.… Read more
In this article, I will introduce two JavaScript frameworks that can be used to build backend projects. Backend development is the development of server-side logic that powers websites and apps from behind the scenes. It typically includes all the code needed to build out the database, server, and application. From database migrations to API integrations to set up the server-side technologies that make a website tick.… Read more
If only the entire world used UTC, wouldn’t life be so much easier? We can dream, can’t we? While some software applications can live in an ecosystem where all dates and times can be stored in a single time zone with no conversions needed, many cannot. As applications grow and interact more with international customers, … Read more
In the last few articles in this series, you learned about three important data manipulation language (DML) statements: SELECT, INSERT, and UPDATE. The statements make it possible to retrieve, add, and modify data in a MySQL database. Another DML statement that is just as important is DELETE, which lets you remove one or more rows from a table, including temporary tables. In this article, I focus exclusively on the DELETE statement to help round out our discussion on the core DML statements in MySQL. Overall, the DELETE statement is fairly basic, but one that’s no less necessary to have in your arsenal of DML tools.
… Read more
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating … Read more
Object Relational Mapping is a database abstraction technique that aids developers in manipulating and interacting with SQL databases using the data types provided in the programming language. ORMs (Object Relational Mappers) are tools (libraries, modules, packages) that provide the functionality for interacting with SQL databases. Popular ORMs are the Prisma ORM for JavaScript, TypeScript, Hibernate … Read more
At the turn of the millennium, I was a young adult, just finished education and starting my very first job at a software company. I was excited, enthusiastic, and felt well-prepared for a life of “Office 9 to 5” and was looking forward to earning my keep. But I was naïve when it came to … Read more
In the previous two articles in this series, you learned about SELECT and INSERT, two important data manipulation language (DML) statements in MySQL. In this article, I focus on the UPDATE statement, another valuable DML statement in MySQL.… Read more
