INDEX

Slonik and CockroachDB

#Slonik#CockroachDB#Node.js#Typescript#PostgreSQL#Javascript

By Morgan Winslow at

Testing out Slonik, "a Node.js PostgreSQL client with runtime and build time type safety", on CockroachDB

Overview

In this blog I'll be looking at some of the basics of Slonik and confirming that they integrate smoothly with CockroachDB. As you'll see in the About section below, there's a lot of cool features of Slonik, but I won't be diving into all of them today. Instead I'll be going through some standard CRUD operations and basic Slonik features.

About Slonik

Slonik is A PostgreSQL client built for Node.js with an emphasis on runtime type safety. As with many things, if it works with Postgres then I want to try it out with CockroachDB. The repo can be found here.

Some core principals and features are:

  1. Promotes writing raw SQL.
  2. Discourages ad-hoc dynamic generation of SQL.
  3. Safe connection handling
  4. Safe transaction handling
  5. Detailed logging
  6. Type safety

One of the other big reasons for the creation of Slonik was "motivation to reduce the repeating code patterns". This is a bit different than my last couple blogs which have been more ORM/SQL generation heavy and is not at all the focus of Slonik.

Further Reading

The founder of Slonik has some great blogs about the birth of Slonik and the reasons for wanting to create a Node.js client for Postgres.

Pre-reqs

  1. A CockroachDB connection string. I recommend using the Serverless offering for a quick start. You can follow the steps outlined here
  2. npm install slonik
  3. npm install zod

Create person Table in CockroachDB

First, let's create a very simple table in CockroachDB that we will use to do our testing. Note, it's not usually recommended to use ints as a Primary Key in CockroachDB, but we'll be using this for the sake example. 

CREATE TABLE person (id INT PRIMARY KEY, name STRING);

Create Connection

Reference

The first thing we'll do is create a connection to the database using the createPool func. 

import {
  createPool,
} from 'slonik';

const pool = await createPool('postgres://username:password@your-serverless-123.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=require');

pool.connect(async (connection) => {
  await connection.query(sql.typeAlias('id')`SELECT 1 AS id`);
});

In this example, the connection will be kept alive until the Promise is resolved. You can also use pool directly, which is what I'll be doing most of the time in my examples for quick testing. 

pool.query(sql.typeAlias('id')`SELECT 1 AS id`);

Define Objects

Slonik Reference

Zod Reference

The first object we'll create is a personObject that represents our person table. You'll notice that "Slonik integrates zod to provide runtime query result validation and static type inference".

const personObject = z.object({
    id: z.number(),
    name: z.string(),
})


const sql = createSqlTag({
    typeAliases: {
        id: z.object({
            id: z.number(),
        }),
        void: z.object({}).strict(),
    }
})

The second object is a Type Alias that utilizes a sql tag.

INSERT

We'll take a look at both a simple INSERT, as well as bulk INSERT. Both of these worked on CockroachDB without any adjustments.

Simple INSERT

In this example we are just inserting an id and passing a value of morgan for the name. 

const morgan = 'morgan'
const personInsertQuery = sql.type(personObject)`INSERT INTO person (id, name) VALUES (1, ${morgan})`

await pool.query(personInsertQuery)

query is one of the most basic query methods and is equivalent to node-postgres.

Bulk INSERT

Reference

await pool.query(sql.unsafe`
  INSERT INTO person (id, name)
  SELECT *
  FROM ${sql.unnest(
    [
      [1, 'mauricio'],
      [2, 'alex']
    ],
    [
      'int4',
      'string'
    ]
  )}
`);

SELECT

The first example here will just be a simple SELECT statement using our personObject. The second will leverage one of the ways that Slonik reduces code patterns.

Simple SELECT with Object

const personSelectQuery = sql.type(personObject)`SELECT id, name FROM person WHERE name = ${morgan}`;

const morganSelect = await pool.any(personSelectQuery);

if (morganSelect.length > 0) {
  console.log(morganSelect[0].id)
}

oneFirst SELECT

Reference

oneFirst is one of the methods that abstracts typical coding patterns.

Returns value of the first column from the first row.

  • Throws NotFoundError if query returns no rows.
  • Throws DataIntegrityError if query returns multiple rows.
  • Throws DataIntegrityError if query returns multiple columns.
try {
  const selectOneFirst = await pool.oneFirst(sql.type(personObject.shape.id)`
      SELECT id
      FROM person
      WHERE name = ${morgan}
  `);
  console.log(selectOneFirst)
} catch (error) {
  console.log(error)
}

DELETE

The main difference in the DELETE case is you'll notice that I'm using the void type alias. 

await pool.query(sql.typeAlias('void')`DELETE FROM person WHERE id = ${morganSelect[0].id}`);

Conclusion

I did not have to make any changes to integrate basic Slonik features with CockroachDB. Since Slonik relies so heavily on raw SQL, rather than generation, I'm confident that this would remain the case in deeper tests.

Although this was just a quick compatibility test, it's clear there's plenty of other areas to explore here. Slonik seems to put things like connection pooling, transaction, and error handling at the forefront, which is a great fit for CockroachDB.

All code can be found here