DEV Community: Emma Alder

These are the best large language models for coding

Emma Alder — Mon, 24 Feb 2025 10:49:01 +0000

As software developers, we dream of effortless coding, where we can transform complex problems into elegant and performant solutions. ⁤⁤⁤⁤However, software development is a complicated process, and writing multiple lines of error-free code is challenging, even for the most experienced developer. ⁤⁤Hence, pair programming, where two programmers work together simultaneously and provide feedback to each other, has been popular in software development.

⁤⁤Traditionally, one programmer writes the code while the other reviews each line, providing real-time feedback and suggestions. ⁤⁤With the rapid adoption of artificial intelligence (AI), pair programming with AI has enabled a single developer to write code quickly, enhancing their efficiency, improving code quality, facilitating rapid learning, and boosting overall productivity.

Today, developers can access an impressive range of tools built on top of large language models (LLMs) that go beyond basic code autocompletion and provide powerful AI-assisted coding experience. Even though OpenAI’s GPT-4o is leading most of the benchmarks for coding, Anthropic Claude and Google’s Gemini are not far behind. In this article, we’ll go through the best LLMs available for software development and use most of these LLMs interchangeably with Sourcegraph’s Cody.

Overview of popular AI Coding Assistants

Various coding tools with high-level languages are available to improve the coding experience. The integrated development environment (IDE) has streamlined the coding process by providing a comprehensive suite of tools, including a code editor, debugger, and compiler, all within a single interface. Developers can easily switch the environment according to their coding preferences for various programming languages.

The integration of artificial intelligence for assisted coding started with the basic autocompletion features that predict and complete code snippets based on context. Later, more advanced code completion tools, such as Microsoft IntelliSense, Kite, and Tabnine, were introduced with advanced machine learning models. Some of the most popular coding tools available today with advanced machine learning models are:

Sourcegraph Cody is a popular tool that uses multiple LLMs and advanced code search and analysis capabilities to enhance developers' understanding of code and generation.
GitHub Copilot was conceptualized from the success of OpenAI's GPT-3 as a code completion tool, with the first technical preview made available in June 2021 within the Visual Studio code development environment. Github Copilot is a proprietary tool that is available as a subscription-based service for developers. Since 2023, GitHub Copilot has used GPT models, such as GPT-4.
Tabnine is a proprietary tool that leverages AI to provide code completions for various programming languages, helping speed up the development process and increase productivity.

There are also a few open-source models, such as code llama. However, most of them use the same LLMs, such as OpenAI GPT-4, Anthropic's Claude, Mistral AI, Google Gemini, and others, for code and chat suggestions. Before diving into the most suitable LLMs for your development workflow, let's understand how these large transformer-based models work.

How LLMs work

A language model is usually a generative model used to understand and generate natural language in machine learning. Large language models have huge parameters (in millions, billions, or even trillions) and use deep learning architecture called transformers. These models are trained on very large datasets to predict the probability of a sequence of words paying attention to input sequences. Hence, these models use a self-attention mechanism that captures dependencies and relationships between the input sequence to process and generate a data sequence.

A wide variety of large language models and multimodel LLMs are available; some of the most popular are as follows:

OpenAI's GPT-4 is a 1 trillion parameter model, significantly improving GPT-3's (175 billion parameters). GPT-4 is still the leading model in many benchmarks. It provides a context window in two versions, i.e., 8,192 and 32,768 tokens.
Google's Gemini is the successor to LaMDA and PaLM2. Gemini 1.5 can handle up to 2 million tokens.
Anthropic Claude 2, Claude 3.5 sonnet, focuses on ethical considerations and safety.
Meta Llama 3 are open-source models available in 8B, 70B, and 405B versions.
Mistral AI Mixtral 8 X 7B and 8 X 22 B models and other variant models are also available for download for general and specialized purposes. Mixtral is also an open source LLMs.

All of these powerful models are trained on code-related data and can generate code and perform other coding tasks.

How code is generated with LLMs

A large language model uses natural language descriptions or prompts with enough context to perform a natural language processing task. Developers can fine-tune such models for coding-related tasks such as generating, debugging, etc. LLMs can demonstrate the ability to understand syntax, coding styles, and programming practices across multiple programming languages.

Given the previous tokens and prompts relevant to the tasks, the weights of these models are fine-tuned on these datasets to predict the next token in a sequence. This fine-tuning helps the transformer-based model generate coherent and context-relevant content.

Generating sound output from an LLM depends on the training data, the model's parameters, and good prompting. Vast and diverse code-related datasets from various sources, such as open-source repositories, documentation, online resources, coding books, tutorials, and coding forums, such as stack overflow, are used to train code generation.

Coding assistants like Cody use different models for various code-related tasks, such as real-time code generation, code completion, and code analysis, to help with debugging, refactoring, writing test cases, and code optimization tasks. They also offer chat interfaces to ask code-related questions, discuss and troubleshoot problems, and inspire developers with solutions.

Comparison of LLMs

Large language models and foundational models such as GPT-4, Gemini, Claude, and Mistral are versatile and unpredictable, which makes evaluating their performance complex and challenging. However, many organizations design specific benchmarks to provide a standardized, rigorous framework for comparing the capabilities of LLMs across core language-related tasks.

Most benchmarks consist of carefully designed tasks such as question answering, logical reasoning, numerical reasoning, code generation, and other natural language processing tasks. For example, Massive Multitask Language Understanding (MMLU) is a comprehensive benchmark designed to test an LLM's ability to understand and answer questions across various subjects. Similarly, Glue and SuperGlue are other popular benchmarks for testing general language understanding.

Benchmarks also require the selection of appropriate metrics for comparison; accuracy, BLEU score, and perplexity are the most common ones, while some other benchmarks also use human judgment for contrast.

Different challenges and benchmarks evaluate models for various purposes. Some benchmarks, such as ARC (AI2 Reasoning Challenge), evaluate LLMs’ ability to reason for complex, multi-step science problems. While benchmarks such as GSM8K (Grade School Math 8K) assess an LLM's math problem-solving skills. These benchmarks have enabled competition and the development of models with knowledge on par with school or college graduates.

Most benchmarks are focused on language and academic capability; however, there are a few benchmarks that evaluate programming-related tasks. The following image from the big code bench leaderboard evaluates LLMs with various practical and challenging programming tasks, including code generation and code completion based on natural language descriptions.

This leaderboard shows that the GPT-4 Turbo tops the chart, followed by the Claude 3.5 Sonnet and other models. Gemini 1.5, Mistral, and GPT-4o are also among the top 10. Diverse sets of benchmarks and leaderboards are available to test LLM coding ability comprehensively. Here are some selected coding benchmarks testing different LLMs:

EvalPlus leaderboard evaluates AI coders with rigorous tests. GPT4-Turbo also tops the chart here.
Chatbot arena leaderboard collected over 1,000,000 human pairwise comparisons to rank LLMs with the Bradley-Terry model and display the model ratings in Elo-scale. ChatGPT-4o ranks first, followed by Gemini-1.5 Pro, Claude 3.5 Sonnet, Meta Llama, and Mistral AI Large, among the top models.
Evo-Eval: Due to the popularity and age of other benchmarks, many are prone to data leakage, where example solutions for code can be readily found online. Evo-Eval transforms existing coding benchmarks (for example, HumanEval) into problems in domains such as difficulty, creativity, subtleness, etc. Here’s how the model ranks in the image below:
Spider and SQL-Eval are large-scale, complex, and cross-domain benchmarks for text-to-SQL tasks. Spider measures how well the generated SQL code or query retrieves the correct data from the database. SQL-Eval measures SQL code complexity and semantic diversity and ensures the generated queries are syntactically correct and meaningful. GPT-4 is used in most of the leading models in their leaderboards.

Strengths and weakness of LLM models

Each LLM has its strengths and weaknesses. Many benchmarks test the ability to generate code in different scenarios to understand the LLM's strengths and weaknesses. The following provides a detailed overview of the strengths and weaknesses of the most used LLMs:

Based on the above results, here is our ranking of these LLMS:

OpenAI’s GPT-4 and GPT-4o: As it performs the best across multiple benchmarks
Anthropic Claude 3.5 Sonnet: It is second to GPT-4 & GPT-4o in most of the benchmarks and also more accurate in different scenarios
Google’s Gemini 1.5: It is more factual and less imaginative than GPT-4, and it might fail in some scenarios requiring a more creative coding approach.
Mistral AI: Fast but has a comparably short context window

Over time as these models’ architecture improves and is trained on more data, this ranking is likely to change. Hence, we need a coding assistant that allows us to easily switch between these models. Sourcegraph’s AI assistant, Cody, can integrate with our favorite IDEs and allow us to change LLMs interchangeably.

Integrating LLMs with Cody

There are a large number and variations of LLMs, each with its own strengths and weaknesses. Cody, Sourcegraph’s AI assistant, can use different LLMs to help developers understand, write, and fix code more efficiently. Using multiple advanced LLMs allows Cody to provide fast single and multiple-line code completion. If you haven't used Cody yet, you can sign up for free to get started.

Installing Cody
Cody provides seamless integration with IDEs and code editors of your choice; it can be integrated into Microsoft Visual Studio Code, JetBrains IntelliJ IDEA, PhpStorm, PyCharm, WebStorm, RubyMine, GoLand, Google Android Studio, and NeoVim.
For Visual Studio Code IDE integration, you can get the Cody extension from the marketplace. Alternatively, you can open the page by clicking View > Extensions in VS code and searching for Cody AI.

When you install Cody in Microsoft Visual Studio for the first time, it is usually the last item in the sidebar. Check for the icon, as shown in the image below. You’ll need to sign in using the same login method and ID you used while creating your account.

Press the button again. You'll get a Cody chat sidebar, which you can use to chat with Cody to generate code, document your code, explain parts of code, write unit tests, find code smells, and more.

Switching LLMs within Cody
After installing Cody in VS Code, you can select between different LLMs through the drop-down menu below the Cody: Chat bar prompt box. Each model has its strengths and weaknesses, as previously discussed. However, Cody has grouped different models for accuracy, speed, or best of both.

Here’s the list of LLMs supported by Cody:

Anthropic Claude	Open AI GPT	Google Gemini	Mistral AI
Claude 3.5 Sonnet	GPT-4o	Gemini 1.5 Pro	Mixtral-8x22B
Claude 3 Sonnet	GPT-4 Turbo	Gemini 1.5 Flash	Mixtral 8x7B
Claude 3 Opus	GPT-3.5 Turbo
Claude 3 Haiku

The integration of these models provides several benefits to the programmers for AI-assisted software development:

Cody recommends that users select different options for better accuracy of code, faster generation speed, or balance. Thus, users can easily choose their preferences without researching each LLM.
LLMs enable Cody to provide more accurate and context-aware code completions and debugging.
LLMs empower Cody to understand code semantics better. As a result, it can explain complex code constructs, algorithms, and design patterns.

Using LLM models in Cody for code generation tasks

The following code example illustrates how Cody integrations allow experimenting with different LLMs to solve coding problems:

Generating algorithm to solve a Leet code problem:

Let’s experiment with the problem: Longest substring without repeating characters

Copy the description and put it in the chat or prompt in Cody using Claude 3.5 Sonnet. Cody will generate the solution that passes all the test cases. In this case, we are writing Python code. Here’s the code output:

The code passes all the test cases. Claude is quite detailed in its generation as it generates additional details and explanations of how the code works in the chat and the space and time complexity. Different LLMs will result in various output formats. Using the same prompt with Gemini 1.5 Pro generates the following code.

Using GPT-o, we get the following output with an explanation of the code and example usage.

The runtime for all the generations is in milliseconds, and none of the syntax errors in the generated code are present.

Generate code to train a deep-learning model
Training AI models is a common coding task these days. AI training AI models is another goal for automated coding systems. Using the following prompt, we can generate functioning Python code to train an object detection model.

Generate Python code to create and train a deep learning model for object detection using PyTorch. The code should include the following steps:

Import necessary libraries and modules.
Define the dataset and data loaders. Here, images are available in the dataset folder, while the annotations are provided in a json file, as mentioned in the Coco dataset website.
Use a pre-trained neural network model suitable for object detection.
Define the loss function and optimizer.
Implement the training loop with validation.
Save the trained model.

Ensure the code is well-commented and follows best practices for readability and efficiency.

Here's the code generated by Claude 3.5 Sonnet model; sections are capped to reduce the lengthy code.

import torch
import torchvision
...

# Step 1: Import necessary libraries and modules
import torch.optim as optim
from tqdm import tqdm

# Step 2: Define the dataset and data loaders
class CocoDataset(Dataset):
    def __init__(self, root_dir, annotation_file, transform=None):
        self.root_dir = root_dir
        self.coco = COCO(annotation_file)
        ...

# Create dataset and data loaders
train_dataset = CocoDataset(root_dir='dataset/train', annotation_file='annotations/instances_train2017.json')
val_dataset = CocoDataset(root_dir='dataset/val', annotation_file='annotations/instances_val2017.json')

train_loader = DataLoader(train_dataset, batch_size=2, shuffle=True, num_workers=4, collate_fn=lambda x: tuple(zip(*x)))
val_loader = DataLoader(val_dataset, batch_size=2, shuffle=False, num_workers=4, collate_fn=lambda x: tuple(zip(*x)))

# Step 3: Use a pretrained neural network model suitable for object detection
def get_model(num_classes):
    model = fasterrcnn_resnet50_fpn(pretrained=True)
    in_features = model.roi_heads.box_predictor.cls_score.in_features
    ...

# Step 4: Define the loss function and optimizer
params = [p for p in model.parameters() if p.requires_grad]
optimizer = optim.SGD(params, lr=0.005, momentum=0.9, weight_decay=0.0005)

# Step 5: Implement the training loop with validation
num_epochs = 10
for epoch in range(num_epochs):
    model.train()
    train_loss = 0
    for images, targets in tqdm(train_loader, desc=f"Epoch {epoch+1}/{num_epochs}"):
        ...

# Step 6: Save the trained model
torch.save(model.state_dict(), 'object_detection_model.pth')
print("Model saved successfully!")

The code works as expected, with slight adjustments to correct the format in the project directory and some minor error fixing. Again, the model generates additional explanations, such as before, explaining the code generation and the assumptions the LLM model has made. GPT4-o, Gemini, and Mixtral also give very similar results, requiring some fundamental changes to the code.

Wrapping up

Integrating large language models in IDEs has transformed software development by enhancing efficiency, improving code quality, and facilitating rapid learning and prototyping. These advanced tools use LLMs to provide real-time code completion, code generation, explanation, debugging, and understanding features.

Among all the popular models, GPT-4o leads many coding benchmarks, and Claude 3.5 Sonnet is among the top five models in these benchmarks. Claude 3.5 and Mixtral-8X7B are extremely fast, while GPT-4o and Gemini work well with multimodal inputs. Each model has its strengths and weaknesses, and different scenarios may require different models.

Cody allows you to find the best-fitting LLM for the coding scenario and your organization’s coding practices. Whether you are looking for speed, accuracy, or balance, Cody’s adaptable integrations have you covered. You can start experimenting today with multiple LLMs, from GPT-4 to Claude 3.5, directly available from Sourcegraph Cody, and select the best model for your coding problem.

Simplify Your Complex Code Migration Projects With AI

Emma Alder — Thu, 20 Feb 2025 16:18:46 +0000

Remember when Reddit faced the challenge of migrating 2,000 Git repositories? This wasn’t a routine transition. It required detailed planning to prevent downtime and operational disruption while addressing technical complexities like repository structure and scalability.

Like Reddit’s experience, code migration has become critical for organizations modernizing their systems to stay competitive. These migrations introduce significant operational and architectural challenges, particularly in distributed systems and legacy architectures. Businesses risk data compatibility issues, delays, and rising costs without careful management.

This guide explores how AI tools can simplify complex migrations while maintaining efficiency and business continuity. Let’s begin by examining the common obstacles organizations face during code migrations.

Key challenges in code migration

Compared to Reddit’s migration of 2,000 repositories, the stakes are even higher for organizations transitioning from monolithic architectures to microservices.

Here’s what makes large-scale migrations so challenging:

Keeping systems running smoothly
Maintaining uptime during a migration is no small feat. It often means juggling feature flags, parallel systems, and complex database transitions while avoiding disruptions to everyday operations.

Balancing data integrity and change
As data models evolve, preserving integrity across versions becomes a puzzle. Backward compatibility and rolling out new features demand sophisticated transformation layers and constant oversight.

Navigating business impacts
Poor planning can bring operations to a halt. When FactSet migrated from Perforce to GitHub, they had to anticipate potential compatibility issues and avoid bottlenecks in their development cycles.

Managing dependencies and integrations
Today’s applications are interconnected ecosystems. Version mismatches, deprecated APIs, third-party updates, and overlooked security patches can create ripple effects that disrupt entire systems.

Time, resources, and human effort
Traditional migrations often rely heavily on manual labor. This slows progress and increases the risk of errors, inflates costs, and delays the launch of new features.

These challenges underline why migrations are often seen as challenging. But with the right tools, they don’t have to be. Let’s examine how AI can simplify these complexities and deliver better outcomes.

How AI simplifies code migration

AI tools automate migration tasks, reduce errors, and accelerate delivery. Here’s how they help:

Understanding code in context
AI analyzes codebases to identify patterns and dependencies, making tasks like API migrations seamless. In the screenshot, the AI suggests precise updates with added features based on context.

Driving business impact
AI speeds up migrations by 60%, cuts bugs, and boosts productivity:

Faster migrations: FactSet reduced repository analysis times while moving to GitHub.
Fewer bugs: AI catches issues missed in manual reviews, reducing technical debt.
Higher productivity: Automating repetitive tasks improves PR merge times and developer satisfaction.

Refactoring at scale
AI applies consistent fixes across large codebases. For instance, Reddit used AI to detect anti-patterns and refactor the 2,000 repositories efficiently (see example in screenshot).

Improving transparency
AI maps dependencies, predicts change impacts and generates logs to simplify troubleshooting and handovers.

Learning and adapting
AI evolves with every migration, suggesting better patterns, flagging risks, and aligning with coding standards.

Next, we’ll explore specific migration types AI can handle.

Types of code migrations AI can handle

Some of the code migration tasks that AI can include:

Language version upgrades
Upgrading language versions, like Python 2 to Python 3 or Java 8 to Java 11, is a common migration scenario. AI excels at identifying and transforming syntax changes, deprecated features, and API modifications, just like this example:

Framework migrations
Whether transitioning from Angular.js to Angular or Spring to Micronaut, AI tools can understand framework-specific patterns and generate equivalent code. For example:

Database schema migrations
AI systems have become particularly adept at handling database migrations, including schema changes, ORM updates, and query optimizations. AI can analyze existing database structures and generate appropriate migration scripts while maintaining data integrity:

Architectural transformations
One of the most complex migrations involves architectural changes, such as moving from monolithic to microservices architecture. AI tools can analyze dependencies, suggest service boundaries, and help maintain system functionality during the transition:

The next section will show you steps to automate your migration process. Let us get into it.

Automating code migration: A step-by-step AI guide

To follow this guide, we’ll use Sourcegraph’s code intelligence AI, which includes prompts and custom commands. These features allow you to:

Work with context-aware prompts tailored to your codebase.
Apply best practices across multiple programming languages.
Choose from different generative models for flexibility and precision.

Before starting, make sure Sourcegraph is set up in your code editor. For installation instructions, refer to the official documentation.

With these tools in place, let’s walk-through how to automate code migrations efficiently and reduce risks.

Phase 1: Initial assessment and planning
Sourcegraph's AI analyzes repository structures, dependencies, and code patterns to build a comprehensive migration strategy. For example, it can scan your codebase and automatically identify:

Deprecated API usage patterns
Legacy framework dependencies
Outdated language features
Complex inheritance hierarchies
Technical debt hotspots

You can configure it to identify specific patterns relevant to your migration using custom commands or prompts. The example below shows how I prompted Sourcegraph AI with context to my codebase to draft out my migration strategy:

Phase 2: Defining migration rules
Sourcegraph's AI intelligent prompting system allows you to define clear migration rules through natural language. For instance, we will ask the AI to provide rules for its migration strategy so that workflows will not be broken, downtimes will be curbed, and the transition will be easy.

The results in the image below are migration rules to follow while implementing the migration strategies that the AI tool generated earlier.

Phase 3: Staged migration execution
The migration process is executed in carefully planned stages, with the AI providing:

Context-aware transformations: Sourcegraph’s AI understands the full context of your code, ensuring changes maintain business logic and system integrity. When transforming code, it considers:
- Surrounding code context
- Import dependencies
- Function relationships
- Error handling patterns
- State management
Intelligent code smell detection: Throughout the migration, the AI identifies potential issues like:
- Redundant code patterns
- Performance bottlenecks
- Security vulnerabilities
- Inconsistent error handling
- Resource leaks

At this stage, we can create personalized prompts or custom commands for the features or changes we want to make in the codebase. We can also use the chat feature, pass the file(s) in question as context, and add our prompt so Sourcegraph’s AI will give us a new version of that piece or group of code. Here is an example to fix the error handling issue the AI detected as an anti-pattern in our code:

Phase 4: Validation and testing
Sourcegraph's AI advanced validation capabilities ensure the migration's success through automated tests. In this phase, we can create a prompt/custom command or use the chat interface to generate unit tests for any module, function, or service in our new migration code. For the new error-handling service in the migration, here is a sample prompt to write tests for it using AI:

Best practices for secure code migrations

A robust security framework must be implemented throughout migration to protect sensitive data, maintain compliance, and ensure business continuity. Here are some security and compliance best practices for code migration:

Conduct extensive testing before migration, including performance stress testing to validate system behavior under load and staged rollouts to minimize risk. Ensure a comprehensive strategy with clear rollback procedures for immediate execution if needed.
Outline scope, timeline, and impacts to identify potential business disruptions and mitigation strategies. Track changes via version control with audit trails and use migration runbooks for consistent, error-free execution.
Configure firewalls to secure source and target environments. Maintain environment isolation by separating development, staging, and production with proper access controls.
Define emergency response procedures for handling issues during migration. Establish communication protocols to keep stakeholders informed and escalation paths to engage relevant teams quickly.

While we look at the general best practices, we should also consider the security implications of the AI tool we use in the migration process. Some of the essential security measures used by Sourcegraph to protect your code are:

Data Protection and Encryption: Sourcegraph Cody Gateway avoids storing sensitive data and retains only diagnostic data for rate-limiting and error tracking, configurable in account settings.
Compliance Monitoring: Guardrails enforce coding standards and prevent AI from replicating copyrighted code using verification mechanisms.
Self-Hosting: Enterprises can self-host Sourcegraph AI for complete control, following this setup guidance.

Final thoughts

As organizations grow their systems, complex code migrations pose significant challenges, from maintaining system continuity to managing data integrity and operational impacts. Without careful oversight, businesses risk disruptions and increased costs during these transitions.

AI tools, such as Sourcegraph, can simplify the migration process by automating error-prone tasks and enhancing code quality, thereby reducing completion times by up to 60% and improving developer productivity.

Check out Sourcegraph today to begin your journey toward faster, more secure, and more efficient code migrations.

Typesafe PostgreSQL queries with Kysely and Neon

Emma Alder — Sat, 16 Mar 2024 23:05:40 +0000

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:

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