Introducing Ampt SQL: Relational data for the cloud

At Ampt we continue to push the boundaries of what's possible with Infrastructure from Code. Today, we're excited to announce the introduction of Ampt SQL, a serverless relational database service powered by Neon. Ampt SQL lets you store and query relational data using familiar Postgres SQL. It's a simple and powerful way to add a relational database to your Ampt apps without worrying about complicated configurations or managing infrastructure.

Using Ampt SQL

Using Ampt SQL is easy. Install @ampt/sql and import it into your application. Then, use the sql tag to execute SQL queries.

Here's an API to create and query simple blog posts:

javascriptCopy code
import { api } from '@ampt/api';
import { sql } from '@ampt/sql';

const router = api().router('/api');

router.post('/posts', async (event) => {
  const { author, content } = await event.request.json();
  const post =
    await sql`INSERT INTO posts(content) VALUES(${content}) RETURNING *`;
  return event.body({ post });
});

router.get('/posts', async (event) => {
  const posts = await sql`SELECT * FROM posts`;
  return event.body({ posts });
});

The sql tag returns a promise that resolves to the result of the query, and supports parameterized queries so you don't have to worry about SQL injection attacks.

Using Kysely

Raw SQL queries are powerful, and some devs prefer to just write SQL and not introduce another layer of abstraction. But if you prefer a type-safe fluid API, @ampt/sql provides a Kysely API as well.

Here's the same blog post API using Kysely style queries:

typescriptCopy code
import { api } from '@ampt/api';
import { Kysely } from '@ampt/sql';
import { GeneratedAlways } from 'kysely';

interface Database {
  posts: PostsTable;
}

interface PostsTable {
  id: GeneratedAlways<number>;
  content: string;
  author: string;
}

const db = new Kysely<Database>();

const router = api().router('/api');

router.post('/posts', async (event) => {
  const { author, content } = await event.request.json();
  const post = await db
    .insertInto('posts')
    .values({ content })
    .returningAll()
    .executeTakeFirst();
  return event.body({ post });
});

router.get('/posts', async (event) => {
  const posts = await db.selectFrom('posts').selectAll().execute();
  return event.body({ posts });
});

Migrating your schema

Before you can insert and query data, you need to create your database schema. Ampt provides a simple migration tool for this. First create a migration script in the migrations directory of your project:

javascriptCopy code
// migrations/00001-create-posts-table.js
export async function up(db) {
  await db.schema
    .createTable('posts')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('author', 'text', (col) => col.notNull())
    .addColumn('content', 'text', (col) => col.notNull())
    .execute();
}

export async function down(db) {
  await db.schema.dropTable('posts').execute();
}

Migration scripts export an up() and a down() function that accept a single db parameter. The migration system uses Kysely(https://kysely.dev/docs/migrations) under the hood, so you can use any of the methods provided by Kysely to create your schema.

Next add an ampt:migrate script in your package.json:

jsonCopy code
{
  "scripts": {
    "ampt:migrate": "ampt-sql migrate"
  }
}

Now you can use run migrate in the Ampt CLI to update your database schema. The migration tool will determine which migrations need to run and apply them in alphabetical order. It's also possible to reverse migrations. See the docs for more information.

When you deploy your application to a stage, Ampt will automatically run any pending migrations for that stage.

Pricing

Ampt SQL uses Neon under the hood, and the pricing is the same as Neon's. You pay for CPU time, the amount of data you store, and the amount of data you read and write. You can see the Neon pricing page for more information and use their pricing calculator to estimate your monthly costs.

Preview accounts come with a limited amount of free usage you can use to try out Ampt SQL, and when you are ready to deploy your application, you can upgrade to a paid plan. Paid plans include the same free tier as preview accounts and we'll bill you for additional usage above that.

What's next?

Ampt SQL is available today in beta. We'd love for you to try it out and let us know what you think!

We're working on dashboard updates so you'll be able to inspect your schema and data, view metrics, backup and restore databases, and much more.

Get started with Ampt SQL today! Sign up now, checkout out the SQL docs, join our community on Discord and show us what you can build!