Connect D1 Database Remotely from Local Svelte Development

Introduction

Cloudflare Pages is a great platform for hosting static sites. With Functions support, it is possible to run serverless functions on Cloudflare Pages, and deploy full-stack website. Svelte is natively supported by Cloudflare Pages Functions. You can achieve Server Side Rendering (SSR) with SvelteKit and Cloudflare Pages. Regarding the database, it seamlessly binds to Cloudflare D1, a serverless SQL database. However, the D1 database is not directly accessible from local development environment. This article will guide you through the steps to connect to D1 database remotely from local Svelte development environment.

Background

As a developer, we often need to access the database from local development environment. It is convenient to test the database connection, and run SQL queries from local development environment. Most of the Cloud Database support this function, e.g. Firebase and AWS RDS. However, the D1 database is not directly accessible from local development environment. This has been confirmed from their offical website. This is because the D1 database is a serverless database, and it is not exposed to the public internet. The D1 database is only accessible from the Cloudflare Workers, and Cloudflare Pages. Therefore, we need to find a way to connect to the D1 database remotely from local development environment.

In the D1 Cloudflare documentation, it has a section to teach you how to query the D1 database from the SvelteKit. However, it only works for the local D1 database and does not synchronize with the remote Cloudflare D1 database. If you create a table and insert data to online D1 database through the web interface, you will not be able to query the data from local development environment. This is because the local D1 database is not synchronized with the remote Cloudflare D1 database.

Solution

There is no direct way to connect to the D1 database remotely from local development environment. However, we can use a workaround to achieve this. The big idea is to execute the same SQL queries on both local D1 and remote D1 database. This way, our local development environment would have the same data as the remote D1 database.

To achieve this, we need wrangle installed on our local development environment. Then use wrangle cli to login to your Cloudflare account. After that, we can use wrangle to directly execute the SQL queries on the local and remote D1 database. This way, we can synchronize the data between local and remote D1 database.

Login to Cloudflare Account

To login to Cloudflare account, run the following command in your terminal:

npx wrangler login

Create a Table in D1 Database

After login, you can use wrangle to execute the SQL queries on the D1 database. For example, to create a table in an existing D1 database called "my_db", you can run the following command:

npx wrangler d1 execute my_db --command "CREATE TABLE IF NOT EXISTS my_table (id INT PRIMARY KEY, name TEXT)"

and run the same command with --local flag to create the table in the local D1 database:

npx wrangler d1 execute my_db --command "CREATE TABLE IF NOT EXISTS my_table (id INT PRIMARY KEY, name TEXT)" --local

Insert Data to D1 Database

To insert data to the remote D1 database, you can run the following command:

npx wrangler d1 execute my_db --command "INSERT INTO my_table (id, name) VALUES (1, "John")"

and run the same command with --local flag to insert the data to the local D1 database:

npx wrangler d1 execute my_db --command "INSERT INTO my_table (id, name) VALUES (1, "John")" --local

Execute Query using a .sql File

You can also execute a .sql file on the D1 database. For example, to execute a .sql file called "query.sql" on the remote D1 database, you can run the following command:

npx wrangler d1 execute my_db --file query.sql

and run the same command with --local flag to execute the .sql file on the local D1 database:

npx wrangler d1 execute my_db --file query.sql --local

Test on SvelteKit

After the data is synchronized between local and remote D1 database, you could test the database connection on SvelteKit. Use the same code from the official documentation to query the D1 database from SvelteKit. The data should be consistent between local and remote D1 database.

Conclusion

In this article, we have discussed the steps to connect to the D1 database remotely from local Svelte development environment. We have learned that the D1 database is not directly accessible from local development environment. However, we can use wrangle to execute the same SQL queries on both local and remote D1 database. This way, we can synchronize the data between local and remote D1 database. This allows us to test the database connection on SvelteKit, and develop the website with the real data from the remote D1 database.