Import from existing SQL database

[dlasusa]

I'm not a DBA and haven't used databases much, just some personal projects/playing. We have databases where I work, and I'd like to play with that data and write queries against it. I think most of our current DBs are PostgreSQL. Since underneath EdgeDB is PostgreSQL (at least that's how I understand it).

Is there a way to:

1. Point EdgeDB to an existing PostgreSQL
2. Or even better import data from the existing PostgreSQL into EdgeDB?

Sorry for the newbie question, but....I'm a noob! :)

Thanks!

[haikyuu]

@dlasusa you'll find a more active community in https://discord.gg/Npm8dTqD so make sure you join and ask your questions there.

As for your question, short answer is no.

Long answer is that you'll need to write a script (for each table) that does that.

Here is an example in js

function iter$__(a) { let v; return a ? ((v = a.toIterable) ? v.call(a) : a) : []; };

/*body*/
// create database x; \c x; drop database edgedb; create database edgedb; \c edgedb;  drop database x;
import { db } from '../../src/services/database'/*$path$*/;
import * as edgedb from 'edgedb'/*$path$*/;
import e, { Scrim } from "../edgeql-js"/*$path$*/;;
const client = edgedb.createClient();;

// let u\Scrim
function escape(_0) {

	if (!(_0)) { return _0 };
	return _0.replace(/[\u202a-\u202e\u2066-\u2069]/g, "");
	// e.set(e.RoleEnum.Admin, e.RoleEnum.Mod)
};

async function run() {

	const users = await db.table('users').select().whereNotNull('created_at');
	// .where(id:'uyb4Vnhd')
	// console.log users
	const eUsers = users.map(function (user) {
		var φ, φ2, φ3, φ4;

		(((φ = user.s), delete user.s, φ));
		(((φ2 = user.v), delete user.v, φ2));
		(((φ3 = user.topics), delete user.topics, φ3));
		(((φ4 = user.links), delete user.links, φ4));
		// if !user.created_at
		// 	user.created_at = '2018-12-20 15:14:21.950669+03'
		user.github_profile = escape(JSON.stringify(user.github_profile));
		user.name = escape(user.name);
		user.bio = escape(user.bio);
		user.location = escape(user.location);
		// console.log(user)
		const roles = user.roles?.map?.(function (_0) { return _0.charAt(0).toUpperCase() + _0.slice(1); }).filter(function (_0) { return _0; }).map(function (_0) { return _0.replace('-', ''); });

		user.roles = roles || [];
		// Object.keys(user).map do
		// 	if user[$1] === null 
		// 		delete user[$1]
		// console.log user.github_scopes
		if (!user.github_scopes) {

			user.github_scopes = [];
		};
		return user;
	});


	// console.log users
	return await client.transaction(async function (tx) {
		var i$φ;

		console.time("queryTime");;
		const query = e.params({ raw_data: e.json }, function (params) {

			return e.for(e.json_array_unpack(params.raw_data), function (item) {

				// const roles = item.roles..map(do e.RoleEnum[$1.charAt(0).toUpperCase() + $1.slice(1)])
				// 	.filter(do $1)
				const user = {
					pg_id: e.cast(e.str, item.id),
					username: e.cast(e.str, item.username),
					name: e.cast(e.str, item.name),
					location: e.cast(e.str, item.location),
					bio: e.cast(e.str, item.bio),
					github_user_id: e.cast(e.str, item.github_user_id),
					github_profile: e.cast(e.json, item.github_profile),
					github_app: e.cast(e.json, item.github_app),
					github_data: e.cast(e.json, item.github_data),
					twitter_handle: e.cast(e.str, item.twitter_handle),
					github_handle: e.cast(e.str, item.github_handle),
					medium_handle: e.cast(e.str, item.medium_handle),
					github_token: e.cast(e.str, item.github_token),
					profile_image: e.cast(e.str, item.profile_image),
					short_bio: e.cast(e.str, item.discord_id),
					discord_id: e.cast(e.str, item.short_bio),
					avatar_image: e.cast(e.str, item.avatar_image),
					is_org: e.cast(e.bool, item.is_org),
					created_at: e.cast(e.datetime, item.created_at),
					email_verified: e.cast(e.datetime, item.email_verified),
					github_scopes: e.cast(e.array(e.str), item.github_scopes),
					roles: e.array_unpack(e.cast(e.array(e.RoleEnum), item.roles))
				};


				// console.log item.github_scopes
				// user.roles = e.set(...roles) if roles
				return e.insert(e.User, user);
			});
		});

		console.log(query.toEdgeQL());
		let perChunk = 50000;
		let r = eUsers.reduce(function (resultArray, item, index) {

			const chunkIndex = Math.floor(index / perChunk);

			if (!resultArray[chunkIndex]) {

				resultArray[chunkIndex] = [];
			};

			resultArray[chunkIndex].push(item);

			return resultArray;
		}, []);

		console.log(r.length);;
		i$φ = 0; for (let chunk of iter$__(r)) {
			let i = i$φ++;
			console.log(i);
			const result = await query.run(tx, { raw_data: JSON.stringify(chunk) });
		};
		return console.timeEnd("queryTime");;

	});
};
run();

[magick93]

As for your question, short answer is no.

What is the difference in a EdgeDb table compared to a Postgres Table?

[1st1]

Under the hood we compile EdgeDB object types to Postgres tables. Properties and links occupy individual columns. In fact if you look at how EdgeDB stores data you'd discover a fairly canonical normal layout.

[dlasusa]

Wow! Thanks so much for such a detailed answer @haikyuu