Database2api

<h1 align="center">database2api</h1> <p align="center">`DataBase to API`, use database, generate open `API`</p> <div align="center">

</div> <div align="center">

English | 中文

</div> <hr />

database2api is a powerful and convenient tool. Its main function is to automatically generate open API interfaces based on the existing database, which can significantly save time and energy for developers. It is especially suitable for scenarios where there is an existing database and an API interface needs to be provided, or where only the database is built and the API interface needs to be implemented quickly.

---

RELEASE ✈️

• 0.0.2 2024-7-18 First Released Version
• 0.0.3 2024-7-20 Add Bearer Authentication
• 0.0.4 2024-7-21 Add Oracle Database Support
• 0.0.5 2024-8-11 Delete jar File From Repository, Add Query At list/paged API
• 0.0.6 2024-8-12 Add GET API Cache Support
• 0.0.7 2024-8-14 Add gen-setting Support. #17

I. Introduction ⚡

database2api can intelligently parse the database structure and automatically generate the corresponding API interfaces according to the user's requirements and configuration. It enables you to easily achieve the interaction between the database and external applications without the cumbersome manual coding.

In today's software development, the interaction between the database and external applications is a crucial link. However, manually writing API interfaces is often a time-consuming and error-prone task, and it requires proficiency in a certain backend programming language, with a relatively high threshold. The development of database2api aims to solve this pain point, allowing developers to focus more on the implementation of business logic rather than spending excessive time and effort on interface development.

For example, in a rapidly evolving project, the database structure may change frequently. Using database2api, you only need to update the configuration file to quickly regenerate the API interfaces adapted to the new structure, greatly improving the agility of the project.

Whether you are an individual developer or a team, database2api will be your powerful assistant to enhance development efficiency and accelerate the project process.

II. Principle 💛

This tool uses Ktor as the underlying framework and JDBC as the database access layer. It obtains the database structure through java.sql.DatabaseMetaData, and then dynamically registers the API routes through Ktor to realize the generation of API interfaces directly from the database.

III. Supported Databases 🌟

Currently, database2api supports the following mainstream databases:

• ✅ Sqlite
• ✅ MySQL
• ✅ Postgresql
• ✅ Microsoft SQL Server
• ✅ MariaDb
• ✅ Oracle

IV. Advantages 💥

1. Efficient and convenient: Through a simple configuration file, the required API interfaces can be quickly generated, greatly improving the development efficiency.
2. Widespread database support: Covers common database types to meet the needs of different projects.
3. Easy to maintain: The generated interface structure is clear, the code is standardized, and it is convenient for subsequent expansion and testing.

V. How to use ❓

(1) Running on docker (Dockerfile in the directory ./docker)

i. Build docker image with Dockerfile

NOTE: Modify DB_URL in the file ./docker/data/setting.ini

rm -y docker/database2api.jar
copy release/database2api.jar docker/database2api.jar
cd docker
docker build -t database2api:0.0.4 .

ii. Running docker

docker run -d -p 8989:8080 -v ./data:/usr/app/data database2api:0.0.4

(2) Use JAR

Click to download or use release/database2api.jar.

• Preview of the directory structure

│  database2api.jar  <-- Main program (required)
└─ data
     └─ ext          <-- Directory for placing extended APIs (optional)
     └─ web          <-- Directory for static files (optional)
     └─ setting.ini  <-- Configuration file (required)

• Sample configuration file setting.ini

Using the command line tools can quickly generate configuration files, see #17

# Default port for API
API_PORT=8080
# Prefix for generating API, if set to api/v1, then the API becomes: http://localhost:{PORT}/api/v1/xxxxxx
API_PREFIX=api
# Whether to enable API documentation, address http://localhost:{PORT}, if set to false, no API documentation will be generated
API_INDEX_ENABLED=true
# Whether to enable the interface authorization access function, default is false, and all APIs can be accessed without authorization and authentication
API_AUTH_ENABLED=false
# Interface authorization access, supports: Basic, JWT, Bearer. (Other authorization and authentication methods may be supported in the future)
API_AUTH_TYPE=JWT
# List of user names and passwords allowed to access the interface
API_AUTH_USERS=admin:123456,user:1234
# When Bearer authorization is used, it should be configured as [tag:token], where tag represents the attribution of this token, and tag can be empty (the colon cannot be omitted).
# API_AUTH_USERS=CompanyA:123,CompanyB:456,:789
# Default connection address of the database (mainly the database connection string here is required, and samples of other database connection strings are below)
DB_URL=jdbc:sqlite://G:/database2api-test/sqlite/fqb.db
# Database user name
DB_USER=
# Database password
DB_PWD=
# Table names for generating API, if empty, all tables will generate API, multiple tables are separated by commas
INCLUDE_TABLES=
# Table names that need to be ignored, if not empty, the specified table names will be filtered, multiple tables are separated by commas
IGNORED_TABLES=
# Whether to enable the static website, if enabled, a web directory will be created, and static resources can be accessed by putting them in
STATIC_WEB_ENABLED=true
# Whether to enable extended API, allowing users to use JS code to query the database with custom SQL
EXT_API_ENABLED=true
# Whether to enable the table structure API, and the default is false.
SCHEMA_API_ENABLED=false
# Whether to enable GET-type API cache, which defaults to true.
GET_API_CACHE=true
# The cache time of GET-type API, default is 30 seconds.
GET_API_CACHE_TIMEOUT=30000

• Startup method:

java -jar database2api.jar

After startup, the console log is as follows:

2024-07-11 23:43:14.367 [main] DEBUG cn.hutool.log.LogFactory - Use [Slf4j] Logger As Default.
2024-07-11 23:43:14.369 [main] INFO  com.mrhuo.Database2Api - Database2Api: 开始初始化
2024-07-11 23:43:14.382 [main] INFO  com.mrhuo.Database2Api - Database2Api: 开始初始化 API 配置
2024-07-11 23:43:14.431 [main] DEBUG cn.hutool.setting.SettingLoader - Load setting file [D:\work\java\database2api\data\setting.ini]
2024-07-11 23:43:14.444 [main] INFO  com.mrhuo.Database2Api - Database2Api: 静态网站主页[http://127.0.0.1:8080/web/index.html]
2024-07-11 23:43:14.444 [main] INFO  com.mrhuo.Database2Api - Database2Api: 开始初始化数据库
2024-07-11 23:43:14.444 [main] INFO  com.mrhuo.Database2Api - Database2Api: 使用链接字符串[jdbc:sqlite://G:/database2api-test/sqlite/fqb.db]
2024-07-11 23:43:15.236 [main] INFO  com.mrhuo.Database2Api - Database2Api: 获取到所有数据表的表结构
2024-07-11 23:43:15.236 [main] INFO  com.mrhuo.Database2Api - Database2Api: 已保存到文件[D:\work\java\database2api\data\tables.json]
2024-07-11 23:43:15.236 [main] INFO  com.mrhuo.Database2Api - Database2Api: 初始化全部成功
2024-07-11 23:43:15.383 [main] INFO  ktor.application - Autoreload is disabled because the development mode is off.
2024-07-11 23:43:16.241 [main] INFO  ktor.application - Application started in 0.928 seconds.
2024-07-11 23:43:16.242 [main] INFO  ktor.application - Application started: io.ktor.server.application.Application@299266e2
2024-07-11 23:43:16.633 [DefaultDispatcher-worker-1] INFO  ktor.application - Responding at http://127.0.0.1:8080

After successful startup, the directory structure becomes:

│  database2api.jar
└─ data
     │  setting.ini
     │  tables.json      <-- This is the name of all tables in the database, and it will not be retrieved from the database again on the next startup, and this file will be used directly. If the database has been updated, delete this file.
     │  table_names.json <-- This is the structure of all tables in the database, and it will not be retrieved from the database again on the next startup, and this file will be used directly. If the database has been updated, delete this file.
     └─ ext              <-- Directory for placing extended APIs (optional)
     └─ web              <-- Directory for static files (optional)
         └─ index.html   <-- This is the default homepage of the static webpage.

(3) Previews

Open the browser and visit http://127.0.0.1:8080. If the configuration API_INDEX_ENABLED=true is enabled, the interface will be as follows at this time:

The port setting can be found in the configuration file API_PORT=8080<br/> If API_INDEX_ENABLED=false is set, the API documentation interface will not be displayed.

Find a test to get all data at