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
:
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:
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:
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:
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.
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:
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:
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:
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!