Let’s Talk About Database Schema

Previous post on this theme, “Designing a Relational Database – Data Modeling“.

Seriously, let’s talk about schema in the abstract and the literal implemented schema in some of the most popular databases.

What is a schema?

In general, outside the specific realm of relational databases, a “schema” is a conceptual framework or blueprint that defines the structure, relationships, and constraints of data or information. It provides a way to describe and organize data in a structured manner. This concept of schema is not unique to databases; for instance, in GraphQL, a schema defines the types, queries, mutations, and the relationships between them, outlining the set of possible operations that can be executed against the API and the shape of the data that is returned.

The Oddity of Different Implementations in Databases

From the viewpoint of someone familiar with the general idea of a schema, it can indeed seem unusual that databases like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL each interpret and implement schemas in slightly (or sometimes, vastly) different ways. While the core idea behind a schema as a structured container or namespace for database objects remains somewhat consistent, the exact nature, utility, and behavior of schemas vary across these systems.

Why Each Database Implements Schema Differently

  1. Historical and Legacy Reasons: Many database systems started their journey decades ago. Over time, as they evolved, they built upon their existing architectures, leading to variations in features like schema. For instance, the idea of a schema in Oracle being closely tied to a user comes from Oracle’s early architectural decisions.
  2. Design Philosophy and Target Audience: Some databases were designed with specific audiences in mind. Oracle, for example, was designed for enterprise-level applications, which might have influenced their user-centric schema design. On the other hand, MySQL, initially envisioned for web applications, treats schemas synonymously with databases, perhaps for simplicity.
  3. Standards Compliance vs. Practicality: While there are ANSI SQL standards that databases might strive to adhere to, there’s also a balance to strike between standards compliance and offering features that are deemed more practical or beneficial for the database’s primary users. For instance, PostgreSQL, which aims to be highly standards-compliant, also introduces advanced features not in the standard when it sees fit.
  4. Competitive Differentiation: Sometimes, databases introduce or tweak features to differentiate themselves in the market. These variations can sometimes lead to differences in core concepts like schemas.
  5. Community and Governance Influence: Open-source databases like PostgreSQL and MariaDB can be influenced by their developer communities. Different communities might prioritize certain features or philosophies, leading to divergent implementations.
  6. Flexibility and Extensibility Concerns: Databases might implement schemas in ways they believe are more flexible or extensible for future changes. This might lead them to adopt non-standard or unique approaches.

In essence, the varying implementation of schemas in different databases is a result of a blend of historical decisions, design philosophies, target audiences, and practical considerations. It’s a testament to the evolving nature of database systems and the diverse needs they aim to address.

Looking @ Relational Database Schema

Continue reading “Let’s Talk About Database Schema”

GraphQL VS Code Extension Review && Simple Schema Build

Today I checked out the GraphQL extension – “VSCode GraphQL” – for Visual Studio Code. It’s available on the Marketplace @ VSCode GraphQL and of course you can navigate to plugging in VS Code and install it just like this.

Installing the Extension
Continue reading “GraphQL VS Code Extension Review && Simple Schema Build”

Getting an Existing GraphQL `schema.graphql` of Your API

Let’s say you’ve built out your GraphQL API. Now let’s say you want a schema.graphql file but for some reason you don’t have your repo or for some reason it’s not immediately available. What to do? Here are two tools to get a quick schema of your GraphQL API by pointing them at your API and letting them introspect against it!


For both of these you’ll need to have NPM installed, which I’ll just assume you do if you’re dealing with GraphQL. If you don’t, head on out and get NPM and Node.js installed for good measure, and per my suggestion do yourself a favor and pick a version manager. I generally use nvm, check out details on install NVM here.


Install with the following command.

npm install -g graphqurl
Continue reading “Getting an Existing GraphQL `schema.graphql` of Your API”

Apollo GraphQL Federation Schema Validation Error [Solved!]

This is the error I’ve bumped into while working through the example for Apollo’s GraphQL Federation when setting up a subgraph API. I’ve tried several things to resolve this error including changing versions for the GraphQL library in use but that hasn’t fixed it. I’ve also got this now on MacOS, Linux, and Windows so it isn’t something odd about the environment.

 ~/Codez/AppoloFederationCore-v2/ [main] node index.js
        throw new GraphQLSchemaValidationError_1.GraphQLSchemaValidationError(errors);

GraphQLSchemaValidationError: Unknown directive "@entity".
    at buildSchemaFromSDL (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/apollo-graphql/lib/schema/buildSchemaFromSDL.js:50:15)
    at buildSubgraphSchema (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/@apollo/subgraph/dist/buildSubgraphSchema.js:26:58)
    at Object.<anonymous> (/Users/adronhall/Codez/AppoloFederationCore-v2/index.js:29:11)
    at Module._compile (node:internal/modules/cjs/loader:1095:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1124:10)
    at Module.load (node:internal/modules/cjs/loader:975:32)
    at Function.Module._load (node:internal/modules/cjs/loader:816:12)
    at Function.executeUserEntryPoint [as runMain] (node:internal/modules/run_main:79:12)
    at node:internal/main/run_main_module:17:47 {
  errors: [
    GraphQLError [Object]: Unknown directive "@entity".
        at Object.Directive (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/graphql/validation/rules/KnownDirectivesRule.js:56:29)
        at Object.enter (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/graphql/language/visitor.js:323:29)
        at visit (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/graphql/language/visitor.js:243:26)
        at Object.validateSDL (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/graphql/validation/validate.js:92:22)
        at buildSchemaFromSDL (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/apollo-graphql/lib/schema/buildSchemaFromSDL.js:48:31)
        at buildSubgraphSchema (/Users/adronhall/Codez/AppoloFederationCore-v2/node_modules/@apollo/subgraph/dist/buildSubgraphSchema.js:26:58)
        at Object.<anonymous> (/Users/adronhall/Codez/AppoloFederationCore-v2/index.js:29:11)
        at Module._compile (node:internal/modules/cjs/loader:1095:14)
        at Object.Module._extensions..js (node:internal/modules/cjs/loader:1124:10)
        at Module.load (node:internal/modules/cjs/loader:975:32)
Continue reading “Apollo GraphQL Federation Schema Validation Error [Solved!]”

Beyond CRUD n’ Cruft Data-Modeling

I dig through a lot of internet results and blog entries that show CRUD data modeling all the time. A lot of these blog entries and documentation are pretty solid. Unfortunately, rarely do we end up with data that is accurately or precisely modeled the way it ought to be or the way we would ideally use it. In this post I’m going to take some sample elements of data and model it out for various uses. Then reconstitute that data into different structures for various uses within microservices, loading, reading, both in normalized form and denormalized form.

The Domain: Railroad Systems & Services

The domain I chose for this particular example is the entire global spectrum of rail services. Imagine if you would a system that can track all the trains in the world, or even just the trains in a particular area of the world, like the United States. In the United States the trains can be broken down into logical structures of data for various things like freight trains and passenger trains. Trains operated under a particular operator like Amtrak, Union Pacific, or Norfolk Southern, and their respective consists that the train is made up of. Let’s get into some particular word definitions to fully detail this domain. Continue reading “Beyond CRUD n’ Cruft Data-Modeling”