Bring Your Own SDK with Turso's HTTP API

Bring Your Own SDK with Turso's HTTP API

Execute SQL over HTTP with Turso.

Turso is a distributed database built on libSQL, a fork of SQLite, designed for lightning-fast query responses in global applications.

Turso not only delivers exceptional performance but also offers flexibility for developers working with TypeScript, Go, Rust and Python.

However, if you're working with a framework or language that isn't officially supported, fear not! Turso provides an HTTP API for your database.

The HTTP API handles single and batching queries to the database, executing all statements within a batch as a single transaction. This ensures data integrity, as any failure within the batch triggers an error and aborts the transaction, leaving the database unchanged.

It's worth noting that the HTTP API follows a stateless approach. While this provides simplicity and reliability, it does come with a trade-off. Interactive transactions, involving statements like BEGIN, END, ROLLBACK, are off-limits in this stateless environment, as they conflict with the batch transaction model.

In a recent post, James showed how you can create a fast, performant admin UI with Retool and Turso using the same API. In this post, we’ll learn how easy it is to execute SQL via HTTP, without using one of the official or experimental libSQL SDKs.

1. Retrieve Database HTTP URL

First, locate your database URL using the Turso CLI (or Platform API):

turso db show [databaseName] --url

Next, replace the URL protocol libsql:// with https:// so it looks something like:

https://[databaseName]-[organizationName].turso.io

2. Create an Auth Token

You’ll need to create an auth token that can be used to connect to your database. You can use the Turso CLI (or Platform API):

turso db tokens create [databaseName]

You can also create a token that works for all databases in a group if needed:

turso db tokens create [databaseName] --group

3. Execute SQL over HTTP

We’re now ready to send a POST request to the HTTP URL we created above. We’ll be sending the request body as JSON in the following format:

{
  "statements": [
    "SELECT * FROM todos"
  ]
}

Using JavaScript with fetch, it looks something like:

const url = "https://[databaseName]-[organizationName].turso.io";
const authToken = "...";

fetch(url, {
  method: "POST",
  headers: {
    Authorization: `Bearer ${authToken}`,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({
    statements: ["SELECT * FROM todos"],
  }),
})
  .then((res) => res.json())
  .then((data) => console.log(data))
  .catch((err) => console.log(err));

If you’re using something like Ruby, it works in the same way using net/http:

require 'net/http'
require 'json'
require 'uri'

url = "https://[databaseName]-[organizationName].turso.io"
auth_token = "..."

uri = URI(url)
request = Net::HTTP::Post.new(uri)
request["Authorization"] = "Bearer #{auth_token}"
request["Content-Type"] = "application/json"
request.body = JSON.generate({ statements: ["SELECT * FROM todos"] })

begin
  response = Net::HTTP.start(uri.hostname, uri.port, use_ssl: uri.scheme == 'https') do |http|
    http.request(request)
  end

  puts JSON.parse(response.body)
rescue => e
  puts e.message
end

Make sure to update the statement so it uses SQL relating to your own database.


Using SQL arguments with statements

When you progress past the fundamentals of SELECT *, it's beneficial to use arguments in your queries. Turso is compatible with both named and positional arguments, similar to what you might be accustomed to in the official SDKs.

For straightforward queries, positional arguments can be utilized in the request body as follows:

{
  "statements": [
    {
      "q": "INSERT INTO todos (text, completed) VALUES (?, ?)",
      "params": ["Add a section for using positional args", 1]
    }
  ]
}

When you begin to execute more complex queries that involve filtering and limiting data with a variety of arguments, placeholders can be utilized within the q string.

{
  "statements": [
    {
      "q": "INSERT INTO todos (text, completed) VALUES (:text, :completed)",
      "params": {
        ":text": "Add a section for using parameters",
        ":completed": 1
      }
    }
  ]
}

Bring Your Own SDK

When developing your application, it's a good practice to encapsulate specific functionalities into separate packages or modules. For instance, in the context of a Ruby application, you could create a Rubygem:

require "uri"
require "json"
require "net/http"

class Turso
  def initialize(base_url, token)
    @uri = URI(base_url)
    @token = token
  end

  def execute(statements)
    statements = prepare_statements(statements)

    https = Net::HTTP.new(@uri.host, @uri.port)
    https.use_ssl = true

    request = Net::HTTP::Post.new(@uri)
    request["Authorization"] = "Bearer #{@token}"
    request["Content-Type"] = "application/json"
    request.body = JSON.dump({ "statements": statements })

    begin
      response = https.request(request)
      handle_response(response)
    rescue StandardError => e
      puts "Error: #{e.message}"
    end
  end

  private

  def prepare_statements(statements)
    statements = [statements] if statements.is_a?(String)

    statements.map do |statement|
      statement.is_a?(String) ? { q: statement } : statement
    end
  end

  def handle_response(response)
    case response
    when Net::HTTPSuccess
      JSON.parse(response.body)
    else
      { "error" => "Something went wrong: #{response.code}" }
    end
  end
end

This approach allows you to set up the database client and credentials just once, and then reuse this configuration across your entire application. This not only simplifies your code but also enhances maintainability and scalability.

Going beyond HTTP

If you need to use interactive transactions, Turso provides a more complex remote protocol that the official SDKs implement, learn more.

Join us on Discord

Regardless of whether you choose to utilize the HTTP API or use one of the officially supported SDKs, we're eager to hear about the various frameworks and programming languages you're integrating with Turso. Join us on Discord and share your experiences.