Baike.dev
All toolsOnline toolsAI codingTrendingOpen sourceNewsSubmit
Log in
< Back to tools
S

sqlite-to-rest

> 编程语言
Open source

Sqlite To Rest Koa routing middleware allowing you to expose a sqlite database via RESTful CRUD Table of Contents - Why build it? - Feature…

392 stars0 likes3 views
WebsiteGitHub

About

Sqlite To Rest Koa routing middleware allowing you to expose a sqlite database via RESTful CRUD Table of Contents - Why build it? - Feature…

Sqlite To Rest

Koa routing middleware allowing you to expose a sqlite database via RESTful CRUD

Table of Contents

  • Why build it?
  • Features
  • Limitations
  • Tutorial
  • CLI
  • API
  • RESTful CRUD Operations
  • Reference
  • Test

Why build it?

Mostly because I wanted to dig deeper into node web server code, but also because I haven't jumped onto the NoSQL bandwagon and think that web APIs are extremely useful. The result is a modest attempt at automating the CRUD boilerplate that every developer hates, while following the specs to make API consumption intuitive. I chose sqlite to keep the database side of things simple, with the intent that the API isn't serving heavy loads.

Features

  • Spec compliant CRUD RESTful API to an existing database's tables and views
  • GET utilizes JSONStream so the entire response is not held in memory, allowing for arbitrarily large responses.
  • Range requests with the custom range unit 'rows' can be used to GET specific rows. While compliant with rfc7233, the syntax and semantics were kept extremely similar to byte-ranges.
  • The server can configure a maximum request range per table since the amount of data per row will vary per-table.
  • The server can also configure whether to send the content-range header in a HEAD request. This allows the author to save the server from unnecessarily calculating the count on a table that is known to be very large.
  • Custom request header 'order' and conditional response header 'accept-order' exposes row sorting by column with optional ascending and descending specifiers
  • The API enforces correct usage, while sending developer-friendly error messages upon 4xx errors.
  • Comes with a friendly CLI to create a bare-bones koa server to get you up and running quickly.

Limitations

  • All tables must use primary keys. The next limitation explains why.
  • In effort to mitigate damage, unsafe methods only allow modification of single rows. This is enforced by matching the query parameters with the 'primary key' columns - friendly errors will tell you if called called an unsafe method incorrectly.
  • No built-in API key management. The library as-is can only serve trusted consumers.
  • It's sqlite. This library is not meant for clustering or large workloads. See 'Situations Where A Client/Server RDBMS May Work Better' for details.
  • This is my foray into reading rfc's and working with web server libraries (koa and middleware in general). I have tests and feel confident in my comprehension of the concepts, but the code is not the prettiest.
  • No friendly data validation currently. Right now contextless 500 statuses are returned if data doesn't pass constraints, and I don't have tests ensuring consistent behavior around data validation.

Tutorial

This tutorial will walk you through

  1. Creating an initial database
  2. Using sqlite-to-rest's CLI to create a bare-bones koa server
  3. Walk you through some curl commands to test the server's CRUD RESTful api.

CLI

By installing this library globally, you receive access to sqlite-to-rest.

The CLI currently contains one command generate-skeleton which creates an initial bare-bones koa server from an existing sqlite database. This should help you get started.

See sqlite-to-rest --help for more info.

API

require('sqlite-to-rest') returns an object with two properties.

  • generateSkeleton: madonna-function -> promise(undefined)
    This will usually be called from the CLI but is also made available via the js API. Its purpose is to generate a barebones koa server to get you up and running. In the directory it will:

    1. Run npm init -f if a package.json doesn't exist
    2. Output the generated koa server named 'skeleton.js'
    3. Install and save the dependencies required to run the server

    It takes two properties

    • dir: isLadenString isDirectory
      Directory to generate the koa server.

    • dbPath optional: isSqliteFile
      Path to your sqlite3 database.

    javascript
    // example
    sqliteToRest
      .generateSkeleton({
        dir: beerApiDir,
        dbPath: 'path/to/your/db.sqlite3',
      })
      .then(() => {
        /* skeleton.js is ready to be ran */
      })
  • getSqliteRouter: madonna-function -> promise(koa-router)
    This function generates the RESTful CRUD routing and returns the modified koa-router instance.

    It takes two properties

    • dbPath: isSqliteFile
      Path to your sqlite3 database.

    • config optional: A routing config object

    javascript
    // example
    const app = new require('koa')(),
      dbPath = 'path/to/your/db.sqlite3'
    
    getSqliteRouter({ dbPath }).then((router) => {
      app.use(router.routes())
      // ...
    })

RESTful CRUD Operations

The following is a list of the available crud operations made available by the RESTful API in the form of pseudo examples. All assume a beer table with two columns id INTEGER PRIMARY KEY and name which is nullable.

As noted in limitations, Be aware that unsafe methods (DELETE and POST) can only affect one row at a time.

  • GET This allows for the most variation. Click here for all available query operators. Keep in mind the following examples ignore proper query encoding

    Headers may be specified right below urls

    • /beer
      Requests for all rows

    • /beer?id=1
      Where id = 1

    • /beer
      range: rows=0-2
      First three rows

    • /beer
      range: rows=-5
      Last five rows

    • /beer
      range: rows=0-
      As many rows as the server is able to provide, which in practice will be the smaller of maxRange and total row count.

    • /beer
      range: rows=1-
      As many rows as the server is able to provide, starting from row 1.

    • /beer
      order: name
      Ordered by name ascending

    • /beer
      order: name desc
      Ordered by name descending

    • /beer
      order: name desc,id
      Contrived, but orders first by name descending, and in the case of a tie by id ascending.

    • /beer?id>1
      Where id > 1

    • **/beer?id>=2&id= 2 and id =1&id= 1 AND id .isDirectory](https://nodejs.org/api/fs.html#fs_class_fs_stats)

isFile

  • Returns the result of fs.statsSync followed by .isFile

GET query operators

Query conditions must be delimited by ampersands e.g. id>5&name!=Spotted Cow

Binary operators (require a value after)
=
!=
>=


**`. The reason it's configurable is that calculating max-range may be more work than its worth, depending on the load of the server and the size of your tables.

Custom Headers

Request

  • order: This header is only defined for GET, and can be thought of as the sql ORDER BY equivalent. It must contain a comma-delimited column names, each optionally followed by a space and the strings 'asc' or 'desc'. If incorrect order values are sent, a 400 response will indicate which ones.

Response

These aren't all necessarily custom, but all their usage falls outside what's defined in the spec and thus need clarification.

  • GET

    • max-range: This header is returned when the requested number of rows surpasses the configured maxRange. Note the request might not specify the range header, but the number of rows resulting in that resource will still be checked.

    • content-range: rfc7233 states

      only the 206 (Partial Content) and 416 (Range Not Satisfiable) status codes describe a meaning for Content-Range.

      When sqlite-to-rest responds with a 200 status code, the content-range header is sent with the 206 format of -/.

      When a request is sent without a range header and the number of resulting rows surpasses maxRange, a 400 is returned with content-range set in the 416 format of */

      Note this header may be returned in a HEAD response.

    • accept-order: This will be returned if the request header order had bad syntax or specified incorrect column names. For details, refer to HEAD -> accept-order below.

  • HEAD

    • accept-order: accept-order is just a comma-delimited list of the requested table columns, intended to tell the client the valid columns able to be used in the request header order.

    • max-range: The configured maxRange of the requested table.

    • content-range: This header will only be sent if the table has been configured with the flag sendContentRangeInHEAD. In that case, content-range is set to the 416 format of */

Test

npm test

Issues· 0 open

View all issuesOpen on GitHub

No open issues yet, or sync has not completed.

> Tags

JavaScriptautomatic-api

No comments yet. Be the first to share.

> Details

PublishedAug 1, 2026
UpdatedSep 17, 2026
Category编程语言
PricingOpen source

> Related tools

T
TypeScript
JavaScript 的超集,为前端与全栈提供静态类型
P
Python
通用编程语言,广泛用于 Web、数据与 AI
G
Go
Google 推出的简洁高效系统语言