Javascript

Connecting to PostgreSQL using TypeORM

Apr 6, 2021

4 min read

In my previous article we learned how to connect to a PostgreSQL database using the node-postgres package pg. This works fine, and will be perfect for many applications, but one may also choose to opt for using an ORM instead.

What is an ORM?

ORM stands for object-relational-mapping and allows you to interact with your database using objects from your programming language's type system rather than hand writing queries. This allows ORMs to simplify your logic that manipulates data in the database. In this article, we've opted to use TypeORM as it is widely used, and it supports many of the advanced features expected from an ORM framework.

Setup

We'll be demonstrating examples with both Data Mapper and Active record patterns.

Both the Data Mapper and Active Record patterns are supported by TypeORM, and we'll cover the former first. Data Mapper allows you to represent tables and other data as Entity classes. You can then define properties in these classes that map to the respective columns in the database, and even include custom methods in the entities that manipulate them.

For starters, you'll want to have TypeScript and TypeORM installed. TypeORM uses TypeScript exclusive features for defining your entities, and although it's not required to use TypeORM, it makes it much easier to do so. TypeScript is an extension to JavaScript that adds a strict type system. At the time of writing this article, TypeORM requires TypeScript v3.3 at a minimum, and this is subject to change.

# Install TypeScript globally if it isn't already installed.
npm install -g typescript

# Check that you're using a supported version of TypeScript.
tsc --version

Now, TypeORM has to be installed! This can be done with the following npm command in your project root:

# Initialize a node project.
npm init

# Install needed dependencies.
npm install typeorm pg --save

Let's define a database and a simple schema with a people tables like we did in our node-postgres article. Please check out that article if you need instructions on how to set up a PostgreSQL to develop against.

-- Create a database for our demo.
CREATE DATABASE typeormdemo;

-- Setup the database schema.
CREATE TABLE people (
    id BIGSERIAL,
    fullname TEXT,
    gender TEXT,
    phone TEXT,
    age INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

Data Mapper

The Data Mapper pattern allows you to define Entities that represent your data types in the database, and repositories to store your query methods and other domain logic. Entities in Data Mapper are very simple.

The following example is how an Entity for our person table is defined using the Data Mapper pattern. This is contained in a file called person.ts.

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

// The property "name" sets the table name. This is usually implied from the
// class name, however this can be overridden if needed.
@Entity({ name: "people" })
export class Person {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  fullname!: string;

  @Column()
  gender!: string;

  @Column()
  phone!: string;

  @Column()
  age!: number;

  @Column({ name: "created_at" })
  createdAt?: Date;
}

Note that this Entity uses decorators that are experimental at the time of writing this article. Make sure you set experimentalDecorators and emitDecoratorMetadata in your tsconfig.json for this to work. You can generate a tsconfig.json by running tsc --init. Let's break down what these decorators and their options mean.

**@Column**: Put above a property that maps to a column. The name of the property is assumed to be the name of the column in the table. If the name in the Entity doesn't match the database column name, you can specify a name in an options object in the decorator. **@PrimaryGeneratedColumn()**: This signals to TypeORM that this is a PRIMARY KEY column that uniquely represents the entity.

To write a query method, we'll need to create a repository. A repository is a class that contains query methods and other helpers. Domain logic that works with Entities is separated from the definition of the data types themselves. This will go in people-repository.ts.

import { EntityRepository, Repository } from "typeorm";
import { Person } from "./person";

@EntityRepository(Person)
export class PersonRepository extends Repository<Person> {
  findByName(fullname: string) {
    return this.createQueryBuilder("people")
      .where("people.fullname = :fullname", { fullname })
      .getOne();
  }

  updateName(id: number, fullname: string) {
    return this.createQueryBuilder("people")
      .update()
      .set({ fullname: fullname })
      .where("people.id = :id", { id })
      .execute();
  }
}

The following code can be used to set up a connection and interact with the database with our Entity and Repository. It's a better practice to change the fullname property on the class instance and use save() to update it in the database. However, I wanted to demonstrate how the query builder can be used for UPDATE queries as well. We put this in our main index.ts file.

import { createConnection } from "typeorm";

import { Person } from "./person";
import { PersonRepository } from "./person-repository";

(async () => {
  // Initialize a connection pool against the database.
  const connection = await createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "Theeb6uu",
    database: "typeormdemo",
    entities: [Person],
  });
  const personRepository = connection.getCustomRepository(PersonRepository);

  // Register a new person in the database by calling the repository.
  const newPerson = new Person();
  newPerson.fullname = "Jane Doe";
  newPerson.gender = "F";
  newPerson.phone = "5555555555";
  newPerson.age = 29;
  await personRepository.save(newPerson);

  // Find the person we just saved to the database using the custom query
  // method we wrote in the person repository.
  const existingPerson = await personRepository.findByName("Jane Doe");
  if (!existingPerson) {
    throw Error("Unable to find Jane Doe.");
  }

  // Change the person's full name.
  await personRepository.updateName(existingPerson.id, "Jane Johnson");

  // Remove the person from the database.
  await personRepository.remove(existingPerson);

  // Clean up our connection pool so we can exit.
  await connection.close();
})();

Now, with all the code in place, it can be executed with tsc && node index.js. The above demos a basic CRUD flow just like we do in our previous article where we use raw SQL. All methods for interacting with the data exist in the Repository class, and this class has our own custom functions and pre-existing functions for saving and removing data that we use.

Active Record

The Active Record approach allows you to define query methods in the model itself, rather than doing it in a repository like we did with the Data Mapper pattern. You may have used the Active Record pattern before if you've ever used Ruby on Rails. For our example, there won't be very many changes.

We can actually use the same Entity as we did in the Data Mapper class, however we will move our query methods into this class. We also make one of the queries static so we don't need an instance of the class to call the method. The name update method can remain an instance method as it can utilize the id property in an instance to simplify making calls to it. Here is the new person.ts.

import { Entity, PrimaryGeneratedColumn, Column, BaseEntity } from "typeorm";

@Entity({ name: "people" })
export class Person extends BaseEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  fullname!: string;

  @Column()
  gender!: string;

  @Column()
  phone!: string;

  @Column()
  age!: number;

  @Column({ name: "created_at" })
  createdAt?: Date;

  /*
   * The following methods used to be in the PersonRepositoy class, but are now
   * in the entity instead.
   */

  static findByName(fullname: string) {
    return this.createQueryBuilder("people")
      .where("people.fullname = :fullname", { fullname })
      .getOne();
  }

  updateName(fullname: string) {
    const id = this.id;

    return Person.createQueryBuilder("people")
      .update()
      .set({ fullname: fullname })
      .where("people.id = :id", { id })
      .execute();
  }
}

The code in index.ts is similar to the data mapper approach, but differs in how the query methods are called. Since we don't have a repository class with Active Record, we just call the methods directly on the Entity instance.

import { createConnection } from "typeorm";

import { Person } from "./person";

(async () => {
  // Initialize a connection pool against the database.
  const connection = await createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "Theeb6uu",
    database: "typeormdemo",
    entities: [Person],
  });

  // Register a new person in the database by calling the repository.
  const newPerson = new Person();
  newPerson.fullname = "Jane Doe";
  newPerson.gender = "F";
  newPerson.phone = "5555555555";
  newPerson.age = 29;
  await newPerson.save();

  // Find the person we just saved to the database using the custom query
  // method we wrote in the person repository.
  const existingPerson = await Person.findByName("Jane Doe");
  if (!existingPerson) {
    throw Error("Unable to find Jane Doe.");
  }

  // Change the person's full name.
  await existingPerson.updateName("Jane Johnson");

  // Remove the person from the database.
  await existingPerson.remove();

  // Clean up our connection pool so we can exit.
  await connection.close();
})();

Both methods are valid, and you should choose whatever you're more comfortable with, and whatever makes the most sense for your project.

Conclusion

TypeORM is a useful tool, and is capable of so much more than what is showcased here. We've only skimmed the surface of what TypeORM provides, and there is so much more. There is support for many more advanced features such as lazy relations, migrations, pagination, etc. The full documentation on their website has a lot of useful information.

You can find all the source code in this GitHub repo!

This Dot is a consultancy dedicated to guiding companies through their modernization and digital transformation journeys. Specializing in replatforming, modernizing, and launching new initiatives, we stand out by taking true ownership of your engineering projects.

We love helping teams with projects that have missed their deadlines or helping keep your strategic digital initiatives on course. Check out our case studies and our clients that trust us with their engineering.

Jamie Kuppens

I’m a software engineer with an interest in web development and some more esoteric things like emulator development.

@Reshurum @Reshurum

Why Every Developer Should Try Elm, + Are We Abandoning JavaScript? with Lindsay Wardell

Lindsay Wardell, Senior Software Engineer at NoRedInk joins Rob Ocel by covering many of the functional programming advantages afforded by Elm. By immersing themselves in the intricacies of languages like Elm, developers can harness its functional approach to bolster application resilience and minimize errors. Lindsay draws parallels with TypeScript's emphasis on strong typing, and how it empowers developers to detect and rectify errors early on, fostering the creation of more maintainable codebases. A pivotal theme emerging from Lindsay's discussions is the importance of adaptability in choosing which languages a developer focuses their energy on. As technology continues to evolve, developers must embrace versatility to remain competitive. Lindsay and Rob Ocel talk about the potential shifts in front-end development paradigms, beyond the dominance of component-based frameworks like React. This underscores the necessity of keeping an open mind and exploring emerging technologies to stay at the forefront of the industry's evolution. Moreover, Lindsay and Rob accentuate the significance of tools such as Nuxt for facilitating seamless backend integration within frameworks like Vue.js. These integrations streamline the development process, enabling developers to concentrate on crafting robust and scalable applications. Their conversations serve as a testament to the ongoing evolution of programming tools, marked by a trend towards simplification and enhanced development efficiency. Download this episode....

Apr 3, 2024

1 min

JavaScriptTypeScript

JSR - The cross-platform package manager for ESM

JSR - The cross-platform package manager for ESM Move over NPM, there’s a new package manager in town. JSR is a new open-source package registry for JavaScript and TypeScript. This particular release has caught my attention, and I’m excited to explore if and where JSR fits into our overflowing ecosystem. Setting the stage The JSR website has a page called Why JSR? I want to explore the _why_ a little bit further. I think that the _why_ points to JSR potentially being the future of the JavaScript ecosystem. NPM and JavaScript modules If you’ve found yourself confused about publishing and consuming packages and modules in the modern ESM era, you’re not alone. When NodeJS was created, there was no standard for JS modules. Node introduced CommonJS modules, and NPM provided a way to publish and consume these modules in our applications. It’s been years now since ES Modules became a standard and we are still in a weird module purgatory with no clear end in sight. JSR represents a fresh start, free from the baggage of the Node and NPM ecosystems. JavaScript runtimes and interoperability New JavaScript runtimes are popping up every year. WinterCG (_Web-interoperable Runtimes Community Group)_ was started to improve interoperability for the various runtimes with some standard APIs. Node has made steps towards this goal, but it might never get all the way there and NPM is coupled pretty tightly to that ecosystem. JSR supports several runtimes (depending on the package). This is a huge opportunity for it to become the package manager for the ecosystem at large. See cross-runtime support section for more details. NPM Compatibility JSR doesn’t end up being a complete departure from NPM since it includes an NPM compatibility layer. The docs highlight some limitations and provide deeper technical details on how this piece works. The simple overview is that you can install and use packages from JSR to a Node/NPM based project. The packages will get added to your package.json and node_modules directory. About the same as any package installed from NPM. JSR Overview At a high level, JSR is very similar to NPM. You can search for packages on the website. You can publish packages to their registry, and you can download packages from it as well. We touched on the two major features that set it apart from NPM: It only supports ESModules, and it has cross-runtime support. In the rest of the article we’ll explore the essentials (package management) and also some other really great features that feel like are bringing the JS package management ecosystem up to speed with our modern workflows. Native TypeScript I have to say I’m not surprised by this feature, considering JSR was developed by the same people behind Deno (a runtime that supports TS natively). It’s a great feature. If you haven’t figured it out yet TypeScript is a big deal and doesn’t seem to be fading away anytime soon. So what exactly does TS support look like in a package manager, though? If your runtime supports TypeScript natively (like Deno) - it can consume the TS files in your package directly. For environments that don’t, it will automatically compile your code to JavaScript and package it with type declaration files (.d.ts). Pretty, stinking, cool. The docs include a page called “about slow types”. It is probably worth a read on its own ,but the TLDR is that JSR will analyze your TS code and penalize you in certain ways if you have some type code that it considers to be slow. On the page, it outlines exactly what these penalties are, as well as what it considers slow types to be. Slow types will also come into play when we get into package scoring later in the article. Packages The docs have a page dedicated to packages. It covers some important stuff like deleting packages, versioning, documentation, and publishing. We’ll touch on some of it with an emphasis on the things that are done differently or better than with NPM. jsr.json file jsr.json is a configuration file that specifies a name, version, and exports of a package. It doesn’t include a list of dependencies like you would have in a package.json file. This is a configuration file for a package to be published to JSR. JSR dependencies are defined in an import map file or a package.json file, depending on the runtime. Adding packages to a project Adding packages to your project will vary depending on your target runtime. Here’s an example of installing it for Deno and NPM using various package managers. For Deno, an import map entry is created. For NPM, it gets installed as an NPM package using the JSR NPM compatibility layer. ` If the runtime has native JSR support, you don’t need to explicitly install packages. You can important them using the jsr: scheme. ` As you can see semantic versioning is supported and works similarly to NPM. Publishing packages If you want to publish a package to JSR, there are many important rules you need to be aware of. I recommend reading “Publishing packages” before moving forward with this. The main things to note are: ESModules only, npm packages are supported, JSR packages are supported, and node APIs are supported. So there aren’t many constraints. You just need to understand how exactly to do these things. IE: how do I use NPM dependencies, how do the imports work, etc? Once you’ve got all that down, you will be ready to publish your first package to JSR 😃 Documentation JSR emphasizes quality package documentation. It’s one of the factors in their package scoring algorithm, which we will discuss shortly. The important parts of a package’s documentation include: * a README.md file * symbol documentation - functions, interfaces, classes, etc * module documentation - docs for each exported module in a package The symbol and module documentation are written using JSDoc. The JSR website generates some nice documentation based on these 3 pieces on your package page. This means you can understand a package's entire public API without ever leaving jsr.io. Browser support The cross-runtime support is amazing, but I was curious where the original JavaScript runtime fit (the browser). According to the documentation: “You can use JSR with any tool that supports ES modules, and either has native support for JSR or supports npm packages using node_modules.” Since modern web browsers support ES modules, I will count this as JSR support. From what I can tell, though, there is one caveat. To include a module on a web page, we need a URL to download it from. Can we publish our package to JSR and use it to load a module via a script tag? According to the JSR usage policy, no. The “Unacceptable use” section states: “It is not acceptable to use JSR as a CDN for serving assets directly to web applications in a browser, except for development purposes.” So, it seems that this is a no-go for now. We will probably need to wait a bit for CDN services like jsdelivr to pull packages from the JSR registry, similar to what they do with NPM currently. Other notable features We’ve covered the most important bits about package management, TS support, NPM compatibility, etc. But there are still a few unique features that add an extra bit of sparkle to JSR. Scoring We mentioned earlier that documentation plays a part in a scoring algorithm. JSR analyzes all of the packages published to its registry and assigns them a score based on certain criteria: documentation, best practices, discoverability, and compatibility. This score is shown with every package listed on the website. You can also add a shiny badge to your README if you’re particularly proud of something. It’s still early on, so it’s likely this will evolve as time goes on. It’s an interesting gimmick, at the very least! Summary I am excited to have a new cross-platform compatible package manager that only supports ESM. As useful as NPM has been all these years, it carries a lot of baggage from “the olden days”. JSR’s native TypeScript support and reliance on modern standards is a breath of fresh air. It feels like I can publish a simple package without much ceremony. I’m excited to release my first package to JSR soon :)...

Apr 12, 2024

6 mins

JSRJavaScript

CSS Hooks: A new way to style your React apps

In the world of web development, the management of styles has always been a crucial aspect of building modern and responsive user interfaces. With the rise of CSS in JS libraries like Material UI and Chakra, developers have started creating dynamic and reusable styles using JavaScript; however, the performance implications of these libraries have led to the exploration of alternative solutions. One such solution is CSS Hooks, a library that offers a different approach to managing styles in web applications. The Problem with CSS in React and CSS in JS Libraries In React applications, you typically write styles directly with the style prop by applying classes with className, or if you’re using a framework like Material UI or Chakra then you may use the sx attribute, which is more powerful than the baseline attributes. There are some performance concerns in the case of the sx attribute specifically. The framework has to dynamically generate classes with your styles and apply them to the document whenever it is used. This can become an issue with large and complex applications as your styles will be regularly regenerated on the fly as the user navigates through the application. This has led developers to seek alternative solutions that offer similar flexibility but with better performance. Introduction to CSS Hooks CSS Hooks improve upon the aforementioned issues by pre-generating your CSS using configuration hooks. The styles that these hooks generate remain static during the lifetime of the application and are reused wherever possible. CSS variables are utilized under the hood whenever dynamic behavior is needed. By dynamic behavior, I’m referring to advanced CSS features such as pseudo-class selectors and media queries, which are both traditionally unavailable for use with inline styles. CSS Hooks utilize an interesting trick under the hood to accomplish this, which we will now explore. The Power of CSS Variables and the Fallback Trick As mentioned before, CSS variables are utilized in order to support using advanced dynamic behavior such as pseudo-selectors in our inline styles. CSS variables are core to something known as the “fallback trick”. The gist of how the fallback trick works is to have the pseudo-selector or media query toggle the state of a variable between initial and an empty invalid value, and then we put the value that we actually want to toggle as the “fallback” value in the reference to the CSS variable inside of the inline style itself. Changing this fallback value inside of an inline style essentially allows us to control the values used by the pseudo-selector without having to regenerate linked stylesheets as we only have to change the inline style attribute. This is best explained with an example: ` This plain HTML and CSS code effectively allows us to utilize the focus pseudo-selector on any element that we want using inline styles only. This is what CSS Hooks effectively does in the background when you use dynamic styles. No nasty re-renders and mucking with stylesheets needed! How to use CSS Hooks Now that we know how CSS Hooks fundamentally work, let’s try using them. The library itself is much easier to use than the aforementioned example. Installation and usage of CSS Hooks is easy. Firstly, we must install the @css-hooks/react package into a React project with the package manager of your choice (npm, pnpm yarn, etc). Once that’s done, all that’s left is a little bit of configuration. You have to first use a utility function called createHooks that is exported from the package we just installed. The result of that function returns a couple of variables in an array that can be deconstructed, the first being a stylesheet in the form of a string, and the second being a function. ` I recommend putting this in a separate file that can be imported from multiple other places in the project as this is where we will be configuring hooks across the project. We’re exporting both hooks and css, and both are important. We need to include hooks into the DOM as it contains the styles of the hooks that we’ll be referencing in our components. In your root component you need to add the following tag so that the generated CSS can be used. ` How this is done may vary based on the libraries that you are using. After that’s done, you can integrate CSS Hooks into your components while writing inline styles mostly just like you would do normally, with the only change being the addition of an additional call to the css function that we exported earlier: ` Now that the core boilerplate is set up, your components can now utilize CSS Hooks in theory; however we need to actually add some hooks to start with before we can use them, or else we’ll just be limited to basic styles in the same way that React inline styles are. Make your own hooks We can define our hooks inside of the css-hooks file that we created earlier, the same one that exports the css helper function that gets CSS Hooks working with our components. Hooks are defined in the options parameters that are currently empty. Defining a simple hook that allows us to define inline hover styles for a component is very easy and can be done as follows: ` Although both the key and the value in this example are the same, they both serve different purposes. The key is the name of the hook that we reference when we want to use it, while the value is the spec. The spec is the actual CSS selector that we’re writing. The name can be anything that you want it to be so long as it doesn’t clash with any existing style properties or hooks. Recommended hooks Although making your own hooks may be necessary at times, there is another repository provided by the CSS Hooks authors that adds a way to generate many useful hooks called @css-hooks/recommended. I highly recommend using this library to generate your hooks if at all possible, and the usage is pretty simple. Here’s an example showing how you can use it to generate media queries, color scheme specific styles and pseudo-selectors: ` Then with that done, these can be utilized as follows: ` We can also modify the hooks configuration to add our own hooks in addition to the recommended hooks by using the spread operator: ` Then that custom hook can be used by simply using its key name: ` Summary CSS Hooks offers a performant alternative to traditional CSS in JS libraries by leveraging CSS variables and providing a unique approach to managing styles in web applications. By pre-generating the stylesheet based on configured hooks and utilizing CSS Variables for dynamic behavior, developers can create reusable styles without the performance overhead associated with dynamic stylesheet generation. If you're interested in exploring CSS Hooks further, be sure to check out @CSSHooks for more information. You can also find some of the examples demonstrated here in a working app in our demos repository. Thank you for reading!...

Feb 28, 2024

5 mins

CSSReact

How to Go from Software Engineer to AI Engineer with Shawn Wang

Shawn Wang discusses AI engineering’s potential impact on the software development labor market and developer experience. With the rise of artificial intelligence technologies, there is an increasing demand for professionals who can bridge the gap between AI and software engineering, and engineers are looking around for guidance on how to best learn AI. Shawn emphasizes the importance of acquiring core knowledge and skills in AI engineering, highlighting the need for a systematic approach to education and development. He envisions a future where AI engineering becomes a recognized profession, with specialized training programs and certifications. He further talks about mental models for approaching AI literacy, and skill building. Shawn also discusses the use of advanced AI technology in software development. He showcases tools like GPT Vision, which can automate tasks and generate code efficiently. While acknowledging the potential of AI in enhancing productivity, Shawn emphasizes the need to leverage AI tools to augment human capabilities rather than replace them. Download this episode here....

Apr 17, 2024

1 min

Artificial IntelligenceEngineering Leadership

Connecting to PostgreSQL using TypeORM

What is an ORM?

Setup

Data Mapper

Active Record

Conclusion

Jamie Kuppens

You might also like

Why Every Developer Should Try Elm, + Are We Abandoning JavaScript? with Lindsay Wardell

JSR - The cross-platform package manager for ESM

CSS Hooks: A new way to style your React apps

How to Go from Software Engineer to AI Engineer with Shawn Wang

You might also like

Why Every Developer Should Try Elm, + Are We Abandoning JavaScript? with Lindsay Wardell

JSR - The cross-platform package manager for ESM

CSS Hooks: A new way to style your React apps

How to Go from Software Engineer to AI Engineer with Shawn Wang