table2json - tiny table structure reader

table2json is a minimalistic database structure reader. It can load structure data for postgresql or sql-server. It returns json data for the following items:

• tables
• table columns ( names, types, default values, identity usage, etc. )
• primary keys
• foreign keys
• unique indexes

table2json was tested on postgres and sql-server but as it relies on information_schema views it might be plugged in to other DBMS with some effort.

How to use it?

You will need to install this package to reference it

npm install --save https://github.com/jakubiszon/table2json

Requiring the package require( 'table2json' ) will return a function. The function can be used to create a database interface object.

The db-interface object expose just 2 functions reading data: listAllTables and defineTable. There also are open and close methods to manage the connection.

Example usage:

consttable2json=require('table2json');constfs=require('fs').promises;(asyncfunction(){// create a db-interface, we need to pass dbms name// this could be either "postgres" or "sqlserver"// in this example we will play with postgresconstdb=table2json('postgresql');// connect to the databaseawaitdb.open({"host":"localhost","port":5432,"database":"xxx","password":"xxx","user":"xxx"});// get the list of tableNames for the schema publiclettables=awaitdb.listAllTables();console.log(tables);// get table definitionsconsttableData=awaitPromise.all(tables.map(t=>db.defineTable(t.table_schema,t.table_name)));// close the reader nowawaitdb.close();// now tableData contains table objects// what you do with them is up to you// here we simply save them as filesconstfilePromises=tableData.map(tableObject=>fs.writeFile(`output/${tableObject.table_schema}.${tableObject.table_name}.json`,JSON.stringify(tableObject,null,4)));awaitPromise.all(filePromises);})();

For backwards compatibility, it is still possible to use the old listTables function but it requires you to know what schema you want to use in advance.

// get the list of tableNames for the schema publiclettableNames=awaitdb.listTables('public');

Connecting to postgres

To connect to postgres you need to:

1. Create a database interface by passing 'postgres' as the argument

constdb=table2json('postgresql');

2. Pass connection description object to the open method, this object is consumed by the pg library and should be compatible with its specification

awaitdb.open({"host":"localhost","port":5432,"database":"xxxxx","password":"xxxxx","user":"xxxxx"});

Connecting to sqlserver

To connect to sqlserver you need to:

1. Create a database interface by passing 'sqlserver' as the argument

constdb=table2json('sqlserver');

2. Pass connection description object or a connection string to the open method. The format is identical to the one used when connecting to postgres.

// exampleawaitdb.open({"host": "localhost","port": 1433,"database": "xxxxx","password": "xxxxx","user": "xxxxx"});

Note:

• The sqlserver you connect to should accept TCP/IP connections.