Support SQLite - Githubissues

SQLite, and any relational database with a JDBC driver, is supported today. API Simulator has had the capability for a few years, actually. But I can see how the documentation is not clear about it.

Here are a few steps to follow and a simple simlet modeled after this one from the examples.

Add the JDBC driver for the respective database (usually a JAR) to either a "lib" directory under the simulation's home directory or to the "lib" directory under the API Simulator's installation folder.

Create a SQLite database and populate it with some data. For this I'm using a CSV data file from the examples - product-data.csv - to create a table named product and populate it with data:

> sqlite3 products.db
sqlite> .import product-data.csv product --csv
sqlite> select * from product;
2706414|Men|Hoodies & Sweatshirts|Fleece Pullover Hoodie|Black Charcoal|S|769409213
2706414|Men|Hoodies & Sweatshirts|Fleece Pullover Hoodie|Black Charcoal|M|769413463
2706414|Men|Hoodies & Sweatshirts|Fleece Pullover Hoodie|Black Charcoal|L|769413609
...

For SQLite, place the database file in the simulation's home directory along side the all-in-one apisim.yaml.

Add the following simlet to apisim.yaml:

simlet: sqlite-demo

request:
  - method: GET
  - uriPathPattern: /tests/sqllite/products/{SKU}
  - where: parameter
    named: ProductData
    exists: true

SKU:
  is: parameter
  from: uriPathPattern
  pattern: /tests/sqllite/products/{SKU}

ProductData:
  is: parameter
  from: sql
  connection:
    uses: driver
    # Driver's fully-qualified class name
    driver: "org.sqlite.JDBC"
    url: "jdbc:sqlite:${sim.path}/products.db"
  # Parameterized SQL query
  query: `
SELECT *
FROM PRODUCT p
WHERE SKU = ?
`
  pick: first
  parms:
  - SKU

response:
  from: template
  template: Simula
  status: 200
  headers:
  - "Content-Type: application/json; charset=UTF-8"
  body: `
{
   "product": {
      "id": "${ ProductData['ProductID'] }",
      "sku": "${ ProductData['SKU'] }",
      "name": "${ jsonEncode( ProductData['Name'] ) }",
   }
}
`

The global variables ${simlets.path}, ${sim.path}, and ${simlet.path} are described here.

After starting a local API Simulator for this simulation, run

curl -v http://localhost:6090/tests/sqllite/products/769409213
...
< HTTP/1.1 200 OK
< Content-Type: application/json; charset=UTF-8
< Server: API Simulator, v1.12
< ...
< Content-Length: 128
< 
{
   "product": {
      "id": "2706414",
      "sku": "769409213",
      "name": "Fleece Pullover Hoodie",
   }
}

It is to be expected that fetching data from an external database is slower compared to loading it all in memory from CSV Data Sources.

It is a great feature in API Simulator, isn't it? :tada:

apimastery / APISimulator

Support SQLite #30