SQL::Translator::Parser::OpenAPI - generate a relational database from an OpenAPI schema

Version 0.04 of SQL::Translator::Parser::OpenAPI has just been uploaded to CPAN. You can give it an OpenAPI 2 spec and it will generate a relational database from it. How is that useful?

Normally when you want to make an information service, you'll start by manually making a database, or writing the API code, possibly writing tests along the way, and then writing an OpenAPI spec after that - see this excellent article by Jan Henning Thorsen for more on using OpenAPI with Perl.

With this module, you can start by writing a spec, and letting code generate a lot of the infrastructure. This has a number of advantages:

  • it's quicker
  • it's easier
  • it's more reliable - let solved problems stay solved rather than reinvent the wheel!
  • it lets you keep an eye on the big picture

The distribution uses for its tests, as well as one that tests that many-to-many relationships are correctly generated, a couple of specs written by others:

  • v3 of the one used by the CPAN Testers infrastructure - interestingly, the generated DB doesn't closely resemble the real one, which keeps reports largely as MySQL JSON columns
  • the minimal "pet store" example put out by the OpenAPI folks
  • the more expansive "pet store" example
  • the Realworld backend API spec

Let's start from the minimal pet store, and see how far we can get without writing any code, only data and configuration. This uses Yancy, in standalone mode (no Perl code needed).

$ cpanm SQL::Translator::Parser::OpenAPI Yancy
$ wget https://metacpan.org/source/ETJ/SQL-Translator-Parser-OpenAPI-0.04/t/03-corpus.json -o pet-api.json
$ sqlt -f OpenAPI -t SQLite pet-api.json >pet-api-db.sqlite
$ echo ".read pet-api-db.sqlite" | sqlite3 pet-api-db.db # creates the DB

Yancy needs a configuration file to run standalone. Since currently Yancy can only use its own JSON schema format instead of using a separate OpenAPI spec (either given, or read from the DB schema), you would need to create the Yancy config from that. However, the collections key of the Yancy config would be largely the same as the definitions key of the OpenAPI spec, together with the backend key:

$ cat >yancy.conf <<EOF
backend: sqlite:pet-api-db.db

Alternatively, after creating the DB, you can generate a DBIx::Class::Schema and start from that:

$ cpanm DBIx::Class::Schema::Loader
$ dbicdump -o dump_directory=./lib PetApi dbi:SQLite:pet-api-db.db

Leave a comment

About Mohawk

user-pic I blog about Perl.