@planetscale/database

What is @planetscale/database?

@planetscale/database is an npm package designed to facilitate interaction with PlanetScale databases. It provides a simple and efficient way to connect to, query, and manage databases hosted on PlanetScale, which is a serverless MySQL platform.

What are @planetscale/database's main functionalities?

Connecting to a Database

This feature allows you to establish a connection to a PlanetScale database using the provided credentials. The `connect` function is used to create a connection object that can be used for executing queries.

const { connect } = require('@planetscale/database');

const connection = connect({
  host: 'your-database-host',
  username: 'your-username',
  password: 'your-password'
});

Executing Queries

This feature demonstrates how to execute SQL queries against a PlanetScale database. The `execute` method is used to run a query and retrieve results, which can then be processed or logged.

const { connect } = require('@planetscale/database');

const connection = connect({
  host: 'your-database-host',
  username: 'your-username',
  password: 'your-password'
});

async function fetchData() {
  const results = await connection.execute('SELECT * FROM your_table');
  console.log(results);
}

fetchData();

Handling Transactions

This feature illustrates how to handle transactions using the @planetscale/database package. Transactions allow you to execute a series of operations atomically, ensuring that either all operations succeed or none are applied.

const { connect } = require('@planetscale/database');

const connection = connect({
  host: 'your-database-host',
  username: 'your-username',
  password: 'your-password'
});

async function performTransaction() {
  const transaction = await connection.transaction();
  try {
    await transaction.execute('INSERT INTO your_table (column) VALUES (value)');
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    console.error('Transaction failed:', error);
  }
}

performTransaction();

Other packages similar to @planetscale/database

mysql2

mysql2 is a popular npm package for connecting to MySQL databases. It offers a similar feature set to @planetscale/database, including support for prepared statements and transactions. However, mysql2 is more general-purpose and not specifically optimized for PlanetScale's serverless architecture.

knex

Knex.js is a SQL query builder for Node.js that supports multiple database types, including MySQL. It provides a more abstracted way to build queries compared to @planetscale/database, which might be beneficial for complex query building but less direct for simple operations.

sequelize

Sequelize is a promise-based Node.js ORM for various SQL databases, including MySQL. It offers a higher-level abstraction compared to @planetscale/database, providing features like model definitions and associations, which can simplify database interactions at the cost of some flexibility.

README

PlanetScale serverless driver for JavaScript

A Fetch API-compatible PlanetScale database driver for serverless and edge compute platforms that require HTTP external connections, such as Cloudflare Workers or Vercel Edge Functions

Installation

npm install @planetscale/database

Usage

import { connect } from '@planetscale/database'

const config = {
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)
const results = await conn.execute('select 1 from dual where 1=?', [1])
console.log(results)

Database URL

A single database URL value can be used to configure the host, username, and password values.

import { connect } from '@planetscale/database'

const config = {
  url: process.env['DATABASE_URL'] || 'mysql://user:pass@host'
}

const conn = connect(config)

Connection factory

Use the Client connection factory class to create fresh connections for each transaction or web request handler.

import { Client } from '@planetscale/database'

const client = new Client({
  host: '<host>',
  username: '<user>',
  password: '<password>'
})

const conn = client.connection()
const results = await conn.execute('select 1 from dual')
console.log(results)

Transactions

Use the transaction function to safely perform database transactions. If any unhandled errors are thrown during execution of the transaction, the transaction will be rolled back.

The following example is based on the Slotted Counter Pattern.

import { connect } from '@planetscale/database'

const config = {
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)
const results = await conn.transaction(async (tx) => {
  const whenBranch = await tx.execute('INSERT INTO branches (database_id, name) VALUES (?, ?)', [42, "planetscale"])
  const whenCounter = await tx.execute('INSERT INTO slotted_counters(record_type, record_id, slot, count) VALUES (?, ?, RAND() * 100, 1) ON DUPLICATE KEY UPDATE count = count + 1', ['branch_count', 42])
  return [whenBranch, whenCounter]
})
console.log(results)

Usage with PlanetScale Boost

To enable PlanetScale Boost, run SET @@boost_cached_queries = true once. All subsequent queries run on the same connection will use boost if it's enabled for the query pattern. Non-matching queries will run as normal.

To learn more, visit: Query caching with PlanetScale Boost

const conn = client.connection()
// Enable boost for the connection
await conn.execute('SET @@boost_cached_queries = true')

const results = await conn.execute('...')

// Optionally, you may disable boost for the connection by setting to false
await conn.execute('SET @@boost_cached_queries = false')

Custom fetch function

Node.js version 18 includes a built-in global fetch function. When using an older version of Node.js, you can provide a custom fetch function implementation. We recommend the undici package on which Node's built-in fetch is based.

import { connect } from '@planetscale/database'
import { fetch } from 'undici'

const config = {
  fetch,
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)
const results = await conn.execute('select 1 from dual')
console.log(results)

To leverage HTTP/2, you can use the fetch-h2 shim. fetch-h2 also supports Node.js 12+.

import { connect } from '@planetscale/database'
import { context } from 'fetch-h2'
const { fetch, disconnectAll } = context()

const config = {
  fetch,
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)
const results = await conn.execute('select 1 from dual')
console.log(results)
await disconnectAll()

Custom query parameter format function

Query replacement parameters identified with ? are replaced with escaped values. Named replacement parameters are supported with a colon prefix.

const results1 = await conn.execute('select 1 from dual where 1=?', [42])
const results2 = await conn.execute('select 1 from dual where 1=:id', { id: 42 })

Providing a custom format function overrides the built-in escaping with an external library, like sqlstring.

import { connect } from '@planetscale/database'
import SqlString from 'sqlstring'

const config = {
  format: SqlString.format,
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)
const results = await conn.execute('select 1 from dual where 1=?', [42])
console.log(results)

Custom type casting function

Column values are converted to their corresponding JavaScript data types. This can be customized by providing a cast function.

import { connect, cast } from '@planetscale/database'

function inflate(field, value) {
  if (field.type === 'INT64' || field.type === 'UINT64') {
    return BigInt(value)
  }
  return cast(field, value)
}

const config = {
  cast: inflate,
  host: '<host>',
  username: '<user>',
  password: '<password>'
}

const conn = connect(config)

You can also pass a custom cast function to execute. If present, this will override the cast function set by the connection:

const result = await conn.execute(
  'SELECT userId, SUM(balance) AS balance FROM UserBalanceItem GROUP BY userId',
  {},
  {
    cast: (field, value) => {
      if (field.name === 'balance') {
        return BigInt(value)
      }
      return cast(field, value)
    }
  }
)

Row return values

Rows can be returned as an object or an array of column values by passing an as option to execute.

const query = 'select 1 as one, 2 as two where 1=?'
const objects = conn.execute(query, [1], { as: 'object' })
// objects.rows => [{one: '1', two: '2'}]

const arrays = conn.execute(query, [1], { as: 'array' })
// arrays.rows => [['1', '2']]

Development

npm install
npm test

Need help?

Get help from the PlanetScale support team, or join our community on Discord or GitHub discussion board to see how others are using PlanetScale.

License

Distributed under the Apache 2.0 license. See LICENSE for details.