A Software Engineer's Tips and Tricks #2: Template Databases in PostgreSQL
Tags: postgres, webdev, programming, tutorial
Published: 2024-04-30 09:10:00

ey there! We're back for our second edition of Tips and Tricks. As we said in our [first post on Drizzle ORM](https://www.koyeb.com/blog/a-software-engineers-tips-and-tricks-1-drizzle), our new Tips and Tricks mini blog series is going to share some helpful insights and cool tech that we've stumbled upon while working on technical stuff.

Today, we're going to talk about the template databases of PostgreSQL.
Remember, these posts will be super short reads. If you don’t like the topic of one of the posts, no problem! Just skip it and check out the next one.

But if you do find something you like, please share it, tweet it, or tell your friends about it. And more importantly, don’t forget to check out the "further reading" links we’ll include at the bottom of each post for more info to dive deeper into the topic.

Sound good? Keep an eye on our blog, and let’s see where this goes!

## Template Databases in PostgreSQL

Have you ever noticed the `template0` and `template1` databases in PostgreSQL?

```
postgres=# \\l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(3 rows)

```

These databases are template databases, used as the basis for new databases. In other words, when you create a new database, PostgreSQL simply makes a copy of `template1`.

### Helpful for Creating Extensions Automatically

Let’s say you want to create a new `users` table, and have a `id` of type `uuid` with a default value.

```json
create table users(id uuid default uuid_generate_v4(), email varchar);
```

By default, executing this statement will fail because you don’t have the extension `uuid-ossp` installed:

```json
ERROR:  function uuid_generate_v4() does not exist
LINE 1: create table users(id uuid default uuid_generate_v4(), email...
                                           ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
```

The fix here is simple. Create the extension!

```json
create extension "uuid-ossp";
```

Then, you can create the table:

```json
# create table users(id uuid default uuid_generate_v4(), email varchar);
CREATE TABLE
```

If you often create new databases, you would have to run this `CREATE EXTENSION` every time. To avoid doing it manually, you can also add the extension to the `template1` database:

```json
\c template1
CREATE EXTENSION "uuid-ossp";
```

Now, all the newly created databases will have the `uuid-ossp` extension by default, because `CREATE DATABASE` does nothing more than copy `template1` to the new database.

### #2 Great for Facilitating Quick, Local Backups

During development, you might want to backup your database locally and restore it just after. For example, you might want to run [`you-super-script.sh`](http://you-super-script.sh) that could destroy all your precious data.

One way to create a backup of your database locally is to run `pg_dump` and `pg_restore`. This works well, but it can take some time, and there’s also a simpler solution: use **template databases**!

You can simply create a new `backup` database using the database you want to backup as a template. By default, `CREATE DATABASE xxx` creates `xxx` by copying `template1`. If you specify `TEMPLATE 'yyy'`, it copies `yyy` instead!

```sql
CREATE DATABASE backup TEMPLATE 'mydb';

```

When you are done with `your-super-script.sh`, you can simply recreate your database from the backup previously made:

```sql
\c postgres
DROP DATABASE mydb;
CREATE DATABASE mydb TEMPLATE 'backup';
```

It wouldn’t be wise to use this method to backup your production databases, since `mydb` and `backup` are both stored on the same physical hard drive. But for testing purposes, this handy method can help you to create and restore databases rapidly.

**Further reading**

- PostgreSQL template databases: https://www.postgresql.org/docs/current/manage-ag-templatedbs.html
- Backup and restore a PostgreSQL database: https://www.postgresql.org/docs/current/backup.html
- Serverless PostgreSQL databases on Koyeb: https://www.koyeb.com/docs/databases

<Banner
  title="Blazing-Fast Deployments"
  description="Run your serverless Postgres databases next to your serverless Apps and Services on Koyeb."
  type="claim-free"
  buttonText="Deploy Now"
  buttonLink="https://www.koyeb.com/tutorials/build-and-run-a-web-app-using-turso-drizzle-orm-and-express-on-koyeb"
/>

## SIGTERM

That’s it for today! We hope you enjoyed today's tips and tricks. If you have any feedback or suggestions for future posts, feel free to reach out! You can find us on Twitter (or X) at [@gokoyeb](https://twitter.com/koyeb_com), [LinkedIn](https://www.linkedin.com/company/koyeb/), or the [Koyeb Community](https://community.koyeb.com/).