Skip to content
TrailBase

A CRUD App

In this tutorial, we’ll set up a database with an IMDB test dataset, spin up TrailBase and write a simple client CLI application to access the data using Record APIs.

In an effort to demonstrate TrailBase’s loose coupling and the possibility of simply trying out TrailBase with an existing SQLite-based data analysis project, we will also offer an alternative path to bootstrapping the database using the vanilla sqlite3 CLI.

Create the Schema

By simply starting TrailBase, the migrations in traildepot/migrations will be applied, including U1728810800__create_table_movies.sql:

CREATE TABLE movies IF NOT EXISTS (
  rank         INTEGER PRIMARY KEY,
  name         TEXT NOT NULL,
  year         ANY NOT NULL,
  watch_time   INTEGER NOT NULL,
  rating       REAL NOT NULL,
  metascore    ANY,
  gross        ANY,
  votes        TEXT NOT NULL,
  description  TEXT NOT NULL
) STRICT;

Note that the only schema requirement for exposing an API is: STRICT typing and an integer (or UUIDv7) primary key column.

The main benefit of relying on TrailBase to apply the above schema as migrations over manually applying the schema yourself, is to:

  • document your database’s schema alongside your code and
  • even more importantly, letting TrailBase bootstrap from scratch and sync-up databases across your dev setup, your colleague’s, every time integration tests run, QA stages, and in production.

That said, TrailBase will happily work on existing datasets, in which case it is your responsibility to provide a SQLite database file that meets expectations expressed as configured TrailBase API endpoints.

Feel free to run:

Terminal window
$ mkdir traildepot/data
$ sqlite3 traildepot/data/main.db < traildepot/migrations/U1728810800__create_table_movies.sql

before starting TrailBase the first time, if you prefer bootstrapping the database yourself.

Importing the Data

After creating the schema above, either manually or starting TrailBase to apply migrations, we’re ready to import the IMDB test dataset. We could now expose an API endpoint and write a small program to first read the CSV file to then write movie database records… and we’ll do that in a little later. For now, let’s start by harnessing the fact that SQLite databases are simply a local file and import the data using the sqlite3 CLI side-stepping TrailBase:

$ sqlite3 traildepot/data/main.db
sqlite> .mode csv
sqlite> .import ./data/Top_1000_IMDb_movies_New_version.csv movies

There will be a warning for the first line of the CSV, which contains textual table headers rather than data matching our schema. That’s expected. We can validate that we successfully imported 1000 movies by running:

sqlite> SELECT COUNT(*) FROM movies;
1000

Reading the Data

With TrailBase up and running (trail run), the easiest way to explore your data is go to the admin dashboard under http://localhost:4000/_/admin and log in with the credentials provided to you in the terminal upon first start. You can also run trail user reset-password admin@localhost <new> to reset the password.

In this tutorial we want to explore more programmatic access and using TrailBase record APIs.

record_apis: [
  # ...
  {
    name: "movies"
    table_name: "movies"
    acl_world: [READ]
    acl_authenticated: [CREATE, READ, UPDATE, DELETE]
  }
]

By adding the above snippet to your configuration (which is already the case for the checked-in configuration) you expose a world-readable API. We’re using the config here but you can also configure the API using the admin dashboard via the tables view and the “Record API” settings in the top right.

Let’s try it out by querying the top-3 ranked movies with less than 120min watch time:

Terminal window
curl -g 'localhost:4000/api/records/v1/movies?limit=3&order=rank&watch_time[lt]=120'

You can also use your browser. Either way, you should see some JSON output with the respective movies.

Writing Data with a Custom CLI

Finally, let’s authenticate and use privileged APIs to first delete all movies and then add them back using type-safe APIs rather than sqlite3.

Let’s first create the JSON Schema type definitions from the database schema we added above. Note, that the type definition for creation, reading, and updating are all different. Creating a new record requires values for all NOT NULL columns w/o a default value, while reads guarantees values for all NOT NULL columns, and updates only require values for columns that are being updated. In this tutorial we’ll “cheat” by using the same type definition for reading existing and creating new records, since our schema doesn’t define any default values (except implicitly for the primary key), they’re almost identical.

In preparation for deleting and re-adding the movies, let’s run:

Terminal window
$ trail schema movies --mode insert

This will output a standard JSON schema type definition file. There’s quite a few code-generators you can use to generate bindings for your favorite language. For this example we’ll use quicktype to generate TypeScript definitions, which also happens to support some other ~25 languages. You can install it, but for the tutorial we’ll stick with the browser version and copy&paste the JSON schema from above.

With the generated types, we can use the TrailBase TypeScript client to write the following program:

examples/tutorial/scripts/src/fill.ts
import { readFile } from "node:fs/promises";
import { parse } from "csv-parse/sync";


import { Client } from "trailbase";
import type { Movie } from "@schema/movie";


const client = new Client("http://localhost:4000");
await client.login("admin@localhost", "secret");
const api = client.records("movies");


let movies = [];
do {
  movies = await api.list<Movie>({
    pagination: {
      limit: 100,
    },
  });


  for (const movie of movies) {
    await api.delete(movie.rank!);
  }
} while (movies.length > 0);


const file = await readFile("../data/Top_1000_IMDb_movies_New_version.csv");
const records = parse(file, {
  fromLine: 2,
  // prettier-ignore
  columns: [ "rank", "name", "year", "watch_time", "rating", "metascore", "gross", "votes", "description" ],
});


for (const movie of records) {
  await api.create<Movie>({
    rank: parseInt(movie.rank),
    name: movie.name,
    year: movie.year,
    watch_time: parseInt(movie.watch_time),
    rating: parseInt(movie.rating),
    metascore: movie.metascore,
    gross: movie.gross,
    votes: movie.votes,
    description: movie.description,
  });
}

What’s Next?

Thanks for making it to the end. Beyond the basic example above, the repository contains a more involved Blog example (/examples/blog) including both, a Web and Flutter UI. The blog example also demonstrates more complex APIs, authorization, custom user profiles, etc.

Any questions or suggestions? Reach out on GitHub and help us improve the docs. Thanks!