May 2023 Summaries

You can build a serverless API using Cloudflare Workers, Drizzle ORM, and Neon by following this guide. You'll start by setting up a new project with create-cloudflare-cli, which will generate the initial code for your worker. Next, you'll add Hono.js to handle different HTTP methods, and then set up a Neon Postgres instance that can automatically scale based on demand. Drizzle ORM is used to define the database schema in TypeScript, and you'll generate database migrations using the `drizzle-orm` package. You'll also connect to the database from your worker and deploy it using wrangler. Finally, you'll use the Neon integration on Cloudflare to simplify credential management and redeploy your worker automatically.

The author, working with the serverless driver for PostgreSQL, encountered a problem with WebSockets support in Node 18 and above using undici, which was causing queries with an ORDER BY clause to hang or fail. The issue was resolved by adding byteLength and byteOffset arguments to the new DataView() call in the undici code, which fixed a bug related to how buffers are handled in Node's Buffer class. This fix worked because the buffer allocated for a WebSocket frame is often stored in a global pool of small buffers, which can lead to issues with writing data to the wrong location if not done correctly. The author also discovered that using writeUInt16BE() instead of creating a DataView() was faster and more readable than the original approach used by undici.

The guide outlines how to construct a serverless API using Cloudflare Workers, Hono, Drizzle ORM, and Neon, providing a comprehensive walkthrough from setup to deployment. Cloudflare Workers allow for serverless code deployment without infrastructure management, while Neon offers a scalable, serverless Postgres database with separate storage and compute layers. Drizzle ORM serves as a lightweight TypeScript ORM compatible with Cloudflare Workers, facilitating SQL migrations via its CLI tool. The guide assumes basic JavaScript or TypeScript knowledge and requires accounts on Cloudflare and Neon. Key steps include setting up a project with create-cloudflare-cli, integrating Hono for API endpoints, defining a database schema with Drizzle ORM, generating and applying database migrations, and deploying the API while managing credentials through Neon integration in Cloudflare. The process is designed to help users understand and implement a serverless architecture efficiently, with practical examples and code snippets.

In a detailed debugging narrative, a developer investigates a persistent issue where a SQL query utilizing WebSockets consistently fails when executed with an ORDER BY clause in Node 18 and above, but not in Node 17 and below. The problem is attributed to the WebSocket support in the experimental undici package, which mismanaged payload length encoding for larger queries, leading to inconsistent and erroneous behavior. By examining network packets using Wireshark and understanding the WebSocket framing protocol, the developer identified that the undici package incorrectly set payload lengths beyond a certain threshold, causing the connection to hang or terminate unexpectedly. The resolution involved modifying the undici code to correctly handle the buffer allocation and payload length encoding, which was successfully merged and released in an updated version of the package. This investigation highlights the importance of understanding low-level protocol details and the potential pitfalls of optimization techniques that compromise reliability.

The text discusses automating the creation of Neon branches using a Git post-checkout hook. Neon is a development database that allows developers to create isolated copies of their database for experimenting with new features without affecting the main production environment. A Githook script can be used to automate this process, saving time and effort in managing local Postgres instances and seed scripts. The script must first check if the Git branch being checked out is a new one, then create a Neon branch using the Neon API, prompt for user confirmation, handle the API response, and ensure that the branch does not already exist. The final code implements these steps, providing a robust solution for automating Neon branch creation in local development environments.

The blog post outlines the process of creating a Githook script that automates the creation of Neon database branches whenever a new Git branch is established, offering a streamlined local development experience. By leveraging the Neon API, developers can manage their database projects and branches efficiently, mitigating the need for cumbersome local Postgres setups and seed scripts. The guide begins with a basic Githook example and gradually adds complexity, including user interactions, API response handling, and ensuring the script only executes on newly created Git branches. The post emphasizes the benefits of using Neon branches to maintain database consistency and highlights upcoming developments in Neon’s CLI tools that will further simplify project management.

Neon, a serverless Postgres system, needs to solve the problem of searching for WAL (Write-Ahead Log) records in its storage system. The read path involves reconstructing requested pages by applying WAL records to recent page images. To speed up this process, Neon introduces a new data structure that uses persistent data structures (RPDS) from the Rust crate to achieve a 70x to 2000x speedup compared to previous non-trivial data structures. The RPDS data structure is based on binary search trees and allows for efficient querying of past states by traversing layers, which are essentially snapshots of the data at different points in time. This approach enables Neon to efficiently answer historical queries with a query runtime of O(log N), making it suitable for large-scale databases.

The developers at Neon have created an online AI SQL playground for Postgres where users can connect to a Neon Postgres database and use natural language to generate SQL queries with the help of OpenAI's gpt-3.5-turbo model. The playground is live and available for testing, allowing users to explore their database schema, ask questions about it, and even get AI-assisted modifications. To establish a connection, users paste their database connection string and can use the "Ask AI" button or a shortcut to submit prompts. The playground uses the Neon serverless driver to connect directly to Postgres from the browser, and is open-source with plans for an HTTP-based driver in development. The AI model generates SQL queries by processing user prompts and database schema, providing a better user experience through streaming responses. The project showcases the potential of combining AI and SQL query generation, offering a unique tool for developers to experiment and learn.

Neon has developed a web-based AI SQL playground for Postgres that allows users to connect to a Neon Postgres database and employ an AI assistant to generate SQL queries using natural language. This project emerged from an internal AI hackathon and utilizes OpenAI’s gpt-3.5-turbo model to generate SQL queries efficiently. The playground, which is now live, can connect to other Postgres databases via a WebSocket-to-TCP proxy using the Neon serverless driver, thus overcoming the limitations of browsers not supporting TCP connections. The platform is open source, allowing users to report bugs or request features, and it supports a user-friendly interface where users can manage database connections, view schemas, and interact with the AI for query generation. The Neon team is also working on an HTTP-based driver to further enhance connectivity options, and they encourage feedback from users to refine the platform.

Neon, a serverless Postgres system, has introduced a new persistent data structure that significantly enhances the speed of their Write-Ahead Log (WAL) indexing, achieving a 70 to 2000x speedup compared to previous methods. The system separates storage from compute and offers features like usage-based billing, autoscaling, and database branching. The challenge addressed is efficiently finding the WAL for a given data point, a problem previously tackled with various data structures, including R-Trees and segment trees, which proved inadequate. The innovative solution utilizes Rust Persistent Data Structures (RPDS), which allow querying past states efficiently by making tree-based data structures immutable through a copy-on-write method. This approach not only accelerates the search process but also keeps the complexity within manageable bounds, highlighting the potential of persistent data structures in high-performance computing scenarios.

This article compares four popular SQL libraries for TypeScript - Drizzle, Kysely, Prisma, and Zapatos. These libraries fall under two categories: Object-Relation Mappers (ORMs) and Query Builders. The comparison is based on factors such as learning curve, support for complex queries, SQL migrations, supported runtimes, and community engagement. Drizzle uses a SQL-like syntax for running queries and focuses on a function-based approach. Kysely and Zapatos use a Typescript-centric syntax focusing on type safety and interfaces. Prisma supports databases other than relational ones and favors a human-readable syntax that works with all types of databases. All four libraries support raw SQL queries using template tags, which make your queries SQL injection-proof. Drizzle and Prisma provide a similar approach to handling migrations using their respective CLIs. Kysely offers flexibility and strong typing but lacks a built-in CLI and requires developers to handle Typescript compilation. Drizzle, Kysely, and Zapatos work in edge-runtimes and Postgres using the @neondatabase/serverless driver or the @vercel/kysely driver, allowing developers to deploy their applications on edge networks for improved performance. Prisma does not support edge-runtimes with Postgres yet. In terms of community engagement, Prisma is the most popular with the highest weekly downloads and a robust community on Stack Overflow. Kysely and Zapatos' more direct representation of table schema, Typescript-centric syntax, and edge-runtime compatibility make it an attractive option for developers looking for a modern and efficient solution. Ultimately, the choice between these libraries will depend on factors such as project requirements, complexity of the schema, preference for a more flexible or automated approach, and community engagement.

Typed SQL libraries for Typescript provide an interface between code and databases, offering developer-friendly, maintainable solutions, yet choosing the right one can be challenging due to their similar functionalities. The blog post focuses on comparing four popular Typescript-based libraries for Postgres databases: Drizzle, Kysely, Prisma, and Zapatos, which fall into two categories: Object-Relation Mappers (ORMs) and Query Builders. ORMs simplify synchronization between objects and database tables but may struggle with complex queries, while Query Builders allow for more SQL-like query writing with Typescript type safety. The post evaluates these libraries based on learning curve, support for complex queries, SQL migrations, supported runtimes, and community engagement. Drizzle, Kysely, and Zapatos work well with edge runtimes, whereas Prisma does not. Prisma, however, has the largest community presence and support on forums like Stack Overflow, making it a popular choice despite its edge-runtime limitations. Each library offers unique benefits, such as Drizzle's intuitive SQL-like syntax and automated migration tool, Kysely's Typescript-centric syntax and flexibility, and Prisma's human-readable syntax and strong community support, making the choice dependent on project needs and personal preferences.

The @vercel/postgres package utilizes a custom tag function to define an SQL query that is not vulnerable to SQL injection attacks because it uses parameterized queries and sanitizes user input, making it safe to use and following best practices in database security.

SQL template tags are not vulnerable to SQL injection attacks due to their use of tag functions, which process strings safely by separating SQL query strings from their parameters. SQL injection, a common security vulnerability, occurs when an attacker manipulates database queries to gain unauthorized access to data. The @neondatabase/serverless driver enables secure database connections in serverless and edge environments by utilizing a syntax that appears similar to template literals but incorporates a tag function to handle the string and parameters separately. This approach, also used by several Postgres clients and query builders, ensures that user-provided input is properly sanitized and validated, preventing SQL injection attacks. The underlying mechanism involves using a custom tag function to return both the query and parameters, which are then passed to a query function, adhering to the parameterized query method for enhanced security.

Vercel and Neon have partnered to introduce Vercel Postgres, the first serverless Postgres database built for the frontend, providing developers with high-performing web applications without upfront costs. The integration allows developers to seamlessly deploy Neon's Serverless Postgres into their Vercel applications, empowering them to focus on innovation and speed. With this partnership, Vercel aims to bring databases to the edge and make the Web faster and more personalized for every visitor globally.

Neon, a leading provider of serverless Postgres in the cloud, has partnered with Vercel, a frontend cloud provider, to launch Vercel Postgres, the first serverless Postgres database tailored for the frontend. This collaboration enables developers to deploy high-performance, low-latency web applications with integrated serverless Postgres capabilities, allowing seamless integration of Neon's database into Vercel applications. With scale-to-zero pricing, developers can focus on innovation without upfront costs. Vercel's CEO, Guillermo Rauch, emphasizes the goal of enhancing web speed and personalization globally by bringing databases to the edge. The integration supports Next.js App Router and Server Components, facilitating dynamic content rendering on the server. This partnership aims to offer developers flexible, scalable, and cost-effective solutions for building modern web applications.