Common Database Operations

Learn how to run common database operations with Thin Backend

1. Introduction

Thin Backend database system is powered by Postgres. Thin Backend provides a few basic functions to read and write to your project's postgres database from the JS frontend.

It covers all the common tasks your web application usually does. TypeScript data structures and types are generated automatically based on your database schema.

Right now the only supported database platform is Postgres. Focusing on Postgres allows us to better integrate advanced Postgres-specific solutions into your application.

Retrieving Records

Querying Records

You can retrieve all visible records of a table using query:

const todos = await query('todos').fetch();

for (const todo of todos) {
    console.log(todo.title);
}

This will run a SELECT * FROM todos query and put a list of Todo structures.

Realtime Queries

To keep the result set in sync with the actual database state, use useQuery:

// Add these imports
import { query } from 'thin-backend';
import { useQuery } from 'thin-backend-react';

function TodoList() {
    const todos = useQuery(query('todos').orderByDesc('createdAt'));

    if (todos === null) {
        return <div>Loading ...</div>;
    }

    return <div>
        {todos.map(todo => <div>{todo.title}</div>)}
    </div>
}

The useQuery react hook is using a websocket to be notified about any changes to the selected data set.

Fetching a single record

When you have the id of a record, you can also use fetchOne to get it from the database:

const todo = await query('todos')
        .where('id', 'd94173ec-1d91-421e-8fdc-20a3161b7802')
        .fetchOne();

This will run the SQL query SELECT * FROM todos WHERE id = 'd94173ec-1d91-421e-8fdc-20a3161b7802' LIMIT 1.

In case the record is not found, fetchOne will return null.

Use useQuerySingleResult() to fetch a single record from a react component and receive realtime updates:

// Add these imports
import { query } from 'thin-backend';
import { useQuerySingleResult } from 'thin-backend-react';

function SingleTodo({ todoId }) {
    const todo = useQuerySingleResult(query('todos').where('id', todoId));

    if (todo === null) {
        return <div>Loading ...</div>;
    }

    return <div>
        <h1>{todo.title}</h1>
    </div>;
}

Selecting Fields

By passing in an array as a second parameter to query you can select which fields you want to retrieve.

const todos = await query('todos', ['id', 'title'])
        .fetch();

Filter Rows

You can filter the results using chaining .where and similar methods:

const todos = await query('todos')
        .where('title', 'test') // filter by equality
        .whereNot('title', 'Another todo') // filter by inequality
        .or(where('userId', userId)) // alternative filter
        .whereIn('status', ['done', 'in_progress']) // `a IN (list)` queries
        .fetch();

Order

Use orderBy, orderByAsc or orderByDesc to get a sorted result:

const latestTodos = await query('todos')
        .orderByDesc('createdAt')
        .fetchOne();

const oldestTodos = await query('todos')
        .orderBy('createdAt') // 'orderBy' is an alias for 'orderByAsc'
        .fetchOne();

Create a Record

To insert a record into the database, call createRecord with a plain javascript object:

createRecord(table, record)

// Example:
const newTodo = {
    title: 'Finish Guide',
    userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5'
}
;
const insertedTodo = await createRecord('todos', newTodo);

console.log('id', insertedTodo.id);

Create Many Records

You can use createRecords to insert multiple records with a single INSERT statement:

createRecords(table, records)

// Example:
const todoA = { title: 'Finish Guide', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };
const todoB = { title: 'Learn Haskell', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };

const todos = await createRecord('todos', [ todoA, todoB ]);

Update a Record

The function updateRecord runs an UPDATE query for a specific record:

updateRecord(table, id, patch)

// Example:
const todo = await updateRecord('todos', '66cc037e-5729-435c-b507-a17492fe44f4', { isCompleted: false });

Update Many Records

You can use updateRecords to updates multiple records with a single UPDATE statement:

updateRecords(table, ids, patch)

// Example:
const todoA = { title: 'Finish Guide', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };
const todoB = { title: 'Learn Haskell', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };

const todoIds = [ todoA, todoB ].map(todo => todo.id)
const todo = await updateRecords('todos', todoIds, { isCompleted: true });

Delete Record

Use deleteRecord to run a simple DELETE query:

deleteRecord(table, id)

// Example:
await deleteRecord('todos', '66cc037e-5729-435c-b507-a17492fe44f4');

This will execute:

DELETE FROM todos WHERE id = "66cc037e-5729-435c-b507-a17492fe44f4"

Delete Many Records

Use deleteRecords to delete multiple records:

deleteRecords(table, ids)

// Example:
const todoA = { title: 'Finish Guide', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };
const todoB = { title: 'Learn Haskell', userId: '49946f4d-8a2e-4f18-a399-58e3296ecff5' };

const todoIds = [ todoA, todoB ].map(todo => todo.id)
await deleteRecords('todos', todoIds);

Foreign Keys

Automatic Foreign Key Constraints

FKs are supported. The easiest way to add a FK is based on the naming. E.g. if you have a table tests and add a column user_id a checkbox will appear and the Schema Designer will automatically wire up the FK constraint if the checkbox is checked:

This of course works with other tables as well.

Manual Foreign Key Constraints

Sometimes you might want to manually set up a FK constraint, for that right click the column and select Add Foreign Key Constraint:

Editing Foreign Key Constraints & On Delete Behaviour

You can click the FOREIGN KEY: users label (at the right) inside a column here to edit the foreign key constraint:

This will then open a modal to edit the FK:

Database Transactions

You can use withTransaction to run a set of operations within a database transaction. If an exception is thrown within the transaction callback, the transaction will automatically be rolled back and the exception is re-thrown. If the callback executes successfully, the transaction will automatically be committed:

import { withTransaction } from 'ihp-datasync';

await withTransaction(async transaction => {
    const team = await transaction.createRecord('teams', { title: 'New Team' });
    
    const project = await transaction.createRecord('projects', {
        title: 'Project 1',
        teamId: team.id
    });

    return [ team, project ];
})

Manual Transactions

If you need more control over your transaction, you can use the Transaction object to manually manage rollbacks and commits:

import { Transaction } from 'ihp-datasync';

const transaction = new Transaction();

Before you can run operations inside the transaction, call .start():

await transaction.start();

Use the .rollback() method to roll back the transaction if needed:

await transaction.rollback();

Call the .commit() method to commit the transaction:

await transaction.commit();

Transaction Limits

You can have max 10 concurrent transactions per session.

Supported Database Types

Thin Backend currently has support for the following postgres column types:

Float or Double

If you're not sure whether you need a Float (6 decimal digits precision) or Double (15 decimal digits precision), double is a good default choice as JS numbers comes closest to that from a precision standpoint.

If you need more than 15 decimals, you could use a type like NUMERIC(20, 18) to e.g. store numbers in format xx.yyyyyyyyyyyyyyyyyy. The Numeric type is not available in the GUI, but you could switch over to the Code Editor in the Schema Designer and swap out e.g. the DOUBLE type with a NUMERIC(..) type there, then click save and then switch back to the Visual Editor.