Postgres CLI
for modern development with SQL
Artisans create their own tools with which they build masterpieces.
Postgres Builder
I started by building an applications generator. It is a small command-line tool (CLI).
Let's call it Builder.
It parses text files and transforms them into building blocks of the application.
Why?
I started my career with Oracle and successfully avoided using stored procedures and writing SQL by hand all these years.
I used many ORMs and couldn't imagine building an app without them.
Why I changed my mind?
- Years of writing boilerplate code for ORMs.
- Package manager hell. Dependencies that are much larger than our app and depend on uncontrollable number of other packages that might break the app.
- Module bundler changes every year: Gulp, Webpack, Rollup, Parcel, Browserify,...
- Package manager changes every year: Composer, Maven, npm, yarn, yarn2,...
Features
What It Does?
Let's start with a Country table.
Doesn't this code look like a class in an ORM?
Generated by the Builder
(some lines are omitted)
...and the indexes:
PostgreSQL does NOT add indexes to foreign keys by default. This isn't an issue for the forward relation (user_id → user), but for the reverse relation (user → things by user_id) it can make the lookup very expensive. The Builder adds indexes to your foreign keys.
...and the triggers:
...and the comments are added to the database too:
...and the constraints:
The Builder automatically finds table relations and generates foreign key constraints for them.
Annotations and Snippets
Comment lines with annotations are responsible for importing snippets of code used in many tables. These snippets are so general that they have their own name. For project specific snippets the @import snippet_name notation may be used. Snippets are just files in the snippets folder.
@id annotation imports the following snippet:
@codeList annotation imports the following snippet:
@timestamps annotation imports the following snippet:
Entity-Relationship Diagrams
The Builder also generates E/R Diagrams for each table and their first-level related tables. Custom diagrams may be prepared by adding import statements for additional tables in a PlantUML text file. Custom diagrams are then generated by the Builder.
Source Files
Directory Structure
Source directory of the Builder project has a flexible structure with some required conventions to enable automatic building of the application's building blocks.
Most of the applications code is in the modules folder.
Most modules have domains, enums, functions and tables folders.
Additionally they may also use policies, relations, triggers, types and views folders.
Names of these folders are part of the convention but they may be anywhere in the directory structure. These folders may also have sub-folders if required.
Generating all.ddl
The Builder reads the source files and generates a single all.ddl used for creating database objects (e.g. tables, functions,...).
The Builder combines the files in the order expected by the database (e.g. functions must be created before they can be used in triggers, tables must be created before they can be used in functions,...).
There are special ordered folders that are used for explicit ordering if needed (e.g. creating objects used application wide before any other objects from modules are created).
You can put your code in separate files or use annotations and let the Builder handle combining the code in the correct order for all.ddl.
Bellow is an example of using @triggers annotation to put create table and create trigger in the same source file.
Similar annotations are available for other folder types (e.g. functions, relations, views,...).
GraphQL | REST API Servers
Integrate with API servers by pointing them at your PostgreSQL database
PostGraphile
The Builder generated all.ddl file may be used with PostGraphile to instantly serve GraphQL endpoint for your database.
PostGraphile automatically detects tables, columns, indexes, relationships, views, types, functions, comments, and more — providing a GraphQL server that is highly intelligent about your data, and that automatically updates itself without restarting when you modify your database.
PostGraphile Starter
PostGraphile comes with a full starter application with pre-built user account system for full-stack application development: PostGraphile Starter.
The Builder generated all.ddl file is integrated with the Graphile migrations used by the PostGraphile Starter.
Prisma 2
Modern Database Access for TypeScript & Node.js
Prisma makes database access easy with an auto-generated and type-safe query builder that's tailored to your database schema. It provides an alternative to traditional ORMs and SQL query builders. Start with a new database or introspect an existing one to get started.
- API Server
- No more CRUD boilerplate
- Supports all web frameworks
Prisma is an open-source database toolkit. It replaces traditional ORMs and makes database access easy with an auto-generated query builder for TypeScript & Node.js.
PostgREST
You may use PostgREST if you prefer REST API.
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.
Generating Complete Applications
Generate ORM classes for different frameworks or generate complete applications
CUBA Platform
The Builder generates entity and CRUD screen classes for the CUBA Platform.
It also generates service wrappers for calling business logic in the database.
CUBA provides powerful full stack architecture along with RAD tools for easy start and fast development of modern business web applications on Java.
ORMs
The Builder can generate ORM classes for any platform.
We use cookies on this website. By using this site you agree with the use of cookies. More information: Privacy.
