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 and Neon PostgreSQL. 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 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 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: safeql is a library that validates and auto-generates TypeScript types from raw SQL queries.
• ts-query-builder: 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 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 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 features excellent examples of how to use it and guides on how to get started.

What is Neon?

Neon 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 documentation to get up and running in just a few minutes.

GitHub link to view source code

Check out the complete source code here.

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:

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

Setting up Neon PostgreSQL

Sign up for Neon’s free tier 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

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:

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:

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:

npm install kysely-neon

To connect to the Neon instance, you need a WebSocket. Install the ws module using the following command:

npm install ws

Perfect — now let’s create our project directory with the following commands:

mkdir contact-management-system

Now, navigate inside this directory with the command:

cd contact-management-system

Next, run this command to create a TypeScript project:

npx tsc --init

That will leave you with a folder structure like this:

├── 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:

 mkdir src

  cd src

 touch index.ts

Finally, you should have this folder structure as shown below:

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.

//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:

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.

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.

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.

// 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.

// 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.

// 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:

npx ts-node src/index.ts

If the code runs without throwing any errors, it means the database transaction has been successfully completed.

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

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.

// 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.

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.

// 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
• kyesly examples
• Neon Docs