Typesafe PostgreSQL queries with Kysely and Neon
Tags: neon, postgressql, database, programming
Published: 2024-03-16 23:05:40

Your database is one of the most critical components of your application, and it’s essential to query it correctly to extract the necessary data. Typesafe database queries allow you to leverage the type system to prevent type errors, catch errors at compile-time, debug easily, and reduce security vulnerabilities. 

This tutorial will walk you through creating a contact management system using [Kysely](https://kysely.dev/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) and [Neon PostgreSQL](https://neon.tech/?ref=hm). The application will introduce you to the fundamentals of typesafe programming using Kysely integrated into the Neon PostgreSQL serverless database.

By the end of this tutorial, you'll have a functional application and a solid understanding of how to apply typesafe practices in your database interactions.

## What are typesafe database queries?
In software development, data integrity is paramount. Ensuring that the data stored in a database is accurate, consistent, and reliable is essential for the proper functioning of any application. [Typesafe database queries](https://programmingsharing.com/type-safe-queries-in-spring-data-jpa-a4ca195d7c37) play a pivotal role in achieving this data integrity by leveraging the power of static type checking to prevent errors and maintain the overall robustness of the codebase.

## PostgreSQL
[PostgreSQL](https://www.postgresql.org/) is a powerful, open-source object-relational database management system (ORDBMS) known for its reliability, flexibility, and extensibility. It has gained widespread popularity among developers thanks to its robust feature set, including data integrity, ACID compliance, and extensibility.

## Type safety and its significance in database queries
Typesafe query builders provide a type-safe way to construct SQL queries without using an ORM (object-relational mapping) tool. This gives developers more control over their queries and can be a good choice for applications requiring more complex queries.

Some popular typesafe query builders for JavaScript and TypeScript include:
-    Kysely: Kysely is a popular query builder known for its ease of use and support for autocompletion.
-     [safeql](https://safeql.dev/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity): safeql is a library that validates and auto-generates TypeScript types from raw SQL queries.
-     [ts-query-builder](https://ts-sql-query.readthedocs.io/en/stable/#gsc.tab=0?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity): A simple and lightweight query builder that is easy to start.

In this tutorial, we’ll use **Kysely** as it's easy to use, supports auto-completion for faster development, and integrates seamlessly with Neon serverless Postgres.

## What is Kysely?
[Kysely](https://kysely.dev/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) is a TypeScript SQL query builder that guarantees type safety and auto-completion. It is primarily designed for Node.js but can run on [Deno](https://deno.com/?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) and web browsers. Kysely ensures that you only refer to tables and columns visible to the part of the query you're writing. Moreover, the generated result type includes only the selected columns, complete with their correct types and aliases.

Kysely's [documentation](https://kysely.dev/docs/intro?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity) features excellent examples of how to use it and guides on how to get started.

## What is Neon?
[Neon](https://neon.tech/?ref=hm) is a serverless Postgres database that separates compute and storage to offer modern developer features such as autoscaling, branching, bottomless storage, and more.

Refer to the Neon [Get Started](https://neon.tech/docs/introduction?utm_source=guides&utm_medium=content&utm_campaign=hackmamba) documentation to get up and running in just a few minutes.

## GitHub link to view source code
Check out the complete source code [here](https://github.com/namancoderpro/contact-management-system-db?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity).

## Contact management system with Kysely and Neon PostgreSQL
To build the CRUD (Create, Read, Update, Delete) contact management system application, you will need a few things:
    - Node.js and npm
    - Familiarity with TypeScript
    - Fundamental SQL and database concepts

This tutorial uses NodeJs LTS version `v20.10.0` and npm version `10.2.3` 

To know the versions on your computer, run the following commands:

```js
node -v // Get nodeJS version
npm -v // Get npm version
```

## Setting up Neon PostgreSQL
[Sign up](https://console.neon.tech/) for [Neon’s free tier](https://neon.tech/docs/introduction/free-tier?ref=hm) and upgrade to a paid plan when ready to scale.
After signing up, you will be redirected to the Neon console to create your first project. 

Enter a `name` for your project, select a `Postgres version`, provide a `database name`, and select a `region`. 

When you're done, click **Create Project**

![Neon’s create a new project screen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/epwho2d8fxvyc155e2yr.png)

You’ll be presented with the connection details for your new project, which you will use to connect to your database from a client or application. You can retrieve them later from the connection details widget on the Neon dashboard.

## Installing TypeScript and Kysely
TypeScript is a superset of JavaScript, adding static types to the language. It is installed via npm (Node Package Manager).

Open your terminal and run the following command:

```js
npm install -g typescript
```
TypeScript is installed globally with the command above, so you can use it in any project.

Run the following command in your terminal to install Kysely:

```js
npm install kysely
```
For Kysely's query compilation and execution to work, it needs to understand your database's SQL specification and how to communicate with it. This requires a dialect implementation.

Kysely uses the `kysely-neon` dialect for Neon serverless Postgres. Run the following command to install the dialect:

```js
npm install kysely-neon
```
To connect to the Neon instance, you need a WebSocket. Install the ws module using the following command:

```js
npm install ws
```
Perfect — now let’s create our project directory with the following commands:

```js
mkdir contact-management-system
```
Now, navigate inside this directory with the command:

```js
cd contact-management-system
```
Next, run this command to create a TypeScript project: 

```js
npx tsc --init
```
That will leave you with a folder structure like this:

```js
├── node_modules/                      // Node modules and dependencies
├── package-lock.json                  
├── package.json
└── tsconfig.json
```
Finally, run these three commands to create the `index.ts` file in a `src` folder:

```js
 mkdir src
```
```js
  cd src
```
```js
 touch index.ts
```
Finally, you should have this folder structure as shown below:

```js
contact-management-system/
│
├── node_modules/              # Node modules and dependencies
│
├── src/                       # Source files directory
│   └── index.ts               # Main entry point for the application
│
├── dist/                      # Compiled JavaScript files (generated after TypeScript compilation)
│
├── package.json               # Node.js project metadata and dependency list
│
├── package-lock.json          # Automatically generated for any operations where npm modifies the node_modules tree or package.json
│
└── tsconfig.json              # TypeScript compiler configuration file
```

## Database schema design
The contact management system requires a simple yet effective database schema. You must create a `contacts` table that will store the contact information. The table will have the following fields:
- `id`: A unique identifier for each contact. This will be the primary key. 
- `name`: The name of the contact. 
- `phone`: The phone number of the contact. 
- `email`: The email address of the contact.

## Implementing schema with Kysely
With Kysely, you can define this schema directly in your TypeScript code. Kysely provides a way to define table schemas using TypeScript interfaces, which are then used to generate corresponding SQL queries. Here's how you can define the `contacts` table schema using Kysely.

Note: Use environment variables as a best practice for security and convenience. Refer to this DotEnv article to learn how to use Node environment variables.

In the `src/index.ts` you will import all the required modules and initialize Kysely with your database connection information. 

This `src/index.ts` file will also contain all the application code.

```js
//index.ts
import * as dotenv from "dotenv"; 
import { GeneratedAlways, Kysely } from "kysely"
import { NeonDialect } from "kysely-neon"
import ws from "ws"

dotenv.config();
```

Line 2 - imports the `dotenv` module, which allows you to load environment variables from a `.env` file into `process.env`.

Line 3 - imports the `GeneratedAlways` and `Kysely` types from the `kysely` module. These types are used for interacting with the database.

Line 4 - imports the `NeonDialect` type from the `kysely-neon` module. This type is used to specify the dialect of the database.

Line 5 - imports the `ws` module, which allows you to create WebSocket servers and clients.

The `dotenv.config()` line calls the `config` function from the `dotenv` module, which loads the environment variables from the .env file into process.env.

Now, let’s move on to the next lines, where the database’s types and tables are declared inside the `src/index.ts` as shown below:

```js
interface Database{
    contacts: ContactTable
}

interface ContactTable {
    id: GeneratedAlways<number>
    name: string
    phone: string
    email: string
}
```

First, a type for the Database object with `interface Database{}` was defined with a `contacts` property of type `ContactTable`. This interface is used to define the structure of the database.

The `interface ContactTable` defines a type for the `ContactTable` object, representing the database table.

After this, we will declare the database, the schema, and a function to create the contacts table within our database.

As you can see in this code snippet, we declare the `db` variable to establish a connection with the database using the connection string, which can be obtained from your Neon console.

Next, we access the database schema inside the `src/index.ts` using `db.schema` and then declare a function `createContactsTable()` that creates the `contacts` table and adds the following columns: `id`, `name`, `phone`, and `email`.

```js
const db = new Kysely<Database>({
    dialect: new NeonDialect({
        connectionString: process.env.DATABASE_URL, // URL from Neon Connection Dashboard
        webSocketConstructor: ws,
    }),
})

const schema = db.schema

// create table
async function createContactsTable(): Promise<void> {
    await schema
        .createTable('contacts')
        .ifNotExists()
        .addColumn('id', 'serial', col => col.primaryKey())
        .addColumn('name', 'text', col => col.notNull())
        .addColumn('phone', 'text', col => col.notNull())
        .addColumn('email', 'text', col => col.notNull())
        .execute()
}

createContactsTable();
```

## Implementing the CRUD Operations
Now that we’ve created our table, it’s time to perform CRUD operations on it to create, add, read, update, and delete contacts.

### Insert
This code snippet will show how you can insert data into the database in a type-safe manner. Add this to the `src/index.ts` file itself.

```js
async function insertContact(name: string, phone: string, email: string) {
    await db
        .insertInto('contacts')
        .values([
            {
                name,
                phone,
                email
            }
        ])
        .returning('id')
        .execute()
}

insertContact("Kysely Doe", "1234567890", "qTqQ1@example.com");
insertContact("Neon Doe", "1234567890", "qTqQ1@example.com");
```
In this code, we created a function `insertContact()` and accept the name, phone, and email as arguments to it. Next, we’re inserting into the `contacts` table using the `insertInto()` function, and the values are taken from `insertContacts()`'s arguments.

### Update
Next, let’s create a function to update existing contacts in the database inside `src/index.ts` file.

```js
// Update a contact in the database
async function updateContact(id: number, name: string, phone: string, email: string) {
    return await db
      .updateTable('contacts')
      .set({ name, phone, email })
      .where('id', '=', id)
      .returningAll()
      .executeTakeFirst()
  }

updateContact(1, 'Jane Doe', '1234567890', 'qTqQ1@example.com');
```
This time, the function will take the `id` as an argument along with the other fields so that we can point to a specific record and update it.

`updateTable()` is an built-in function in Neon to perform update operations. We set the updated fields like regular SQL queries using the `set()` function.

### Delete
After that, the delete function will allow us to remove contacts from our database.

```js
// src/index.ts
...
// Delete a contact from the database

async function deletePerson(id: number) {
    return await db.deleteFrom('contacts').where('id', '=', id)
      .returningAll()
      .executeTakeFirst()
  }

deletePerson(1);
```
In this case, we only need the `id` to remove that specific entry from the database using the `deleteFrom()` function.

### Read
Finally, the `getContacts()` function inside the `src/index.ts` will allow us to fetch the existing records in the database.

```js
// src/index.ts
...
// Read from the database
async function getContacts() {
    const contacts = await db.selectFrom("contacts").selectAll().execute();
    return contacts;
}

const contacts = getContacts();
console.log(contacts);
```
To execute the code, you must run the following code:

```js
npx ts-node src/index.ts
```
If the code runs without throwing any errors, it means the database transaction has been successfully completed. 


![Running npx ts-node in terminal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3cedhrbeujm4texk56vx.png)

To check, you can also log into your Neon console, select the project, and navigate to **Tables → contacts**.


![Sample database in Neon](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bi0q0ot7b8w4eiuso0va.png)

## Error handling and type-safety in TypeScript with Kysely
TypeScript, when used with a typesafe SQL query builder like Kysely, offers several advantages, particularly in the context of error handling and maintaining a robust codebase:

1. **Compile-time error checking:** TypeScript's static typing system allows for catching type-related errors at compile time. This means issues like passing a string where a number is expected in a query can be identified before the code is run.
2. **Code clarity and predictability:** The explicit type definitions in TypeScript make the code more readable and understandable. It's clearer what data type is expected and returned by each function or operation.
3. **Auto-completion and intelliSense:** Modern IDEs use TypeScript's type information to provide better auto-completion, which reduces the likelihood of typos and incorrect method calls that could lead to runtime errors.


## Handling errors in a type-safe environment
In a typesafe environment, errors are more predictable, and handling them becomes more straightforward. Here are some examples of how you can handle errors when using Kysely with TypeScript:

### **Catching syntax errors at compile time:**
With TypeScript, many syntax errors in your queries will be caught during compilation. For example, if you accidentally try to insert a string into a column that expects a number, TypeScript will flag this as an error.

```js
// Assuming 'id' is a number
await db.insertInto('contacts').values({ id: 'not-a-number', name: 'John Doe' }).execute();
// TypeScript will flag an error here
```

### **Handling runtime errors:**
Despite the best efforts at compile time, some errors can only be caught at runtime (like database connectivity issues or unique constraint violations). You can handle these gracefully in your code.

```js
try {
    await db.insertInto('contacts').values({ name: 'John Doe', email: 'john@example.com' }).execute();
} catch (error) {
    console.error("An error occurred:", error);
    // Additional error handling logic
}
```
### **Schema changes and refactoring:**
When you modify your database schema, TypeScript helps ensure changes are reflected throughout your code. If a column is removed or its type is changed, TypeScript will flag any incompatible queries.

```js
// If 'email' column is removed from the database schema
await db.updateTable('contacts').set({ email: 'new@email.com' }).where('id', '=', 1).execute();
// TypeScript will flag an error if the 'email' column no longer exists
```

## Conclusion
In this tutorial, you created a typesafe Contact Management System using Kysely and Neon Postgres. Throughout the tutorial, you learned how to design and implement a database schema in a typesafe manner using Kysely, which allowed you to define your contacts table directly in TypeScript.

The application's core functionality involved creating, reading, updating, and deleting contacts. You used Kysely's typesafe query builder to perform these tasks, which helped prevent many common errors associated with database interactions.

The Contact Management System built in this tutorial serves as a foundational application, but it has the potential to be expanded and scaled. For example, integrating it with frontend frameworks and optimizing performance as the application grows might involve query optimization, database indexing, or leveraging caching mechanisms.

## Resources:

- [kysely-neon](https://github.com/seveibar/kysely-neon/tree/main?utm_source=guides&utm_medium=content&utm_campaign=hackmamba)
- [kyesly examples](https://kysely.dev/docs/category/examples?utm_source=hackmamba&utm_medium=blog&utm_id=HMBcommunity)
- [Neon Docs](https://neon.tech/docs/introduction?ref=hm)