Supabase is great for quickly starting Postgres DB and building a POC. It’s really a product that has the entire developer workflow mapped out, with a real focus on how developers use the product and what features they can build to accommodate. One of my favorite features of Supabase is that they offer Rest API and JavaScript SDK Used to interact with databases, making it easy to quickly start querying data from your application. In fact, I’ve used it extensively many samples.
Unfortunately, not everyone can use Supabase or even Postgres at work. according to This database’s popularity ranking – Many of you are using MySQL and have never experienced what I’m talking about. I decided to challenge myself and try to recreate the Supabase experience for MySQL. If you want to skip to the final result, View repository.
Table of contents
Select database platform
You probably already have MySQL running somewhere in the cloud, but if you don’t, I recommend using a platform with a free tier. For cost-sensitive production workloads, AWS MySQL is also an option – but I don’t want to deal with setting it up. For this tutorial I will use Alvin. Creating a project is fairly simple.
Your repository will actually be hosted on AWS using Aiven’s free tier, which makes it easy to measure production latency.
Building a Postgres API using PostgREST
If you read through Supabase’s REST API documentation, you’ll see that the backend is actually built using a project called after break. PostgREST is a web server that can create a REST API on any Postgres database. it has a Specific query structure Used to convert query parameters into Postgres queries. I’ll show you how to handle these queries in MySQL later.
GET /rest/v1/books?select=title,description&description=ilike.*cheese*&order=title.desc&limit=5&offset=10 HTTP/1.1
Host: localhost:54321
Provided by Supabase A playground for learning grammar If you want to see it in action. You may find that building these queries is difficult for your clients.
Build PostgREST queries using postgrest-js
Thankfully, Supabase already offers PostgREST client library Used to build queries from the front end. It seems to have the exact same syntax as the Supabase JS library – which helps a lot in our task of recreating devx.
import { PostgrestClient } from "@supabase/postgrest-js";
const REST_URL = "http://localhost:3000";
const postgrest = new PostgrestClient(REST_URL);
const { data, error } = await postgrest.from("countries").select();
Host your MySQL API
Okay, so now we have a database, PostgREST server library, and a query client. If we are using a Postgres database, you can simply go to Digital Ocean and deploy the server and frontend to the Droplet. Unfortunately, we can’t use PostgREST directly here because MySQL and Postgresql syntax are not fully compatible. Instead, we have to host our own API that translates PostgREST syntax into MySQL queries. Fortunately, our friends at Clouds below zero Created a This is what the library does.
I’m going to challenge myself to build this API using tools that a typical web geek (i.e. a typical Supabase developer) would know – i.e. Typescript + NodeJS, Vercel for hosting, and NextJS as my client and server framework . This approach will be completely serverless, so you won’t need to manage any infrastructure beyond the database.
Use NextJS to build a PostgREST-compatible API
Take the Subzero example – we just need a few dependencies
npm install @subzerocloud/rest @supabase/postgrest-js copy-webpack-plugin mariadb dotenv
-
@subzerocloud/restIs a library that converts PostgREST queries into MySQL -
@supabase/postgrest-jsas mentioned above -
mariadbAs a driver for accessing my MySQL database -
dotenvEnvironment variables used to configure database connections -
copy-webpack-pluginFixed bug in NextJS handling of wasm files (see
next.config.tsin the examples repository)
API boilerplate
In your NextJS repository, create a file called route.ts below src/app/api/[...query].
After creating the archive, the first step is to add some boilerplate archives to handle the various REST verbs.
import { SubzeroError } from "@subzerocloud/rest";
async function handler(request: Request, method: Method)
if (!["GET", "POST", "PUT", "DELETE", "PATCH"].includes(method)) {
throw new SubzeroError(`Method ${method} not allowed`, 400);
}
}
async function handlerWrapper(request: Request, method: Method) {
try {
return await handler(request, method);
} catch (e) {
if (e instanceof SubzeroError) {
console.log("SubzeroError:", e);
return new Response(e.toJSONString(), {
status: e.status || 500,
headers: { "content-type": "application/json" },
});
} else {
console.log("Error:", e);
return new Response((e as Error).toString(), { status: 500 });
}
}
}
export const GET = async (request: Request) =>
await handlerWrapper(request, "GET");
export const PUT = async (request: Request) =>
await handlerWrapper(request, "PUT");
export const POST = async (request: Request) =>
await handlerWrapper(request, "POST");
export const PATCH = async (request: Request) =>
await handlerWrapper(request, "PATCH");
export const DELETE = async (request: Request) =>
await handlerWrapper(request, "DELETE");
export async function OPTIONS() {
return new Response(null, {
status: 204,
headers: {
"access-control-allow-origin": "*",
"access-control-allow-methods": "GET, POST, PUT, DELETE, PATCH",
"access-control-allow-headers": "Content-Type, Prefer",
},
});
}
The code above will handle any call to /api/{table_name}.
Initialization below zero
Now let the database connection begin. First – we need to manage permissions/access to the database. At the same level as your route, create a permissions.json document
[
{
"table_schema": "" ,
"table_name": "" ,
"role": "admin",
"grant": ["all"],
"using": [{ "sql": "true" }]
}
]
we are creating a admin The role here will have access to the database. now let us return to route.ts.
import Subzero, {
SubzeroError,
Env as QueryEnv,
Method,
getIntrospectionQuery,
fmtMySqlEnv,
} from "@subzerocloud/rest";
import mysql, { PoolConfig } from "mariadb";
import { resolve } from "path";
import { readFileSync, existsSync } from "fs";
const urlPrefix = "/api";
const schema = process.env.DATABASE_NAME!;
const dbType = "mysql";
export const dynamic = "force-dynamic";
let subzero: Subzero;
const role = "admin"; // Set according to permissions.json
const connectionParams: PoolConfig = {
connectionLimit: 75,
connectTimeout: 5 * 1000,
insertIdAsNumber: true,
bigIntAsNumber: true,
port: parseInt(process.env.DATABASE_PORT!),
host: process.env.DATABASE_HOST,
user: process.env.DATABASE_USERNAME,
password: process.env.DATABASE_PASSWORD,
allowPublicKeyRetrieval: true,
trace: true,
database: schema,
ssl: {
rejectUnauthorized: true,
ca: process.env.DATABASE_CA_CERTIFICATE,
},
};
// WARNING! do not use this connection pool in other routes since the
// connections hold special user defined variables that might interfere with
// other queries
const subzeroDbPool = mysql.createPool(connectionParams);
async function introspectDatabaseSchema() {
const permissionsFile = resolve(
process.cwd(),
"src/app/api/[...query]/permissions.json",
);
// eslint-disable-next-line @typescript-eslint/no-explicit-any
let permissions: any[] = [];
if (existsSync(permissionsFile)) {
permissions = JSON.parse(readFileSync(permissionsFile, "utf8"));
} else {
console.error("permissions file not found", permissionsFile);
}
const { query, parameters } = getIntrospectionQuery(
dbType,
[schema], // the schema/database that is exposed to the HTTP api
// the introspection query has two 'placeholders' to adapt to different configurations
new Map([
["relations.json", []], // custom relations - empty for now
["permissions.json", permissions],
]),
);
const db = await mysql.createConnection(connectionParams);
const result = await db.query(query, parameters);
const dbSchema = result[0].json_schema;
await db.end();
return dbSchema;
}
// Initialize the subzero instance that parses and formats queries
let initializing = false;
async function initSubzero() {
if (initializing) {
return;
}
initializing = true;
let wait = 0.5;
let retries = 0;
const maxRetries = 3; // You can adjust this or make it configurable
while (!subzero) {
try {
const dbSchema = await introspectDatabaseSchema();
subzero = new Subzero(dbType, dbSchema);
} catch (e) {
const message = e instanceof Error ? e.message : e;
retries++;
if (maxRetries > 0 && retries > maxRetries) {
throw e;
}
wait = Math.min(10, wait * 2); // Max 10 seconds between retries
console.error(
`Failed to connect to database (${message}), retrying in ${wait} seconds...`,
);
await new Promise((resolve) => setTimeout(resolve, wait * 1000));
}
}
initializing = false;
}
async function handler(request: Request, method: Method) {
...
// initialize the subzero instance if it is not initialized yet
if (!subzero) {
await initSubzero();
}
}
This code essentially creates a connection to the database so that Subzero can “introspect” it to understand the schema (eg columns, types, etc.). Access rights are governed by the following rules permissions.json We created it before.
Set environment variables
Now it’s up to you to create a .env file and start filling in the variables in the code above. If you use Aiven, you can find them on the service’s home page:
Execute query
let’s finish handler function and the following code
async function handler(request: Request, method: Method) {
...
const queryEnv: QueryEnv = [
["role", role],
["request.method", method],
["request.headers", JSON.stringify(request.headers)],
[
"request.get",
JSON.stringify(Object.fromEntries(new URL(request.url).searchParams)),
],
["request.jwt.claims", JSON.stringify({})],
];
const { query: envQuery, parameters: envParameters } = fmtMySqlEnv(queryEnv);
const db = await subzeroDbPool.getConnection();
// eslint-disable-next-line @typescript-eslint/no-explicit-any
let result: any;
try {
await db.query("BEGIN");
await db.query(envQuery, envParameters);
if (method === "GET") {
const { query, parameters } = await subzero.fmtStatement(
schema,
`${urlPrefix}/`,
role,
request,
queryEnv
);
const rows = await db.query(query, parameters);
result = rows[0].body ? JSON.parse(rows[0].body) : null;
} else {
const statement = await subzero.fmtTwoStepStatement(
schema,
`${urlPrefix}/`,
role,
request,
queryEnv
);
const { query: mutate_query, parameters: mutate_parameters } =
statement.fmtMutateStatement();
const rows = await db.query(mutate_query, mutate_parameters);
const { insertId, affectedRows } = rows;
if (insertId > 0 && affectedRows > 0) {
const ids = Array.from(
{ length: affectedRows },
(_, i) => insertId + i
);
statement.setMutatedRows(ids);
} else {
const idRows = await db.query(`
select t.val
from json_table(
@subzero_ids,
'$[*]' columns (val integer path '$')
) as t
left join json_table(
@subzero_ignored_ids,
'$[*]' columns (val integer path '$')
) as t2 on t.val = t2.val
where t2.val is null;
`);
statement.setMutatedRows(idRows);
}
const returnRepresentation = request.headers
.get("Prefer")
?.includes("return=representation");
if (returnRepresentation) {
const { query: select_query, parameters: select_parameters } =
statement.fmtSelectStatement();
const selectResult = await db.query(select_query, select_parameters);
result = selectResult[0].body ? JSON.parse(selectResult[0].body) : null;
}
}
await db.query("COMMIT");
} catch (e) {
await db.query("ROLLBACK");
throw e;
} finally {
await db.end();
}
return new Response(JSON.stringify(result), {
status: 200,
headers: {
"Content-Type": "application/json",
},
});
}
It’s a bit complicated – you don’t need to understand everything. Essentially, Subzero is reading the properties of the request that builds the MySQL statement. If the request is a GET, it will only execute that request. If it is a mutation, a two-step mutation is performed and an error is generated that causes a rollback. With this last addition, we have a working MySQL PostgREST API that you can push to Vercel!
Make a query
Now that we have an API endpoint running, you can use postgres.js The library starts making requests from the NextJS frontend. You can build a simple page.tsx in the root directory of your project.
"use client";
import { PostgrestClient } from "@supabase/postgrest-js";
const REST_URL = `${process.env.NEXT_PUBLIC_API_URL}/api`;
export default function Home() {
const performFetch = async () => {
const postgrest = new PostgrestClient(REST_URL);
const { data, error } = await postgrest
.from("products")
.select("*")
.order("id", { ascending: false });
if (data) {
console.log(data);
}
if (error) {
console.error(JSON.stringify(error, null, 2))
}
};
return (
<button
onClick={performFetch}
>
Fetch Table Data
</button>
);
}
If you want to see it in action, check out My demo application.
(Optional) API Gateway
Before you expose your API publicly, I generally recommend that you put it behind an API gateway (such as Zuplo). Some features you may find valuable include:
- rate limit request
- Cache database read
- verify
Setup is easy, just clone and run this warehouseand change your process.env.NEXT_PUBLIC_API_URL to match the URL of your gateway.
Summarize
Congratulations, you now have a No server MySQL REST API with Supabase developer experience! All code can be found in this warehouse This way you can run it yourself. All of the services used above have generous free plans, so this is a good choice for a hobby project.
If you don’t like using Typescript/Node as backend – check out This PHP alternative You can probably run on Vercel’s PHP runtime Save everything in monorepo.
If you prefer a more automated solution – Check out these options.
Finally, if you want to be able to customize the CRUD API while still conforming to your database table schema – check out how Generate OpenAPI from the database.