Lesson goal

In this lesson you’ll learn how to store, retrieve and update data in a single table in postgres database.

You’ll also learn about database types and creating tables.

We’ll cover relations in a later lesson.

Database course index

This is part of a full course on persistence in postgres with typeorm and sql!

There is a github repo to go with this course. See part 2 for instructions.

Glossary: SQL flavours

There's an ANSI standard SQL language that describes SELECT, INSERT etc. But every major database has their own extended language in addition to the standard to cover procedural SQL. Think things like variables, flow control, mathematics functions. Even some of the ANSI commands get new special extensions on some database engines.

This makes some of the SQL that you write for one database incompatible with another database. If you learn pgSQL in Postgres there will be things that T-SQL on Microsoft SQL Server does differently.

It's just something to be aware of.

These differences even pop up on different versions of the same database!

SQL implementations change and improve over time. It’s a good thing but can be confusing sometimes.

Common postgres data types

There are many types available in postgres but these are the types I see again and again in postgres schema for line of business web applications.

Sqlite data types

In postgres the container for values has a strict type. i.e. the columns are typed strictly and you cannot put an integer in a string column.

In sql lite the columns are not typed strictly. Any column can contain any data type (except primary key ids columns). The very latest versions of SQL lite allow you to enforce strict types but it’s not very common yet.

These are the sqlite data types

Dynamo data types

In dynamo the columns are not typed.

All of these datastores also allow for some kind of “Null” or “no value set” data-type in a column.

Glossary: ORMs

When we write code in languages like Javascript and C# we usually use objects. Objects can have properties that are other objects resulting in a complex tree structure of objects.

// pet owner
class PetOwner {
name: string,
pets: {
  name: string,
  type: "cat" | "dog"
}[] // array of pets
}

This pet owner object can’t be directly placed in a relational datastore because there is no concept of nested tables. Instead we would have 2 tables and join them with a relation.

When reading this data back from the database we have to turn the tabular data from 2 tables back into object structure. This problem is the miss-match of object and relations.

There are more complex problems in object-relational mapping like handling tree-structures and inheritance. These concepts are easy in procedural programming languages but tricky in relational data stores. ORMs will abstract away some well-known solutions to things like the tree structure problem for you.

Object-Relational Mappers (ORMs) are helper tools that perform some parts of this mapping for us so we don’t have to write the same code over and over again. They help prevent errors when we forget to assign data from a table property to an object property for example.

There are ORMs for most platforms and they all do the same thing with slightly different syntax. NHibernate is a common Java ORM, Entity Framework is a common .NET ORM.

I will use TypeORM for typescript in my examples here.

https://typeorm.io/

GLOSSARY: normalization

There’s a concept called “normalization” when structuring relational schemas. Normalization is process of applying the “normal forms” to your schema to reduce duplication and ensure data integrity.

Essentially normalisation is identifying how one table of data should be split into multiple tables to better suit relational data stores.

There are advantages and disadvantages to putting all your data into one table (de-normalized - dynamodb) vs many tables (normalized - postgres).

Denormalised data can be tricky to update because every row might have an instance of a piece of data. In the example above if we wanted to change the owner's name and the owner had multiple pets, we would have to update multiple rows. In the normalised case we could just update the pet_owner table. Denormalised data is often more performant to read and write because there are no joins required.

Hopefully this gives you an understanding for when you hear the word normalisation!

See wikipedia for more information and a detailed example: https://en.wikipedia.org/wiki/Database_normalization#Example_of_a_step_by_step_normalization

GLOSSARY: Postgres naming and strings

In postgres if you have a table or column that has mixed casing: mixedCase then you must wrap that name in double quotes

-- this will NOT work because ownerName has mixed case
INSERT INTO "lesson3"."pet"(name, ownerName) VALUES ('bobby the dog','maria');

-- this will work because I have wrapped it in quotes
INSERT INTO "lesson3"."pet"(name, "ownerName") VALUES ('bobby the dog','maria');

So to simplify, it’s almost easier just to wrap all table and column names in quotes if you use a mixed case naming strategy.

Remember that string literals should use single quotes like ANSI SQL. See the VALUES above for examples.

Creating tables in postgres

When we want to create tables there are specific commands to define the structure.

We name the new table, we define the column names and their types, then we apply any constraints we would like.

You should be able to open your database in postico, double click “SQL Query” and paste in the commands below.

CREATE TABLE "lesson3"."pet" ("id" SERIAL NOT NULL,
"name" character varying NOT NULL,
"ownerName" character varying NOT NULL,
CONSTRAINT "PK_lesson3_pet" PRIMARY KEY ("id"));

Select it all and click “Execute Selected”.

Here is a breakdown for one of the commands

Error when re-running the CREATE commands

You’ll note that if you try to run CREATE twice you’ll get an error

ERROR: relation "pet" already exists

This is the database protecting your data and schema. It’s letting you know that the structure already exists.

If you like you can remove the tables we added with the following commands to test CREATEing again.

DROP TABLE IF EXISTS "lesson3"."pet";

Before moving on, please make sure that the tables exist by running the CREATE block again! We will use them soon.

OK! Let’s start working with the database

Inserting data - sql

You can use postico to run these commands.

Inserting a single entry to a single table with raw sql follows this pattern

-- insert into TABLE_NAME(column_name,column_name...) values (column_value, column_value...)

INSERT INTO "lesson3"."pet"("name", "ownerName") VALUES ('bobby the dog','maria');

If you want to insert multiple rows you can pass an array of values. You’ll see that this returns 0 2 for 2 items inserted.

-- insert into TABLE_NAME(column_name,column_name...) values (column_value, column_value...),(column_value, column_value...)

INSERT INTO "lesson3"."pet"("name", "ownerName") VALUES ('bobby the dog','maria'), ('slinky the cat','ben');

If you want to return the created id, in postgres you can specify returning and it will return the created row id(s).

INSERT INTO "lesson3"."pet"("name", "ownerName") VALUES ('bobby the dog','maria'), ('slinky the cat','ben') RETURNING "id";

Inserting data - typeorm

TypeORM requires a connection to our database and some pre-configuration that I won’t cover here but can be seen in the project if you’re interested.

For now just know that any file with .entity.ts will be treated as a special database entity by typeorm.

An entity in typeORM describes how a table should be mapped to an object and vice versa. In typeorm we use special decorators provided by the typeORM library (@Entity, @Column etc).

It’s very important to remember that the entity class is a DTO only - an entity’s data must be controlled by typeorm. Never initialise and of the properties on an entity class.

// examples of initializing properties on an entity

@Entity()
class MyEntity() {
  constructor() {
     this.name = "" // DON'T do Constructor Initialization in a db entity!!
  }
 public name:string = "" // OR Property Initialization!! BAD IDEA !!
}

Note: You don’t have to type the typescript code here unless you need to practice. It’s in the folder src/lesson3 and file src/lesson3.

The following class is decorated to describe an entity for typeorm.

// lesson3/models/pet.entity.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity({ schema: 'lesson3' })
export class PetL3 {
  @PrimaryGeneratedColumn()
  id!: number

  @Column()
  name!: string

  @Column()
  ownerName!: string
}

When we have described an entity we can use a typeorm entity repository to work with the entity. The repository has many methods but we typically use save() for saving and findBy() for retrieving.

.save() will check if the entity being saved contains a primary key (id). If it does .save() will update the entity instead of trying to insert. This saves us having to choose between inserting and updating in our code. Save will also return the saved entity.

If you're performing a partial update with PATCH or a PUT then you can use save(). With save typeorm will ignore any undefined properties - save will not set undefined properties to NULL unless the properties are explicitly set to null on the object passed to typeorm.

// get a repository for Pet
const connection = await AppDataSource.connection()
const petRepository = connection.getRepository(Pet)

// create and save a new pet
const petToSave = new Pet()
petToSave.name = 'bobby the dog'
petToSave.ownerName = 'mike'
const savedPetOwner = await petRepository.save(petToSave)

The test code in the repository also shows retrieving the saved item and then deleting it to clean up.

You can also use the repository to create() a new instance of an entity without new-ing one up.

I prefer this method because it’s a bit cleaner than using new classes and could be mocked easily if you needed to.

const pet = petRepository.create({
  name: 'bobby - used create method',
  ownerName: 'mike - used create method',
})

const savedPet: Pet = await petRepository.save(pet)

You can see the power of typescript here - because the repository understands the model that we're trying to work with, the repository methods and parameters are all typed correctly.

typed orm queries

You'll see that there are .insert() and .update() methods. These methods mimic the sql commands. Insert will try to create a new entry and return the raw insert response with number of records inserted.

You can insert directly using the .insert() method. This will not check if an entity exists. It is the equivalent of the raw commands we pasted in to postico.

The result returned is the same count of inserted items, rather than the entity(ies).

const savedPetResult: InsertResult = await petRepository.insert(pet)

You can insert multiple entities in one go by passing an array to .save() or insert()

const pet1 = petRepository.create({
  name: 'bobby - used create method',
  ownerName: 'mike - used create method',
})

const pet2 = petRepository.create({
  name: 'bobby - used create method',
  ownerName: 'mike - used create method',
})

// save all pets in one call
await petRepository.save([pet1, pet2])

Never have a repository “save()” ORM call in a loop, use an array instead.

// this is bad
for (let petInstance of petArray) {
  petInstance.name = 'newName'
  await petRepository.save(pet1)
}

// instead update the items and save the array
for (let petInstance of petArray) {
  petInstance.name = 'newName'
}

await petRepository.save(petArray)

Selecting data - sql

Now that we can insert data we need to read it back out.

You must think about SQL queries as declarative statements returning sets (arrays). The query will always return a set that matches your request.

You can see in the select below there is no where clause. This will return a set with all the items in the pet owner table.

 SELECT "Pet"."id" AS "Pet_id", "Pet"."name" AS "Pet_name", "Pet"."ownerName" AS "Pet_ownerName" FROM "lesson3"."pet" "Pet";

Also note that I specify all the columns, even though I want all the columns. You can use "_" - SELECT _ FROM... to select all columns in a query. This is generally thought of as bad practice because the schema might change so you'll have to rename the columns anyway. Usually you're also joining with other tables in a query but will only want a small subset of the potential columns in results.

We restrict the result set with conditions like the WHERE clause.

Sometimes a query will return multiple results when you expect just one, especially when joining.

Because of this you should generally support arrays of results in your code rather than a single result.

There are hacks to force one result like using TOP 1 or LIMIT 1 but these are usually a code smell in your query.

Next we add a where clause to limit results to ones named mike loves doggos. You should node that the strings are defined with single apostrophes but the schema elements use double quotes - WHERE ("PetOwner"."name" ='mike loves doggos').

 SELECT "Pet"."id" AS "Pet_id", "Pet"."name" AS "Pet_name", "Pet"."ownerName" AS "Pet_ownerName" FROM "lesson3"."pet" "Pet" WHERE ("Pet"."name" = 'tabby');

Selecting data - typeorm

Let’s do the same two selects in typeorm.

// get a repo as usual
const connection = await AppDataSource.connection()
const petOwnerRepository = connection.getRepository(PetOwner)

// call an empty "find" command to get all entries in the table
const foundEntries = await petOwnerRepository.find()

// get a repo as usual
const connection = await AppDataSource.connection()
const petOwnerRepository = connection.getRepository(PetOwner)

// call findBy with the where clause specifying "name".
const foundEntries = await petOwnerRepository.findBy({
  name: pet1Name,
})

If you only need a subset of the entities properties then it is faster to request only those properties.

This is what the sql looks like.

SELECT "Pet"."ownerName" AS "Pet_ownerName", "Pet"."id" AS "Pet_id" FROM "lesson3"."pet" "Pet" WHERE ("Pet"."ownerName" = "tabby");

Here is how to specify the properties to select in typeorm. Again you can see that it’s all strongly typed for us.

const foundItems = await petRepository.find({
  where: { ownerName: 'dave' },
  select: { ownerName: true }, //only return ownerName
})

We’ll cover more advanced select queries with ordering and paging in part 5 of this series.

Updating data - sql

When updating with raw sql you almost always need a WHERE clause to limit the updates to a few entries. It’s a good trick to first write you’re UPDATE statement as a SELECT statement.

-- start with SELECT * FROM "lesson3"."pet" WHERE "id" IN 1;

-- then convert to update
UPDATE "lesson3"."pet" SET "name" = "new name" WHERE "id" IN 1;

update result

An important thing to note here is the declarative nature of sql queries still applies. The previous example will change the name of all entries if you take away the WHERE.

Updating a record - typeorm

There isn't much to add here because I showed all of the save and "upsert" commands above in the insert section.

This is one of the nice things that typeorm gives us, we can use the same save command and know that typeorm will figure out if we’re updating or inserting. It checks if the entity we save has an id assigned to the object.

// update properties as usual
foundPet!.name = nameToUpdateTo

// update the name
const saveResult = await petRepository.save(foundPet!)

There is a specific update method to do an UPDATE..WHERE that accepts the search criteria as an object in the first parameter, and the values to update to in the second parameter.

// update the name - the first param is the "where" details
// the second param is what to update to
await petOwnerRepository.update(
  { name: 'existing name' },
  {
    name: 'this is an even newer name',
  }
)

This allows you to update records without retrieving them. The resulting sql looks like this.

: UPDATE "lesson3"."pet" SET "name" = 'this is an even newer name' WHERE "name" = 'existing name';

GLOSSARY: null vs undefined in typeorm entities

If you have a nullable column in your entity model you have to set it to javascript null for typeorm to null it during update() or save().

If your nullable property is undefined then typeorm will just ignore that column.

This is great because it allows you to do partial updates. But it’s important to underhand the difference in usage.

Deleting data - sql

You’ll start to see a pattern here where we always use a where clause to limit the set a command will work on.

-- with no where clause this will delete ALL entries in the table
DELETE FROM "lesson3"."pet";

-- truncate is faster if you really want to clear a table.
-- but this is not too common for business objects
-- it won't work with relations to other tables present
TRUNCATE TABLE "lesson3"."pet";

Even though it applies to relations I'll briefly mention cascading here. If you specified that cascade should happen when creating the tables in the schema then when you delete a record that has relations, all the relations will also be deleted.

You can specify this with truncate also.

TRUNCATE TABLE "lesson3"."pet" CASCADE;

Result: NOTICE: truncate cascades to table "lesson3"."pet" TRUNCATE TABLE

-- with a where clause you can limit the deletion
DELETE FROM "lesson3"."pet" WHERE id = 20;

Deleting data - typeorm

To delete in type orm you can get an entity and remove() it.

await petRepository.remove(petInstance)

You can delete based on a search/where to avoid having to retrieve an item.

await petRepository.delete({ id: 20 })

You can still truncate with clear() but we almost never do this on production databases.

await petRepository.clear()

Glossary: jsonb

jsonb is a special data type for postgres. It allows us to save json straight in to the database, and to read it back.

The really nice thing about jsonb is that it can be queried and it’s fast!

You would want to avoid using jsonb for everything. Use your RDBMS for what it’s good at - ensure consistency. There are no schema checks for jsonb fields. There’s no guarantee that the structure will be as expected so you have to check it.

With typeorm you have to set the column type to jsonb. Then you treat it like an object property.

// in your entity.ts
@Column({
        type: 'jsonb',
        default: () => "'{}'"
})

TypeORM cannot query in the jsonb property. If you want to do that you will have to use a raw select query in querybuilder. We’ll cover query builder later.

The docs for querying jsonb are here: https://www.postgresql.org/docs/9.5/functions-json.html

Summary

You should be able to read and write data to a single table now.

Most of our applications have more complicated domains with relations. We’ll cover working with those next.